
<p>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.</p>



<p>We like to dive deeper into our data than these tools allow.</p>



<p>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.</p>



<figure class="wp-block-image"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-1-acquisition-blog.png" alt="Signups per day by channel" class="wp-image-73790"/></figure>



<h2 class="wp-block-heading"><strong>Get Intimate with Your Data</strong></h2>



<p>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.</p>



<p>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.</p>



<h2 class="wp-block-heading"><strong>Total Signups</strong></h2>



<pre class="wp-block-code"><code>select 
  count(1) 
from signups</code></pre>



<p>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.</p>



<p>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.</p>



<p>Here’s how the results look:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/10.8k-image-slice-blog.png" alt="10.8k Total Signups" class="wp-image-73867"/></figure>



<h2 class="wp-block-heading"><strong>Signups per Day</strong></h2>



<pre class="wp-block-code"><code>select 
  date_trunc('day', created_at),
  count(1)
from beta_signups
where created_at >= '2014-10-01' 
group by 1</code></pre>



<p>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.</p>



<p>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.</p>



<p>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.</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-2-acquisition-blog.png" alt="Signups per day chart" class="wp-image-73795"/></figure>



<h2 class="wp-block-heading"><strong>Signups per Week and Month</strong></h2>



<pre class="wp-block-code"><code>select 
  date_trunc('week', created_at), 
  count(1)
from signups 
where created_at >= '2014-10-01' 
group by 1</code></pre>



<p>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(&#8216;week&#8217;, created_at) with ‘week.’ For monthly aggregation, use ‘month’ instead.</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-3-acquisition-blog.png" alt="Signups per week chart" class="wp-image-73780"/></figure>



<h2 class="wp-block-heading"><strong>Signups over a Custom Time Period</strong></h2>



<pre class="wp-block-code"><code>select 
  date_trunc('day', created_at), 
  count(1)
from signups 
where created_at >= now() - interval '30 days'
group by 1</code></pre>



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



<p>To accomplish this, we replace created_at &gt;= &#8216;2014-10-01&#8217; with the now() function in the where clause. The above query returns signups per day over the last 30 days.</p>



<p>For a refresher on now(), <a href="https://www.sisense.com/blog/sql-for-marketers-part-2/">check out our second post</a> in the SQL for Marketers series.</p>



<h2 class="wp-block-heading"><strong>Signups per Day by Channel</strong></h2>



<pre class="wp-block-code"><code>select 
  date_trunc('day', created_at), 
  channel,
  count(1)
from beta_signups
where created_at >= '2014-10-01' 
group by 1, 2</code></pre>



<p>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.</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-1-acquisition-blog.png" alt="Signups per day by channel" class="wp-image-73790"/></figure>



<h2 class="wp-block-heading"><strong>Signups per Day by Quality</strong></h2>



<pre class="wp-block-code"><code>select 
  date_trunc('day', created_at), 
  quality_score,
  count(1)
from signups
where created_at >= '2014-10-01' 
group by 1, 2</code></pre>



<p>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.</p>



<h2 class="wp-block-heading"><strong>Go Forth with Your New Knowledge</strong></h2>



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



<p>If you learned something from this post, please share it with a friend who you think it would help as well.</p>


SQL for Marketers — 3 Ways to Slice Your Acquisition Data

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article