Retained activity and revenue

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.

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.

How to interpret the results

1Choose an activation milestone that helps predict whether people will keep using your product.
2Give every cohort the same return window before comparing retention.
3A correlation between activity and revenue does not prove that activity increased revenue.

Keep the result

Run this query on your own events

Create a free workspace only when you are ready to save the query, connect real events, and monitor the result. No credit card is required.

Save with my events

Connect retained product activity to revenue context

Connect retained product activity to revenue context

Recurring revenue with retained product activity

How to read the query

How to interpret the results

Related lessons

Run this query on your own events