Your question is Unify Campaign Performance Sources. Start with the requirements and the four 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 asked to build a unified monthly campaign report by combining lead data from Salesforce, email engagement from Marketo, and web sessions from Google Analytics. Write a PostgreSQL query that returns one row per campaign for January 2024, showing the campaign name, total leads created, number of MQLs, total email sends, total email clicks, total web sessions, and a derived campaign_status that is Healthy when the campaign has at least 2 MQLs and a click-through rate of at least 10%; otherwise mark it Needs Review. Include campaigns even if they are missing data from one of the sources.
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Unique campaign identifier |
| campaign_name | VARCHAR(100) | Campaign name |
| Column | Type | Description |
|---|---|---|
| lead_idPK | INT | Unique lead identifier |
| campaign_id | INT | Associated campaign identifier |
| created_at | DATE | Lead creation date |
| lead_status | VARCHAR(20) | Lead lifecycle status |
| Column | Type | Description |
|---|---|---|
| stat_idPK | INT | Unique email stat record identifier |
| campaign_id | INT | Associated campaign identifier |
| activity_date | DATE | Email activity date |
| sends | INT | Email sends for the record |
| clicks | INT | Email clicks for the record |
| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Unique session record identifier |
| campaign_id | INT | Associated campaign identifier |
| session_date | DATE | Web session date |
| sessions | INT | Number of sessions |