Quickbase Analytics: Improve your monthly insights by implementing a Months table

Quickbase Analytics: 

Improve your monthly insights by implementing a Months table 

This article is the first in the Quickbase Analytics series. These posts outline different approaches builders can use to improve the analytical insights they gain from their Quickbase apps. Future posts in this series will build on this one. These posts are geared towards experienced builders as some more advanced concepts are used. 
 

Use the Quickbase Analytics Example App to follow along 
 

For those who view their organization’s results on a monthly basis, adding a Months table is one of the best ways to streamline reporting and insights. 

Grouping by month for summary reports and visual charts is great for quick and simple reporting, but there are limitations. Let’s look at a few: 

In this example we have an app that is tracking Opportunities through an Opportunities table. We are capturing Close Date, Revenue, Cost of Goods Sold, and calculating Profit Margin (Revenue - COGS)/Revenue. We’d like to track our Profit Margin monthly, so we spin up this summary report. 

Look at the January entry. Revenue is $5838.89 and COGS is $3273.10. So, our profit margin, using the formula above, should be 43.94%, but our summary report is showing 22.66%. 

Based on the way we set up this report, Quickbase is calculating profit margin for each record and averaging the result. When we do this, we lose the weight each sale has on our overall numbers! Anytime we have a formula, we need to be really careful we aren’t setting ourselves up to misreport our numbers.  

If we did want to find the correct Profit Margin for each month, our best bet is likely to export the data to a .csv and create the Profit Margin formula in a spreadsheet. Or, we can utilize a Months table. 

Here we have a Months table, where each month is a unique record. Opportunities closed in each month are tied to that Month through a relationship. Now we just need to create a couple of summary fields and recreate our profit margin formula at the Months level. In this report we can see the correct Profit Margin being shown for January. 
 

Another benefit of the Months table is the ability to add details specific to an individual month. For example, setting a revenue goal for each month. This is helpful if our sales tend to vary monthly/seasonally or we are anticipating growth as the year goes on. We can take this a step further and calculate % of goal with a simple formula (Total Revenue/Revenue Goal). 

Setting up the Months table: 

We want to create a process where Opportunities are tied to a month in the Months table based on the Close Date field. For example, if an Opportunity is closed on 9/16/20 it should be tied to the September 2020 record in the Months table. To accomplish this, we need to have: 

• A Months table, where each record is a unique month 
• A relationship between Months (parent) and Opportunities (child) 
• A way for Opportunities to automatically tie to the correct month when the Close Date field is filled in (so we don’t have to select the Related Month manually) 
   

To create our Months table, we will need a unique identifier/primary key that can be easily derived from an Opportunity’s Close Date field (covered in later step). We’ll simply use the first date of the month, captured in the field Month Start (ex. September 2020 record’s key will be 9/1/2020). Additionally, we can add a Month Name field to help with readability and reporting. You can download a csv file with months from 2017 - 2023 in the example app. 

Next, we’ll create a relationship between Months and Opportunities. Since numerous sales roll up to a single month, Months will be the parent table. 

Once we have our relationship, we need to figure out how to go from an entered Close Date on an Opportunity to the matching month’s Month Start. Our first step here is to create a field called Month Start Formula in the Opportunities table using Quickbase’s FirstDayOfMonth() function: 

Now we’ve identified the correct Month Start value, but we still haven’t linked the opportunities to the Month record. To do this we need to populate the Related Month field with the result of our formula. For this we will set up a simple pipeline. When an Opportunity’s Close Date field is updated, update the same record’s Related Month field with the value of the formula field.  

Once we have this mechanism set up, we are free to create our summary fields, formulas, and reports! Keep in mind, once we understand the basic set up, we can apply everything in this post to different time intervals (Weeks, Quarters, Years, etc.). 
 

If you want to try implementing a Months table in your app, check out the Quickbase Analytics Example App. There is a .csv ready to be downloaded containing everything you need in a Months table: Quickbase Analytics Example App