Simple month-to-date Forecasting

low_priority
done
moderate
(Marcus O'hanlon) #1

A very common piece of analysis people like to report on is projecting KPIs out for the remainder of the month. This helps them answer questions like:

  • How much revenue will I achieve by the end of the month?
  • How many customers can I expect to acquire by month end?

Method
The simplest and quickest way to create these projections is to simply take your current month performance and add it to the same value divided by the current day of month and then multiply this by the number of remaining days in the month.

Example Output
The output of this pattern will allow us to forecast the remainder of the current months performance for any KPI we are interested in (red bar indicates the forecast):

Try it Yourself - How it’s Done
Simply bring in an appropriate month column and the KPI you want to forecast (the logic below could be re-worked for weekly, quarterly on yearly projections).

Dimensions:
An approriate month column

Measures:
The KPI you want to forecast (in this example gross_margin)

Table Calcs:
The table calcs explained below could be merged into a single calculation but in order to help understand the logic they have been separated here :

Days in Month: calculate the total days in the current month

extract_days(
            add_days( -extract_days(now()),  add_months( 1, now()) ) )

Day of Month - 1: calculate the current day of the month

extract_days(now())-1

Forecast: use the formula described above to calculate the forecast:

 if(row()=1,
     (${order_items.total_gross_margin}/${day_of_month_1})*(${days_in_month}-${day_of_month_1})
    ,0)

7 Likes

The Podium — March 20th, 2019
(Nicholas Wong) #2

Thanks Marcus! This have been really helpful.

Manage to implement it successfully.

29%20AM

1 Like

(Nick Morrison) #3

Great explanation, Marcus!

For my use case, I needed a forecast for the month where performance was compared to the same month in the previous year. I found that I needed a forecast that was not going to be skewed heavily by early outliers (i.e. high or low spikes in performance early in the month that would have a big impact on the extrapolation). To refine the forecast, I developed a formula that would take the current year’s performance to date and then add on the previous year’s performance for the remainder of that month.

First of all, my table is set up with events / categories as the row labels, and then I have a MTD columns of data for the current year and previous year.

if(is_null(${2018_mtd}),
  ${2019_mtd}/extract_days(now())
  *extract_days(add_days(-1,date(extract_years(add_months(1, now())), 
  extract_months(add_months(1, now())), 1))), 
  ${2019_mtd}+${2018_remainder}*[forecast growth rate]))

Hope this helps anyone in a similar situation.

1 Like

(Marcus O'hanlon) #4

Hi Nick, thanks for the additional context, I actually spoke with a customer who was recently looking for just this type of analysis and with your comment it spurred me to try and create some re-usable LookML to do this. The code below could be updated to include your forecast_growth parameter too:

  measure: total_gross_margin_mtd {
type: sum value_format_name: decimal_2
sql: ${gross_margin} ;;
filters: {field:created_date value:"this month"}
}


dimension: is_last_year_mad {
type: yesno
description: column to evaluate if the date is 
sql:   (((CAST(order_items.created_at as TIMESTAMP) ) >= DATEADD(month,-12,current_date()) 
AND (CAST(order_items.created_at as TIMESTAMP) )   < ((CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(month,1, 
 DATEADD(month,-12, DATE_TRUNC('month', DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', current_date()))) ) )))))) ;;
}

measure: total_gross_margin_last_year_mad {
type: sum value_format_name: decimal_2
sql: ${gross_margin} ;;
filters: {field:is_last_year_mad value:"Yes"}
}

measure: forecast {
value_format_name: decimal_2
type: number
sql: ${total_gross_margin_mtd} + ${total_gross_margin_last_year_mad} ;;
}
4 Likes