Dataset

Marketing Campaigns SQL Practice Dataset

Channels, campaigns, ad groups, and two years of daily performance where every metric is a ratio.

Tables: 5
Rows: 303,609
Size: Large
Level: Intermediate
Domain: Marketing
Formats: Parquet · Schema SQL
In browser: 5,756 rows in the browser

Download this dataset, free

No account, no email, no attribution required: dedicated to the public domain under CC0 1.0, so commercial use and redistribution are fine. Open it in the browser playground, or download the Parquet files below and run marketing_campaigns.schema.sql in DuckDB to create the tables and load them.

Open in playground → 15 questions with answers ↓

marketing_campaigns.schema.sql All 5 tables, 303,609 rows, as CREATE TABLE and COPY statements that load the files below. Download →

This is a large dataset. The downloads here are the complete 303,609 rows as Parquet. Opening it in the playground loads a 5,756-row sample instead, so the browser tab stays responsive. The schema is identical, so a query you write against the sample runs unchanged against the full download.

channels.parquet 9 rows campaigns.parquet 600 rows ad_groups.parquet 3,000 rows daily_performance.parquet 260,000 rows conversions.parquet 40,000 rows

Two years of a multi-channel marketing program, reported the way an ad platform reports it: one row per ad group per day, with impressions, clicks, spend and conversions. Every number anyone actually asks for (click-through rate, conversion rate, cost per acquisition, return on ad spend) is a ratio, and that is what makes this dataset harder than it looks.

A ratio cannot be averaged. The daily click-through rates in this data average out at about 1.53%. The actual click-through rate, total clicks divided by total impressions, is 0.404%. That is not a rounding difference; it is wrong by nearly four times, and both numbers come from the same rows. AVG(clicks / impressions) weights a day with 2 impressions exactly as heavily as a day with 942,052, and in this data the small days are the ones with the flattering rates. For an overall rate the correct form is SUM(clicks) / SUM(impressions): divide the totals rather than averaging the daily ratios. The same trap has its own write-up at why averaging averages is wrong.

That gap is deliberate and it is built into the data rather than sprinkled on. Broad, high-volume ad groups engage badly; small, tightly targeted ones engage well. It is the ordinary shape of paid media, and it is precisely the correlation between the rate and its own denominator that makes the two calculations disagree.

The denominators also go to zero. A paused or archived ad group still reports its daily rows, most of them with zero impressions, and more than a quarter of all rows look like that. Dividing by it is not a small number, it is a division by zero, and DuckDB, which runs this site, does not stop: 0 / 0 comes back as NaN and anything else over zero as Infinity. One such row turns an AVG or SUM of the rates into NaN, with no error. PostgreSQL and SQL Server stop the query instead. NULLIF(impressions, 0) turns the zero into NULL, so that row's rate is NULL and aggregates skip it, on every engine. Conversions are zero on about nine ad-group days in ten, which leaves cost per acquisition undefined far more often than it is defined.

Attribution is the last piece. Every conversion records the channel that first brought the visitor in and the channel they arrived on last, and the two disagree on more than a third of journeys. Which one gets the credit is a decision, not a lookup, and the channel rankings it produces are not the same.

All of it is synthetic. No real advertiser, platform, campaign, or person is represented.

Schema

Arrows point from a column to the key it references. PK marks a table's key and FK a column that references another table.

Join conditions

campaigns.channel_id = channels.channel_id
ad_groups.campaign_id = campaigns.campaign_id
daily_performance.ad_group_id = ad_groups.ad_group_id
conversions.ad_group_id = ad_groups.ad_group_id
conversions.first_touch_channel_id = channels.channel_id
conversions.last_touch_channel_id = channels.channel_id

Schema · Marketing Campaigns View dataset schema 5 tables

Table · channels

Marketing channels. is_paid separates the ones with a media cost from the ones without, which is what makes a blended return on ad spend misleading.

4 columns · 9 rows

channel_id int channel_name text medium text is_paid bool

Table · campaigns

One row per campaign. ended_on is null while the campaign is still running, and budget is the planned spend rather than the actual.

7 columns · 62 rows

campaign_id int campaign_name text channel_id int objective text started_on date ended_on date budget double

Table · ad_groups

