Cohort Analysis of DeFi Users on Dune: SQL Deep Dive

By DeFi Intel Research · Reviewed 2026-07-02 · 7 min read · advanced

Cohort analysis of DeFi users on Dune SQL is the definitive method for moving beyond vanity metrics and uncovering the real behavioral dynamics of onchain protocols. By grouping users by the week they first minted, supplied liquidity, or borrowed, you can map retention curves, identify power users, and benchmark protocol health against competitors.

This guide goes beyond basic definitions. We write production-grade Dune queries using Uniswap v3 and Aave v2 data, explain how to handle wallet-to-user aggregation, and show how to calculate cohort-based LTV. You’ll learn to segment high‑value users, avoid common pitfalls like exchange wallet contamination, and build a retention matrix that any DeFi analyst can run on live data.

Why Cohort Analysis Matters for DeFi Protocols

In DeFi, total value locked (TVL) fluctuates with token prices, making it a poor measure of user engagement. A cohort analysis of DeFi users reveals whether your protocol is building a sticky base or bleeding users after yield events. For example, a liquidity provider might deposit once and never return; cohort retention tells you the fraction that comes back in week 2, week 4, etc.

Real protocols like Uniswap, Aave, and Compound use onchain data to track cohorts. Dune Analytics provides the SQL interface to slice this data natively. By querying uniswap_v3_ethereum.trades or aave_v2_ethereum.borrow, you can define a cohort by the first block_time of a user action and then count subsequent events per relative week.

• Retention – weekly or monthly active users from a given first-time bucket.
• Churn – users who never return after a certain period.
• Power users – the top decile by number of transactions or volume, often responsible for 60–80% of activity.

Setting Up the Cohorts: Defining First Action Date

The foundation is a query that gets each user’s first interaction with a smart contract. In DeFi, a “user” is typically an Ethereum address, but you may want to aggregate across addresses owned by the same entity using labels from Dune’s labels.addresses or third‑party tags (e.g., Nansen). For this guide, we assume one address = one user.

Using the Uniswap v3 trades table:

This common table expression (CTE) establishes the cohort date. For lending protocols like Aave, use MIN(mint_block_time) for depositors or MIN(borrow_block_time) for borrowers. Always filter to a known token pair or asset to avoid noise from dust attacks.

Tip: For protocols with multiple actions (swap, supply, borrow), choose the first value‑bearing action. A single sweep of 0 ETH should not define a cohort.

Writing the Retention Query on Dune SQL

Once you have cohort dates, calculate retention by counting user actions in each relative week. Truncate timestamps to weeks and compute each activity week’s offset from the cohort week with DATE_DIFF.

Example query skeleton:

WITH cohort AS (
  SELECT taker AS user, DATE_TRUNC('week', MIN(block_time)) AS cohort_week
  FROM uniswap_v3_ethereum.trades
  WHERE block_time >= '2023-01-01' AND pool = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
  GROUP BY taker
),
weekly_activity AS (
  SELECT taker AS user, DATE_TRUNC('week', block_time) AS activity_week
  FROM uniswap_v3_ethereum.trades
  WHERE block_time >= '2023-01-01'
)
SELECT c.cohort_week,
       DATE_DIFF('week', c.cohort_week, a.activity_week) AS week_offset,
       COUNT(DISTINCT a.user) AS active_users
FROM cohort c
LEFT JOIN weekly_activity a ON c.user = a.user
GROUP BY 1, 2
ORDER BY 1, 2

The week offset measures period 0 (first week), period 1, etc. The result is a retention matrix. To calculate retention percentage, divide each period’s active users by the total users in that cohort.

Identifying Power Users with Cohort Deciles

Power users drive the network effect. Use cohort analysis on Dune to segment addresses by transaction count or volume within the first 30 days. Then track whether those top decile users sustain activity longer.

Query approach: build a per‑user metric (e.g., sum of amount USD in first 30 days), assign a decile rank using NTILE(10) OVER (ORDER BY volume DESC), then compute retention by decile cohort. You’ll typically find that the top decile retains far better than the bottom decile, and that lending markets like Aave v2 concentrate most borrow volume among a small share of addresses — run the decile query on live data to measure the exact split for your protocol.

• Protocols: Compound (cETH, cDAI) shows similar skews.
• Application: Identify which user segment responds to incentives (e.g., liquidity mining boosts bottom decile but not top).

This insight helps set fee structures and loyalty programs. A Dune dashboard can refresh daily to monitor the power‑user base.

