Skip to content

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.

  1. Myth: It’s too time-consuming to learn SQL.
  2. Myth: You can get by without learning SQL.
  3. Myth: Pre-built reports and dashboards are good enough.
  4. 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:

  1. Problem Statement
  2. Setting Up The Data
  3. Basic Questions
  4. 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:

Sync: The Ultimate Data Connector
Sync is the ultimate data connector for HubSpot/Salesforce and Google Sheets that provides real-time insights, accurate data, and advanced features like custom field mapping, incremental data sync, event triggers, and data visualization. Sign up today and start syncing your data easily with a 30-day free trial. Try Sync Now

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:

ERD for Website Visit Analytics Project

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:

idreferrer_idvisit_date
112022-01-01
212022-01-01
322022-01-02
432022-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_titlecount
Homepage4
Furniture1
Contact Us1
About Us1

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_namecount
Google2
Facebook1
Yahoo1
About Us1

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 (VisitPage, 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 (VisitVisitorReferrer) 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:

Sync: The Ultimate Data Connector
Sync is the ultimate data connector for HubSpot/Salesforce and Google Sheets that provides real-time insights, accurate data, and advanced features like custom field mapping, incremental data sync, event triggers, and data visualization. Sign up today and start syncing your data easily with a 30-day free trial. Try Sync Now

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 :)