Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

SQL for Marketers Part 4 — Common Analysis Queries

SQL for Marketers Part 4 — Common Analysis Queries
November 27, 2014

Jon Bishop recently joined Periscope to scale our marketing. This blog post is the fourth in a series he’s writing as he learns SQL. You can read part onepart two, and part three in the archives.

Thanks for tuning in to another SQL for Marketers post! For round four, we’re going to cover a few core SQL queries that can answer many common marketing questions.

Time Series

A common part of many marketing questions is: How many visits does our website receive each day? Fortunately we’ve already covered one of the two concepts we need to know for this: date().

The other concept we need is count()count() is similar to sum(), which we covered in our last lesson. The difference is that count() only counts the number of times something occurs rather than adding items up.

Now for our SQL. First, we write a query to find out which days each visit occurred on. This is simple with date(created_at).

select date(created_at)
from visits

Next, we count() how many visits occurred on each day by simply adding  count(1) and  group by date.

select 
    date(created_at),
    count(1)
from visits
group by date

And that’s the SQL for the time series chart!

Distinct Time Series

We might also want to know how many unique people visited, rather than the total number of visits. We only need one new concept for this: distinct.

As you may have guessed, distinct grabs only the unique occurrences of your data. Counting distinct values is frequently used for measuring active users and calculating retention.

Previously we counted all visits on a day with count(1), which simply counts the rows. We can get the number of unique visitors by only counting each visitor once: count(distinct visitor_id).

The SQL for the Unique Visits chart is:

select 
    date(created_at),
    count(distinct visitor_id)
from visits
group by date

The most recent days have had about 300,000 total visits, and 50,000 unique visits, meaning visitors averaged half a dozen visits each. Great!

Grouped Time Series

While it’s nice to get the big picture on visits, sometimes we need to dig deeper and see more detail about how people are visiting us. What if we want to see visitors by platform?

For this information, we’ll use group by. One great thing about group by is that you group several times in a single query.

In this case, we could group by both the day a visitor came to visit, as well as which platform they visited on. This means we’ll know how many people visited each day by platform.

The two changes are to also select the platform column as well as including it in the group statement.

The SQL for the Unique Visits by Platform chart is:

select 
    date(created_at),
    platform,
    count(distinct visitor_id)
from visits
group by date, platform

New insights! We can see that mobile is really taking off while desktop stagnates.

Time to write some queries

With these common queries (and probably several joins), you should be equipped to make some insightful charts from your SQL database. Enjoy!

Tags:
Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?