Design metrics and write SQL for a case

Case: Measure the impact of outreach on subsequent purchases and diagnose anomalies. Define your primary metric and write SQL. Schema and tiny samples below.

users(user_id INT, signup_date DATE, country STRING) +---------+-------------+---------+ | user_id | signup_date | country | +---------+-------------+---------+ | 1 | 2025-07-15 | US | | 2 | 2025-07-20 | US | | 3 | 2025-07-25 | CA | | 4 | 2025-08-01 | US | | 5 | 2025-08-05 | IN | | 6 | 2025-08-10 | US | +---------+-------------+---------+

events(user_id INT, event_time TIMESTAMP, event_name STRING, product_id INT, device STRING) +---------+---------------------+-------------+------------+--------+ | user_id | event_time | event_name | product_id | device | +---------+---------------------+-------------+------------+--------+ | 1 | 2025-08-11 09:00:00 | page_view | 101 | iOS | | 1 | 2025-08-12 10:00:00 | add_to_cart | 101 | iOS | | 1 | 2025-08-15 12:00:00 | purchase | 101 | iOS | | 2 | 2025-08-18 14:00:00 | page_view | 102 | Web | | 2 | 2025-08-19 16:00:00 | purchase | 102 | Web | | 3 | 2025-08-20 11:30:00 | page_view | 101 | Android| | 4 | 2025-08-21 09:15:00 | page_view | 101 | iOS | | 4 | 2025-08-28 17:45:00 | purchase | 101 | iOS | | 5 | 2025-08-22 08:05:00 | unsubscribe | NULL | Web | | 6 | 2025-08-23 19:20:00 | add_to_cart | 102 | Android| +---------+---------------------+-------------+------------+--------+

purchases(order_id INT, user_id INT, order_time TIMESTAMP, amount DECIMAL(10,2), product_id INT) +----------+---------+---------------------+--------+------------+ | order_id | user_id | order_time | amount | product_id | +----------+---------+---------------------+--------+------------+ | 5001 | 1 | 2025-08-15 12:00:00 | 199.99 | 101 | | 5002 | 2 | 2025-08-19 16:00:00 | 49.99 | 102 | | 5003 | 4 | 2025-08-28 17:45:00 | 129.00 | 101 | | 5004 | 6 | 2025-08-25 20:10:00 | 59.00 | 102 | +----------+---------+---------------------+--------+------------+

marketing_contacts(contact_id INT, user_id INT, contact_time TIMESTAMP, channel STRING, campaign STRING) +------------+---------+---------------------+---------+-----------+ | contact_id | user_id | contact_time | channel | campaign | +------------+---------+---------------------+---------+-----------+ | 9001 | 1 | 2025-08-11 08:00:00 | email | P_launch | | 9002 | 2 | 2025-08-18 09:00:00 | push | P_launch | | 9003 | 4 | 2025-08-21 09:00:00 | email | P_launch | | 9004 | 6 | 2025-08-23 09:00:00 | sms | P_launch | +------------+---------+---------------------+---------+-----------+

products(product_id INT, category STRING, launched_at DATE) +------------+----------+-------------+ | product_id | category | launched_at | +------------+----------+-------------+ | 101 | Elec | 2025-07-01 | | 102 | Apparel | 2025-08-01 | +------------+----------+-------------+

Tasks: A) Define a primary success metric for the campaign that is attributable, time‑bounded, and robust to activity spikes (e.g., 14‑day post‑contact purchase conversion among first contacts), plus two guardrails (e.g., unsubscribe rate within 3 days, latency‑sensitive engagement). Write the precise metric formulas. B) Write SQL to compute, for each contact_week and country, the 14‑day post‑contact purchase conversion rate and average revenue per contacted user. Only use the first contact per user; exclude purchases that occur before contact_time. C) Produce SQL to generate a matched baseline: for each contacted user, pair to one non‑contacted user in the same signup_week and country (deterministic tie‑break by smallest user_id) and compute the same 14‑day purchase rate for matches. D) On 2025‑08‑20, US contacted‑user conversion drops by 20% vs its prior 7‑day average. Write SQL to produce a breakdown table by device and product_id for 2025‑08‑20 contacts with: count_contacted, 14‑day conversion, and delta vs the prior 7‑day average for the same slice; return the top‑3 slices contributing most to the drop (hint: approximate contribution = exposure × delta). Be precise about windowing and joins. E) Briefly describe one check you would add to ensure your metrics are not biased by users who unsubscribed immediately after contact.