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.
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 which 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.
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:
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 easier 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 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!
If you learned something from this post, please share it with a friend who you think it would help as well.