Campaign ROI Dashboard

Project Overview

Dataset Description

SQL Analysis

1. Spend vs. Revenue by Channel

Aggregate total spend and total revenue per channel to calculate net ROI.

SELECT
    c.channel,
    SUM(s.amount)                             AS total_spend,
    SUM(cv.revenue)                           AS total_revenue,
    SUM(cv.revenue) - SUM(s.amount)           AS net_profit,
    ROUND(
        (SUM(cv.revenue) - SUM(s.amount)) * 100.0
        / NULLIF(SUM(s.amount), 0), 2
    )                                         AS roi_pct
FROM campaigns c
JOIN ad_spend s   ON s.campaign_id = c.campaign_id
JOIN conversions cv ON cv.campaign_id = c.campaign_id
GROUP BY c.channel
ORDER BY roi_pct DESC;

2. Cost Per Acquisition (CPA) and Customer Acquisition Cost (CAC)

Calculate acquisition efficiency metrics per channel and campaign.

-- CPA by channel
SELECT
    c.channel,
    COUNT(DISTINCT cv.conversion_id)          AS total_conversions,
    SUM(s.amount)                             AS total_spend,
    ROUND(
        SUM(s.amount) / NULLIF(COUNT(DISTINCT cv.conversion_id), 0), 2
    )                                         AS cpa
FROM campaigns c
JOIN ad_spend s   ON s.campaign_id = c.campaign_id
JOIN conversions cv ON cv.campaign_id = c.campaign_id
GROUP BY c.channel
ORDER BY cpa ASC;

-- CAC: cost per NEW customer (first-time purchasers only)
WITH first_purchase AS (
    SELECT
        user_id,
        MIN(conversion_date) AS first_date,
        MIN(campaign_id)     AS first_campaign_id
    FROM conversions
    GROUP BY user_id
)
SELECT
    c.channel,
    COUNT(DISTINCT fp.user_id)                AS new_customers,
    SUM(s.amount)                             AS total_spend,
    ROUND(
        SUM(s.amount) / NULLIF(COUNT(DISTINCT fp.user_id), 0), 2
    )                                         AS cac
FROM first_purchase fp
JOIN campaigns c  ON c.campaign_id = fp.first_campaign_id
JOIN ad_spend s   ON s.campaign_id = c.campaign_id
    AND s.spend_date BETWEEN c.start_date AND c.end_date
GROUP BY c.channel
ORDER BY cac ASC;

3. Campaign Comparison Within Channel

Rank individual campaigns by ROI within each channel to find top performers.

WITH campaign_roi AS (
    SELECT
        c.campaign_id,
        c.campaign_name,
        c.channel,
        SUM(s.amount)       AS spend,
        SUM(cv.revenue)     AS revenue,
        ROUND(
            (SUM(cv.revenue) - SUM(s.amount)) * 100.0
            / NULLIF(SUM(s.amount), 0), 2
        )                   AS roi_pct
    FROM campaigns c
    JOIN ad_spend s    ON s.campaign_id = c.campaign_id
    JOIN conversions cv ON cv.campaign_id = c.campaign_id
    WHERE c.status = 'completed'
    GROUP BY c.campaign_id, c.campaign_name, c.channel
)
SELECT
    channel,
    campaign_name,
    spend,
    revenue,
    roi_pct,
    RANK() OVER (PARTITION BY channel ORDER BY roi_pct DESC) AS channel_rank
FROM campaign_roi
ORDER BY channel, channel_rank;

Python Analysis

1. ROI Visualization by Channel

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

# Load data (replace with your DB connection)
spend_df = pd.read_csv('ad_spend.csv')
conv_df  = pd.read_csv('conversions.csv')
camp_df  = pd.read_csv('campaigns.csv')

# Merge datasets
merged = spend_df.merge(camp_df[['campaign_id', 'channel']], on='campaign_id')
revenue = conv_df.groupby('campaign_id')['revenue'].sum().reset_index()
merged = merged.groupby('channel')['amount'].sum().reset_index()
merged = merged.merge(
    conv_df.merge(camp_df[['campaign_id', 'channel']], on='campaign_id')
           .groupby('channel')['revenue'].sum().reset_index(),
    on='channel'
)
merged['roi_pct'] = ((merged['revenue'] - merged['amount']) / merged['amount']) * 100

