Connect retained product activity to revenue context

RevenueAdvanced

Connect retained product activity to revenue context

Which plans activate, return after seven days, and contribute recurring revenue?

Reduce product events to one account-level lifecycle before joining recurring revenue and collected billing outcomes.

Read-only SQL

WITH account_activity AS (
  SELECT
    a.account_id,
    a.plan,
    a.monthly_revenue_usd,
    MAX(CASE
      WHEN p.event_name = 'dashboard_created' AND p.status = 'success'
      THEN 1 ELSE 0
    END) AS activated,
    MAX(CASE
      WHEN p.timestamp_utc >= a.signup_at + INTERVAL '7 days'
        AND p.status = 'success'
      THEN 1 ELSE 0
    END) AS returned_after_7d
  FROM accounts AS a
  LEFT JOIN product_events AS p ON p.account_id = a.account_id
  GROUP BY a.account_id, a.plan, a.monthly_revenue_usd
),
collections AS (
  SELECT
    account_id,
    SUM(CASE
      WHEN event_name = 'invoice_paid' AND status = 'success'
      THEN amount_usd ELSE 0
    END) AS collected_usd
  FROM billing_events
  GROUP BY account_id
)
SELECT
  activity.plan,
  COUNT(*) AS accounts,
  SUM(activity.activated) AS activated_accounts,
  SUM(activity.returned_after_7d) AS returned_accounts,
  SUM(activity.monthly_revenue_usd) AS recurring_revenue_usd,
  SUM(COALESCE(collections.collected_usd, 0)) AS collected_usd
FROM account_activity AS activity
LEFT JOIN collections ON collections.account_id = activity.account_id
GROUP BY activity.plan
ORDER BY recurring_revenue_usd DESC, activity.plan;

The dataset and query remain in this browser.

Published expected result

Recurring revenue with retained product activity

Chart value

growth

4,200 USD

business

1,200 USD

starter

498 USD

free

0 USD

plan	accounts	activated_accounts	returned_accounts	recurring_revenue_usd	collected_usd
growth	2	2	2	4,200	4,200
business	1	1	0	1,200	1,200
starter	2	0	0	498	0
free	1	0	0	0	0

How to read the query

Product rows are collapsed to activation and return flags before revenue is introduced.
The seven-day return test is anchored to each account's signup time rather than a global calendar week.
Recurring revenue and collected invoice value stay separate because a contract value is not proof of payment.

Decisions the SQL cannot make

1Choose a durable activation milestone that predicts value for this product.
2Give every cohort the same return window before comparing retention.
3Do not present correlation between activity and revenue as causal lift.

Continue from this lesson

Cohort retention recipe Net and gross retention recipe Product journey guide