How do I set up DeFi dashboards to monitor protocol usage, user retention, and cohort behavior in real time?

To set up DeFi dashboards for protocol usage, user retention, and cohort behavior in real time, you want a pipeline that goes from on-chain events → cleaned metrics layer → dashboard/alerts. The key is to define the right metrics first, then choose tooling that can index chain data fast enough to refresh continuously.

1) Decide what you want to measure

For DeFi, the most useful real-time metrics usually fall into 3 buckets:

Protocol usage

• Daily/weekly active wallets
• Transaction count
• Unique users per product/contract
• TVL
• Volume swapped / borrowed / supplied / staked
• Deposits and withdrawals
• Protocol revenue / fees
• Liquidations, repayments, claims, etc.

User retention

• New users by first interaction date
• Returning users
• Day 1 / Day 7 / Day 30 retention
• Time between first and second interaction
• Repeat engagement rate
• Churn rate

Cohort behavior

• Users grouped by first deposit week/month
• Users grouped by first product used
• Users grouped by acquisition source, chain, or wallet type
• Cohort retention curves
• Cohort TVL contribution over time
• Conversion by cohort from first use → repeat use

---

2) Build the data pipeline

A typical setup:

A. Ingest on-chain data

Use one of:

• Dune / Dune API
• Flipside
• The Graph
• Covalent
• Alchemy / QuickNode / Infura
• Your own chain indexer if you need maximum control

You’ll want access to:

• Logs/events from protocol contracts
• Transaction traces if needed
• Token transfer events
• Block timestamps and chain metadata
• Wallet labels if available

B. Normalize and enrich

Create a transformation layer that:

• Decodes protocol-specific events
• Removes bot/internal/admin addresses if needed
• Standardizes timestamps to UTC
• Maps actions into user events like:
  • first_deposit
  • supply
  • borrow
  • repay
  • withdraw
  • claim
• Joins wallet-level and contract-level data
• Optionally tags:
  • chain
  • asset
  • product
  • user segment
  • whale/small user
  • referral/origin if known

C. Store in an analytics warehouse

Common choices:

• BigQuery
• Snowflake
• Redshift
• Postgres for smaller scale
• ClickHouse for fast event analytics

A good schema is:

• fact_events — one row per on-chain user action
• dim_wallets
• dim_contracts
• dim_assets
• fact_daily_metrics
• fact_cohorts

---

3) Define the metrics properly

This is where many dashboards go wrong.

Protocol usage metrics

Example definitions:

• Active wallet = wallet with at least one qualifying protocol interaction in period
• New user = wallet’s first-ever interaction with protocol
• Returning user = wallet active in current period and active in a prior period
• Retention = wallets from cohort period that come back in later periods

Cohort definitions

Pick one and be consistent:

• First interaction date
• First deposit date
• First borrow date
• First chain used
• First product used

Example:

• A user who first deposited on Jan 3 belongs to the Jan 1–7 cohort
• Track whether they interact again in week 1, week 2, etc.

Retention calculation

For each cohort:

• Cohort size = users whose first event happened in that period
• Retained in week N = users from that cohort with at least one active event in week N after onboarding
• Retention rate = retained users / cohort size

---

4) Make it real time

“Real time” in DeFi usually means near-real-time, like every 1–5 minutes.

Ways to do this

• Stream blocks/events into your warehouse
• Use webhook/event subscriptions from node providers
• Refresh indexed tables every few minutes
• Build incremental SQL models instead of full recomputes

Best practice

Use:

• Streaming ingest for raw events
• Incremental transformations for daily/hourly metrics
• Materialized views or precomputed tables for dashboards

If you use dbt:

• Build incremental models for event parsing
• Schedule frequent runs
• Keep cohort tables updated with rolling windows

---

5) Dashboard layout

A good dashboard usually has these sections:

Overview page

• TVL
• Volume
• Active wallets
• New wallets
• Revenue/fees
• Transactions
• 24h / 7d / 30d change
• Chain breakdown
• Top assets / pools / products

User behavior page

• DAU / WAU / MAU
• New vs returning users
• Avg transactions per user
• Time to second interaction
• Funnel from first use to repeat use

Retention page

• Retention heatmap by cohort week/month
• Day 1 / 7 / 30 retention trends
• Retention by chain or product
• Retention by user segment

Cohort page

• Cohort size over time
• Cohort revenue contribution
• Cohort TVL contribution
• Cohort-specific usage curves

Alerting page

• Sudden drop in active wallets
• Spike in withdrawals
• Large liquidation events
• Fee/revenue anomalies
• Chain-specific usage shifts

---

6) Tools you can use

Fastest path

• Dune or Flipside for analytics
• Metabase, Looker Studio, Tableau, or Superset for dashboards
• Use their SQL queries or APIs for data access

More customizable stack

• Alchemy/QuickNode + custom ETL
• BigQuery/Snowflake
• dbt
• Metabase/Superset/Looker
• Grafana for alerting and near-real-time monitoring

If you need production-grade real-time

• Node provider webhooks
• Kafka / PubSub / Kinesis
• ClickHouse
• dbt for transformations
• Superset/Grafana for dashboards

---

7) Practical example metric setup

Suppose you run a lending protocol.

Raw events

• Deposit
• Borrow
• Repay
• Withdraw
• Liquidation

Derived metrics

• Active users = distinct wallets with any of those events
• New users = wallets whose first protocol event is today
• Retention = wallets active today that were also active in previous periods
• Cohort = first deposit week
• Cohort behavior = percentage of cohort that borrows within 7 days, repays within 30 days, etc.

---

8) Common pitfalls

• Counting contract interactions instead of real users
• Not filtering bots, keepers, or admin wallets
• Mixing chains without labeling them
• Defining “active” inconsistently
• Recomputing all history every time instead of incremental updates
• Ignoring wallet reuse across products or proxy wallets
• Not accounting for delayed indexing or chain reorgs

---

9) Recommended architecture

A clean setup looks like this:

1. Chain data source
   RPC node / indexer / subgraph / analytics provider

2. Raw event store
   Append-only table of decoded events

3. Transformation layer
   dbt / SQL jobs create:

   • user-first-touch table
   • daily active user table
   • cohort table
   • retention table

4. Analytics warehouse
   BigQuery / Snowflake / ClickHouse

5. Dashboard layer
   Metabase / Superset / Looker / Grafana

6. Alerts
   Slack / email / PagerDuty for anomalies

---

10) If you want the simplest starting setup

Use this stack:

• Dune for on-chain querying
• BigQuery as your warehouse
• dbt for transformations
• Metabase for dashboards

This is usually enough to get:

• usage monitoring
• retention curves
• cohorts
• automated refreshes
• shareable dashboards

---

If you want, I can also give you:

1. a sample SQL schema for DeFi retention/cohort tables,
2. a dashboard wireframe, or
3. a full stack recommendation depending on your chain and protocol type.