Don’t Get Left Behind: 3 Essential Skills Every Digital Marketer Needs – Plus How To Learn Each One

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.

​Follow along with this interactive SQL code session​

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', 'john@email.com', 'New York'),
  (2, 'Jane', 'jane@email.com', 'California'),
  (3, 'Bob', 'bob@email.com', 'Texas');

The code is creating a new table named visitors with four columns: id, name, email, and location. The 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 visitors table.

For example, the first record has an id value of 1, a name value of ‘John’, an email value of ‘john@email.com’, and a 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:

​Follow along with this interactive SQL code session​

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 visitors table), pageviews, time_spent, session_duration, and session_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:

​Follow along with this interactive SQL code session​

-- 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 visitors table.

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:

​Follow along with this interactive SQL code session​

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;

Results

avg_pages_viewed
9.11

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

Results

visitor_id	date_visited	pages_viewed	time_spent_seconds
1	2022-01-02T00:00:00.000Z	15	180

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;

Results

visitor_id	pages_viewed	running_total
1	10	10
1	15	25
1	8	33
2	5	5
2	8	13
2	7	20
3	7	7
3	12	19
3	10	29

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.