Business Intelligence for Web3 and Web3 On-Chain User Insights: Turning Indexed Ethereum Data Into Dashboards

Summary: This guide shows decision‑makers how to turn raw Ethereum and L2 activity into executive‑ready dashboards using modern indexers, subgraphs, and data warehouses—complete with working queries, schemas, and 2026 best practices. It reflects post‑Dencun realities (blobs, L2 costs/throughput) and the rise of account‑abstraction analytics.

---

Why on‑chain BI looks different in 2026

• Dencun (activated on March 13, 2024) introduced EIP‑4844 “blobs,” a separate fee market and short‑lived (≈18 days) data channel that slashed L2 costs and pushed usage to rollups—massively changing data shapes and cost curves for analytics. (investopedia.com)
• Each blob is ~128 KB; blocks target 3 blobs and cap at 6, with blob fees tracked via new header fields (blob_gas_used, excess_blob_gas). Blob payloads aren’t accessible to the EVM and are pruned after ~18 days, so BI teams must source blob content from archivers if needed. (eips.ethereum.org)
• L2s became the default execution layer for retail and mid‑frequency DeFi/social activity; fees dropped by up to ~90–99% on major rollups, enabling dashboards to segment users at far finer granularity. (investopedia.com)

Implication: Your BI stack must speak “blobs,” handle L2 log volumes, and stitch together identity across chains and abstractions (e.g., ERC‑4337).

---

The reference Web3 BI stack

• Data capture
  • Execution clients or managed RPCs (good: Reth, Erigon; or providers with log indexing). (paradigm.xyz)
  • Event/log retrieval with pagination and provider‑specific range caps (e.g., eth_getLogs limited by plan/chain). (alchemy.com)
  • Blob content via archivers (Blockscout Blobs, Blobscan) because beacon nodes prune after ~18 days. (blog.blockscout.com)
• Indexing
  • Subgraphs on The Graph Network (now on Arbitrum One), or equivalent hosted indexers. (thegraph.com)
• Warehousing
  • BigQuery public crypto datasets (“crypto_ethereum” and others) and your own curated marts. (cloud.google.com)
• Modeling
  • Community‑vetted transformations (Dune Spellbook/dbt) as a blueprint for your dbt models. (github.com)
• Visualization
  • Metabase/Looker Studio/Tableau; Dune API for embeddable charts; or custom web apps. (metabase.com)

---

Capture and index: precise 2026 guidance

1) Choose your node/indexer footprint

• Reth 1.0 is production‑ready and fast for RPC, indexing, and simulations; its staged‑sync and revm stack deliver low‑latency reads. Use it when you need local control over RPC behavior and historical traces. (paradigm.xyz)
• Erigon 3 documents practical disk profiles: mainnet full ~920 GB vs archive ~1.77 TB (Sep 2025 figures). Plan NVMe and RAM accordingly; prefer prune modes for analytics that don’t need historical state proofs. (docs.erigon.tech)

Tip: Fan out reads by chain (mainnet, Base, Arbitrum, OP Mainnet), and isolate heavy log scans on dedicated endpoints to avoid throttling.

2) Respect eth_getLogs realities

• Provider APIs cap block ranges/bytes to protect infra; design a paginator that chunks by block and topic. Example: Alchemy caps vary by plan; Chainstack recommends 5k–10k block windows. (alchemy.com)

Pseudo‑code pagination (JS):

async function* paginatedLogs(provider, baseFilter, from, to, step=5000) {
  for (let start = from; start <= to; start += step) {
    const end = Math.min(start + step - 1, to);
    const logs = await provider.send('eth_getLogs', [{
      ...baseFilter,
      fromBlock: '0x' + start.toString(16),
      toBlock:   '0x' + end.toString(16)
    }]);
    yield logs;
  }
}

3) Handle blob data

