Skip to content
/Chapter 17 · Guided Playground Missions
Lesson 17.7·website_analytics
Lesson 17.7

SQL Playground Mission: Campaign Revenue Report

Start from campaigns, keep zero rows visible, and calculate purchase revenue without dropping quiet campaigns.

Campaign reports should show quiet campaigns too. If you start from sessions or purchases, a campaign with no traffic disappears. Starting fromcampaigns and using LEFT JOINs keeps the full marketing list visible.

This mission combines three common metrics: sessions, sessions that purchased, and purchase revenue.

Pattern
select campaign_name,
       count(distinct session_id) as sessions,
       count(distinct session_id) filter (where event_name = 'purchase') as buyers,
       coalesce(sum(value) filter (where event_name = 'purchase'), 0) as revenue
from joined_campaign_events
group by campaign_name
Schema · Website AnalyticsTable · campaigns5 columns · 5 rows
Table · campaigns

Marketing campaigns with their channel and UTM tags.

5 columns · 5 rows
campaign_id intcampaign_name textchannel textutm_source textutm_medium text
SchemaTable · sessions7 columns · 30 rows
Table · sessions

One row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.

7 columns · 30 rows
session_id intuser_id intsession_date datechannel textcampaign_id intdevice textduration_seconds int
SchemaTable · events4 columns · 39 rows
Table · events

Funnel events (signup, add_to_cart, checkout, purchase). Only purchase events carry a value.

4 columns · 39 rows
event_id intsession_id intevent_name textvalue decimal
Your task

Return one row per campaign with campaign_name,channel, distinct sessions, distinctpurchasing_sessions, and purchase_revenue. Keep campaigns with no sessions or purchases. Sort by purchase_revenue descending, then campaign_name.

SQL Workbench
query.sqlwebsite_analytics · SQL engine loading
⌘↵ to run
·
Expected answer
  • Columns: campaign_name, channel, sessions, purchasing_sessions, purchase_revenue.
  • Rows: one row per campaign (5 rows).
  • Social Promo has 8 sessions, 3 purchasing sessions, and 146.35 in purchase revenue.
Next · 17.8 Rolling sessions report →