SQL JOIN Practice: Attributing Sessions to Campaigns
Turn campaign ids into campaign names, and watch which campaigns quietly fall out of an inner join.
Each paid, social, or email session carries a campaign_id, but a report needs the campaign's name. That lives in the campaigns table, reached by joining on the key.
Grouping the joined rows by campaign_name answers a core marketing question: which campaigns actually brought people to the site?
An INNER JOIN only keeps matches
The campaigns table lists a Retargeting campaign, but no session references it. Because an INNER JOIN keeps only rows that match on both sides, that campaign never appears in the result. Sessions with a NULL campaign_id (organic and direct traffic) drop out too. That is exactly what you want when the question is about campaigns.
select dim.label, dim.attribute, count(*) as events
from fact_table as fact
join dim_table as dim on fact.dim_id = dim.dim_id
group by dim.label, dim.attribute
order by events desc, dim.labelSchema · Website AnalyticsTable · campaigns5 columns · 5 rows
Marketing campaigns with their channel and UTM tags.
For each campaign that drove traffic, return the campaign_name, its channel, and the number of sessions (sessions). Sort by sessions descending, then campaign name.
- Columns: campaign_name, channel, sessions.
- Rows: 4 campaigns that actually drove sessions.
- Social Promo leads with 8 sessions; the Retargeting campaign drops out with zero.