Why You Should Use SQL in Marketing Analytics

Have you been wondering how you could use SQL in your marketing data analysis work? Would you like to create SQL reports to guide your marketing decisions? In this article, you will learn how to use SQL for marketing through plenty of use cases.

Are you a marketing professional wanting to better understand the results of your marketing efforts? Do you want better means to prove your point and demonstrate the value you create? Do you have plenty of marketing data but lack the skills to tap into it?

You might want to learn SQL! SQL is a powerful data analytics language because of its simplicity and widespread popularity. If your company has a marketing database, learning how to query it can be valuable for you, your team, and your company.

In this article, you will learn how marketing analytics uses data analysis and specifically SQL to generate better insights. We show you many hands-on business cases for you to answer marketing questions with a few lines of SQL.

We will also share resources so that you can learn SQL quickly and with specific business outcomes in mind.

So, do you want to learn about data analysis in marketing? Read on!

Data Analysis in Marketing Analytics

Marketing analytics aims to inform you about better ways to reach potential customers. Here's a quote from a great book, Marketing Analytics: Essential Data-Driven Decision Making Tools, by Rajkumar Venkatesan:

Marketing analytics involves using the data that you can glean from customer decisions to determine how a company should use its marketing money to achieve the most fruitful results.

It is not a new trend, but the recent emergence of data analysis has introduced new possibilities and challenges.

Marketing analytics has many areas, just as varied as marketing itself. Here is a list of the most prominent ones:

• Customer acquisition and customer journey analysis.
• Market and competitive analysis.
• Media analytics.
• Product and service analytics.

The emergence of digitalization and the acceleration of marketing data generation has changed the ways you can investigate these areas. The analytics process has become more data-driven and has opened up the possibilities for using more advanced quantitative and qualitative methods.

This trend has become so prominent that some researchers have even called for the introduction of engineering principles into the marketing analytics process:

Marketing Engineering: a systematic approach to harness data and knowledge to drive effective marketing decision making and implementation through a technology-enabled and model-supported interactive decision process.

This quote from the book Principles of Marketing Engineering and Analytics illustrates the situation well. And this is not just an abstract trend. If you work in marketing, it has direct consequences to your work.

Your digital interaction with customers and your marketing strategies and efforts produce data with valuable information. If you have the knowledge and the skills to analyze it, you can be more effective and valuable, even find better opportunities.

For this reason, it is especially helpful to have the skills for tapping into your marketing data and getting insights. And the main way to do this is with SQL!

SQL is the most widespread tool for data analysis and has been a very reliable tool for decades. Learning it allows you to get the answers to your marketing questions today and also to any data-analysis-related problem in the future.

“That sounds nice,” you might be thinking, “but what does using SQL look like?”

This is a good and important question. Many people feel intimidated by learning new technical skills. They seem to be too “technical” or “complicated.”

In the rest of this article, we demonstrate the many practical examples of using SQL to get marketing insights from data. As you will see, the SQL syntax is neatly structured and uses familiar English terms, so you can start to create simple queries relatively easily.

Are you excited? Let’s jump right in!

SQL Reports for Marketing Analytics: Examples

In this section, you will see several examples of creating SQL reports to answer marketing analytics questions.

We have collected examples from the following marketing analytics areas:

• Customer Behavior Analysis
• Customer Segmentation
• Marketing Channel Analysis

These domains cover common marketing problems. So, there is a good chance you come across similar problems in your marketing work.

These examples simulate real-life problems. They are from our SQL learning platform courses, where you can find even more exercises with detailed explanations.

Customer Behavior Analysis

The goal of customer behavior analysis is to get information about what our customers do and how they behave. We want to understand how they interact with our offerings and media channels.

Let’s start with an easy example. Let’s say you run a store and would like to know how many new customers have registered there this week.

You can get this information quickly by writing a short basic SQL query. You take your customers table, filter it to the current week, and count the number of customer IDs.

SELECT COUNT(customer_id) AS registrations_current_week
FROM customers
WHERE registration_date >= DATE_TRUNC('week', CURRENT_DATE);

Here, we use the DATE_TRUNC() function to find the beginning of the week to get the number of registrations in the current week. The result shows we have had three customers signed up this week.

You’re probably thinking you could easily do this in Excel. But what if you have not just 3 registrations, but instead, for example, 2,000,000? Can your spreadsheet handle it? Unfortunately, no. But a few lines of SQL query does the job.

You may want to see an overview of registrations over time. With the following query, you count all the new customers who registered during 2017. You take the same customers data, group it, and filter by date:

SELECT
  DATE_PART('month', registration_date) AS registration_month,
  COUNT(customer_id) AS registration_count
FROM customers
WHERE registration_date >= '2017-01-01'
AND registration_date < '2017-01-01'::date + INTERVAL '1' year
GROUP BY registration_month
ORDER BY registration_month;

Running this SQL query produces the following report of the monthly registrations. The result gives you a general idea of the number of registrations you can expect each month and identifies trends and one-off changes. And this is a solid insight to help you plan your activities going forward.

You may want to know which customers have ordered the most from your store. In the following query, you combine the customer and the order data and group them by customer name.

SELECT
  full_name,
  SUM(total_amount) AS order_amount
