Power BI - Essential SQL Queries for Marketing

Power BI - Essential SQL Queries for Marketing 

Campaign Performance

• Total leads generated by campaign:

SELECT CampaignID, CampaignName, COUNT(LeadID) AS TotalLeads
FROM Leads
GROUP BY CampaignID, CampaignName;

• Conversion rate by campaign:

SELECT CampaignID,
       SUM(CASE WHEN Status = 'Converted' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS ConversionRate
FROM Leads
GROUP BY CampaignID;

• Revenue generated per campaign:

SELECT CampaignID, SUM(OrderAmount) AS CampaignRevenue
FROM Orders
GROUP BY CampaignID;

---

🎯 Customer Segmentation

• Leads by source (social, email, referral):

SELECT LeadSource, COUNT(*) AS LeadCount
FROM Leads
GROUP BY LeadSource;

• Customer demographics by region:

SELECT Region, COUNT(CustomerID) AS CustomerCount
FROM Customers
GROUP BY Region;

• High-value customers (spend > 50,000):

SELECT CustomerID, SUM(OrderAmount) AS TotalSpend
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) > 50000;

---

ROI & Spend Analysis

• Marketing spend vs revenue:

SELECT CampaignID, SUM(Spend) AS TotalSpend, SUM(OrderAmount) AS TotalRevenue,
       (SUM(OrderAmount) - SUM(Spend)) AS ROI
FROM CampaignSpend cs
JOIN Orders o ON cs.CampaignID = o.CampaignID
GROUP BY CampaignID;

• Cost per lead (CPL):

SELECT CampaignID, SUM(Spend) / COUNT(LeadID) AS CostPerLead
FROM CampaignSpend cs
JOIN Leads l ON cs.CampaignID = l.CampaignID
GROUP BY CampaignID;

• Customer acquisition cost (CAC):

SELECT CampaignID, SUM(Spend) / COUNT(DISTINCT CustomerID) AS CAC
FROM CampaignSpend cs
JOIN Orders o ON cs.CampaignID = o.CampaignID
GROUP BY CampaignID;

---

Engagement Metrics

• Email open rate:

SELECT CampaignID,
       SUM(CASE WHEN Opened = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS OpenRate
FROM EmailCampaigns
GROUP BY CampaignID;

• Click-through rate (CTR):

SELECT CampaignID,
       SUM(CASE WHEN Clicked = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS CTR
FROM EmailCampaigns
GROUP BY CampaignID;

• Social media engagement:

SELECT PostID, SUM(Likes) AS TotalLikes, SUM(Shares) AS TotalShares, SUM(Comments) AS TotalComments
FROM SocialPosts
GROUP BY PostID;

---

Key Notes

• Indexes: Use on CampaignID, CustomerID, and OrderDate for faster queries.
• Dashboards: These queries feed KPIs like ROI, CAC, CPL, CTR, and conversion rates.
• Best practice: Always validate campaign spend and revenue data for consistency.

---