How to Use SQL to Filter Your Client Email Database

Looking for ways to be on friendly terms with your email database? Here's my way of using SQL to evaluate a promotion.

A client database plays an essential role in developing your sales and marketing strategies. Having a clear and well-structured database has several benefits. For example, you can increase your company's revenue by managing your current and new clients – they can be monitored and offered special promotions. Moreover, such an effort develops customer satisfaction and increases value for your current customers.

In this article, you will find out how to filter an email database using simple SQL statements. I will show you how to quickly track the clients you want to reach. 

Business Scenario: SaaS Company

Imagine you're a SaaS company offering an automation tool available in two plans: basic and pro. Recently, the customer success team did research and found out that customers on a basic plan tend to churn (stop using your service) after 6 months. This theory led to a conclusion that basic users don't have the chance to explore the full potential of the tool.

You decided to conduct a cross-promotion: 20% off the pro plan. Yet, you can only offer this promotion to clients that meet specific criteria. For example, they have to be currently on the basic plan.

I'll be working on a sample clients table that contains the following columns:

• ID – The unique identifier for that B2B client.
• status – Whether this client is active, paused, or churned.
• date_of_first_invoice – When your client was issued their first invoice.
• current_plan – The client's current plan.
• billing_email – The client's email address (used for payments and invoices).
• admin_email – The email address of the person managing the client's account (i.e. their account manager).
• admin_first_name – The account manager's first name.
• admin_last_name – The account manager's last name.

Here's a look at the data in this table:

client

Let's start with the first part of the SQL query. First, we'll define the information we need. To conduct an email outreach, we'll need the email address of the person managing the client's account (the admin). Let's look at the query:

SELECT
  company_name,
  admin_first_name,
  admin_email,
FROM client

I told my database that I only need to see the columns company_name, admin_first_name, and admin_email.

Now, it's good to have some personalized data to include in your email; the contact's first name can be put in the email introduction (“Hi, Jon!”) or the company name can be used in your email subject line (“The solution for Company XX.”) This way, you add the feeling that your email was specifically created for that client and there's a better chance that more people will reply to you. And, of course, you want more replies – the more replies you get, the more sales opportunities you have.

To make the most of your cross-promotion, you have to make sure that you target the right people. You can't offer a Porsche to a hermit, right? Or a camel to a pop star? Well, you can, but we both know how it will end.

So, getting back to SQL, we need to track active clients who have been on a basic plan for at least 5 months. The SQL WHERE clause will filter our records and return the ones who meet this condition.

Let's look at the query:

SELECT
  company_name
  admin_first_name,
  admin_email,
FROM client
WHERE status = 'active'
  AND current_plan = 'basic'
  AND date_of_first_invoice >= '2020-09-01'

Here, the WHERE clause includes only the records that:

• Are currently active (status = 'active').
• Are not on the pro plan (current_plan = 'basic').
• Have been using the service for at least five months (date_of_first_invoice >= '2020-09-01').

OK, but what about those company admins who refused to provide their first name or their company_name? This is a problem; if I get NULL values for those fields, my email campaign may throw errors. Even if it does not, I don't want to go through every email and make sure there are no missing names in the greeting lines. (If you're not familiar with NULL values, see this article on NULLs in SQL.)

That's why I will add the IS NOT NULL operator to my query. It will test for non-empty values ( i.e. NOT NULL values.) Lastly, I want to group my data by company_name.

SELECT
  company_name
  admin_first_name,
  admin_email,
FROM client
WHERE status = 'active'
  AND current_plan = 'basic'
  AND date_of_first_invoice >= '2020-09-01'
GROUP BY company_name

The result of the SQL query looks like this:

Managing Marketing Databases with SQL

In this article, you've found out that you can boost your marketing efforts with the help of SQL. Using the example of SaaS company data, I showed you how to use the SQL WHERE clause to filter client records. Moreover, we combined WHERE with the AND operator to filter those records based on more than one condition.

If you're up for some more SQL practice, try our SQL Basics course. There's a special section dedicated to filtering rows with conditional operators where you'll get hands-on experience in solving this type of exercise.

Now that you know how to write a simple SQL query to filter your email database and track the right audience for a cross-promotion – go on, try it for yourself. You can adjust this query to your business needs; there's nothing to lose and a lot to win. You already have your client data – just find a way to show your current clients how your product or service can make (or is making) their lives better.

Don't have access to a database where you can practice what you have learned here? No worries; you can get the data from your data analysis team and filter client records with SQL-like queries in Google Sheets, or you can recommend creating a database environment in your company with the free, open-source PostgreSQL  database management system. My colleague Jakub wrote an interesting article on which major companies use PostgreSQL.