Today, I’m sharing 3 SQL skills every digital marketer needs to master in order to unlock the potential of their marketing data.
Being able to work with your digital marketing data is critical these days. And most of the current solutions work, but just barely. Using pivot tables and VLOOKUPs in Excel can lead to serious errors.
Not to mention if a formula breaks (which happens a lot – especially when you’re crunching to get an analysis completed before a deadline).
Unfortunately, most digital marketers don’t get a chance to breathe because they are locked into using Excel or vendor dashboards for their reports.
Learning how to use SQL can seem daunting
- Not sure where to start
- Information overload
- Nobody to ask for help
- Excel is “good enough”
But by working through this guide, you’ll be able to work through a real-world analysis using SQL code.
Skill 1: Create Tables and Insert Sample Data
The first step in mastering SQL is to create the tables that will hold your data and insert sample records.
This sets the foundation for your analysis. By creating the tables and inserting sample data, you’ll be able to practice writing SQL queries and get a better understanding of how your data is organized.
Be sure to double-check your SQL syntax before running any queries. A simple typo can cause errors that can be difficult to track down.
Here are a few quick examples to get you started:
CREATE TABLE visitors ( id INTEGER PRIMARY KEY, name TEXT, email TEXT, location TEXT ); INSERT INTO visitors (id, name, email, location) VALUES (1, 'John', 'email@example.com', 'New York'), (2, 'Jane', 'firstname.lastname@example.org', 'California'), (3, 'Bob', 'email@example.com', 'Texas');
The code is creating a new table named
visitors with four columns:
id column is defined as the primary key for the table, which means each record in the table must have a unique value in this column.
The second part of the code is inserting sample records into the
For example, the first record has an
id value of 1, a
name value of ‘John’, an
location value of ‘New York’. The 2nd and 3rd records have the same structure with different values that match other website visitors.
This code creates a table and inserts sample data into the table for use in queries to analyze the website visitors’ data.
Here’s a second example:
CREATE TABLE visitor_stats ( visitor_id INTEGER, date_visited DATE, pages_viewed INTEGER, time_spent_seconds INTEGER, PRIMARY KEY(visitor_id, date_visited) ); INSERT INTO visitor_stats (visitor_id, date_visited, pages_viewed, time_spent_seconds) VALUES (1, '2022-01-01', 10, 120), (2, '2022-01-01', 5, 60), (3, '2022-01-01', 7, 90), (1, '2022-01-02', 15, 180), (2, '2022-01-02', 8, 90), (3, '2022-01-02', 12, 150), (1, '2022-01-03', 8, 90), (2, '2022-01-03', 7, 120), (3, '2022-01-03', 10, 120);
This will create a
visitor_stats table with columns for
id (primary key),
visitor_id (foreign key to the
session_duration, and s
ession_date. It also inserts some sample data into the table to work with.
Don’t be intimidated by this step! With a little practice, you’ll be able to create tables and insert sample data in no time.
Skill 2: Write Basic SQL Queries to Extract Data
The second step in mastering website visitor analysis using SQL is to write basic SQL queries to extract data from your tables.
This allows you to retrieve the data you need to analyze. By writing basic SQL queries, you’ll be able to extract data based on specific criteria, such as location, date, and more.
Don’t worry if you’re new to SQL.
Here are a few quick examples to get you started:
-- Retrieve all visitors SELECT * FROM visitors; -- Retrieve visitors from New York SELECT * FROM visitors WHERE location = 'New York'; -- Retrieve visitors who signed up before January 1, 2022 SELECT * FROM visitors WHERE signup_date < '2022-01-02';
This code contains three SQL queries to retrieve data from the
visitors table that we created earlier.
The first query retrieves all visitors from the table by using the SELECT statement with the wildcard (*) character. The wildcard tells the database to return all columns from the
visitors table. The FROM clause specifies the name of the table that we want to retrieve data from. The result of this query will be all records in the
The second query retrieves only visitors who are from “New York”. The WHERE clause specifies the condition that we want to apply to filter the results. In this case, the condition is that the
location column must be equal to ‘New York’. Only records that meet this condition will be returned.
The third query retrieves only visitors who signed up before January 2, 2022. The WHERE clause specifies the condition that we want to apply to filter the results. In this case, the condition is that the
date_visited column must be less than ‘2022-01-02’. Only records that meet this condition will be returned.
Each query will return a result set that shows the records that meet the specified condition. The output will include all columns in the
visitors table for each matching record.
Make sure to use the correct syntax for your queries. The incorrect syntax can lead to errors or incorrect results.
Skill 3: Refine Your Analysis with More Advanced SQL Techniques
Now that you have joined and filtered your data, it’s time to refine your analysis with more advanced SQL techniques.
Here are a few examples:
Example 1: Using Aggregate Functions
Let’s say you want to know the average number of pages viewed per visitor on your website. You can use the
AVG function to calculate this:
SELECT AVG(pages_viewed) AS avg_pages_viewed FROM visitor_stats;
Example 2: Using Subqueries
You may want to find the visitors who viewed the most pages on your website. You can use a subquery to accomplish this:
SELECT * FROM visitor_stats WHERE pages_viewed = ( SELECT MAX(pages_viewed) FROM visitor_stats );
Example 3: Using Window Functions
You may want to calculate the running total of pages viewed by each visitor. You can use the
SUM function in combination with a window function to achieve this:
SELECT visitor_id, pages_viewed, SUM(pages_viewed) OVER (PARTITION BY visitor_id ORDER BY visit_date) AS running_total FROM visitor_stats;
Congratulations! By mastering these three SQL skills, you’ll be able to analyze website visitor data like a pro. Remember, practice makes perfect, so keep working with SQL and you’ll be able to accomplish anything you set your mind to.