Comparison Table: Cohort Retention Across DeFi Verticals

Percentages are illustrative, not current live figures. The table illustrates that yield aggregators tend to retain better because of auto‑compounding, while derivatives lose traders quickly. Use Dune to compute your own protocol’s specific numbers.

Handling Cross‑chain Users and Bridge Activity

Many DeFi users operate across Ethereum, Arbitrum, Optimism, and Polygon. A cohort analysis limited to one chain may show low retention if users simply bridge to another chain. Because EVM chains share the same address format, you can match the same wallet address across chains and use bridge transaction data (e.g., from LayerZero, Stargate) to link activity.

To build a multi‑chain cohort, union tables from each chain’s Dune dataset and define the first action anywhere. Then track activity chain‑by‑chain. For example, a user’s first interaction might be on Ethereum (Uniswap), then they move to Arbitrum (GMX). Your churn calculation would be wrong if you only look at Ethereum.

Practical approach: use Dune’s per‑chain raw tables such as ethereum.logs, or join per‑chain tables with a common tag. DuneSQL (Dune’s Trino‑based engine) lets you query tables from multiple chains in a single query. Example: SELECT * FROM ethereum.logs UNION ALL SELECT * FROM polygon.logs.

Be warned: gas‑less transactions and bridge spam can create false first‑action dates. Filter out zero‑value transactions and contract deployments.

Common Pitfalls in Onchain Cohort Analysis

• Exchange wallet contamination: Centralized exchange deposit addresses (e.g., Coinbase, Binance) appear as frequent users. Exclude them using Dune’s labels.addresses table, filtered to exchange labels.
• Multiple addresses per user: A single person may use many wallets. Unless you have offchain data (email, KYC), accept address‑level analysis as a proxy.
• First action definition: Including fee claims or airdrop sweeps as first action inflates retention (users who only claimed never interact again). Use value‑bearing actions only.
• Lookback bias: Recent cohorts have incomplete history. Only compare cohorts of the same age (e.g., cohorts from 3+ months ago).
• Schema naming errors: Table and column names changed when Dune migrated from its deprecated Spark SQL engine to DuneSQL (Trino‑based). Double‑check schema names before running.

Visualizing the Cohort Retention Matrix on Dune

Dune doesn’t offer native charting for cohort heatmaps, but you can embed the SQL result into a custom dashboard using a pivot table approach. Export the query result as a CSV and import into a Dune chart, or use Dune’s new Python notebooks (beta) with libraries like seaborn.

Alternatively, use Dune’s visualization engine: plot line charts of retention per cohort week. For a heatmap-style view, use a table visualization with conditional color formatting (Dune’s custom CSS feature allows background-color based on value).

Pro tip: Create a parameterized query where users can select the protocol (Uniswap, SushiSwap) and the type of action (swap, add liquidity). Then parameterize the table name and filter. This makes your cohort analysis reusable across different DeFi dApps.

Example dashboard: dune.com/queries/123456 (fictional) — a retention matrix for Uniswap v3 LPs updated daily.

Step-by-step

1. Connect to Dune and create a new query using the DuneSQL engine.
2. Write a CTE to find each address's first value-bearing action (e.g., swap, deposit) as the cohort date.
3. Build a second CTE that records all subsequent actions with their week timestamps.
4. Join the cohort CTE to the activity CTE on user address and calculate weekly offsets using DATE_DIFF.
5. Aggregate by cohort week and offset, counting distinct active users per cell.
6. Filter out known exchange and bridge contract addresses using Dune's labels tables.
7. Order results by cohort week and export to a table visualization with conditional formatting.
8. Save the query and schedule daily refresh for up-to-date retention metrics.

Common mistakes to avoid

• Including contract deployer addresses as users, which skews cohort sizes.
• Using absolute timestamps instead of week offsets, making cross-cohort comparison impossible.
• Forgetting to filter by specific token pairs or pools, leading to noise from dust transactions.
• Assuming one address equals one user without cross-referencing labels or bridge activity.
• Ignoring recent cohorts that have incomplete data, leading to artificially high retention.
• Not removing zero-value or failed transactions that can create phantom first actions.

Frequently asked questions

Related reading

• Blackrock Crypto Strategy Deep Dive
• Onchain Protocol Revenue Analysis
• Bonding Curve
• DEFI
• Aave Complete Guide
• Advanced Concentrated Liquidity Uniswap V4
• Bitcoin Spot ETF Flows Analysis
• Common Mistakes First Time Crypto Users