Targeting groups inside a campaign. A paused ad group still reports daily rows, with zero impressions.

5 columns · 60 rows

ad_group_id int campaign_id int ad_group_name text target_audience text status text

Table · daily_performance

One row per ad group per active day. impressions can be zero on a paused day, which makes click-through rate a division by zero rather than a small number.

8 columns · 4827 rows

performance_id int ad_group_id int activity_date date impressions int clicks int spend double conversions int conversion_value double

Table · conversions

Individual conversions with the channel that first brought the visitor in and the channel they arrived on last. The two often disagree, which is why attribution is a choice rather than a lookup.

8 columns · 798 rows

conversion_id int ad_group_id int first_touch_channel_id int last_touch_channel_id int converted_at timestamp revenue double touch_count int device text

Best for

Rate metrics done properly: dividing the totals rather than averaging the daily divisions.
Guarding a zero denominator with NULLIF: DuckDB turns x / 0 into Infinity or NaN, which poisons an average, and other engines raise an error.
Comparing first-touch and last-touch attribution, which rank the channels differently.

Starter questions

What is the real click-through rate by channel?
Which ad groups spend money and never convert?
Does first-touch or last-touch credit change the best channel?

Practice questions

Work these in the playground against the browser sample. Each link opens the dataset with the question in the editor, ready for your query. Solutions are collapsed, so open one only after you have an answer of your own.

Beginner

1. How many campaigns run on each channel, and how many are still running?

COUNT with a FILTER, and IS NULL as the definition of 'still running'.

Try it in the playground →

Show solution

select c.channel_name,
       count(*) as campaigns,
       count(*) filter (where ca.ended_on is null) as still_running
from campaigns ca
join channels c on c.channel_id = ca.channel_id
group by c.channel_name
order by campaigns desc;

2. What are the total impressions, clicks, and spend for each channel?

Aggregating a fact table up through two joins.

Try it in the playground →

Show solution

select c.channel_name,
       sum(d.impressions) as impressions,
       sum(d.clicks) as clicks,
       round(sum(d.spend), 2) as spend
from daily_performance d
join ad_groups g on g.ad_group_id = d.ad_group_id
join campaigns ca on ca.campaign_id = g.campaign_id
join channels c on c.channel_id = ca.channel_id
group by c.channel_name
order by spend desc;

3. How many reported days had no impressions at all, broken down by ad group status?

Finding the rows that will break a ratio before you write one.

Try it in the playground →

Show solution

select g.status,
       count(*) as reported_days,
       count(*) filter (where d.impressions = 0) as days_with_no_impressions
from daily_performance d
join ad_groups g on g.ad_group_id = d.ad_group_id
group by g.status
order by days_with_no_impressions desc;

4. How many ad groups target each audience type?

A plain GROUP BY on the dimension, before any rates are involved.

Try it in the playground →
Show solution
```
select target_audience, count(*) as ad_groups
from ad_groups
group by target_audience
order by ad_groups desc;
```
5. How many conversions came from each device, and what revenue did they bring?

Two aggregates over one grouping, on the table where a conversion is a row.

Try it in the playground →
Show solution
```
select device,
       count(*) as conversions,
       round(sum(revenue), 2) as revenue
from conversions
group by device
order by revenue desc;
```

Intermediate

1. Calculate the real click-through rate for each channel: total clicks divided by total impressions. Guard against channels with no impressions.

The correct form of a rate. Divide the totals; NULLIF turns a zero denominator into NULL rather than the Infinity or NaN DuckDB returns (other engines raise an error).

Try it in the playground →

Show solution

select c.channel_name,
       sum(d.impressions) as impressions,
       sum(d.clicks) as clicks,
       round(100.0 * sum(d.clicks) / nullif(sum(d.impressions), 0), 3) as ctr_pct
from daily_performance d
join ad_groups g on g.ad_group_id = d.ad_group_id
join campaigns ca on ca.campaign_id = g.campaign_id
join channels c on c.channel_id = ca.channel_id
group by c.channel_name
order by ctr_pct desc;

2. Show both calculations side by side: the average of the daily click-through rates, and total clicks over total impressions. How far apart are they?

The mistake this dataset exists for. Both numbers come from the same rows; only one of them is the click-through rate.

