Comparing your campaign budgets with actuals is a good way to monitor the performance. Before you can do that, you first need to take those siloed budget sheets and import them into Funnel. This article will show how you can add your marketing budget to Funnel using Google Sheets and then compare it with the cost.

Budget templates

The way companies organize their budget can differ depending on the type of business and the use case. Since it is not possible to cover all the cases, we have compiled a list of the most common ways to work with budgets:

Case A: Comparing total budget with a monthly cost:
A simple case where you want to keep track of the monthly spending and compare it to the total budget for that month.

Google Sheet: Budget [Templates] - Case A - Monthly Budget

Case B: Comparing the budget broken down on dimension with a monthly cost:
When companies for e.g. have a monthly budget for each 'Traffic Source'.

Google Sheet: Budget [Templates] - Case B - Monthly-Traffic Source Budget

Case C: Comparing the budget for each campaign with cost, depending on the period for that campaign:
A common use case for companies that run multiple campaigns over different periods, e.g. a campaign with a start and end date that does not start/end with month/week. One way to use this budget information would be to split the budget for that campaign daily.

Google Sheet: Budget [Templates] - Case C - Campaign Budget

Case A: Comparing total budget with a monthly cost:

There are multiple different ways in which a month can be expressed in a budget sheet e.g. 'January 2020', 'Jan 2020', '202001' etc. Since the final goal is to compare that budget for a month with cost, we recommend adding the entire budget to the 'first day of the month' in the sheet. Funnel is able to recognize these dates and will auto map the budget to the correct month.

Below is a screenshot of how this type of template may look, you can find the Google Sheet here. The Google Sheet has multiple sheets, use the sheet called 'Monthly Budget':

Case B: Comparing the budget broken down on dimension with a monthly cost

Similar to the case above we use 'first day of the month' for the entire budget, then add an extra field by which the budget is distributed. In this example, we are taking 'Traffic Source'. Make sure the extra fields are spelled exactly how they are in Funnel.

The Google Sheet template can be found here and it's called Monthly-Traffic Source Budget

Case C: Comparing the budget for each campaign with cost, depending on the period for that campaign

One of the common ways to store these campaign budgets is using a campaign identifier like 'campaign name/id', along with the date range. The date range can be expressed with the fields 'Start date' and 'End date'.

Below is the screenshot of that sample use case:

The budget above can be linearly distributed to each day, In this case, we distribute the budget equally among the days of the campaign, e.g. if the budget for 'Campaign X' is 100 USD and runs for 10 days then each day will have a budget of 10 USD. This is achieved by using the google sheet custom function and processing each row of data to create daily budget limits.

Below is how this result of the function would look like.

We have already created an example of the function for you, which would be made available once you have copied the sheet, and would look like '=SplitToDaily('Campaign Budget'!1:1000,"Start date","End date","Budget")' in the sheet 'Campaign Budget Daily'. In case you have named your columns differently this formula might needs adjustments.

Follow the below steps to convert the budget for a period to the daily budget:

  1. Make a copy of the Budget [Templates - Campaign Budget] by going to File -> Make a copy

  2. Add your campaign information on the sheet 'Campaign Budget' with the relevant budgets

  3. The sheet 'Campaign Budget Daily' is where the campaign daily budget will be updated, it uses a custom function 'SplitToDaily'. You do not need to edit this as the data will update automatically once the 'Campaign Budget' sheet is updated

Upload your budget, and normalize those fields with your data in Funnel

Depending on the template which you have chosen above, use that google sheet to upload in Funnel. A few things to note when uploading the google sheet:

  • Make sure that the field which has the date is set as 'DATE' with a format as 'YYYY-MM-DD'

  • The Budget Value is set to unit type 'MONETARY' and aggregation as 'SUM'

  • The Date attribution is based on a 'Data by daily date'

  • If you are using extra fields in your budget for e.g. 'Traffic Source' you can normalize that by editing one of the custom dimensions and adding a rule to use traffic source dimension from the sheet

Closing thoughts

Adding the budget data is the first step to start comparing your budgets with actuals. In the article How to create a forecast for your monthly advertising spend in Data Studio, we go into more details on how you can start comparing those.

Did this answer your question?