Skip to content

Master the Basics of SQL for Digital Marketing with this Step-by-Step Case Study – Even If You’re Just Getting Started

One of the best ways to learn SQL for digital marketing analytics is through case studies.

Case studies take you through a real-world scenario that you would see in a real business. This is much better than just working on a theory. Instead, case studies make it so that you can work on practical situations that you’d see in a real workplace situation as a data analyst like marketing, sales, or human resources.

Unfortunately, many digital marketers don’t work on case studies.

Put your SQL for digital marketing training into practice with case studies.

  1. You work through real-world problems, not theoretical ones.
  2. You apply everything you know and can connect the dots easily.
  3. You learn how to think through problems, not just copy and paste code.
  4. You avoid tutorial hell by working on something that takes you from start to finish.

Below, you’ll work through a digital marketing analytics case study to sharpen your SQL skills.

It’s broken up into 5 parts:

  1. Problem statement
  2. Setting up the data
  3. Case study questions (with answers)
  4. Bonus questions (without answers)
  5. Advanced questions (with answers)

Everything you need to get started and master the basics of digital marketing analytics with SQL is included, so let’s get started!

Part 1: Problem statement

A small office supply store owner recently ran an ad campaign to promote their new products. They want to know how effective the campaign was in terms of generating sales and attracting new customers.

They have collected data on the ad campaign, including information about the ads that were shown, the number of clicks each ad received, and the number of sales that were made as a result of the campaign.

Part 2: Setting up the data

The following datasets will be used in this case study:

  • Ads: This table contains information about each ad that was shown as part of the campaign. It includes the ad id, the ad title, the ad description, and the ad budget.
  • Clicks: This table contains information about the number of clicks each ad received. It includes the ad id, the date the ad was shown, and the number of clicks the ad received on that date.
  • Sales: This table contains information about the sales made as a result of the campaign. It includes the ad id, the date the sale was made, and the sale amount.

Part 3: Case study questions (with answers)

Question 1: What was the total budget for all ads in the campaign?

Answer:

SELECT SUM(ad_budget) AS total_budget
FROM Ads;

This query will select the sum of the ad_budget column from the Ads table and alias it as total_budget. The result will be the total budget for all ads in the campaign.

For example, if the Ads table contained the following records:

ad_idad_titlead_descriptionad_budget
1FUR-PROMOFurniture Promo100.00
2OFF-PROMOOffice Supplies Promo150.00
3TEC-PROMOTech Promo200.00

The query would return a result of 450.00, which is the total budget for all ads in the campaign.

Question 2: How many clicks did the ads receive, on average?

Answer:

SELECT AVG(clicks) as average_clicks
FROM Clicks;

This query selects the average of the clicks column from the Clicks table and creates an alias of average_clicks. The result is the average clicks for all ads in the campaign.

For example, if the Clicks table contained the following records:

ad_iddateclicks
12022-01-01100
22022-01-0150
32022-01-0175
12022-01-0250
22022-01-0225
32022-01-0250

The query returns a result of 58.33, which is the average clicks for all ads in the campaign.

Question 3: What was the total amount of sales made as a result of the campaign?

Answer:

SELECT SUM(amount) as total_sales
FROM Sales;

This query selects the sum of the amount column from the Sales table and creates an alias of total sales. The result is the total sales for all ads in the campaign.

For example, if the Sales table contained the following records:

ad_iddateamount
12022-01-01250.00
22022-01-01150.00
32022-01-01300.00
12022-01-02125.00
22022-01-0275.00
32022-01-02150.00

The query returns a result of 1050.00, which is the sum of sales amount for all ads in the campaign.

You can continue practicing with these queries for additional practice:

  1. Add a Group By clause
  2. Add a Where clause
  3. Add a Limit clause

Then, when you’re ready, move on to these slightly more difficult case study questions for more of a challenge.

Part 4: Case study questions (without answers)

  1. Which ad had the highest number of sales?
  2. Which ad had the highest return on investment (ROI), calculated as the total amount of sales divided by the ad budget?
  3. For each ad, show the total number of clicks, total amount of sales, and ROI.

Try these on your own using this interactive SQL environment!

Part 5: Advanced questions (with answers)

Question 1: Create a table called Customers that includes the customer’s name and email address.

For each sale in the Sales table, include the customer’s name and email address.

Use this interactive SQL environment for these more advanced case study questions.

To answer this question, you will need to create a new table called Customers, with the following SQL code:

