You are given Lyft Ads data for advertisers, campaigns, and daily performance. Write a PostgreSQL query that analyzes advertiser performance by monthly signup cohort and month since cohort start. For each cohort month and cohort_age_month, return the number of advertisers in the cohort, the number of active advertisers in that period, total spend, total attributed rides, average spend per active advertiser, and the prior-period spend change using a window function. Treat an advertiser as active in a month if total spend in that month is greater than 0. Include advertisers with no campaign activity after signup so cohort sizes are not understated.
advertisers| column | type | description |
|---|---|---|
| advertiser_id | INT | Unique advertiser ID |
| advertiser_name | VARCHAR(100) | Advertiser name |
| signup_date | DATE | Date the advertiser first joined Lyft Ads |
| vertical | VARCHAR(50) | Advertiser industry vertical |
campaigns| column | type | description |
|---|---|---|
| campaign_id | INT | Unique campaign ID |
| advertiser_id | INT | Advertiser that owns the campaign |
| campaign_name | VARCHAR(100) | Campaign name |
| launch_surface | VARCHAR(50) | Lyft surface where the ad ran |
daily_campaign_performance| column | type | description |
|---|---|---|
| performance_id | INT | Unique performance row ID |
| campaign_id | INT | Campaign ID |
| activity_date | DATE | Performance date |
| spend_usd | NUMERIC(10,2) | Daily ad spend |
| attributed_rides | INT | Rides attributed to the campaign |
advertisers| advertiser_id | advertiser_name | signup_date | vertical |
|---|---|---|---|
| 1 | Urban Eats | 2024-01-05 | Restaurants |
| 2 | City Style | 2024-01-20 | Retail |
| 3 | Night Owl | 2024-02-02 | Entertainment |
daily_campaign_performance| performance_id | campaign_id | activity_date | spend_usd | attributed_rides |
|---|---|---|---|---|
| 1001 | 101 | 2024-01-10 | 120.00 | 14 |
| 1002 | 101 | 2024-02-03 | 80.00 | 9 |
| 1003 | 102 | 2024-02-14 | 60.00 | 7 |
| cohort_month | cohort_age_month | cohort_size | active_advertisers | total_spend_usd | total_attributed_rides | avg_spend_per_active_advertiser | prior_period_spend_change |
|---|---|---|---|---|---|---|---|
| 2024-01 | 0 | 2 | 1 | 120.00 | 14 | 120.00 | null |
| 2024-01 | 1 | 2 | 2 | 140.00 | 16 | 70.00 | 20.00 |
| Column | Type | Description |
|---|---|---|
| advertiser_idPK | INT | Unique advertiser identifier |
| advertiser_name | VARCHAR(100) | Advertiser name |
| signup_date | DATE | Date the advertiser joined Lyft Ads |
| vertical | VARCHAR(50) | Advertiser industry vertical |
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Unique campaign identifier |
| advertiser_id | INT | Advertiser that owns the campaign |
| campaign_name | VARCHAR(100) | Campaign name |
| launch_surface | VARCHAR(50) | Lyft surface where the ad was shown |
| Column | Type | Description |
|---|---|---|
| performance_idPK | INT | Unique daily performance row identifier |
| campaign_id | INT | Campaign identifier |
| activity_date | DATE | Date of campaign activity |
| spend_usd | NUMERIC(10,2) | Daily ad spend in USD |
| attributed_rides | INT | Rides attributed to the campaign on that date |
| campaign_id | advertiser_id | campaign_name | launch_surface |
|---|---|---|---|
| 101 | 1 | Winter Promo | Lyft Rider App |
| 102 | 1 | Late Night Push | Lyft Rider App |
| 103 | 2 | Style Refresh | Lyft Email |
| 104 | 3 | Weekend Events | Lyft Rider App |
| 105 | 4 | Grocery Saver | Lyft Rider App |
| 106 | 5 | Service Reminder | Lyft Driver App |
| 107 | 6 | Self Care Spring | |
| 108 | 99 | Orphan Campaign | Lyft Rider App |
| 109 | 7 | January Test | Lyft Email |
| 110 | Unassigned Campaign | Lyft Rider App | |
| 1 | 49 | Self Care Spring | Lyft Checkout |
| 2 | 23 | Game Day Push | Lyft SMS |
| 3 | 52 | Back to School | Lyft In-App Banner |
| 4 | 36 | Retention Pulse | Lyft Web |
| 5 | 75 | Late Night Push | Lyft Rider Inbox |
| 6 | 63 | Flash Sale | Lyft Destination Screen |
| 7 | 29 | Summer Kickoff | Lyft Email |
| 8 | 100 | Service Reminder | Lyft Ad Network |
| 9 | 67 | Loyalty Reengagement | Lyft Web |
| 10 | 4 | Concert Nights | Lyft Map Surface |
| 11 | 6 | Referral Burst | Lyft Scheduled Rides |
| 12 | 29 | Rainy Day Promo | Lyft Rider Inbox |
| 13 | 54 | Game Day Push | Lyft Scheduled Rides |
| 14 | 55 | Driver Perks | Lyft Driver App |
| 15 | 43 | Grocery Saver | Lyft SMS |
| 16 | 33 | Self Care Spring | Lyft Ride Receipt |
| 17 | 42 | Self Care Spring | Lyft Ad Network |
| 18 | 3 | Summer Kickoff | Lyft Rewards Hub |
| advertiser_id | advertiser_name | signup_date | vertical |
|---|---|---|---|
| 1 | Urban Eats | 2024-01-05 | Restaurants |
| 2 | City Style | 2024-01-20 | Retail |
| 3 | Night Owl | 2024-02-02 | Entertainment |
| 4 | Fresh Mart | 2024-02-18 | Grocery |
| 5 | RideReady | 2024-03-01 | Automotive |
| 6 | Glow Spa | 2024-03-15 | Beauty |
| 7 | Fit Hub | 2024-01-28 | |
| 8 | Book Corner | 2024-04-03 | Retail |
| 1 | Auto Lane | 2024-03-02 | Coffee |
| 2 | Peak Fitness | 2024-01-06 | Health |
| 3 | Urban Bloom | 2024-03-24 | Nightlife |
| 4 | Downtown Deals | 2024-02-26 | Local Services |
| 5 | Corner Pharmacy | 2024-03-11 | Apparel |
| 6 | Bloom Salon | 2024-03-25 | Education |
| 7 | Fit Hub | 2024-02-15 | Restaurants |
| 8 | City Style | 2024-02-05 | Retail |
| 9 | Green Basket | 2024-01-30 | Furniture |
| 10 | Prime Repair | 2024-03-16 | Beauty |
| 11 | Urban Eats | 2024-01-27 | Electronics |
| 12 | Quick Wash | 2024-01-04 | Sports |
| 13 | Book Corner | 2024-01-07 | Retail |
| 14 | Bloom Salon | 2024-01-16 | Home Services |
| 15 | Urban Bloom | 2024-02-25 | Home Services |
| 16 | Home Harvest | 2024-02-19 | Pharmacy |
| 17 | Auto Lane | 2024-03-23 | Automotive |
| 18 | Book Corner | 2024-03-21 | Pharmacy |
| 19 | Metro Diner | 2024-01-18 | Automotive |
| performance_id | campaign_id | activity_date | spend_usd | attributed_rides |
|---|---|---|---|---|
| 1005 | 103 | 2024-02-12 | 60.00 | 7 |
| 1001 | 101 | 2024-01-10 | 120.00 | 14 |
| 1009 | 105 | 2024-03-03 | 90.00 | 10 |
| 1002 | 101 | 2024-02-03 | 80.00 | 9 |
| 1006 | 104 | 2024-02-20 | 200.00 | 22 |
| 1012 | 106 | 2024-04-05 | 110.00 | 12 |
| 1003 | 102 | 2024-02-14 | 60.00 | 7 |
| 1010 | 105 | 2024-04-01 | 0.00 | 0 |
| 1007 | 104 | 2024-03-10 | 150.00 | 18 |
| 1011 | 106 | 2024-03-18 | 5 | |
| 1004 | 103 | 2024-03-05 | 40.00 | 4 |
| 1008 | 109 | 2024-01-30 | -10.00 | -1 |
| 1 | 48 | 2024-03-12 | 142 | 61 |
| 2 | 57 | 2024-03-12 | 230 | 94 |
| 3 | 3 | 2024-02-11 | 7 | 12 |
| 4 | 63 | 2024-03-09 | -14 | 42 |
| 5 | 82 | 2024-01-24 | 69 | 26 |
| 6 | 92 | 2024-03-14 | null | 9 |
| 7 | 81 | 2024-02-11 | -26 | 5 |
| 8 | 67 | 2024-03-19 | 188 | null |
| 9 | 5 | 2024-02-06 | 91 | 16 |
| 10 | 90 | 2024-01-11 | null | 3 |
| 11 | 74 | 2024-01-29 | 65 | 81 |
| 12 | 91 | 2024-01-12 | 185 | 56 |
| 13 | 2 | 2024-01-12 | null | 91 |
| 14 | 71 | 2024-03-04 | -15 | 25 |
| 15 | 53 | 2024-03-11 | 128 | 13 |
| cohort_month | cohort_age_month | cohort_size | active_advertisers | total_spend_usd | total_attributed_rides | avg_spend_per_active_advertiser | prior_period_spend_change |
|---|---|---|---|---|---|---|---|
| 2024-01 | 0 | 9 | 1 | 110.00 | 13 | 110.00 | null |
| 2024-01 | 1 | 9 | 2 | 200.00 | 23 | 100.00 | 90.00 |
| 2024-01 | 2 | 9 | 1 | 40.00 | 4 | 40.00 | -160.00 |
| 2024-02 | 0 | 4 | 1 | 200.00 | 22 | 200.00 | null |
| 2024-02 | 1 | 4 | 2 | 240.00 | 28 | 120.00 | 40.00 |
| 2024-02 | 2 | 4 | 0 | 0.00 | 0 | null | -240.00 |
| 2024-03 | 0 | 5 | 0 | 0.00 | 5 | null | null |
| 2024-03 | 1 | 5 | 1 | 110.00 | 12 | 110.00 | 110.00 |
