<h2 class="wp-block-heading">Daily vs. Weekly vs. Monthly</h2>



Depending on your data, looking at the same metric with daily, weekly, or monthly aggregations can yield startlingly different results. This is why SaaS companies will look at new customers by day, week, and month. Each metric tells a unique, but important story.



Let’s take a look at an analysis staple, new customers per day:



<pre class="wp-block-code"><code>select date(created_at), count(1)
from customers
group by 1</code></pre>



Here’s our graph:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/New-Customers.png" alt="New customers" class="wp-image-75685"/></figure>



Mostly, we see pronounced day-of-week effects. The peaks have certainly increased in recent months, but it’s hard to say about the average.



Now let’s look at the weekly version:



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



Here we see:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/New-Customers-2.png" alt="Weekly customers aggregation" class="wp-image-75690"/></figure>



Now we can see the average is most definitely increasing. We can also clearly make out the trough we’d expect around the holidays.



Let’s see what a monthly aggregation can tell us:



<pre class="wp-block-code"><code>select date_trunc('month', created_at), count(1)
from customers
group by 1</code></pre>



These results are predictably much clearer about the long-term trend:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/New-Customers-3.png" alt="Monthly customer aggregations" class="wp-image-75695"/></figure>



All the seasonal effects are gone, and we see clearly that new customer growth was mostly flat for several months, and has started growing rapidly quite recently.



<h2 class="wp-block-heading">Combining Information</h2>



Given that all these aggregations tell us different things about the datasets, it would be ideal if one combined chart could communicate all the information.



The simplest way to write the query is to join all the above queries together using their dates:



<pre class="wp-block-code"><code>select daily.day, daily_count, weekly_count, monthly_count from (
 select 
 date(created_at) as day, 
 count(1) daily_count
 from customers
 group by 1
) daily 
left join (
 select 
 date_trunc('week', created_at) as week, 
 count(1) weekly_count
 from customers
 group by 1
) weekly on week = day
left join (
 select 
 date_trunc('month', created_at) as month, 
 count(1) monthly_count
 from customers
 group by 1
) monthly on month = day</code></pre>



Remember to join the monthly table on month = day, not month = week; otherwise, you’ll only include months whose first day happens to be the start of a week.



The results are accurate if not entirely useful:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/New-Customers-4.png" alt="Combined information customer chart" class="wp-image-75700"/></figure>



In particular, the fact that we only get a new weekly number once a week, and a new monthly number once a month, is glaring. What if we could keep the broader perspective of the longer-term metrics while maintaining granularity?



Instead of one point on the monthly line per month, let’s plot a trailing month for every single day. (And let’s do the same thing for weeks.) We can do this with <a href="https://www.sisense.com/blog/window-functions-by-example/">window functions</a>!



Let’s take a look:



<pre class="wp-block-code"><code>select
 d,
 daily_customers,
 sum(daily_customers) over (
 order by d
 rows between 7 preceding and current row
 ) weekly_avg,
 sum(daily_customers) over (
 order by d
 rows between 30 preceding and current row
 ) monthly_avg
from (
 select date(created_at) d,
 count(1) daily_customers
 from customers
 group by 1
) daily_new_users</code></pre>



This gives us much more natural lines that still preserve the relative benefits of the daily, weekly and monthly aggregations:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/New-Customers-5.png" alt="New customers chart 5" class="wp-image-75705"/></figure>



As a bonus, we only have to do the table scan once, and we don’t have to do any joins. The query will run much faster on large datasets.



<h2 class="wp-block-heading">Bonus Round: Trailing Averages</h2>



Now that we have our window function, it’s easy to modify it to get additional information about our customers! For example, by changing sum to avg, we can see trailing weekly and monthly averages of our new customer growth:



<pre class="wp-block-code"><code>select
 d,
 daily_users,
 avg(daily_users) over (
 order by d
 rows between 7 preceding and current row
 ) weekly_avg,
 avg(daily_users) over (
 order by d
 rows between 30 preceding and current row
 ) monthly_avg
from (
 select date(created_at) d,
 count(1) daily_users
 from customers
 group by 1
) daily_new_users</code></pre>



Voilà!



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/New-Customers-6.png" alt="Customers chart 6" class="wp-image-75710"/></figure>



By playing with the window definition, we can change our trailing average into any kind of running average we like!



Showing multiple aggregations on a single chart is one of the best ways to add context to your data. We hope these techniques for pulling that data have been helpful!

Daily, Weekly, and Monthly Charts On The Same Graph

Hack Your Analyst Skillset

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