FROM orders ord
JOIN customers cu
  ON ord.customer_id = cu.customer_id
GROUP BY full_name
ORDER BY order_amount DESC;

The resulting SQL report shows the customers with the largest total order amount. You can go even further and merge this data with other marketing performance data like organic traffic, keyword rankings, or average time on a page. You can store data from Google Analytics, keyword position tracker, or similar tools in separate tables in your database. This way, after combining the needed tables, you can see what website pages bring you the most active customers, what is the organic traffic of these pages or, what are the organic positions of these URLs, etc. So you will be able not only to analyze customer behavior but also to see a broader picture to identify the strengths of your business and build an effective strategy.

In this example, you have also tapped into another valuable area of marketing analytics: customer segmentation. Let’s examine it further in the next section!

Customer Segmentation

By segmenting your customers into groups, you can identify their different needs and provide more value.

The previous customer behavior analysis example also did this. But you can segment customers in other meaningful dimensions, not just the order amount.

Again, we start with a simple example. Let’s say you want to know where your customers are. With the following query, you can quickly see the number of customers in each city.

You do this by grouping the customer data by city and counting the number of customer IDs in each group. You also order the resulting table by the number of customers to see the cities with the most customers.

SELECT
    city,
    COUNT(customer_id) AS customers_quantity
FROM customer
GROUP BY city
ORDER BY customers_quantity DESC

In this case, you have two customers in Dallas, while other cities have only one customer each. The report shows there are three customers for whom you have no city information. It seems the city is not the best dimension for you to group your customers.

Another common segmentation task is to group customers by the marketing channel through which they joined. This can help you decide which channel to use for reaching your customers.

Using the query below, you see the channels from which you have gained your customers each week. This is done by connecting your customers and channels tables, grouping them by year, week, and channel, then counting the number of registrations in each category.

SELECT
  DATE_PART('year', registration_date) AS registration_year,
  DATE_PART('week', registration_date) AS registration_week,
  channel_name,
  COUNT(*) AS registration_count
FROM customers cu
JOIN channels ch
  ON cu.channel_id = ch.id
GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date), channel_name
ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);

The report shows you have a few customers registered from each channel, but “Organic Search” seems outstanding. This insight might mean many things, but it suggests examining your marketing channels further.

Next, you will see examples for getting this type of information.

Marketing Channel Analysis

If you use multiple marketing channels, you may want to focus your resources on the most valuable ones. For a better overview, you can conduct a marketing channel analysis.

Let’s say you would like to see which marketing channel got you the largest total order amount. For this, you have to join several tables (customers, orders, and channels), group the results by channel, then calculate the total amount spent through each.

SELECT
  channel_name,
  SUM(total_amount) AS order_amount
FROM orders ord
JOIN customers cu
  ON ord.customer_id = cu.customer_id
JOIN channels ch
  ON ch.id = cu.channel_id
GROUP BY channel_name
ORDER BY order_amount DESC;

Our report shows you get the most amount ordered from customers acquired through organic search. It generates more than five times the order amount of the “Direct” channel, the next one in the ranking.

However, the total order amount in each channel is influenced by two factors: the number of customers reached through that channel and the order amount by each customer.

To get a better picture, let’s break this report even further. We show the number of customers reached through each channel and their average order amount.

We can do this by adding two more lines of code to our previous query:

SELECT
  channel_name,
  COUNT(cu.customer_id) AS number_of_customers,
  SUM(total_amount) AS order_amount,
  SUM(total_amount) / COUNT(cu.customer_id) AS avg_order_amount
FROM orders ord
JOIN customers cu
  ON ord.customer_id = cu.customer_id
JOIN channels ch
  ON ch.id = cu.channel_id
GROUP BY channel_name
ORDER BY avg_order_amount DESC;

The resulting SQL report shows you reached most customers through organic search, which is the main reason for the high aggregate order amount. However, customers reached through social media and customers reached directly had higher average order amounts. Based on this insight, you might give a boost to your social media presence!

We have reached the end of our demonstration of SQL reporting in marketing analytics. You have seen how you can quickly get valuable insights about your marketing activities with some basic SQL skills.

Of course, using SQL well requires practice. The uses for SQL extend well beyond what we have covered here. For example, marketers often use white label reporting solutions to enhance the analytical capabilities of SQL and ensure reports are delivered under their own brand. If you are wondering where and how to start your SQL learning journey, we have some tips for that!

Create Marketing Reports in SQL!

A common challenge in learning SQL is trying to learn it on your own. You need to set up a database and find your way around it. However, creating and managing a database environment is not something you commonly do as an analyst. Your job is to run queries on it for actionable insights!

At LearnSQL.com, we have created our learning platform with this in mind. Our courses work in a pre-built environment with an easy-to-use interface. So, you can quickly learn the most important analytical skills for your job. All you need is a browser and a bit of motivation!

We have also organized our courses into clear learning paths. You can target your learning with particular outcomes in mind, like getting familiar with SQL, having general competency, or creating business reports. Following these tracks, you will produce results much faster than trying to “learn SQL.”

So, what do you think? Are you ready to generate better marketing insights? Perform an analysis of the potential from learning SQL. See that insight already? Yes, it pays off, a lot!