In this tutorial we will cover how to calculate monthly KPI's using monthly aggregate in sql, why it is important, a step-by-step example.

‍

What is the Monthly Aggregate and why is it important?

Monthly aggregate is the sum or average of all the data points over a month's time period. By breaking down the data points by month it is much easier for business teams to deduce insights and identify trends. This is particularly used in organizations with sales, marketing, financial, and shipping data. 

‍

Taking it a step further, plotting sales over time is a common and useful business intelligence task that helps a business understand how sales are changing. These plots are key to manage revenue, but also to help inform supply chain and other business operation decisions. Plotting over short timeframes can result in extremely noisy plots and may make it hard to determine the actual behavior. Aggregating the data to a longer time period can help smooth out the noise and see the information that is needed.

‍

Monthly Aggregate Example: 

Using retail sales data, such as internet sales in the AdventureWorks dataset, there can be a large volume of sales each day, and importing each sale into a visualization tool (e.g., Tableau, Looker, etc.) can be time-consuming and, in extreme cases, can exceed available memory of your computer. Even if the data fits into memory, loading the dashboard can be slow. To avoid these problems, the calculations can be performed in the database using SQL, and then only aggregated data can be imported into the tool.

‍

Below we will be looking at all of the monthly sales averages and totals for all internet sales from the AdventureWorks data. These totals will then be used to populate a dashboard showing how sales are changing over time.

‍

SQL:

Since the AdventureWorks internet sales data is at the transaction level and multiple transactions can occur on a given day, the data needs to be aggregated to a monthly level. First, the relevant month needs to be calculated for each transaction:

SELECT *
,  DATE_TRUNC(month, ORDERDATE) as MONTH 
FROM FACTINTERNETSALES

‍

Using this block either saved as a view, a subquery, or a common table expression (CTE), the monthly aggregates can be calculated. Here, a CTE is used.

WITH MONTHLY AS (SELECT *
,  DATE_TRUNC(month, ORDERDATE) as MONTH 
FROM FACTINTERNETSALES)

SELECT
MONTH
, SUM(SALESAMOUNT) as SALESAMOUNT_SUM
, AVG(SALESAMOUNT) as SALESAMOUNT_AVG
FROM MONTHLY
GROUP BY MONTH

‍

Conclusion: 

By following the steps above you have used the power of Monthly Aggregate and SQL to transform data directly in your cloud data warehouse. Please feel free to reach out to a member of our team if you have any additional questions.

‍

‍

‍