Estimate your total advertising spend for the current month and compare it with your budget in Google Data Studio. The forecast uses spend through yesterday so that incomplete data from today does not affect the result.

You can use the forecast to identify these conditions:

Underspending: the forecast is less than 90% of the monthly budget.
Within budget: the forecast is between 90% and 110% of the monthly budget.
Overspending: the forecast is greater than 110% of the monthly budget.

Before you begin

You can create data sources, custom dimensions and exports in Funnel.
You can edit the target Google Data Studio report.
You have the monthly budget values and any dimensions that you want to compare, such as Traffic Source.

Connect the calendar sheet

Open the monthly forecast calendar sheet. The Monthly estimate multiplier field divides the number of days in the month by the number of elapsed days.
In Google Sheets, go to File > Make a copy.
Enter a name for your copy.
Connect the sheet to Funnel by following Google Sheets: Connection guide.
Set Monthly estimate multiplier to the STRING type.
Select Data by daily date.

Create the forecast dimensions

Create a custom dimension that uses a Google Sheets lookup rule to return Monthly estimate multiplier.
Use the calendar date as the lookup key.
Create a Filter Today from ad-platform dimension.
Return FALSE when the advertising platform date is today.
Return TRUE for all other dates.

(Optional) Create dimensions for the percentage of the month that has passed and the percentage that remains. Use these dimensions to calculate how much of the budget you should have spent.

Create the Google Data Studio export

Create a Google Data Studio export as described in Export Funnel data to Google Data Studio.
Add the spend fields, date, Monthly estimate multiplier, Filter Today from ad-platform and required breakdown dimensions.
Edit the export filter.
Set Filter Today from ad-platform to TRUE. This filter excludes today's incomplete spend.
Connect the export to your Google Data Studio report.

Add your budget data

Import your budget data from Google Sheets.
If you have one total budget for each month, use the first day of the month as the date.
If your budget is split by a dimension, include that dimension with the date and budget. For example, include Traffic Source when each traffic source has a separate budget.
Add the budget fields to the Google Data Studio export.

Create the forecast fields

In Google Data Studio, open the data source from the report or from the data sources page.
Click Add a field.
Create a Monthly Forecast field that multiplies spend by Monthly estimate multiplier, as shown in the following image.

Create a Projection Percentage field with this calculation: Spend Projection / Budget.
Set the field type to Percentage.

You can also create these fields:

Percentage of the month passed: MAX(CAST(% of month passed as number))
Budget that you should have spent through yesterday: MAX(CAST(% of month passed as number)) * SUM(Target Spend)
Percentage of the month remaining: MIN(CAST(% of month remaining as number))
Days remaining: MIN(CAST(Days remaining as number))

Visualize the forecast

Add a table to your Google Data Studio report.
Add the budget, spend forecast and projection percentage fields.
Select the table.
Open the Style section.
Under Conditional formatting, click Add.
Select Color scale.
Set the threshold for each color range. For example, use ranges for less than 90%, 90% to 110% and more than 110%.

What to expect

Your report shows the projected spend at the end of the month and the forecast as a percentage of your budget. The color scale helps you identify underspending, spending within budget and overspending.

Data Studio export: Create a monthly advertising spend forecast