One of the most common questions analysts ask is “Have our metrics improved over time?”



This is typically answered with a week-over-week, month-over-month, or year-over-year comparison. For this post, we’ll do a year-over-year comparison.



A few standard calculations are required to do this:



<ul><li>Grouping your data by year</li><li>Calculating percent change</li><li>Plotting years in parallel</li></ul>



<h2 class="wp-block-heading">Grouping Data By Year</h2>



Let’s use Revenue as the example for our year-over-year comparison. To get revenue by year, we group by year and sum the price.



<pre class="wp-block-code"><code>select
 extract(year from created_at) as year
 , sum(price) as revenue
from
 purchases
group by
 1</code></pre>



This will give you a graph that looks like this:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/year-over-year-1.png" alt="Year over year 1" class="wp-image-76829"/></figure>



<h3 class="wp-block-heading">Calculating Percent Change</h3>



To enhance the data grouped by years, we can do a quick percent change calculation.



In Redshift and Postgres, we can use window functions to quickly do the calculation. To do this, we lag the revenue column over the previous year using the lag function. Remember to set the order of the rows to ensure we get the correct row:



<pre class="wp-block-code"><code>lag(revenue) over (order by year)</code></pre>



In our query, we subtract this year’s value over last year’s value from one to get the percent change:



<pre class="wp-block-code"><code>select
 year
 , revenue
 , (revenue / nullif(lag(revenue) 
 over(order by year), 0)) - 1 as percent_change
from
 (
 select
 extract(year from created_at) as year
 , sum(price) as revenue
 from
 purchases
 group by
 1
 )
 annual</code></pre>



We can see both the actual values as well as the percent change with the results:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/year-over-year-2.png" alt="Year over year 2" class="wp-image-76834"/></figure>



<h3 class="wp-block-heading">&nbsp;Plotting Years in Parallel</h3>



Sometimes we want to compare exact months or even days between years. In this example, we want to be able to compare January from this year to last year. We will use to_char to get the name of the month for readability. We will then have to order by the number of the month to get our data in the right order.



<pre class="wp-block-code"><code>select
 extract(year from created_at) as year
 , to_char(created_at, 'Mon') as month
 , sum(price) as revenue
from
 purchases
group by
 1
 , 2
 , extract(month from created_at)
order by
 extract(month from created_at)</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/year-over-year-3.png" alt="Year over year 3" class="wp-image-76840"/></figure>



To take it one step further, we can also compare month-over-month growth between the years. This time, we need a partition by in our lag function to make sure that we calculate percent change between months in the same year:



<pre class="wp-block-code"><code>select 
year
 , month
 , revenue
 , (revenue * 1.0 / lag(revenue) 
 over (partition by year order by month_number)) 
 - 1 as percent_change
from (
select
 extract(year from created_at) as year
 , to_char(created_at, 'Mon') as month
 , extract(month from created_at) as month_number
 , sum(price) as revenue
from
 purchases
group by
 1
 , 2
 , 3
)
order by month_number</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/year-over-year-4.png" alt="Year over year 4" class="wp-image-76845"/></figure>



With these techniques in hand, you can now calculate period-over-period metrics with your data!

Calculating Year-over-Year Metrics

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