Vivek Kumar

Posted on Jun 21

Product Analytics with SQL: Tracking What Actually Matters

#webdev #analytics #sql #database

Most product teams have the opposite of an analytics problem. They have too many metrics. Page views, session counts, click-through rates, bounce rates — dashboards full of numbers that don't clearly connect to whether the product is actually working.

SQL changes this. When you query your own event data directly, you stop asking "what does the tool show me?" and start asking "what do I actually need to know?" Tools like Draxlr let you run these queries as live dashboards your team can revisit without re-running anything manually. That shift in framing is worth more than any analytics SaaS subscription.

This article covers the SQL queries that genuinely move the needle: active users, conversion funnels, cohort retention, and feature adoption. All with realistic table structures you can adapt to your own schema.

Your Starting Schema

Most product databases have some version of these three tables. The queries below assume this structure:

-- Who signed up and when
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  email       TEXT,
  plan        TEXT,        -- 'free', 'pro', 'enterprise'
  created_at  TIMESTAMPTZ
);

-- Every meaningful action a user takes
CREATE TABLE events (
  id          SERIAL PRIMARY KEY,
  user_id     INT REFERENCES users(id),
  event_name  TEXT,        -- 'signed_up', 'invited_team', 'created_dashboard', etc.
  properties  JSONB,
  occurred_at TIMESTAMPTZ
);

-- Revenue events
CREATE TABLE subscriptions (
  id            SERIAL PRIMARY KEY,
  user_id       INT REFERENCES users(id),
  plan          TEXT,
  started_at    TIMESTAMPTZ,
  cancelled_at  TIMESTAMPTZ
);

If your schema looks different, the patterns still apply — you'll just swap column names.

1. Daily and Monthly Active Users (DAU / MAU)

The most fundamental health metric. An "active" user is one who triggered at least one event in the time window.

-- Daily Active Users for the last 30 days
SELECT
  DATE_TRUNC('day', occurred_at)::DATE AS day,
  COUNT(DISTINCT user_id)              AS dau
FROM events
WHERE occurred_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

-- Monthly Active Users, last 6 months
SELECT
  DATE_TRUNC('month', occurred_at)::DATE AS month,
  COUNT(DISTINCT user_id)                AS mau
FROM events
WHERE occurred_at >= NOW() - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

DAU/MAU ratio is a sticky-product signal. A ratio above 0.2 (20% of monthly users are active on any given day) is generally considered healthy for a SaaS product.

-- DAU/MAU ratio for current month
WITH dau AS (
  SELECT
    DATE_TRUNC('day', occurred_at)::DATE AS day,
    COUNT(DISTINCT user_id) AS daily_users
  FROM events
  WHERE occurred_at >= DATE_TRUNC('month', NOW())
  GROUP BY 1
),
mau AS (
  SELECT COUNT(DISTINCT user_id) AS monthly_users
  FROM events
  WHERE occurred_at >= DATE_TRUNC('month', NOW())
)
SELECT
  AVG(daily_users)::NUMERIC(5,2)                    AS avg_dau,
  mau.monthly_users,
  (AVG(daily_users) / mau.monthly_users)::NUMERIC(4,3) AS dau_mau_ratio
FROM dau, mau
GROUP BY mau.monthly_users;

2. Conversion Funnel

Funnels answer: "how many users make it from step A to step B?" The classic case is signup-to-activation.

-- Signup → first dashboard created → invited a team member
WITH step1 AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name = 'signed_up'
    AND occurred_at >= NOW() - INTERVAL '90 days'
),
step2 AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN step1 s ON e.user_id = s.user_id
  WHERE e.event_name = 'created_dashboard'
    AND e.occurred_at > (
      SELECT MIN(occurred_at) FROM events
      WHERE user_id = e.user_id AND event_name = 'signed_up'
    )
),
step3 AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN step2 s ON e.user_id = s.user_id
  WHERE e.event_name = 'invited_team_member'
)
SELECT
  COUNT(DISTINCT step1.user_id)                              AS signed_up,
  COUNT(DISTINCT step2.user_id)                              AS created_dashboard,
  COUNT(DISTINCT step3.user_id)                              AS invited_team,
  ROUND(COUNT(DISTINCT step2.user_id)::NUMERIC /
        NULLIF(COUNT(DISTINCT step1.user_id), 0) * 100, 1)  AS pct_to_step2,
  ROUND(COUNT(DISTINCT step3.user_id)::NUMERIC /
        NULLIF(COUNT(DISTINCT step2.user_id), 0) * 100, 1)  AS pct_to_step3
FROM step1
LEFT JOIN step2 ON TRUE
LEFT JOIN step3 ON TRUE;

Example output:

signed_up	created_dashboard	invited_team	pct_to_step2	pct_to_step3
1200	684	291	57.0%	42.5%

That 57% to first dashboard tells you: four in ten users who sign up never even create a dashboard. That's your biggest lever, and you wouldn't have known it from page view analytics.

3. Cohort Retention

Funnel analysis shows a snapshot. Cohort retention shows whether you're getting better over time. You group users by signup month, then track what percentage return each month after.