# Plot
fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# Bar chart: spend vs revenue
x = np.arange(len(merged))
axes[0].bar(x - 0.2, merged['amount'], 0.4, label='Spend', color='#e74c3c')
axes[0].bar(x + 0.2, merged['revenue'], 0.4, label='Revenue', color='#2ecc71')
axes[0].set_xticks(x)
axes[0].set_xticklabels(merged['channel'], rotation=15)
axes[0].set_title('Spend vs Revenue by Channel')
axes[0].legend()
axes[0].yaxis.set_major_formatter(plt.FuncFormatter(lambda v, _: f'${v:,.0f}'))

# ROI percentage bar
colors = ['#3498db' if r > 0 else '#e74c3c' for r in merged['roi_pct']]
axes[1].barh(merged['channel'], merged['roi_pct'], color=colors)
axes[1].set_xlabel('ROI %')
axes[1].set_title('ROI Percentage by Channel')
axes[1].axvline(x=0, color='black', linewidth=0.8)

plt.tight_layout()
plt.savefig('roi_by_channel.png', dpi=150)
plt.show()

2. Attribution Modeling

# Simple last-touch vs first-touch attribution comparison
user_journeys = conv_df.merge(camp_df[['campaign_id', 'channel']], on='campaign_id')
user_journeys = user_journeys.sort_values(['user_id', 'conversion_date'])

# First-touch attribution
first_touch = user_journeys.groupby('user_id').first().reset_index()
ft_revenue = first_touch.groupby('channel')['revenue'].sum().reset_index()
ft_revenue.columns = ['channel', 'first_touch_revenue']

# Last-touch attribution
last_touch = user_journeys.groupby('user_id').last().reset_index()
lt_revenue = last_touch.groupby('channel')['revenue'].sum().reset_index()
lt_revenue.columns = ['channel', 'last_touch_revenue']

# Compare models
attribution = ft_revenue.merge(lt_revenue, on='channel')
attribution['delta'] = attribution['last_touch_revenue'] - attribution['first_touch_revenue']

fig, ax = plt.subplots(figsize=(10, 5))
x = np.arange(len(attribution))
ax.bar(x - 0.2, attribution['first_touch_revenue'], 0.4, label='First-Touch', color='#9b59b6')
ax.bar(x + 0.2, attribution['last_touch_revenue'], 0.4, label='Last-Touch', color='#f39c12')
ax.set_xticks(x)
ax.set_xticklabels(attribution['channel'], rotation=15)
ax.set_title('Attribution Model Comparison')
ax.legend()
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda v, _: f'${v:,.0f}'))
plt.tight_layout()
plt.savefig('attribution_comparison.png', dpi=150)
plt.show()

3. Budget Allocation Optimization

from scipy.optimize import minimize

# Current channel metrics
channels = merged['channel'].tolist()
current_spend = merged['amount'].values
current_revenue = merged['revenue'].values
roi_rates = current_revenue / current_spend  # revenue per dollar spent

total_budget = current_spend.sum()

# Objective: maximize total revenue given budget constraint
def neg_revenue(allocation):
    return -np.sum(allocation * roi_rates)

# Constraints: total spend = budget, each channel gets at least 5%
constraints = [{'type': 'eq', 'fun': lambda x: np.sum(x) - total_budget}]
bounds = [(total_budget * 0.05, total_budget * 0.60) for _ in channels]

result = minimize(neg_revenue, current_spend, bounds=bounds, constraints=constraints)
optimal = result.x

# Display results
alloc_df = pd.DataFrame({
    'Channel': channels,
    'Current Spend': current_spend,
    'Optimal Spend': optimal,
    'Change %': ((optimal - current_spend) / current_spend * 100).round(1)
})
print(alloc_df.to_string(index=False))
print(f"\nExpected revenue increase: ${-result.fun - current_revenue.sum():,.2f}")

Excel Dashboard

Key Insights

Knowledge Check