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.