🚀 SQL Project Series #22
Digital Marketing Campaign Analytics 📢
Analyze marketing campaigns, customers, leads, conversions, and advertising performance using SQL to optimize ROI and improve campaign effectiveness.
🎯 Business Objectives
✅ Analyze marketing campaign performance
✅ Track leads and conversions
✅ Measure campaign ROI
✅ Optimize advertising spend
✅ Analyze customer acquisition
✅ Measure conversion funnel
✅ Evaluate marketing channels
✅ Build executive marketing dashboards
📂 Step 1: Create Database
CREATE DATABASE marketing_analytics_db;
USE marketing_analytics_db;
📂 Step 2: Create Campaigns Table
CREATE TABLE campaigns (
campaign_id INT PRIMARY KEY,
campaign_name VARCHAR(100),
marketing_channel VARCHAR(50),
campaign_start DATE,
campaign_end DATE,
budget DECIMAL(12,2)
);
📂 Step 3: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50),
acquisition_channel VARCHAR(50),
signup_date DATE
);
📂 Step 4: Create Leads Table
CREATE TABLE leads (
lead_id INT PRIMARY KEY,
campaign_id INT,
customer_id INT,
lead_date DATE,
lead_status VARCHAR(30),
conversion_date DATE,
FOREIGN KEY (campaign_id)
REFERENCES campaigns(campaign_id),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
📂 Step 5: Insert Sample Campaigns
INSERT INTO campaigns VALUES
(101,'Summer Sale','Google Ads','2025-01-01','2025-01-31',500000),
(102,'New Year Offer','Facebook Ads','2025-01-05','2025-01-25',350000),
(103,'Email Promotion','Email','2025-01-10','2025-01-30',100000),
(104,'Influencer Campaign','Instagram','2025-01-15','2025-02-05',450000);
📂 Step 6: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul Sharma','Mumbai','Google Ads','2025-01-08'),
(2,'Priya Verma','Delhi','Facebook Ads','2025-01-10'),
(3,'Amit Patel','Pune','Email','2025-01-12'),
(4,'Sneha Joshi','Bangalore','Instagram','2025-01-18'),
(5,'Rohan Gupta','Hyderabad','Google Ads','2025-01-22');
📂 Step 7: Insert Sample Leads
INSERT INTO leads VALUES
(1001,101,1,'2025-01-03','Converted','2025-01-08'),
(1002,102,2,'2025-01-06','Converted','2025-01-10'),
(1003,103,3,'2025-01-12','Open',NULL),
(1004,104,4,'2025-01-18','Converted','2025-01-21'),
(1005,101,5,'2025-01-22','Lost',NULL);
🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ Joins
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ CTEs
✔ Window Functions
✔ Date Functions
✔ Marketing KPI Analysis
📊 Business KPIs You Can Build
📈 Total Campaigns
📈 Total Leads
📈 Total Conversions
📈 Conversion Rate
📈 Cost Per Lead (CPL)
📈 Cost Per Acquisition (CPA)
📈 Campaign ROI
📈 Campaign Spend vs Revenue
📈 Leads by Marketing Channel
📈 Conversions by Marketing Channel
📈 Customer Acquisition by Channel
📈 Monthly Lead Trend
📈 Monthly Conversion Trend
📈 Average Conversion Time
📈 Top Performing Campaigns
📈 Lowest Performing Campaigns
📈 Budget Utilization
📈 Revenue by Campaign
📈 Revenue by Marketing Channel
📈 Customer Acquisition Cost (CAC)
📈 Funnel Drop-off Analysis
📈 Channel-wise Performance Dashboard
📈 Campaign Performance Dashboard
📈 Marketing Executive Dashboard
🎯 This project reflects real-world SQL analysis performed by Marketing Analysts, Growth Analysts, Performance Marketing teams, Product Analysts, and Business Intelligence professionals to optimize campaign performance and maximize return on investment.
Double Tap ❤️ For More
