SQL for Marketers — Making Where More Powerful
Jon Bishop recently joined Periscope to scale our marketing. This blog post is the fifth in a series he’s writing as he learns SQL. You can read part one, part two, part three, and part four in the archives.
Filtering Data for Fame and Glory
Data analysis is an important part of a marketer’s work. One of the most common data analyses that marketers conduct is filtering data.
Knowing that visits or leads increased is not enough for marketers. We want to filter that data across channels, demographics and value metrics such as LTV for a deeper understanding.
This is where the where clause comes in.
The Where Clause
This time, we’ll show you a SQL tool to make where even more powerful.
Whether you know the name or not, you’re familiar with comparison operators. They include:
- Equal to: =
- Not equal to: != (or <>)
- Less than: <
- Greater than: >
- Less than or equal to: <=
- Greater than or equal to: >=
Here are some examples of each in action. Keep in mind your column names may be different!
Sometimes, rather than data over a range, you want data around a specific characteristic. For example, you may want to only look at data related to your Facebook ad campaigns.
where channel = 'Facebook Ad'
Not Equal to
This is used exactly like “Equal to,” except it excludes data that meets a specific comparison instead of including it.
If you wanted to look at data related to your medium and high quality leads, but not your low quality leads for example, you’d write:
where quality != 'low'
Less than is useful for looking at data below a certain threshold or before a certain date. For example, you can use it to find channels driving low traffic. This might be especially helpful if you think they have the potential to drive more traffic over time.
where visits < 500
Many times, rather than looking for low performing channels, you want to look at your highest performing channels to find insights.
where sign_ups > 50
Less Than or Equal to
You may want to produce a report for sales and they’re only interested in leads that come from companies with 200 or less employees. Here’s how:
where employee_count <= 200
Greater Than or Equal to
Often, you’ll only want to look at data after a major change to your campaigns or to a specific channel. Data generated previous to this change might not be useful for your current analyses.
where created_at >= '2014-12-7'
Make Us Proud
There you have it. Combining where with comparison operators makes for a surprisingly powerful SQL tool when it comes to filtering your marketing data.
Now go off and filter to your heart’s delight! And if you liked this post, consider signing up below for more SQL blog updates.
Want to discuss this article? Join the Periscope Data Community!