-
Businesses invest significantly in paid marketing campaigns across multiple digital channels to attract customers and drive revenue. However, high advertising spend and website traffic do not always translate into successful conversions or profitable outcomes. Without analyzing campaign performance, customer behavior, and conversion metrics, marketing teams struggle to identify which campaigns deliver the best results and where budget is being wasted.
This project addresses that challenge by analyzing paid marketing data to evaluate campaign effectiveness, traffic quality, customer acquisition, conversion rates, and return on ad spend (ROAS). The insights generated help businesses optimize marketing strategies, allocate budgets more effectively, improve campaign performance, and make data-driven decisions that maximize business growth.
-
The objective of this project is to analyze the effectiveness of paid marketing campaigns by leveraging SQL to transform raw marketing data into actionable business insights. The analysis focuses on evaluating campaign performance, website traffic, customer acquisition, conversion rates, advertising spend, and revenue generation to measure overall marketing efficiency.
By connecting campaign, traffic, customer, and order data, this project identifies high-performing marketing channels, uncovers opportunities to optimize conversion funnels, evaluates Return on Ad Spend (ROAS), and supports data-driven decision-making for improving marketing performance and maximizing business growth.
The project consists of four connected datasets representing the complete customer journey.
| Table | Description |
|---|---|
| Campaign | Marketing campaign information including platform, targeting strategy, and advertising spend |
| Traffic | Website visitor sessions, bounce behavior, page views, and devices |
| Customers | Customer registration information |
| Orders | Purchase transactions, discounts, returns, refunds, and revenue |
Campaign ▶ Traffic ▶ Customers ▶ Orders
SELECT
MIN(DATE(Order_Timestamp)) AS FirstDate,
MAX(DATE(Order_Timestamp)) AS LastDate,
DATEDIFF(MAX(DATE(Order_Timestamp)), MIN(DATE(Order_Timestamp))) AS Duration
FROM Orders;- Using (Scaler Sub Query)
SELECT
-- 1. Total Ad Spend
(SELECT ROUND(SUM(Ad_Spend_USD), 2) FROM Campaign) AS Total_Ad_Spend_USD,
-- 2. Total Traffic (Sessions)
(SELECT COUNT(SessionID) FROM Traffic) AS Total_Traffic,
-- 3. Total Bounces (People who left immediately)
(SELECT SUM(Bounce_Flag) FROM Traffic) AS Total_Bounces,
-- 4. Conversion Rate (Orders divided by Traffic)
ROUND(
( (SELECT COUNT(OrderID) FROM Orders) /
(SELECT COUNT(SessionID) FROM Traffic) ) * 100
, 2) AS Conversion_Rate_Pct,
-- 5. Total Customers (Unique accounts created)
(SELECT COUNT(DISTINCT CustomerID) FROM Customers) AS Total_Customers,
-- 6. Total Orders Placed
(SELECT COUNT(OrderID) FROM Orders) AS Total_Orders;- Total Traffic, Total Bounce, Conversion rate, Total Customers, Total Orders, Total Kept, Returned and Refunded Orders
SELECT
cm.Platform,
COUNT(t.SessionID) AS Traffic,
SUM(t.Bounce_Flag) AS Total_Bounce,
SUM(CASE WHEN t.Pages_Viewed > 1 THEN '1' ELSE '0' END) AS Conversion,
COUNT(DISTINCT o.CustomerID) AS Total_Customers,
COUNT(o.OrderID) AS Total_Order,
SUM(CASE WHEN Return_Status = 'Kept' THEN '1' ELSE '0' END) AS Total_Delivered,
SUM(CASE WHEN Return_Status IN ('Returned', 'Refunded') THEN '1' ELSE '0' END) AS Return_Refunded
FROM Campaign cm
JOIN Traffic t ON cm.CampaignID = t.CampaignID
LEFT JOIN Orders o ON t.SessionID = o.SessionID
GROUP BY cm.Platform
ORDER BY Total_Delivered DESC;- Total Traffic, Total Bounce, Conversion rate, Total Customers, Total Orders, Total Kept, Returned and Refunded Orders
SELECT
(CASE WHEN t.CampaignID IS NULL THEN 'Organic Traffic' ELSE 'Paid Traffic' END) AS Traffic,
COUNT(t.SessionID) AS Total_Traffic,
SUM(t.Bounce_Flag) AS Total_Bounce,
SUM(CASE WHEN t.Pages_Viewed > 1 THEN '1' ELSE '0' END) AS Total_Conversion,
COUNT(DISTINCT o.CustomerID) AS Unique_Cust,
COUNT(o.OrderID) AS Total_Order,
SUM(o.Cart_Value) AS Revenue,
SUM(CASE WHEN Return_Status = 'Kept' THEN 1 ELSE 0 END) AS Total_Delivered,
SUM(CASE WHEN Return_Status IN ('Returned', 'Refunded') THEN 1 ELSE 0 END) AS Return_Refunded
FROM Traffic t
LEFT JOIN Orders o ON t.SessionID = o.SessionID
GROUP BY (CASE WHEN t.CampaignID IS NULL THEN 'Organic Traffic' ELSE 'Paid Traffic' END);- Using Sub_Query Method
-- Main_Query
SELECT
platform,
SUM(Ad_Spend_USD) AS Ad_Spend,
SUM(t.Campaign_Click) AS Campaign_Click,
ROUND(SUM(Ad_Spend_USD) / SUM(t.Campaign_Click), 2) AS Cost_Per_Click_CPC,
SUM(t.Campaign_Click) / SUM(Ad_Spend_USD) AS Visits_Per_Dollar
FROM Campaign c
LEFT JOIN
-- Sub_Query
(SELECT
CampaignID,
COUNT(SessionID) AS Campaign_Click
FROM Traffic t
WHERE CampaignID IS NOT NULL
GROUP BY CampaignID) t
ON c.CampaignID = t.CampaignID
GROUP BY c.platform;- Using CTE Method
-- Sum Ad_Spend on different platform
WITh Platform_budgets AS (
SELECT
Platform,
SUM(Ad_Spend_USD) AS Ad_Spend
FROM Campaign
GROUP BY Platform),
-- Count total traffic and click
Platform_Traffic AS (
SELECT
c.Platform,
COUNT(t.SessionID) AS Campaign_Click
FROM Campaign c
JOIN Traffic t
ON t.CampaignID = c.CampaignID
GROUP BY c.Platform)
-- Merge two table to show the complete result
SELECT
b.Platform,
b.Ad_Spend,
t.Campaign_Click,
ROUND(b.Ad_Spend / t.Campaign_Click, 2) AS Cost_Per_Click_CPC,
ROUND(t.Campaign_Click / b.Ad_Spend, 2) AS Visit_Per_USD
FROM Platform_budgets b
JOIN Platform_Traffic t
ON b.Platform = t.Platform;6. Campaign Fatigue (Bounce Rates): Which specific campaigns have a Bounce Rate higher than 60%, indicating we are targeting the wrong audience?
SELECT
c.Platform,
SUM(CASE WHEN t.Bounce_Flag = 1 THEN '1' ELSE 0 END) AS Bounce,
COUNT(t.SessionID) AS Traffic,
COUNT(t.SessionID) / SUM(CASE WHEN t.Bounce_Flag = 1 THEN '1' ELSE 0 END) * 100 AS Bounce_Rate,
SUM(CASE WHEN t.Bounce_Flag = 1 THEN '1' ELSE 0 END) / COUNT(t.SessionID) * 100 AS B_Rate
FROM Traffic t
JOIN Campaign c
ON t.CampaignID = c.CampaignID
GROUP BY c.Platform;7. Traffic Quality: What is the average number of pages viewed per session, segmented by the device the user is on?
SELECT
Device,
SessionID,
AVG(Pages_Viewed) OVER ( PARTITION BY Device ) AS Avg_Page_Visit
FROM Traffic
GROUP BY Device,
SessionID
ORDER BY AVG(Pages_Viewed) DESC;-- This Showing all the result of Paid campaign
WITH no_order AS (
SELECT
CustomerID,
Pages_Viewed
FROM traffic
WHERE Pages_Viewed > 4 AND CampaignID IS NOT NULL AND
CustomerID NOT IN (SELECT CustomerID FROM Orders))
SELECT COUNT( DISTINCT CustomerID) AS Lost_Customers FROM no_order;- This Query is Showing all the customer who come from organic and who come fro paid campaign who visit more than 4 pages but didn't order
WITH null_Order AS (
SELECT
t.CustomerID,
t.Pages_Viewed
FROM Traffic t
LEFT JOIN Orders o
ON t.CustomerID = o.CustomerID
WHERE t.Pages_Viewed > 4 AND
o.OrderID IS NULL)
SELECT COUNT(DISTINCT CustomerID) AS Lost_Orders FROM null_order;9. Understanding why people buy (or why they leave). The Device Funnel: What is the exact Session-to-Order conversion rate for Mobile vs. Desktop vs. Tablet?
SELECT
t.Device,
COUNT(t.SessionID) AS Total_Traffic,
COUNT(o.OrderID) AS Total_Orders,
ROUND(COUNT(o.OrderID) / COUNT(t.SessionID) * 100, 2) AS Conversion_Rate
FROm Traffic t
LEFT JOIN Orders o
ON t.SessionID = o.SessionID
GROUP BY Device;SELECT
CASE WHEN Discount_Code IS NULL THEN 'Organic' ELSE 'Discounted' END AS Order_Type,
COUNT(OrderID) AS Orders,
(SELECT COUNT(OrderID) FROM Orders) AS Total_Order,
ROUND(COUNT(OrderID) / (SELECT COUNT(OrderID) FROM Orders) * 100, 2) AS Conversion_Rate
FROM Orders
GROUP BY CASE WHEN Discount_Code IS NULL THEN 'Organic' ELSE 'Discounted' END;11. Time-to-Activation: How many days, on average, does it take for a newly signed-up user to make their first website visit?
SELECT
DISTINCT c.CustomerID,
c.SignupDate,
DATE(MIN(o.Order_Timestamp)) AS Fisrt_Purchase,
DATEDIFF(DATE(MIN(o.Order_Timestamp)), c.SignupDate) AS Duration
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
ORDER BY DATEDIFF(DATE(MIN(o.Order_Timestamp)), c.SignupDate);12. True Return on Ad Spend (ROAS): What is the exact ROAS multiplier for Meta, Google, and TikTok, strictly excluding refunded items?
-- Calculating Platform Ad Spend
WITH Platform_Spend AS (
SELECT
Platform,
SUM(Ad_Spend_USD) AS Ad_Spend
FROM Campaign
GROUP BY Platform),
-- Calculating platform Net Revenue
Platform_Revenue AS (
SELECT
c.Platform,
SUM(o.Cart_Value) AS Gross_Revenue,
SUM(CASE WHEN Return_Status = 'Kept' THEN o.Cart_Value ELSE 0 END) AS Net_Revenue
FROM Campaign c
JOIN Traffic t
ON c.CampaignID = t.CampaignID
JOIN Orders o ON
t.SessionID = o.SessionID
GROUP BY c.Platform)
-- Merge Both CTE Table to get Final result
SELECT
s.Platform,
s.Ad_Spend,
r.Net_Revenue,
r.Gross_Revenue,
r.Net_Revenue - s.Ad_Spend AS ROAS1,
s.Ad_Spend / r.Net_Revenue AS ROAS
FROM Platform_Spend s
JOIN Platform_Revenue r
ON s.Platform = r.Platform
GROUP BY s.Platform;13. Targeting Profitability: Which ad targeting strategy (Lookalike, Retargeting, Broad) generates the highest net revenue?
-- Calculating total campaign and spend
WITH Targeting_Spend AS (
SELECT
Targeting_Type,
COUNT(CampaignID) AS Total_Campaign,
SUM(Ad_Spend_USD) AS Ad_Spend
FROM Campaign
GROUP BY Targeting_Type),
-- Calculating Revenue, Orders by targeting Types
Targeting_Rev AS (
SELECT
c.Targeting_Type,
SUM(CASE WHEN o.Return_Status = 'Kept' THEN 1 ELSE 0 END) AS Total_Order,
SUM(CASE WHEN o.Return_Status = 'Kept' THEN o.Cart_Value ELSE 0 END) AS Net_Revenue
FROM Campaign c
JOIN Traffic t ON c.CampaignID = t.CampaignID
JOIN Orders o ON o.SessionID = t.SessionID
GROUP BY c.Targeting_Type)
-- Combine both to get final result Total Campaign, Ad Spend, Orders and net revenue
SELECT
s.Targeting_Type,
s.Total_Campaign,
s.Ad_Spend,
r.Total_Order,
r.Net_Revenue
FROM Targeting_Spend s
JOIN Targeting_Rev r ON s.Targeting_Type = r.Targeting_Type
ORDER BY r.Net_Revenue DESC;Month-over-Month (MoM) Growth: What is our rolling 30-day gross revenue trend over the dataset's lifespan?
SELECT
DATE_FORMAT(Order_Timestamp, '%Y-%m') AS Sales_Month,
SUM(Cart_Value) AS Current_Month_Revenue,
LAG(SUM(Cart_Value)) OVER ( ORDER BY DATE_FORMAT(Order_Timestamp, '%Y-%m')) AS Pre_Month,
SUM(Cart_Value) - LAG(SUM(Cart_Value)) OVER ( ORDER BY DATE_FORMAT(Order_Timestamp, '%Y-%m')) AS Diff,
((SUM(Cart_Value) - LAG(SUM(Cart_Value)) OVER ( ORDER BY DATE_FORMAT(Order_Timestamp, '%Y-%m'))) /
LAG(SUM(Cart_Value)) OVER ( ORDER BY DATE_FORMAT(Order_Timestamp, '%Y-%m'))) * 100 AS MoM_Growth
FROM Orders
GROUP BY DATE_FORMAT(Order_Timestamp, '%Y-%m')
ORDER BY DATE_FORMAT(Order_Timestamp, '%Y-%m');- Another Query Using CTEs
WITH Monthly_Sales AS (
SELECT
DATE_FORMAT(Order_Timestamp, '%Y-%m' ) AS Sales_Month,
SUM(Cart_Value) AS Current_Month_Rev
FROM Orders
GROUP BY DATE_FORMAT(Order_Timestamp, '%Y-%m' )
),
MoM_Comparision AS (
SELECT
Sales_Month,
Current_Month_Rev,
LAG(Current_Month_Rev) OVER ( ORDER BY Sales_Month) AS Pre_Month_Rev
FROM Monthly_Sales
)
SELECT
Sales_Month,
Current_Month_Rev,
Pre_Month_Rev,
ROUND(
((Current_Month_Rev - Pre_Month_Rev) / Pre_Month_rev) * 100, 2) AS MoM_Change
FROM MoM_Comparision
WHERE Pre_Month_Rev IS NOT NULL
ORDER BY Sales_Month;
