DataBrighten

    Marketing campaigns sample database for MySQL

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

    A ready-to-run MySQL script with budget, spend, impressions, clicks, CPA and ROAS per campaign. Tested on MySQL 8.4.13. Updated 19 November 2026.

    textnumberdateyes/no

    Marketing campaigns database for MySQL

    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 MySQL script: one table and 1,000 rows, with column types chosen for MySQL, 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 MySQL 8.4.13 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

    mysql client

    mysql -u root -p -e "CREATE DATABASE practice"
    mysql -u root -p practice < marketing-campaigns-mysql.sql

    MySQL Workbench

    File > Open SQL Script, choose the database with USE practice; then Execute.

    Tables and columns

    marketing_campaigns 1,000 rows

    ColumnMySQL type
    campaign_idVARCHAR(10)
    campaign_nameVARCHAR(50)
    channelVARCHAR(20)
    start_dateDATE
    end_dateDATE
    budgetDECIMAL(8,2)
    spendDECIMAL(10,2)
    impressionsINT
    clicksINT
    ctrDECIMAL(8,4)
    conversionsINT
    cpaDECIMAL(8,2)
    revenueDECIMAL(11,2)
    roasDECIMAL(7,2)

    Practice questions

    answers from MySQL 8.4.13

    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;

    1.0 * a / b gives a decimal result.

    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(DATEDIFF(end_date, start_date) AS DECIMAL(18,4))), 2) AS avg_days
    FROM marketing_campaigns
    GROUP BY channel
    ORDER BY avg_days DESC, channel;

    DATEDIFF(later, earlier) returns 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 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_yearspendconversions
    20235,052,869.99163,216.00
    20244,325,613.39136,386.00
    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;

    1.0 * a / b gives a decimal result.

    Result

    channelcpa
    Email20.28
    Affiliate21.37
    Paid search28.74
    Social37.50
    Display52.09
    Video55.67

    MySQL differences used here

    • DATEDIFF(later, earlier) returns the number of days between two dates.
    • 1.0 * a / b gives a decimal result.
    • LIMIT n at the end returns the first n rows.
    • YEAR(date) returns the year as a number.