Try it in the playground →

Show solution

select round(100.0 * avg(case when impressions = 0 then null else clicks * 1.0 / impressions end), 3) as avg_of_daily_rates_pct,
       round(100.0 * sum(clicks) / nullif(sum(impressions), 0), 3) as true_ctr_pct,
       round(
         avg(case when impressions = 0 then null else clicks * 1.0 / impressions end)
         / (sum(clicks) * 1.0 / nullif(sum(impressions), 0)),
         2
       ) as overstated_by_factor
from daily_performance;

3. What is the return on ad spend for each campaign? Return only campaigns that actually spent money, worst first.

A ratio with a denominator that is legitimately zero for some rows, so the filter belongs in HAVING.

Try it in the playground →

Show solution

select ca.campaign_name,
       round(sum(d.spend), 2) as spend,
       round(sum(d.conversion_value), 2) as revenue,
       round(sum(d.conversion_value) / nullif(sum(d.spend), 0), 2) as roas
from daily_performance d
join ad_groups g on g.ad_group_id = d.ad_group_id
join campaigns ca on ca.campaign_id = g.campaign_id
group by ca.campaign_name
having sum(d.spend) > 0
order by roas
limit 25;

4. Show total spend, clicks, and the true click-through rate for each month.

Rolling a daily fact up to a month without averaging the daily rates on the way.

Try it in the playground →

Show solution

select date_trunc('month', activity_date) as month,
       round(sum(spend), 2) as spend,
       sum(clicks) as clicks,
       round(100.0 * sum(clicks) / nullif(sum(impressions), 0), 3) as ctr_pct
from daily_performance
group by month
order by month;

5. Which ad groups spent money but never converted once? Return the ad group, its campaign, and what it cost.

An aggregate filter that finds absence, which a WHERE on the daily rows cannot express.

Try it in the playground →

Show solution

select g.ad_group_name,
       ca.campaign_name,
       round(sum(d.spend), 2) as spend
from daily_performance d
join ad_groups g on g.ad_group_id = d.ad_group_id
join campaigns ca on ca.campaign_id = g.campaign_id
group by g.ad_group_name, ca.campaign_name
having sum(d.spend) > 0 and sum(d.conversions) = 0
order by spend desc;

Advanced

1. Compare first-touch and last-touch attribution. For each channel, how many conversions and how much revenue does each model give it?

The same conversions counted two ways. Two aggregations over different foreign keys, combined so the models sit side by side.

Try it in the playground →

Show solution

with first_touch as (
  select first_touch_channel_id as channel_id,
         count(*) as conversions,
         sum(revenue) as revenue
  from conversions
  group by first_touch_channel_id
),
last_touch as (
  select last_touch_channel_id as channel_id,
         count(*) as conversions,
         sum(revenue) as revenue
  from conversions
  group by last_touch_channel_id
)
select c.channel_name,
       coalesce(f.conversions, 0) as first_touch_conversions,
       coalesce(l.conversions, 0) as last_touch_conversions,
       round(coalesce(f.revenue, 0), 2) as first_touch_revenue,
       round(coalesce(l.revenue, 0), 2) as last_touch_revenue,
       round(coalesce(l.revenue, 0) - coalesce(f.revenue, 0), 2) as revenue_shift
from channels c
left join first_touch f on f.channel_id = c.channel_id
left join last_touch l on l.channel_id = c.channel_id
order by revenue_shift desc;

2. For each channel, show the true click-through rate next to the average of its ad groups' rates, and the gap between them. Which channel is most distorted by averaging?

Aggregation bias in one result: the unweighted average of group rates and the pooled rate disagree because volume is not spread evenly across groups.

Try it in the playground →

Show solution

with per_group as (
  select ca.channel_id,
         d.ad_group_id,
         sum(d.clicks) as clicks,
         sum(d.impressions) as impressions
  from daily_performance d
  join ad_groups g on g.ad_group_id = d.ad_group_id
  join campaigns ca on ca.campaign_id = g.campaign_id
  group by ca.channel_id, d.ad_group_id
  having sum(d.impressions) > 0
)
select c.channel_name,
       round(100.0 * sum(p.clicks) / nullif(sum(p.impressions), 0), 3) as true_ctr_pct,
       round(100.0 * avg(p.clicks * 1.0 / p.impressions), 3) as avg_of_group_rates_pct,
       round(
         100.0 * avg(p.clicks * 1.0 / p.impressions)
         - 100.0 * sum(p.clicks) / nullif(sum(p.impressions), 0),
         3
       ) as distortion
