SQL Case Study: Create A Website Visitor Analysis From Scratch Using SQL – Including The Code You Need To Be Successful

Today, I’m sharing a digital marketing analytics case study so you can practice using SQL to solve real-world business problems.

Case studies are one of the best ways to put the SQL skills you’re learning to the test. When you’re just getting started, you want to avoid wasting your time on learning SQL theory and spend more time putting your knowledge to good use.

Unfortunately, many digital marketers don’t think about working through case studies.

Businesses pay SQL experts thousands of dollars every day to solve digital marketing problems. You can be that expert.

Myth: It’s too time-consuming to learn SQL.
Myth: You can get by without learning SQL.
Myth: Pre-built reports and dashboards are good enough.
Myth: It’s too hard to learn SQL.

But you can work through a case study, like the website visitor analytics case study I share below, and immediately put the skills you learn to the test.

The case study is broken up into 4 parts:

Problem Statement
Setting Up The Data
Basic Questions
Advanced Questions

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

Special thanks to this week’s sponsor, Sync, who keeps this newsletter free for you to read each week:

Part 1: Problem Statement

A small office supply store owner is frustrated with online sales and wants some answers. As the digital marketer, you propose creating a website visitor analysis to understand where traffic is coming from and which pages visitors viewed on the website. This analysis could then be used to run specific promotions to drive traffic from other websites like Facebook, Instagram, and Google.

The website traffic website has been collected from Google Analytics but is not yet in the database. So that will be your responsibility as well.

Part 2: Setting Up The Data

The following datasets will be used in this case study:

Visitor: This table contains information about each visitor, such as their IP address and the date and time of their visit.
Visit: This table contains information about each page view, such as the page URL and the visitor’s ID.
Referrer: This table contains information about where the visitor came from, such as the referrer URL and a name for the referrer (e.g. “Google”).
Page: This table contains information about each page on the website, such as the page URL and the page title.

Here’s the code to create the tables and populate some example records.

Follow along using this interactive SQL session.

-- Create the Visitor table
CREATE TABLE Visitor (
    ID INT PRIMARY KEY,
    Referrer_ID INT,
    Visit_Date DATE
);

-- Insert sample data into the Visitor table
INSERT INTO Visitor (ID, Referrer_ID, Visit_Date)
VALUES (1, 1, '2022-01-01'),
       (2, 1, '2022-01-01'),
       (3, 2, '2022-01-02'),
       (4, 3, '2022-01-03');

-- Create the Referrer table
CREATE TABLE Referrer (
    ID INT PRIMARY KEY,
    Referrer_Name VARCHAR(255)
);

-- Insert sample data into the Referrer table
INSERT INTO Referrer (ID, Referrer_Name)
VALUES (1, 'Google'),
       (2, 'Facebook'),
       (3, 'Yahoo');

-- Create the Page table
CREATE TABLE Page (
    ID INT PRIMARY KEY,
    Page_Title VARCHAR(255)
);

-- Insert sample data into the Page table
INSERT INTO Page (ID, Page_Title)
VALUES (1, 'Homepage'),
       (2, 'About Us'),
       (3, 'Contact Us'),
       (4, 'Furniture'),
       (5, 'Office Supplies');

-- Create the Visit table
CREATE TABLE Visit (
    ID INT PRIMARY KEY,
    Visitor_ID INT,
    Page_ID INT,
    FOREIGN KEY (Visitor_ID) REFERENCES Visitor(ID),
    FOREIGN KEY (Page_ID) REFERENCES Page(ID)
);

-- Insert sample data into the Visit table
INSERT INTO Visit (ID, Visitor_ID, Page_ID)
VALUES (1, 1, 1),
       (2, 1, 2),
       (3, 2, 1),
       (4, 3, 1),
       (5, 3, 4),
       (6, 4, 1),
       (7, 4, 3);

Here’s what the tables look like in an entity-relationship diagram:

