DataBrighten

    Marketing campaigns sample database for SQLite

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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

    ColumnSQLite type
    campaign_idTEXT
    campaign_nameTEXT
    channelTEXT
    start_dateTEXT
    end_dateTEXT
    budgetREAL
    spendREAL
    impressionsINTEGER
    clicksINTEGER
    ctrREAL
    conversionsINTEGER
    cpaREAL
    revenueREAL
    roasREAL

    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

    channelcampaignsspendrevenueroas
    Email1571,314,413.046,896,058.465.25
    Affiliate1621,609,714.328,018,193.684.98
    Paid search1721,587,707.195,817,976.683.66
    Social1561,715,189.904,893,437.332.85
    Display1711,502,392.362,958,804.941.97
    Video1821,649,066.573,172,947.981.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_idcampaign_nameroas
    MC-0828Affiliate - Spring Sale - Q2 202312.32
    MC-1218Email - New Product - Q4 202311.36
    MC-0771Affiliate - Spring Sale - Q3 202411.10
    MC-0779Affiliate - Spring Sale - Q3 202310.91
    MC-1485Email - Holiday - Q3 202310.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

    channelavg_days
    Affiliate37.63
    Social37.46
    Display36.74
    Paid search36.24
    Video35.79
    Email35.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_yearspendconversions
    20235,052,869.99163216
    20244,325,613.39136386
    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

    channelcpa
    Email20.28
    Affiliate21.37
    Paid search28.74
    Social37.50
    Display52.09
    Video55.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.