Your question is Flag Incomplete Campaign Tracking. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given campaign delivery, web session, and conversion data for a multi-channel digital campaign. Write a PostgreSQL query that returns one row per Arthur J. Gallagher campaign and channel for January 2024, showing delivered clicks, tracked sessions, tracked conversions, session tracking rate, conversion tracking rate, and a data quality flag. Treat missing downstream tracking as zero, and classify each row as complete, partial, or missing based on whether sessions and conversions are present relative to delivered clicks.
Use the campaign delivery table as the base so channels with clicks but no tracked sessions or conversions still appear in the output.
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Unique delivery record |
| campaign_id | INT | Campaign identifier |
| channel | VARCHAR(50) | Marketing channel |
| delivery_date | DATE | Date clicks were delivered |
| impressions | INT | Impressions served |
| clicks | INT | Clicks delivered |
| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Unique session record |
| campaign_id | INT | Campaign identifier |
| channel | VARCHAR(50) | Attributed marketing channel |
| session_date | DATE | Date of tracked session |
| tracked_sessions | INT | Tracked session count |
| Column | Type | Description |
|---|---|---|
| conversion_idPK | INT | Unique conversion record |
| campaign_id | INT | Campaign identifier |
| channel | VARCHAR(50) | Attributed marketing channel |
| conversion_date | DATE | Date of tracked conversion |
| tracked_conversions | INT | Tracked conversion count |