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 from campaigns and using LEFT JOINs keeps the full marketing list visible.

This mission combines three common metrics: sessions, sessions that purchased, and purchase revenue attributed to sessions carrying that campaign id. It is an attribution report, not a measure of incremental revenue caused by the campaign.

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 Analytics Table · campaigns 5 columns · 5 rows

Table · campaigns

Marketing campaigns with their channel and UTM tags.

5 columns · 5 rows

campaign_id int campaign_name text channel text utm_source text utm_medium text

Schema Table · sessions 7 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 int user_id int session_date date channel text campaign_id int device text duration_seconds int

Schema Table · events 4 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 int session_id int event_name text value decimal

Your task

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

Website Analytics · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

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.

Previous · 17.6 Pageview deduplication audit

Next · 17.8 Rolling sessions report →

Reference

SQL JOIN Types

INNER, LEFT, RIGHT, FULL, and CROSS joins.

Cheatsheet

SQL Joins Cheatsheet

Join types and common join patterns.

Interview

SQL JOIN Interview Questions

Practice join keys, left joins, duplicate rows, and debugging.

Mistake