• EIP‑4844 adds BLOBHASH opcode and separate blob fee market; blob contents are propagated as “sidecars” on the consensus layer and pruned after ~18 days. For long‑lived analytics, archive via Blockscout (Blobscout) or Blobscan. (eips.ethereum.org)

---

Subgraphs: faster product KPIs without running ETL

• The Graph Network migrated to Arbitrum One; all new subgraphs and rewards are on L2. Teams get 100k free monthly queries, then pay ~$4 per additional 100k queries. Use the Arbitrum gateway in production. (thegraph.com)

Example GraphQL (sales by week from a marketplace subgraph):

{
  sales(first: 1000, orderBy: timestamp, orderDirection: desc) {
    id
    buyer
    seller
    priceETH
    timestamp
  }
}

Operational notes:

• Curate your subgraph with a small GRT stake to attract indexers and reduce cold‑start latency. (docs.arbitrum.io)
• Version schemas to evolve KPIs without breaking dashboards.

---

Warehousing with Google BigQuery

• Use bigquery-public-data.crypto_ethereum (blocks, transactions, logs, traces, token_transfers, balances), operated via the Blockchain ETL pipeline. It’s ideal for historical trend analysis and joining to off‑chain CRM data. (cloud.google.com)

Practical queries you can run today

1. First‑touch vs repeat‑use by contract (daily)

-- New vs returning addresses interacting with a contract
WITH tx AS (
  SELECT DATE(block_timestamp) d, from_address
  FROM `bigquery-public-data.crypto_ethereum.transactions`
  WHERE to_address = '0xYourAppContract'
),
first_seen AS (
  SELECT from_address, MIN(d) AS first_day
  FROM tx GROUP BY from_address
)
SELECT
  t.d,
  COUNTIF(t.d = f.first_day) AS new_users,
  COUNTIF(t.d > f.first_day) AS returning_users
FROM tx t
JOIN first_seen f USING(from_address)
GROUP BY t.d
ORDER BY t.d DESC;

1. ERC‑20 holder tiers for a token (whales, mid, retail)

-- Snapshot balances from token_transfers double-entry book
WITH debits AS (
  SELECT to_address AS holder, SAFE_CAST(value AS NUMERIC) AS amt
  FROM `bigquery-public-data.crypto_ethereum.token_transfers`
  WHERE token_address = '0xToken'
), credits AS (
  SELECT from_address AS holder, -SAFE_CAST(value AS NUMERIC) AS amt
  FROM `bigquery-public-data.crypto_ethereum.token_transfers`
  WHERE token_address = '0xToken'
),
balances AS (
  SELECT holder, SUM(amt) AS token_raw
  FROM (SELECT * FROM debits UNION ALL SELECT * FROM credits)
  GROUP BY holder
)
SELECT
  CASE
    WHEN token_raw >= 1e9 THEN 'whale'
    WHEN token_raw >= 1e7 THEN 'mid'
    ELSE 'retail'
  END AS tier,
  COUNT(*) holders,
  SUM(token_raw) total_raw
FROM balances
GROUP BY tier
ORDER BY total_raw DESC;

1. Cohorts by first week and 8‑week retention

WITH actions AS (
  SELECT DATE_TRUNC(DATE(block_timestamp), WEEK(MONDAY)) AS week,
         from_address
  FROM `bigquery-public-data.crypto_ethereum.transactions`
  WHERE to_address = '0xYourAppContract'
), cohorts AS (
  SELECT from_address, MIN(week) AS cohort
  FROM actions GROUP BY from_address
), joined AS (
  SELECT a.week, c.cohort, COUNT(DISTINCT a.from_address) AS actives
  FROM actions a JOIN cohorts c USING(from_address)
  WHERE a.week BETWEEN c.cohort AND DATE_ADD(c.cohort, INTERVAL 8 WEEK)
  GROUP BY a.week, c.cohort
)
SELECT cohort, week, actives,
       DATE_DIFF(week, cohort, WEEK) AS wk_num
FROM joined
ORDER BY cohort DESC, week ASC;

