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_id	ad_title	ad_description	ad_budget
1	FUR-PROMO	Furniture Promo	100.00
2	OFF-PROMO	Office Supplies Promo	150.00
3	TEC-PROMO	Tech Promo	200.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_id	date	clicks
1	2022-01-01	100
2	2022-01-01	50
3	2022-01-01	75
1	2022-01-02	50
2	2022-01-02	25
3	2022-01-02	50

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_id	date	amount
1	2022-01-01	250.00
2	2022-01-01	150.00
3	2022-01-01	300.00
1	2022-01-02	125.00
2	2022-01-02	75.00
3	2022-01-02	150.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.