SQL for Marketers Part 4 — Common Analysis Queries
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.
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).
Next, we count() how many visits occurred on each day by simply adding count(1) and group by date.
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:
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:
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!
Want to discuss this article? Join the Periscope Data Community!