Ad Campaign Quartile Analysis

This SQL query performs a quartile-based analysis on ad campaign data from the ads_campaign table by cleaning raw data, computing derived metrics to divide campaigns into four spend quartiles, and aggregating key performance indicators (KPIs) for each quartile. The query uses Common Table Expressions (CTEs) for modularity.

Step 1: Data Cleaning

Loads and cleans the raw data from the ads_campaign table to ensure consistency for calculations.

WITH BaseData AS 
(
    SELECT 
        Campaign_ID,
        Channel_Used,
        Campaign_Goal,
        Date,
        Duration,
        Clicks,
        Impressions,
        Spend,
        CAST(REPLACE(Conversion_Rate, '%', '') AS FLOAT) / 100.0 AS Conversion_Rate_Cleaned,
        CAST(REPLACE(ROI, ',', '.') AS FLOAT) AS ROI_Cleaned -- Added to handle comma-separated decimals
    FROM ads_campaign
)

• Key Actions:
  • Selects core columns like Campaign_ID, Channel_Used, Campaign_Goal, Date, Duration, Clicks, Impressions, and Spend
  • Cleans Conversion_Rate: Removes the '%' symbol, casts it to FLOAT, and divides by 100 to convert to a decimal (e.g., 0.05)
  • Cleans ROI: Replaces commas with dots ('1,5' becomes '1.5' for decimal handling in regions using comma separators), then casts to FLOAT

Step 2: Quartile assignment

Builds on the cleaned data by adding calculated fields and segmenting campaigns into quartiles based on spend

SpendQuartiles AS (
    SELECT 
        *,
        NTILE(4) OVER (ORDER BY Spend) AS Spend_Quartile, 
        (Clicks * Conversion_Rate_Cleaned / 100.0) AS Conversions,
        (Spend * ROI_Cleaned) AS Profit,
        (Clicks * 100.0 / Impressions) AS CTR
    FROM BaseData
)

• Key Actions:
  • NTILE(4) OVER (ORDER BY Spend): Divides the campaigns into 4 equal groups ranked by ascending Spend values. Quartile 1 represents the lowest spend (25% of campaigns), while Quartile 4 represents the highest
  • Conversions: Estimates the number of conversions using (Clicks * Conversion_Rate_Cleaned / 100.0)
  • Profit: Calculates profit as (Spend * ROI_Cleaned), assuming ROI is a multiplier (e.g., ROI of 2.0 means 2x return on spend)
  • CTR (Click-Through Rate): Computes as (Clicks * 100.0 / Impressions) to get the percentage of impressions that resulted in clicks
• Output: Enhanced dataset with quartiles and metrics, where * includes all columns from BaseData

Step 3: Aggregation and Final output

Summarises the data by quartile to reveal performance trends across spend levels

SELECT 
    Spend_Quartile,
    COUNT(DISTINCT Campaign_ID) AS Campaign_Count,
    AVG(ROI_Cleaned) AS Avg_ROI,
    AVG(CTR) AS Avg_CTR,
    AVG(Profit) AS Avg_Profit,
    AVG(Spend) AS Avg_Spend
FROM SpendQuartiles
GROUP BY Spend_Quartile
ORDER BY Spend_Quartile;

• Key Actions:
  • GROUP BY Spend_Quartile: Aggregates rows for each quartile (1–4)
  • COUNT(DISTINCT Campaign_ID) AS Campaign_Count: Counts unique campaigns per quartile
  • AVG(...): Calculates the average of ROI, CTR, Profit, and Spend per quartile
  • ORDER BY Spend_Quartile: Sorts results from lowest (1) to highest (4) spend quartile
• Output: A summary table with one row per quartile, showing how KPIs vary by spend level (e.g., higher-spend campaigns might have better ROI)

Output

This helps identify if higher-spend campaigns (higher quartiles) yield better returns, informing budget allocation