Write a SQL query to calculate monthly campaign conversion rates by channel.
Use campaign_touches and conversions. Count distinct leads reached through each non-null channel, and count a lead as converted when it has a completed conversion on or after the touch date within the same calendar month.
month_start, channel, reached_leads, converted_leads, and conversion_rate_pct.| Column | Type | Description |
|---|---|---|
| touch_idPK | INT | Unique campaign touch identifier |
| lead_id | VARCHAR(20) | Lead reached by the campaign touch |
| channel | VARCHAR(40) | Marketing channel used for the touch |
| touch_date | DATE | Date of the campaign touch |
| campaign_id | VARCHAR(20) | Campaign identifier associated with the touch |
| Column | Type | Description |
|---|---|---|
| conversion_idPK | INT | Unique conversion identifier |
| lead_id | VARCHAR(20) | Lead associated with the conversion |
| conversion_date | DATE | Date the conversion occurred |
| conversion_status | VARCHAR(20) | Conversion processing status |