<h2 class="wp-block-heading">Daily Active Users by Week</h2>



Some of us are familiar with the ubiquitous Daily Active Users metric. It’s a wonderful, concrete way to get a sense of your engagement.



Here’s a simple query that will do the trick:



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



Easy! Put it into your visualization tool and you get a graph like this:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-01-17.png" alt="Daily average users by week" class="wp-image-78673"/></figure>



Holy noise in the data, Batman! It certainly seems to be going up, but it’s hard to identify the trends. If only there were a way to smooth it out.



<h2 class="wp-block-heading">Running Averages</h2>



 To smooth this out, let’s look at&nbsp;Daily Active Users by Week, which is a weekly average of your DAUs. Note this is not the same as Weekly Active Users: Both are valid, yet measure different things. 



To compute DAU by Week, we’ll put our DAU query in a subquery, and wrap it in a query that takes a simple weekly average:



<pre class="wp-block-code"><code>select date_trunc('week', date), avg(count)
from (
 select date(created_at) date, count(distinct user_id) count
 from activity
 group by 1
) t
group by 1</code></pre>



Here’s the revised graph:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-02-18.png" alt="Revised daily average users" class="wp-image-78680"/></figure>



Much smoother! Now we can see that our DAUs are generally trending upward, but not explosively so, and not without some substantial dips.



<h2 class="wp-block-heading">Daily Running Averages</h2>



Weekly averages are all well and good, but they are very coarse-grained! There should be a way to show smoothed-out daily data.



For that, we’ll use a Trailing 7-Day Average. For each date on the graph, we’ll plot the average DAUs of the 7 days ending on that date.



To do this in SQL, we’ll turn to our favorite trick, the window function:



<pre class="wp-block-code"><code>select 
 date(created_at) d,
 avg(count(distinct user_id)) over (
 order by d
 rows between 7 preceding and current row
 )
from activity
group by 1</code></pre>



Let’s take our window function piece by piece:



<ul><li>avg(count(distinct user_id))&nbsp;is our basic operation.&nbsp;count(distinct user_id)&nbsp;is the DAU, and&nbsp;avg&nbsp;averages the DAUs.</li><li>order by d&nbsp;tells the window function in what order to look at the data, which is important because:</li><li>rows between 7 preceding and current row&nbsp;tells the window function which rows to average. In this case we want the 7 rows before the current row.</li></ul>



Put all of that together, and we get this graph:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-03-13.png" alt="7 day average" class="wp-image-78686"/></figure>



Boom! The lines are smoothed out to show macro trends, but not so much that we can’t see daily variations.



<h2 class="wp-block-heading">Or Click The “Running Average” Button</h2>



Try the “Line With Average” chart type. You can get the noisy daily data and the smooth running average in the same chart:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-04-12.png" alt="Daily average users with running data" class="wp-image-78692"/></figure>

Use Subqueries and Window Functions to Compute Running Averages

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