Write a SQL query to calculate monthly retention for customers acquired through marketing campaigns.
Use the provided campaign, acquisition, customer, and activity data. Treat a customer as retained in a month when they have at least one activity during that calendar month on or after acquisition.
Output
- One row per campaign, acquisition cohort month, and activity month.
- Return
campaign_id, cohort_month, activity_month, cohort_customers, retained_customers, and retention_rate.
- Include months from each cohort month through the latest activity month, including zero-retention months. Sort by
campaign_id, cohort_month, and activity_month.
Output details
- Represent
cohort_month and activity_month as the first calendar day of each month.
- Count each customer at most once per campaign and cohort month for both
cohort_customers and retained_customers.
- Round
retention_rate to exactly 4 decimal places, including trailing zeroes.
- Sort
campaign_id, cohort_month, and activity_month in ascending order.