Governance:

• Use INFORMATION_SCHEMA and partition filters to control costs; the first 1TB/month is free under standard BigQuery on‑demand pricing. (cloud.google.com)

---

Dune for rapid analysis and embedding

• Dune’s Spellbook (dbt) normalizes common data (dex.trades, nft.sales, token transfers) and is the fastest path to a stakeholder‑readable dashboard without managing infra. Treat it as a reference when designing your own marts. (github.com)
• The Dune API lets you execute saved queries and embed results in apps or internal portals (e.g., quarterly KPI decks). (docs.dune.com)

Example Dune‑style SQL (conceptual; adapt to your Dune schema):

SELECT
  DATE_TRUNC(block_time, day) AS day,
  COUNT(DISTINCT trader) AS dau_traders,
  SUM(usd_amount) AS volume_usd
FROM dex.trades
WHERE blockchain = 'ethereum' AND project = 'uniswap_v3'
GROUP BY day
ORDER BY day DESC;

---

Analytics for account abstraction (ERC‑4337)

Why it matters: Smart accounts are mainstreaming in wallets and apps; you’ll want KPIs for sponsored transactions, failed validations, paymaster usage, and conversion.

• EntryPoint versions: v0.6 (0x5FF1…) and v0.7 (0x000…7032) are broadly supported; new deployments use v0.7. Ensure your event decoders and parsers handle the PackedUserOperation fields and v0.7 changes. (alchemy.com)
• Key event: UserOperationEvent(sender, userOpHash, paymaster, nonce, success, actualGasCost, actualGasUsed). Track it to attribute gas sponsorship and UX wins. (hackmd.io)
• Bundler RPC (ERC‑7769) exposes eth_sendUserOperation/eth_estimateUserOperationGas; log failures (AAxx) for funnel diagnostics. (eips.ethereum.org)

Example: paymaster ROI (BigQuery over decoded logs you ingest)

-- Assuming you ETL EntryPoint UserOperationEvent into bq.dataset.aa_events
SELECT
  DATE(block_time) AS day,
  paymaster,
  COUNTIF(success) AS sponsored_succ,
  SUM(actual_gas_cost)/1e18 AS total_eth_sponsored
FROM `your_project.bq.aa_events`
GROUP BY day, paymaster
ORDER BY day DESC, total_eth_sponsored DESC;

Tip: If you don’t want to run your own AA ETL, stand up a dedicated subgraph for EntryPoint v0.7 and query events via The Graph gateway on Arbitrum. (docs.arbitrum.io)

---

L2s after blobs: what to track

• Cost and throughput: Post‑Dencun, L2 fees dropped materially and usage climbed; Base, Arbitrum, and others reported spikes in transactions and users. Make “L2‑native DAUs,” “cross‑L2 repeats,” and “unit economics per L2” first‑class metrics. (investopedia.com)
• Tech notes for BI:
  • Blob fees have their own base‑fee curve; you’ll see differences between calldata‑heavy vs blob‑heavy periods in settlement costs. (eips.ethereum.org)
  • Some rollups publish to non‑Ethereum DA layers (e.g., EigenDA); note the DA provider when benchmarking cost and data availability SLAs (e.g., Celo migrated to Ethereum L2 using EigenDA in Mar 2025). (l2beat.com)

---

Enriching user insights beyond transfers

• MEV telemetry: Join Flashbots MEV‑Share public data in BigQuery (eden-data-public.flashbots.mev_share) to measure how much value users recapture via private flow/backs and how many swaps use Protect routes. (docs.edennetwork.io)
• Attestations (EAS): Use on‑chain attestations as privacy‑preserving traits (e.g., “KYCed,” “beta‑tester,” “power user”) that you can segment in dashboards without handling PII; EAS has mainnet and L2 deployments and an SDK. (attest.org)

---

Put it on a dashboard (in hours, not weeks)

