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 error. This article explains how you can estimate total monthly cost based on elapsed days of that month, which can be visualized in Data Studio in 5 easy steps

1 ) Connect a calendar

2) Create a look-up for the Monthly estimate multiplier

3) Add your budget data and compare it with the forecasted values

4) Create the estimated field in Data Studio

5) Visualize in a Data Studio widget

1) Connect a calendar

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 until yesterday.

Start by creating your copy of the google sheet document and then connect that sheet to Funnel, please make sure to use the below configuration:

Please make sure that the Month estimate multiplier is a string and that the option no daily date is selected

2) Create a look-up for the Monthly estimate multiplier

Create a new look-up dimension in your Funnel account called Monthly estimate multiplier. 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.

Once we have created this field we are ready to send cost data along with the breakdown fields to google Data Studio for visualizations using Data Studio views by going to Export -> Data Studio -> New View and connect this to a report.

3) 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, and create a unique key to identify the budget for that combination. For example in this case can a key be created by taking {Traffic Source}_{Date} and we will create the same Key in Funnel. Please note that the values for Traffic Source should exactly match the name in Funnel.

After adding this budget sheet create a new lookup dimension Monthly Budget recreating the Key in Funnel what already exists in the google sheet for the budget so we can lookup budget value for that breakdown

4) Create the estimated field in Data Studio

The next step is to create few custom fields in the Data Studio to calculate the Spend Projection and Percentage Projection.

Go to your Data Studio connection - Your Data 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 Data Studio connection by clicking on "type" next to the field.

5) Visualize in a Data 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 Data 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

Did this answer your question?