Compute ad impression conversion rates

You are given two tables for an ads product.

Table: ad_impressions

Column	Type	Description
impression_id	STRING	Unique identifier for an ad impression. Primary key.
user_id	STRING	User who saw the ad.
ad_id	STRING	Ad that was shown.
campaign_id	STRING	Campaign that owns the ad.
impression_ts	TIMESTAMP	Time the impression was served, stored in UTC.
cost_usd	NUMERIC	Advertiser cost for the impression in USD.

Table: ad_conversions

Column	Type	Description
conversion_id	STRING	Unique identifier for a conversion event. Primary key.
user_id	STRING	User who converted.
ad_id	STRING	Ad associated with the conversion event.
conversion_ts	TIMESTAMP	Time the conversion occurred, stored in UTC.
revenue_usd	NUMERIC	Conversion value in USD.

Attribution rule:

• A conversion can be attributed only to an impression from the same user_id and ad_id.
• The impression must occur before the conversion.
• The conversion must occur within 7 days after the impression.
• If multiple impressions are eligible for the same conversion, attribute the conversion to the most recent eligible impression before the conversion.
• Each conversion should be attributed at most once.

Write a SQL query for impressions served from 2024-01-01 00:00:00 UTC through 2024-01-31 23:59:59 UTC. For each campaign_id and UTC impression_date, return:

• campaign_id
• impression_date
• impressions: number of impressions
• unique_impression_users: number of distinct users with impressions
• attributed_conversions: number of conversions attributed to those impressions
• conversion_rate: attributed conversions divided by impressions
• total_spend_usd: total impression cost
• total_conversion_revenue_usd: total attributed conversion revenue
• avg_hours_to_conversion: average hours between impression and attributed conversion, across attributed conversions only

Rows with impressions but zero attributed conversions should still appear with zero conversions and zero conversion revenue.

Compute ad impression conversion rates

Compute per-campaign, per-UTC impression_date metrics for impressions served between 2024-01-01 00:00:00 and 2024-01-31 23:59:59 UTC, attributing conversions to impressions by user_id and ad_id within a 7-day lookback, using last-touch before conversion and attributing each conversion at most once.

Table ad_impressions

column	type	constraints
impression_id	VARCHAR	PRIMARY KEY
user_id	VARCHAR
ad_id	VARCHAR
campaign_id	VARCHAR
impression_ts	TIMESTAMP
cost_usd	NUMERIC

Sample data:

ad_id	campaign_id	cost_usd	impression_id	impression_ts	user_id
a1	cA	0.5	imp_7	2023-12-31 23:00:00	u2
a1	cA	0.52	imp_8	2024-01-01 08:00:00	u2
a1	cA	0.5	imp_1	2024-01-03 10:00:00	u1
a1	cA	0.55	imp_2	2024-01-05 09:00:00	u1
a1	cA	0.6	imp_3	2024-01-05 12:00:00	u2
a2	cA	0.45	imp_4	2024-01-15 08:30:00	u3
a4	cB	0.7	imp_5	2024-01-20 14:15:00	u1
a3	cB	0.65	imp_6	2024-01-20 16:45:00	u4

Table ad_conversions

column	type	constraints
conversion_id	VARCHAR	PRIMARY KEY
user_id	VARCHAR
ad_id	VARCHAR
conversion_ts	TIMESTAMP
revenue_usd	NUMERIC

Sample data:

ad_id	conversion_id	conversion_ts	revenue_usd	user_id
a1	conv_1	2024-01-01 09:00:00	5	u2
a1	conv_2	2024-01-06 10:00:00	12	u1
a1	conv_3	2024-01-12 12:00:00	8	u2
a1	conv_4	2024-01-09 08:00:00	4	u1
a4	conv_5	2024-01-21 10:00:00	20	u1
a3	conv_6	2024-01-25 16:45:00	10	u4
a1	conv_7	2024-01-05 08:00:00	3	u1
a1	conv_8	2024-01-01 07:30:00	6	u2

Expected output

One row per (campaign_id, impression_date) for January 2024 impressions, including counts of impressions and unique users, last-touch 7-day attributed conversions and rate, total spend, total attributed revenue, and average hours from impression to conversion. Rows with zero attributed conversions are included with zero conversions and revenue and null average hours.

attributed_conversions	avg_hours_to_conversion	campaign_id	conversion_rate	impression_date	impressions	total_conversion_revenue_usd	total_spend_usd	unique_impression_users
1	1	cA	1	2024-01-01	1	5	0.52	1
1	46	cA	1	2024-01-03	1	3	0.5	1
3	96	cA	1.5	2024-01-05	2	24	1.15	2
0	NULL	cA	0	2024-01-15	1	0	0.45	1
2	69.875	cB	1	2024-01-20	2	30	1.35	2

Hints

1. Use a 7-day last-touch attribution: join conversions to impressions on user_id and ad_id with i.impression_ts <= c.conversion_ts <= i.impression_ts + interval '7 days', then pick the most recent impression per conversion via ROW_NUMBER ordered by impression_ts DESC.
2. Determine attribution across all impressions first, then filter to those whose impression falls in January; this prevents misattribution to January impressions when a more recent eligible impression exists in late December.
3. Filter the impression window using a half-open interval: impression_ts >= TIMESTAMP '2024-01-01 00:00:00' and impression_ts < TIMESTAMP '2024-02-01 00:00:00'.
4. Aggregate impressions and conversions separately and left join so dates with impressions but zero conversions still appear with zeros for conversion counts and revenue.
5. Avoid the invalid syntax TIMESTAMP('2024-01-01 00:00:00+00'); in PostgreSQL use TIMESTAMP '2024-01-01 00:00:00'.