Calculate Ad Performance with Click-Through and Conversion Rates

impressions

+---------+---------+---------------------+-----------+
| user_id | ad_id   | event_time          | platform  |
+---------+---------+---------------------+-----------+
| 101     | 9001    | 2023-07-01 10:00:00 | iOS       |
| 102     | 9002    | 2023-07-01 10:00:05 | Android   |
| 101     | 9003    | 2023-07-01 10:00:10 | Web       |
+---------+---------+---------------------+-----------+

clicks_conversions

+---------+---------+---------------------+------------+
| user_id | ad_id   | click_time          | is_purchase|
+---------+---------+---------------------+------------+
| 101     | 9001    | 2023-07-01 10:00:02 | 0          |
| 102     | 9002    | 2023-07-01 10:00:06 | 1          |
| 103     | 9004    | 2023-07-01 10:00:12 | 0          |
+---------+---------+---------------------+------------+

Scenario

You are given two tables that log impressions, clicks and conversions for in-app ads. Build a KPI to evaluate ad performance and write the SQL needed to compute it.

Question

Propose a single metric that captures both click-through and conversion quality. Write a SQL query that joins the two tables and returns the metric by ad_id for the last 7 days.

Hints

Define join keys and timing windows; consider CTR, CVR or a composite score.

Ad performance KPI combining click-through and conversion quality

Table impressions

column	type	constraints
user_id	INTEGER	NOT NULL
ad_id	INTEGER	NOT NULL
event_time	TIMESTAMP	NOT NULL
platform	VARCHAR	NOT NULL

Sample data:

ad_id	event_time	platform	user_id
9001	2025-05-30 10:00:00	iOS	101
9002	2025-05-30 10:00:05	Android	102
9003	2025-05-30 10:00:10	Web	101

Table clicks_conversions

column	type	constraints
user_id	INTEGER	NOT NULL
ad_id	INTEGER	NOT NULL
click_time	TIMESTAMP	NOT NULL
is_purchase	INTEGER	NOT NULL, CHECK (is_purchase IN (0,1))

Sample data:

ad_id	click_time	is_purchase	user_id
9001	2025-05-30 10:00:02	0	101
9002	2025-05-30 10:00:06	1	102
9004	2025-05-30 10:00:12	0	103

Expected output

For each ad_id with impressions between 2025-05-26 and 2025-06-01 (inclusive), return a composite performance metric that incorporates both click-through and conversion quality. We use Impression-to-Purchase Rate (IPR) = purchases/impressions as the main KPI (ad_performance_score). For transparency, also return impressions, clicks, CTR, and CVR.

ad_id	ad_performance_score	clicks	ctr	cvr	impressions	purchases
9001	0	1	1	0	1	0
9002	1	1	1	1	1	1
9003	0	0	0	0	1	0

Hints

1. Join on user_id and ad_id, and attribute clicks that occur after the impression within a reasonable window (e.g., 1 hour).
2. Filter to impressions whose event_time is between 2025-05-26 and 2025-06-01.
3. Compute CTR = clicks/impressions and CVR = purchases/clicks; a composite metric like purchases/impressions (CTR * CVR) captures both.
4. Handle divide-by-zero cases using NULLIF and COALESCE when computing ratios.