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.
| 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 |