Marketing campaigns sample database for SQL Server
A ready-to-run SQL Server script with budget, spend, impressions, clicks, CPA and ROAS per campaign. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.
Marketing campaigns database for SQL Server
Each row is one fictional ad campaign: channel, dates, budget and actual spend, impressions, clicks, click-through rate, conversions, cost per acquisition, revenue and return on ad spend.
This page has it as a SQL Server script: one table and 1,000 rows, with column types chosen for SQL Server, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the marketing campaigns dataset page, where it is also available as CSV and Excel.
We loaded this exact file into SQL Server 2022 16.0.4325.4 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
sqlcmd
sqlcmd -S localhost -Q "CREATE DATABASE practice"
sqlcmd -S localhost -d practice -i marketing-campaigns-sqlserver.sqlSSMS or Azure Data Studio
Create a database, open the .sql file in a new query window connected to it, and press Execute.
Tables and columns
marketing_campaigns 1,000 rows
| Column | SQL Server type |
|---|---|
| campaign_id | NVARCHAR(10) |
| campaign_name | NVARCHAR(50) |
| channel | NVARCHAR(20) |
| start_date | DATE |
| end_date | DATE |
| budget | DECIMAL(8,2) |
| spend | DECIMAL(10,2) |
| impressions | INT |
| clicks | INT |
| ctr | DECIMAL(8,4) |
| conversions | INT |
| cpa | DECIMAL(8,2) |
| revenue | DECIMAL(11,2) |
| roas | DECIMAL(7,2) |
Practice questions
answers from SQL Server 2022 16.0.4325.4
What are spend, revenue and ROAS (revenue ÷ spend) by channel?
SQL
SELECT channel, COUNT(*) AS campaigns, ROUND(SUM(spend), 2) AS spend, ROUND(SUM(revenue), 2) AS revenue,
ROUND(CAST(SUM(revenue) AS DECIMAL(18,6)) / NULLIF(SUM(spend), 0), 2) AS roas
FROM marketing_campaigns
GROUP BY channel
ORDER BY roas DESC, channel;Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.
Result
| channel | campaigns | spend | revenue | roas |
|---|---|---|---|---|
| 157 | 1,314,413.04 | 6,896,058.46 | 5.25 | |
| Affiliate | 162 | 1,609,714.32 | 8,018,193.68 | 4.98 |
| Paid search | 172 | 1,587,707.19 | 5,817,976.68 | 3.66 |
| Social | 156 | 1,715,189.90 | 4,893,437.33 | 2.85 |
| Display | 171 | 1,502,392.36 | 2,958,804.94 | 1.97 |
| Video | 182 | 1,649,066.57 | 3,172,947.98 | 1.92 |
Which 5 campaigns have the best ROAS?
SQL
SELECT TOP 5 campaign_id, campaign_name, roas
FROM marketing_campaigns
ORDER BY roas DESC, campaign_id;SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
Result
| campaign_id | campaign_name | roas |
|---|---|---|
| MC-0828 | Affiliate - Spring Sale - Q2 2023 | 12.32 |
| MC-1218 | Email - New Product - Q4 2023 | 11.36 |
| MC-0771 | Affiliate - Spring Sale - Q3 2024 | 11.10 |
| MC-0779 | Affiliate - Spring Sale - Q3 2023 | 10.91 |
| MC-1485 | Email - Holiday - Q3 2023 | 10.89 |
How long do campaigns run on average, in days, per channel?
SQL
SELECT channel, ROUND(AVG(CAST(DATEDIFF(day, start_date, end_date) AS DECIMAL(18,4))), 2) AS avg_days
FROM marketing_campaigns
GROUP BY channel
ORDER BY avg_days DESC, channel;DATEDIFF(day, earlier, later) returns the number of days between two dates.
Result
| channel | avg_days |
|---|---|
| Affiliate | 37.63 |
| Social | 37.46 |
| Display | 36.74 |
| Paid search | 36.24 |
| Video | 35.79 |
| 35.27 |
How many campaigns spent more than their budget?
SQL
SELECT COUNT(*) AS over_budget
FROM marketing_campaigns
WHERE spend > budget;Result
| over_budget |
|---|
| 149 |
What was the total spend and number of conversions per start year?
SQL
SELECT YEAR(start_date) AS start_year, ROUND(SUM(spend), 2) AS spend, SUM(conversions) AS conversions
FROM marketing_campaigns
GROUP BY YEAR(start_date)
ORDER BY start_year;YEAR(date) returns the year as a number.
Result
| start_year | spend | conversions |
|---|---|---|
| 2023 | 5,052,869.99 | 163216 |
| 2024 | 4,325,613.39 | 136386 |
Which channel has the lowest cost per acquisition (spend ÷ conversions)?
SQL
SELECT channel, ROUND(CAST(SUM(spend) AS DECIMAL(18,6)) / NULLIF(SUM(conversions), 0), 2) AS cpa
FROM marketing_campaigns
GROUP BY channel
ORDER BY cpa, channel;Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.
Result
| channel | cpa |
|---|---|
| 20.28 | |
| Affiliate | 21.37 |
| Paid search | 28.74 |
| Social | 37.50 |
| Display | 52.09 |
| Video | 55.67 |
SQL Server differences used here
- DATEDIFF(day, earlier, later) returns the number of days between two dates.
- Integer divided by integer truncates in SQL Server, so the numerator is cast to DECIMAL first.
- SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
- YEAR(date) returns the year as a number.