Santander wants to evaluate the impact of a campaign promoting the Santander Edge current account. Write a PostgreSQL query that compares each customer signup cohort's conversion rate during the 30 days before campaign exposure with the 30 days after exposure.
Santander Edge Spring 2024.campaign_start_date as the campaign exposure date.campaign_start_date - INTERVAL '30 days' and exclusive of campaign_start_date.campaign_start_date and exclusive of campaign_start_date + INTERVAL '30 days'.cohort_month, cohort_size, before_conversions, after_conversions, before_conversion_rate, after_conversion_rate, and lift_percentage_points, in that order.lift_percentage_points to 2 decimal places, expressing lift as percentage points.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique Santander customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| signup_date | DATE | Date the customer signed up |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Customer exposed to a campaign |
| campaign_name | VARCHAR(150) | Campaign name |
| campaign_start_date | DATE | Date used as the campaign exposure boundary |
| campaign_channel | VARCHAR(30) | Channel through which the campaign was delivered |
| Column | Type | Description |
|---|---|---|
| application_idPK | INT | Unique product application identifier |
| customer_id | INT | Customer who submitted the application |
| application_date | DATE | Date the application was submitted |
| approved | BOOLEAN | Application approval status |
| product_name | VARCHAR(100) | Product requested in the application |