Compute signup rate and retention from raw logs

Scenario

You are analyzing an A/B test for a marketing campaign offering a free 1-month trial.

You are given raw “upstream” tables that resemble production event logs rather than pre-aggregated metrics.

Assume UTC timestamps and that each user has at most one assignment.

Tables (upstream)

experiment_assignment

• user_id (STRING, PK)
• experiment_id (STRING)
• variant (STRING; 'control' or 'treatment')
• assigned_at (TIMESTAMP)

offer_exposure_events

• user_id (STRING)
• exposed_at (TIMESTAMP)
• campaign_id (STRING)

subscription_events

• user_id (STRING)
• event_time (TIMESTAMP)
• event_type (STRING; one of 'trial_start', 'paid_start', 'cancel')

app_sessions

• user_id (STRING)
• session_start (TIMESTAMP)

Metric definitions

1. Signup rate (trial start within 7 days)

• Denominator: users assigned to the experiment.
• Numerator: users with a trial_start event where event_time is in [assigned_at, assigned_at + 7 days).

2. D30 activity retention Among users who started a trial within 7 days, the user is retained if they have ≥ 1 app session with session_start in [assigned_at + 30 days, assigned_at + 37 days).

Task

Write SQL to output one row per variant with:

• variant
• assigned_users
• signup_users_7d
• signup_rate_7d
• retained_users_d30
• retention_rate_d30

Also briefly describe what an “upstream” dataset/logging pipeline must contain to compute these metrics reliably (e.g., assignment logs, exposure logs, subscription lifecycle events, identity consistency).

A/B test: compute signup rate and 7-day retention from raw event logs

You are analyzing an A/B test for a marketing campaign that offers users a free month trial.

Definitions (use only the tables below):

• The experiment assignment population includes users assigned between 2025-05-01 and 2025-05-31 (inclusive).
• A user is considered "signed up" if they have at least one signup event in event_log between 2025-05-01 and 2025-05-31 (inclusive).
• Signup rate = (# signed up users) / (# assigned users), computed per variant.
• 7-day retention is computed on a signup cohort: users who signed up between 2025-05-01 and 2025-05-24 (inclusive).
• A signed-up user is "7-day retained" if they have at least one app_open event on the calendar date exactly 7 days after their signup date.
• 7-day retention rate = (# 7-day retained users) / (# eligible signed-up users), computed per variant.

Task: Write a SQL query that returns one row per variant with:

• assigned_users
• signed_up_users
• signup_rate
• eligible_signup_users_7d
• retained_users_7d
• retention_rate_7d

Notes:

• If a user has multiple signup events, use the earliest signup date.
• Rates should be returned as decimals rounded to 4 decimal places.

Table experiment_assignment

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
variant	VARCHAR(20)	NOT NULL
assigned_at	DATE	NOT NULL

Sample data:

assigned_at	user_id	variant
2025-05-01	1	control
2025-05-03	2	control
2025-05-10	3	control
2025-05-20	4	control
2025-05-25	5	control
2025-05-30	6	control
2025-05-02	7	treatment
2025-05-05	8	treatment
2025-05-12	9	treatment
2025-05-18	10	treatment
2025-05-22	11	treatment
2025-05-28	12	treatment

Table event_log

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
event_type	VARCHAR(20)	NOT NULL
event_ts	TIMESTAMP	NOT NULL

Sample data:

event_id	event_ts	event_type	user_id
101	2025-05-02 09:15:00	signup	1
102	2025-05-09 08:00:00	app_open	1
103	2025-05-10 10:00:00	app_open	1
104	2025-05-11 11:30:00	signup	3
105	2025-05-18 07:45:00	app_open	3
106	2025-05-21 14:10:00	signup	4
107	2025-05-28 09:00:00	app_open	4
108	2025-05-26 16:20:00	signup	5
109	2025-05-27 09:00:00	app_open	5
110	2025-05-03 10:00:00	signup	7
111	2025-05-10 12:00:00	app_open	7
112	2025-05-06 09:30:00	signup	8
113	2025-05-12 09:00:00	app_open	8
114	2025-05-13 18:00:00	app_open	8
115	2025-05-19 13:00:00	signup	10
116	2025-05-26 08:30:00	app_open	10
117	2025-05-23 20:15:00	signup	11
118	2025-05-24 10:10:00	app_open	11
119	2025-05-29 09:05:00	signup	12
120	2025-05-12 10:00:00	signup	3

Expected output

One row per variant with assignment count, signup count and signup rate for 2025-05-01 to 2025-05-31, plus eligible signup cohort size for 7-day retention (signups through 2025-05-24), retained users (app_open exactly 7 days after signup date), and 7-day retention rate. Rates are rounded to 4 decimals.

assigned_users	eligible_signup_users_7d	retained_users_7d	retention_rate_7d	signed_up_users	signup_rate	variant
6	3	3	1	4	0.6667	control
6	4	3	0.75	5	0.8333	treatment

Hints

1. Deduplicate signups by taking the earliest signup date per user.
2. Compute 7-day retention using date arithmetic: app_open date = signup_date + 7 days.
3. Be careful to restrict the retention cohort to signups on or before 2025-05-24 so 7 days is observable by 2025-06-01.