Part 3: Basic Questions

These questions will help you practice the basics of SQL in a real-world business scenario.

If they seem easy, try changing something up. Don’t be afraid to mix it up!

Follow along using this interactive SQL session.

Question 1: How many visitors came to the website on January 1st, 2022?

Answer:

SELECT COUNT(*)
FROM Visitor
WHERE Visit_Date = '01-01-2022';

This query counts all of the records in the Visitor table where the visit date occurred on the date provided in the WHERE clause.

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

id	referrer_id	visit_date
1	1	2022-01-01
2	1	2022-01-01
3	2	2022-01-02
4	3	2022-01-03

The query would return a result of 2 since that’s the total number of visits on that day.

Question 2: What is the page title of the page that was viewed the most?

Answer:

SELECT Page.Page_Title, COUNT(*)
FROM Page
INNER JOIN Visit
ON Page.ID = Visit.Page_ID
GROUP BY Page.Page_Title
ORDER BY COUNT(*) DESC;

This query counts the visits for each page by joining the Page table and the Visit table

Here’s the output from the query. You can see that the Homepage was visited the most with 4 total visits.

page_title	count
Homepage	4
Furniture	1
Contact Us	1
About Us	1

Question 3: Which referrer sent the most visitors to the website?

Answer:

SELECT Referrer.Referrer_Name, COUNT(*)
FROM Referrer
INNER JOIN Visitor
ON Referrer.ID = Visitor.Referrer_ID
GROUP BY Referrer.Referrer_Name
ORDER BY COUNT(*) DESC;

This query counts the visits from each referrer by joining the Referrer table and the Visitor table.

Here’s the output from this query. You can see that Google sent the most visitors to the website.

referrer_name	count
Google	2
Facebook	1
Yahoo	1
About Us	1

Part 4: Advanced Questions

These questions are more advanced, but still doable! They involve joining multiple tables and slightly more advanced SQL concepts.

Follow along using this interactive SQL session.

Question 1: How many visitors viewed the “About Us” page on January 1st, 2022?

Answer:

SELECT COUNT(*)
FROM Visit
INNER JOIN Page
ON Visit.Page_ID = Page.ID
INNER JOIN Visitor
ON Visit.Visitor_ID = Visitor.ID
WHERE Visit_Date = '01-01-2022' AND Page_Title = 'About Us';

This query joins 3 tables together (Visit, Page, and Visitor ) to count the number of visits to the page and the date provided in the WHERE clause.

After running the query, you’ll see that there was 1 visit to the About Us page on Jan 1, 2022.

Question 2: What is the total number of pages viewed by visitors who came from Google?

Answer:

SELECT COUNT(*)
FROM Visit
INNER JOIN Visitor
ON Visit.Visitor_ID = Visitor.ID
INNER JOIN Referrer
ON Visitor.Referrer_ID = Referrer.ID
WHERE Referrer_Name = 'Google';

This query joins 3 tables together (Visit, Visitor, Referrer) to count the number of pages viewed by visitors that came from Google.

After running the query, you can see that there were 3 visits referred from Google.

Question 3: Which pages on the website were not viewed by any visitors?

Answer:

SELECT Page_Title
FROM Page
WHERE ID NOT IN (SELECT Page_ID FROM Visit);

This query uses a subquery to first get the list of Page_IDs from the Visit table and then find the pages that don’t have any visit records.

After running the query, you can see that Office Supplies doesn’t have any visits.

Case studies are a great way to apply your SQL skills to real-world business problems when you are just learning.

With these skills and the experience you gain through case studies, you can move from the basics to more advanced concepts over time.

Here’s a quick recap of what you’ve learned in this case study:

SQL is a powerful tool for answering digital marketing questions
SQL isn’t too hard to learn if you know where to look and stick with it
Practice what you know by working through case studies like this one here

Special thanks to this week’s sponsor, Sync, who keeps this newsletter free for you to read each week:

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:

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