Skip to content

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: idnameemail, 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), pageviewstime_spentsession_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_iddate_visitedpages_viewedtime_spent_seconds
12022-01-02T00:00:00.000Z15180

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_idpages_viewedrunning_total
11010
11525
1833
255
2813
2720
377
31219
31029

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.

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