Analyze Acquisition Channels for User Value and Retention

acquisition

| user_id | acquire_channel | acquire_date | | 101 | organic | 2023-01-05 | | 102 | paid_search | 2023-01-06 | | 103 | social_media | 2023-01-08 |

​

transactions

| transaction_id | user_id | amount | transaction_date | | 9001 | 101 | 45.50 | 2023-01-10 | | 9002 | 101 | 19.00 | 2023-02-05 | | 9003 | 102 | 60.00 | 2023-02-12 | | 9004 | 103 | 30.00 | 2023-03-01 |

Scenario

An e-commerce platform provided two tables: one logging the channel and date each user was acquired, another logging every purchase. Product managers want actionable insights on acquisition effectiveness and user value.

Question

Write a query that returns each acquire_channel together with the count of distinct users acquired from it. 2. For every acquire_channel, rank users by their cumulative spend and output the top three spenders per channel with their totals. 3. Build a monthly cohort table that shows, for each acquisition month, the percentage of users who make at least one purchase in any subsequent month (retention). 4. Within 90 days of each user’s acquire_date, compute average revenue per user (ARPU) by channel and identify the channel with the highest ARPU.

Hints

Expect multiple CTEs, DATE_DIFF/DATE_TRUNC, JOINs, window functions such as SUM() OVER and RANK().