Protocol Health Dashboard

┌─────────────────────────────────────────────────────────┐
│  DAU    │  WAU    │  Daily Volume  │  Success Rate     │  ← Key numbers
├─────────────────────────────────────────────────────────┤
│                Daily Active Users (Line)                │  ← Primary trend
├─────────────────────────────────────────────────────────┤
│     Transactions (Line)      │   Volume (Line)         │  ← Secondary trends
├─────────────────────────────────────────────────────────┤
│  Top Referrers  │  Countries  │  Retention Cohort      │  ← Breakdowns
└─────────────────────────────────────────────────────────┘

SELECT countDistinct(address) AS dau
FROM events
WHERE type = 'connect'
  AND timestamp >= today()

SELECT countDistinct(address) AS wau
FROM events
WHERE type = 'connect'
  AND timestamp >= now() - INTERVAL 7 DAY

SELECT
  toDate(timestamp) AS date,
  countDistinct(address) AS dau
FROM events
WHERE type = 'connect'
  AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date

SELECT
  referrer,
  countDistinct(address) AS wallets,
  count(*) AS events
FROM events
WHERE timestamp >= now() - INTERVAL 30 DAY
  AND referrer != ''
GROUP BY referrer
ORDER BY wallets DESC
LIMIT 10

SELECT
  location AS country,
  countDistinct(address) AS users,
  count(*) AS transactions
FROM events
WHERE timestamp >= now() - INTERVAL 30 DAY
  AND location != ''
GROUP BY country
ORDER BY users DESC
LIMIT 10

SELECT
  toStartOfWeek(first_seen) AS cohort_week,
  countDistinct(address) AS cohort_size,
  countDistinct(CASE
    WHEN dateDiff('day', first_seen, last_seen) >= 7
    THEN address
  END) AS returned_week_1,
  round(returned_week_1 / cohort_size * 100, 1) AS week_1_retention
FROM (
  SELECT
    address,
    min(timestamp) AS first_seen,
    max(timestamp) AS last_seen
  FROM events
  WHERE type = 'connect'
  GROUP BY address
)
WHERE first_seen >= now() - INTERVAL 8 WEEK
GROUP BY cohort_week
ORDER BY cohort_week

SELECT
  toDate(timestamp) AS date,
  countDistinct(anonymous_id) AS visitors,
  countDistinct(CASE WHEN type = 'connect' THEN address END) AS connected,
  countDistinct(CASE WHEN type = 'transaction' THEN address END) AS transacted,
  round(connected / visitors * 100, 1) AS connect_rate,
  round(transacted / connected * 100, 1) AS transaction_rate
FROM events
WHERE timestamp >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date

SELECT
  toStartOfWeek(timestamp) AS week,
  countDistinct(address) AS wau,
  lagInFrame(wau) OVER (ORDER BY week) AS prev_wau,
  round((wau - prev_wau) / prev_wau * 100, 1) AS wow_growth
FROM events
WHERE type = 'connect'
  AND timestamp >= now() - INTERVAL 8 WEEK
GROUP BY week
ORDER BY week