from per_group p
join channels c on c.channel_id = p.channel_id
group by c.channel_name
order by distortion desc;

3. What is the best-converting ad group on each channel, by conversion rate? Ignore ad groups with fewer than 100 clicks.

Top-1 per group where the ranking is itself a ratio, and a volume floor keeps a single lucky click off the top.

Try it in the playground →

Show solution

select channel_name, ad_group_name, clicks, conversions, conversion_rate_pct
from (
  select c.channel_name,
         g.ad_group_name,
         sum(d.clicks) as clicks,
         sum(d.conversions) as conversions,
         round(100.0 * sum(d.conversions) / nullif(sum(d.clicks), 0), 2) as conversion_rate_pct,
         row_number() over (
           partition by c.channel_id
           order by sum(d.conversions) * 1.0 / nullif(sum(d.clicks), 0) desc,
                    g.ad_group_id
         ) as rank
  from daily_performance d
  join ad_groups g on g.ad_group_id = d.ad_group_id
  join campaigns ca on ca.campaign_id = g.campaign_id
  join channels c on c.channel_id = ca.channel_id
  group by c.channel_id, c.channel_name, g.ad_group_id, g.ad_group_name
  having sum(d.clicks) >= 100
)
where rank = 1
order by conversion_rate_pct desc;

4. Which campaigns overtook their budget, and in what month did each one first do it?

A running total next to a fixed campaign budget, followed by a first-occurrence filter.

Try it in the playground →

Show solution

with monthly as (
  select ca.campaign_id,
         ca.campaign_name,
         ca.budget,
         date_trunc('month', d.activity_date) as month,
         sum(d.spend) as spend
  from daily_performance d
  join ad_groups g on g.ad_group_id = d.ad_group_id
  join campaigns ca on ca.campaign_id = g.campaign_id
  group by ca.campaign_id, ca.campaign_name, ca.budget, month
),
running as (
  select campaign_id,
         campaign_name,
         budget,
         month,
         sum(spend) over (
           partition by campaign_id order by month
         ) as cumulative_spend
  from monthly
)
select campaign_name,
       month,
       round(budget, 2) as budget,
       round(cumulative_spend, 2) as cumulative_spend,
       round(cumulative_spend - budget, 2) as over_budget_by
from running
where cumulative_spend > budget
qualify row_number() over (partition by campaign_id order by month) = 1
order by over_budget_by desc;

5. Paid channels have a media cost and unpaid ones do not, so a blended return on ad spend is meaningless. Split it: revenue and spend for paid channels against unpaid.

Grouping on a boolean dimension, and seeing why one blended ratio hides two different businesses.

Try it in the playground →

Show solution

select case when c.is_paid then 'paid' else 'unpaid' end as channel_group,
       count(distinct c.channel_id) as channels,
       round(sum(d.spend), 2) as spend,
       round(sum(d.conversion_value), 2) as revenue,
       round(sum(d.conversion_value) / nullif(sum(d.spend), 0), 2) as roas
from daily_performance d
join ad_groups g on g.ad_group_id = d.ad_group_id
join campaigns ca on ca.campaign_id = g.campaign_id
join channels c on c.channel_id = ca.channel_id
group by channel_group
order by channel_group;

Practice with this dataset

Guided lessons for this dataset are still being written. In the meantime, open it in the playground: the tables are already loaded, and the starter query is waiting in the editor.

Open Marketing Campaigns in the playground →

Small · 5 tables

Website Analytics SQL Practice Dataset

Sessions, pageviews, events, users, and campaigns for funnel and attribution practice.

Large · 7 tables

Ecommerce Orders SQL Practice Dataset

Customers, products, orders, line items, payments, and returns at realistic online-retail volume.

Large · 5 tables

Banking Transactions SQL Practice Dataset

Accounts, merchants, and two years of posted transactions with no stored balance to read off.

Small · 6 tables

Garden Shop SQL Practice Dataset