Back to articles list February 23, 2021 - 5 minutes read Use SQL to Filter Your Client Email Database & Organize a B2B SaaS Cross-Promotion Kate Stolarek Hi, I’m Kate! I’m a growth marketer striving for excellence. I work in the cold emailing sector where I focus on finding innovative ideas to drive user acquisition. I also host webinars and write (mainly) about SQL. Let’s connect on LinkedIn or Twitter and grow something together! :) Tags: sql learn sql sql reports 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: IDcompany_namestatusmonth_of_first_invoicecurrent_planbilling_emailactive_admin_emailadmin_first_nameadmin_last_name 1Webinar Generationactve2020-09-01basicbilling@wgeneration.io/john@wgeneration.ioJohnKowalsky 2Profit Goodactive2019-04-15basicpayments@profitgood.coanna-marie.pettkins@profitgood.coAnna-MariePetkins 3Money Makerpauzed2020-01-01probilling@money-maker.comsteve.mccormick@money-maker.comSteveMcCormic 4GenXactive2021-01-20basicinvoices@genx.comf.lemming@xgen.comNULLLemming 5Fosseractive2020-06-01basicbilling@fosser.ukpierre@fosser.ukPierreSmiths ... 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: IDcompany_nameadmin_emailadmin_first_name 1Fosserpierre@fosser.ukPierre 2Profit Goodanna-marie.pettkins@profitgood.coAnna-Marie 3Webinar Generationjohn@wgeneration.ioJohn ... 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. No matter what, start using SQL! Tags: sql learn sql sql reports You may also like Google Analytics and SQL Find out how you can use Google Analytics and SQL to create custom reports that derive more insights from your website data. Read more Understanding the Use of NULL in SQL Three-Valued Logic NULLs are necessary in databases, learning to use them is fundamental to SQL success. However, NULLs should be handled with care – see how! Read more How to Start Writing SQL Reports Learn how to write SQL reports. Reporting in SQL will benefit you professionally, regardless of your role or industry. Read more What Is a DBMS? Read our article to find out what a DBMS is and what it can do for you and your data. Read more Should I Learn SQL? 4 Convincing Reasons There are 250+ computer programming languages in popular use. However, there are good reasons why you should learn SQL, or Structured Query Language. Read more Stay at Home and Learn SQL for Free with LearnSQL.com Stay at Home and Learn SQL for Free with LearnSQL.com Read more Video Tutorial or Interactive SQL Course – Which Is Better? Decide what SQL learning method will be best for you - tutorial or course. Don't waste your time or money! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.