SQL 101

SQL for Marketers — Making Where More Powerful

Periscope Data

|

January 8, 2015

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.

The Where Clause

This time, we’ll show you a SQL tool to make where even more powerful.

Comparison Operators

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!

Equal to

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.

select*

from marketing_ads

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:

select*

from marketing_leads

where quality !='low'

Less Than

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.

select*

from marketing_visits

where visits <500

Greater Than

Many times, rather than looking for low performing channels, you want to look at your highest performing channels to find insights.

select*

from marketing_leads

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:

select*

from marketing_leads

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.

select*

from marketing_visits

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.