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

The High-Performance SQL Blog

SQL for Marketers — 3 Ways to Slice Your Acquisition Data

February 13, 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 onepart twopart threepart four, and part five in the archives.

Acquisition metrics are a well covered blog topic. It’s easy to figure out the general metrics you should follow. The only issue is that the authors assume you’re using an analytics tool like Google Analytics or Mixpanel.

At Periscope, we like to dive deeper into our data than these tools allow.

That’s where the queries below come in. We took some popular acquisition metrics and wrote the SQL queries that you’d need to measure them.

Get Intimate with Your Data

Before jumping into the queries, you’ll need to know how your database is structured. Specifically, you need to know what tables your acquisition channel, lead, and lead quality data are in. Also, you’ll need to know the column names for your signup timestamps, channel names and quality score.

For this blog post, we’ll keep things simple and assume our acquisition, lead, and lead quality data are in the same table. Our signup timestamp column name is created_at, our channel names column is channel, and our quality score column is quality_score.

Total Signups

select 
  count(1
from signups

We’re going to start with an easy one: getting your total signup count. This query will act as a base for the following queries.

count() is a function that counts through rows. In this case, because we haven’t specified any other restrictions, count() is counting all the rows in the signups table.

Here’s how the results look in Periscope:

Signups per Day

select 
  date_trunc('day', created_at),
  count(1)
from beta_signups
where created_at >= '2014-10-01' 
group by 1

Timestamps count the time down to the second. To get the number of signups per day, we’ll first need to convert the timestamp to a date with the date_trunc() function.

Then we’ll use group by to group results by day. The 1 in the group by statement refers to the first column we selected — in this case, the date.

We don’t want our entire history of signups per day, so we use the where clause to restrict the results to signups that occurred on October 1st, 2014 or later.

Signups per Week and Month

select 
  date_trunc('week', created_at), 
  count(1)
from signups 
where created_at >= '2014-10-01' 
group by 1

Signups per day can get noisy. To make trends eaiser to spot, you can group signups by week or month as well. Just replace the ‘day’ in date_trunc('week', created_at) with ‘week’. For monthly aggregation, use ‘month’ instead.

Signups over a Custom Time Period

select 
  date_trunc('day', created_at), 
  count(1)
from signups 
where created_at >= now() - interval '30 days'
group by 1

While seeing leads per day, week or month is helpful, we’re often interested in metrics over the previous 7 or 30 days.

To accomplish this, we replace created_at >= '2014-10-01' with the now() function in the where clause. The above query returns signups per day over the last 30 days.

For a refresher on now(), check out our second post in the SQL for Marketers series.

Signups per Day by Channel

select 
  date_trunc('day', created_at), 
  channel,
  count(1)
from beta_signups
where created_at >= '2014-10-01' 
group by 1, 2

Splitting out your leads per day by channel is easy. We just add the channel column to the select statement and include the it in the group by statement.

Signups per Day by Quality

select 
  date_trunc('day', created_at), 
  quality_score,
  count(1)
from signups
where created_at >= '2014-10-01' 
group by 1, 2

Getting signups per day by quality is just as easy as splitting the data out by channel. You just replace channel with quality_score in the select statement.

Go Forth with Your New Knowledge

You should now have a solid base of acquisition metric queries to measure your progress!

Want a query for a metric we didn’t cover? Either tweet us at @PeriscopeData or shoot an email to [email protected] and we’ll see what we can do.

And if you learned something from this post, please share it with a friend who you think it would help as well.


Want to discuss this article? Join the Periscope Data Community!
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?

Subscribe to our Newsletter