• Hook BigQuery to Metabase (service account JSON). You’ll get point‑and‑click slices on top of your SQL models and embeddable dashboards. (metabase.com)
• For product‑facing widgets, call Dune’s API to hydrate charts in your marketing site or investor portal while your own warehouse matures. (docs.dune.com)

---

Emerging best practices we recommend to clients

1. Model for longevity in a blob world

   • Keep settlement metrics (blob_gas_used, excess_blob_gas where available) in fact tables, and archive blob bodies via a provider (Blockscout Blobs, Blobscan) for any use‑case that needs payload decoding later. (blog.blockscout.com)

2. Choose the right indexer for the job

   • Subgraphs for product KPIs; BigQuery for cross‑chain joins and history; Reth/Erigon when you need custom traces/latency headroom. (paradigm.xyz)

3. Engineer for log limits and scale

   • Always paginate logs and topic‑filter aggressively; cache decoded ABIs; pre‑materialize “hot” tables (e.g., daily active addresses). Provider caps are real. (alchemy.com)

4. Treat AA as a first‑class funnel

   • Track v0.6 vs v0.7 wallets; attribute sponsored ops to paymasters; alert on AAxx validation failure spikes; read the bundler error taxonomy (ERC‑7769). (alchemy.com)

5. Standardize metrics across L2s

   • Same address can exist across EVM L2s; use chain_id in every dimension key and normalize bridging paths when attributing multi‑chain cohorts.

6. Privacy by design

   • Favor attestations and opt‑in identity (SIWE + EAS) over PII. It simplifies compliance and speeds security reviews. (attest.org)

---

Example: a minimal end‑to‑end pipeline for a DeFi app

• Ingest
  • Use a provider with indexed logs; backfill past 2 years via paginated eth_getLogs by topic (Transfer, Swap). (alchemy.com)
• Index
  • Publish a subgraph for app contracts; curate with a small GRT stake to ensure indexer availability. (docs.arbitrum.io)
• Warehouse
  • Land raw logs and token_transfers in BigQuery; build dbt models mirroring Dune Spellbook patterns for trades and token balances. (github.com)
• Enrich
  • Join EAS attestations to tag “verified/trusted” cohorts; join MEV‑Share tables to quantify Protect usage. (attest.org)
• Visualize
  • Publish Metabase dashboards for DAU, retention, swap conversion, sponsor rate (% AA ops), and L2 mix.

---

30/60/90 with 7Block Labs

• 30 days: Provision RPC/indexers, deploy subgraphs, land base tables (tx, logs, transfers) in BigQuery, ship MVP dashboards for DAU, retention, revenue.
• 60 days: Add AA analytics (EntryPoint v0.7), MEV/Protect joins, blob cost metrics, L2 expansion (Base/Arbitrum).
• 90 days: Identity/model hardening (EAS traits), alerting on KPI regressions, embed Dune/Metabase charts in exec reporting, cost governance for warehouse.

---

Key technical references

• EIP‑4844 blobs: type‑3 tx, blob fee market, 3‑target/6‑max blobs per block; ~18‑day retention at the consensus layer. (eips.ethereum.org)
• Dencun deployment and L2 fee impact. (investopedia.com)
• The Graph on Arbitrum One; pricing and gateway. (thegraph.com)
• BigQuery public Ethereum dataset and architecture. (cloud.google.com)
• Dune Spellbook (dbt models). (github.com)
• Reth 1.0 production readiness; Erigon 3 hardware profiles. (paradigm.xyz)
• eth_getLogs range best practices. (alchemy.com)
• AA analytics: EntryPoint v0.7 address; events; bundler RPC (ERC‑7769). (alchemy.com)
• Blob archiving (Blockscout Blobs; Blobscan). (blog.blockscout.com)

---

If you want us to turn your protocol’s raw chain data into board‑ready BI in 4–6 weeks, 7Block Labs can run this playbook end‑to‑end—indexers, models, dashboards, and executive narratives included.