CREATE TABLE Customers (
  customer_id INTEGER PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

You will also need to modify the Sales table to include a foreign key column for the customer_id, like this:

CREATE TABLE Sales (
  ad_id INTEGER,
  date DATE,
  amount DECIMAL(10,2),
  customer_id INTEGER,
  FOREIGN KEY (ad_id) REFERENCES Ads(ad_id),
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Then, to answer the question, you can use the following SQL query:

SELECT s.*, c.name, c.email
FROM Sales s
JOIN Customers c ON s.customer_id = c.customer_id;

This query will select all columns from the Sales table (aliased as s) and join the Customers table (aliased as c) on the customer_id column. The result will be a table that includes the customer’s name and email address for each sale in the Sales table.

Question 2. Create a table called ProductCategories that includes the product category name and description.

For each product in the Products table, include the product category name and description.

To answer this question, you will need to create a new table called ProductCategories, with the following SQL code:

CREATE TABLE ProductCategories (
  category_id INTEGER PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255)
);

You will also need to create a new table called Products, with the following SQL code:

CREATE TABLE Products (
  product_id INTEGER PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10,2),
  category_id INTEGER,
  FOREIGN KEY (category_id) REFERENCES ProductCategories(category_id)
);

Then, to answer the question, you can use the following SQL query:

SELECT p.*, c.name, c.description
FROM Products p
JOIN ProductCategories c ON p.category_id = c.category_id;

This query will select all columns from the Products table (aliased as p) and join the ProductCategories table (aliased as c) on the category_id column. The result will be a table that includes the product category name and description for each product in the Products table.

That’s it!

In this SQL for Digital Marketers Case Study, you learned:

  1. SQL basics for digital marketing analytics
  2. More advanced SQL topics to work through
  3. How to set up SQL for an ad campaign analysis
  4. How to work through common ad campaign analytics questions
  5. Why case studies are a great way to practice SQL for digital marketing analytics

That’s it for today – I hope you enjoyed it.

See you again next week!

Whenever you’re ready, here are 3 ways I can help you:

  1. View all past issues of my newsletter here.
  2. If you’re ready to build your online data analytics portfolio, my 14-day Data Analytics Portfolio Playbook is for you.
  3. If you want actionable data analytics advice on your specific situation, book a 1:1 coaching session with me today.

Leave a Reply

Your email address will not be published. Required fields are marked *

Tableau & Google Sheets Checklist

This one-page checklist PDF will help you learn how to upload your data into Google Sheets and build an awesome Tableau dashboard without wasting time.

You have Successfully Subscribed!

4 Things For Every Tableau Dashboard Creator

This free 4-page eBook PDF will walk you through the 4 most important steps for creating a Tableau dashboard

You have Successfully Subscribed!

Massive List of Tableau Tools

Tableau is even more powerful with these 20 tools you might not know about. The 2-page PDF includes a description and helpful links for each tool to get you started.

You have Successfully Subscribed!

Career Boosting Business Analytics Trends

Use this list as a road map to take you from boring data job to the GO-TO PERSON in your company that knows the answers to all the questions.

All set! Check your email :)

Massive List of Free Data Viz Books

List of 10 Awesome Data Visualization Books

All set! Check your email to download.

Tableau Dashboards
Beginner’s Guide

Get the full 2-page PDF with the 5 steps outlined in this guide + 3 bonuses to help you along the way

BONUS #1: 5 Beginner mistakes to avoid

BONUS #2: Video walkthrough

BONUS #3: Links to the the Google Sheet and final Dashboard

All set! Check your email :)

How to Create 6 Different Tableau Bar Charts (Free Guide)

Get the step-by-step guide to create all 6 different types of Tableau Bar Charts (with copy-and-paste calculated fields included) for free.

 

All set! Check your email :)

Massive List of Tableau Web Data Connectors

Download the list of 21 different Web Data Connectors for Tableau in this Google Sheet (save a copy for yourself, too!) 

All set! Check your email :)

Slope Graph Examples PDF + Dashboard Walkthrough Video

4-page PDF showing 10 slope graph examples + a video walkthrough

All set! Check your email to download the bonus materials.

Tableau Tutorial Chart Redesign

Download the Tableau Tutorial PDF Checklist & Workbook

The step-by-step checklist for fixing busted charts with Tableau PLUS the full workbook with all of my formatting and calculations for free.

All set! Check your email to download.

Download the TableauHighlight Tables PDF Guide

The step-by-step guide for creating Tableau highlight tables
(plus direct links to the YouTube video walkthrough and link to final Tableau workbook)

You have Successfully Subscribed!

Tableau Scatter Plots: The Complete Guide

Get the step-by-step checklist guide to create scatter plots with Tableau

 

All set! Check your email :)

Rebuild a Busted Chart with Tableau

Enter your best email to get the PDF version of this tutorial + the companion Tableau workbook for free

All set! Check your email to download.

Download the Bonus Materials

Enter your best email to get the PDF checklist version of this guide and the companion Tableau workbook for free.

All set! Check your email :)