WITH cohorts AS (
  -- Month each user signed up
  SELECT
    user_id,
    DATE_TRUNC('month', created_at) AS cohort_month
  FROM users
),
activity AS (
  -- Every month each user was active
  SELECT
    user_id,
    DATE_TRUNC('month', occurred_at) AS active_month
  FROM events
  GROUP BY 1, 2
),
retention AS (
  SELECT
    c.cohort_month,
    EXTRACT(EPOCH FROM (a.active_month - c.cohort_month)) / 2592000 AS months_since_signup,
    COUNT(DISTINCT c.user_id) AS retained_users
  FROM cohorts c
  JOIN activity a ON c.user_id = a.user_id
  GROUP BY 1, 2
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY 1
)
SELECT
  r.cohort_month,
  r.months_since_signup::INT AS month_n,
  r.retained_users,
  cs.cohort_size,
  ROUND(r.retained_users::NUMERIC / cs.cohort_size * 100, 1) AS retention_pct
FROM retention r
JOIN cohort_sizes cs ON r.cohort_month = cs.cohort_month
WHERE r.months_since_signup >= 0
ORDER BY 1, 2;

Run this and you get a cohort table. Month 0 is always 100% (everyone is "active" the month they signed up). Month 1 is typically where you see the steepest drop. If you see month-3 retention improving across successive cohorts, your product changes are working.

4. Feature Adoption

Not all events are equal. The ones that correlate with retention and revenue are your "power features." Here's how to find which features your most-retained users actually use:

-- Feature usage rate among users still active after 60 days
WITH retained_users AS (
  SELECT DISTINCT u.id AS user_id
  FROM users u
  JOIN events e ON u.id = e.user_id
  WHERE u.created_at <= NOW() - INTERVAL '60 days'
    AND e.occurred_at >= u.created_at + INTERVAL '60 days'
),
all_new_users AS (
  SELECT id AS user_id
  FROM users
  WHERE created_at <= NOW() - INTERVAL '60 days'
)
SELECT
  e.event_name,
  COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN e.user_id END) AS retained_users_used,
  COUNT(DISTINCT a.user_id)                                          AS total_cohort,
  COUNT(DISTINCT e.user_id)                                          AS total_users_used,
  ROUND(
    COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN e.user_id END)::NUMERIC /
    NULLIF(COUNT(DISTINCT e.user_id), 0) * 100, 1
  ) AS adoption_among_retained_pct
FROM events e
JOIN all_new_users a ON e.user_id = a.user_id
LEFT JOIN retained_users r ON e.user_id = r.user_id
WHERE e.event_name NOT IN ('signed_up', 'logged_in', 'page_viewed')
GROUP BY 1
ORDER BY adoption_among_retained_pct DESC
LIMIT 20;

Features where adoption_among_retained_pct is significantly higher than the overall adoption rate are your stickiest features — the ones worth investing in and putting in front of new users faster.

5. Time-to-Activation

How long does it take a new user to reach your "aha moment"? Shorter is almost always better.

-- Average and median time from signup to first key action
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (first_action.occurred_at - u.created_at)) / 3600
  ) AS median_hours_to_activation,
  AVG(
    EXTRACT(EPOCH FROM (first_action.occurred_at - u.created_at)) / 3600
  )::NUMERIC(8,1)         AS avg_hours_to_activation,
  COUNT(*)                AS activated_users
FROM users u
JOIN LATERAL (
  SELECT MIN(occurred_at) AS occurred_at
  FROM events
  WHERE user_id = u.id
    AND event_name = 'created_dashboard'
) first_action ON first_action.occurred_at IS NOT NULL
WHERE u.created_at >= NOW() - INTERVAL '90 days';

If median time-to-activation is 4 days but you have a 7-day trial, that's a problem hiding in plain sight.

Common Mistakes and Gotchas

Counting events instead of users. COUNT(*) on an events table counts actions, not people. Use COUNT(DISTINCT user_id) whenever you're measuring engagement or adoption.

Off-by-one in funnel ordering. A funnel step should only count if the user did it after the previous step — not just at any point in history. The subquery pattern in the funnel example above handles this, but hand-rolled funnels often miss it.

Using NOW() in cohort queries. The current month's cohort is always incomplete. Filter to only full months (e.g., < DATE_TRUNC('month', NOW())) to avoid misleading drop-offs in your retention chart.

Conflating activity with value. A logged_in event proves presence, not engagement. Filter out noise events from your feature adoption analysis or you'll see "logging in" as your top retained-user behavior.

Not accounting for timezone. DATE_TRUNC('day', occurred_at) on a TIMESTAMPTZ column uses UTC. If your users are in New York or London, a Monday event might land in Sunday's bucket. Cast to your local timezone: DATE_TRUNC('day', occurred_at AT TIME ZONE 'America/New_York').

Key Takeaways

The five queries above cover the vast majority of product analytics questions a team will actually act on:

DAU/MAU tells you whether the product is used habitually
Funnels show you where users drop off on the path to value
Cohort retention shows whether you're improving month over month
Feature adoption points to what actually causes users to stick
Time-to-activation reveals friction in your onboarding

Start with your real event table, pick one query, and run it. The numbers will immediately suggest the next question. That's how SQL-driven product analytics works — not a dashboard someone built once, but a direct line to your data.

What's Your Approach?

Do you run analytics directly in SQL or pipe events to a warehouse first? Are there queries you've found indispensable that aren't on this list? Drop them in the comments — always curious what patterns other teams rely on.

If you're looking for a way to turn these queries into live dashboards without writing frontend code, tools like Draxlr let you connect your database and embed these charts directly in your app.

DEV Community