Introduction
For marketers, keeping track of ad spend is an important task that helps to estimate monthly costs and assess whether they are on track with their budget for that month. This can be done by using projections and understanding what level of spending we are at currently, and where we need to focus our optimization efforts. These projections can for example be broadly classified as
Underspending: Their monthly projections are less than 90% of their monthly budget.
Overspending: Their monthly projections are greater than 110% of their monthly budget.
Within budget: Their monthly projections are anywhere between the above limits
Keeping track of this information takes a lot of time as it needs to be adjusted on a regular basis, manually maintaining this information might lead to errors. This article explains how you can estimate total monthly cost based on elapsed days of that month, which can be visualized in Looker Studio in 5 easy steps
1) Connect a calendar sheet
This Google Sheet contains a calendar with a field called Monthly estimate multiplier which is the ratio of the number of days in the month to the number of passed days for that day in the row.
Start by first making a copy of the Google Sheets document, which can be done by going to File -> Make a copy, rename the file to make it distinguishable.
Once you have made your own copy, connect that sheet to Funnel, please make sure to use the below configuration while connecting:
Please make sure that the Month estimate multiplier is a string and that the option Data by daily date is selected
2) Create a look-up for the Monthly estimate multiplier
Once you have connected your sheet, start by creating a custom dimension using a lookup rule, this dimension would be later used to calculate the total estimated cost at the end of that month. Below is an example of how to configure that in Funnel.
You can create similar dimensions for '% of the month passed, '% of month remaining' if you want to answers questions like 'How much budget should have been spent?'
3) Filter out data for today's date
As stated before when creating projections we do not want to include today's data from the different cost platforms as they are still not finalized yet, for that we can create a dimension in Funnel to exclude today's data before sending it to Google Looker studio. You need to create a dimension that can help to do that
Filter Today from ad-platform: Using this to assign 'FALSE' if the data coming from the ad-platform is for today's date, for all other dates set as 'TRUE'
4) Create data export connection to Google Looker Studio
In order to get a better estimation of the projection for that month, we are using the cost date until yesterday. In this case, we are ignoring any cost collected today as that data is still not finalized, which can be achieved by using 'filter data' option under Edit Data Share. Filter on the dimension created in the previous step, 'TRUE' will select data until yesterday
Once we have chosen the fields that need to be sent with the breakdown to Google Looker Studio for visualizations using Looker Studio Data Shares, which can be done by going to Share -> Google Looker Studio -> + Create new Data Share and connect this to a report.
5) Add your budget data and compare it with the forecasted values
Add your google sheet containing the budget data to Funnel, if the budget is allocated only on a monthly basis then you can just use the first day of the month as the date and budget as the total budget for that month, but if your budget is for e.g. broken down on traffic source then use the breakdown fields along with the date.
The article "Add your marketing budget to Funnel" describes in more detail different ways your budget can be uploaded to Funnel.
6) Create the estimated field in Looker Studio
The next step is to create few custom fields in the Looker Studio to calculate the Spend Projection (Estimated spend at the end of the month) and Percentage Projection (%age compared to budget).
Go to your Looker Studio connection - Your Looker Studio connection can be accessed either through the report (Resouces -> Manage added data sources) or by following this link.
a) After selecting your data source click on add a field and create a field for Monthly Forecast using the below formula
b) Calculate the monthly Projection Percentage = Spend Projection / Budget by creating another field and changing the field type to Percentage.
The field type can be changed in the Looker Studio connection by clicking on "type" next to the field.
Using the same method as above you can also calculate the
% age through the month
MAX(CAST(% of month passed as number))Total budget that should have been spent until yesterday
MAX(CAST(% of month passed as number)) * SUM(Target Spend)% age of the month remaining
MIN(CAST(% of month remaining as number))Days remaining
MIN(CAST(Days remaning as number))
7) Visualize in a Looker Studio widget
When presenting this data in a report we need to draw the attention of the user to any actionable insights using some of the visible cues. If the projection for a month shows that on a current spending level we are going to either over/underspending or budget, this can be easily pointed out using colors.
Below is an example data where we can see that we are almost within the budget for April for the ad spend for Google, but we can quickly see that we underspending our monthly spending budget for Facebook by 12%. Using this information we can adjust our bids to optimize the performance of our campaigns.
To create a custom color scale in Looker Studio, you need to use a table and apply conditional formatting to that column which can be done by
Select the table
Click on the STYLE section of the Chart > Table
Under conditional formatting click Add
Choose an option for Color scale and define the threshold for each color bucket
