Marketing campaigns sample database for SQLite
A ready-to-run SQLite script with budget, spend, impressions, clicks, CPA and ROAS per campaign. Tested on SQLite 3.37.2. Updated 19 November 2026.
Marketing campaigns database for SQLite
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 SQLite script: one table and 1,000 rows, with column types chosen for SQLite, 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 SQLite 3.37.2 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
Command line
sqlite3 practice.db < marketing-campaigns-sqlite.sql
sqlite3 practice.db ".tables"Or skip the script
Download marketing-campaigns.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 marketing-campaigns.sqlite
Tables and columns
marketing_campaigns 1,000 rows
| Column | SQLite type |
|---|---|
| campaign_id | TEXT |
| campaign_name | TEXT |
| channel | TEXT |
| start_date | TEXT |
| end_date | TEXT |
| budget | REAL |
| spend | REAL |
| impressions | INTEGER |
| clicks | INTEGER |
| ctr | REAL |
| conversions | INTEGER |
| cpa | REAL |
| revenue | REAL |
| roas | REAL |
Practice questions
answers from SQLite 3.37.2
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(1.0 * SUM(revenue) / NULLIF(SUM(spend), 0), 2) AS roas
FROM marketing_campaigns
GROUP BY channel
ORDER BY roas DESC, channel;Multiplying by 1.0 forces decimal division instead of integer division.
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 campaign_id, campaign_name, roas
FROM marketing_campaigns
ORDER BY roas DESC, campaign_id
LIMIT 5;LIMIT n at the end returns the first n rows.
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(julianday(end_date) - julianday(start_date) AS INTEGER)), 2) AS avg_days
FROM marketing_campaigns
GROUP BY channel
ORDER BY avg_days DESC, channel;julianday(b) - julianday(a) gives 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 CAST(strftime('%Y', start_date) AS INTEGER) AS start_year, ROUND(SUM(spend), 2) AS spend, SUM(conversions) AS conversions
FROM marketing_campaigns
GROUP BY CAST(strftime('%Y', start_date) AS INTEGER)
ORDER BY start_year;strftime('%Y', date) returns text, so it is cast to INTEGER.
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(1.0 * SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cpa
FROM marketing_campaigns
GROUP BY channel
ORDER BY cpa, channel;Multiplying by 1.0 forces decimal division instead of integer division.
Result
| channel | cpa |
|---|---|
| 20.28 | |
| Affiliate | 21.37 |
| Paid search | 28.74 |
| Social | 37.50 |
| Display | 52.09 |
| Video | 55.67 |
SQLite differences used here
- julianday(b) - julianday(a) gives the number of days between two dates.
- Multiplying by 1.0 forces decimal division instead of integer division.
- LIMIT n at the end returns the first n rows.
- strftime('%Y', date) returns text, so it is cast to INTEGER.