Comparing your campaign budgets with actuals is a good way to monitor the performance. but 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

achieve that using Google Sheets with some predefined templates which are ready to use.

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 sheets and import them to Funnel

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 [Template]: Budget [Templates] - Case A - Monthly Budget

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

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

Case C: Breakdown your monthly budget into daily targets

If you set your budgets monthly but want to compare them with actuals daily, it is possible to create a daily budget that is linearly distributed across the month, you can also distribute your targets e.g Target revenue. You can use the Budget template from either of the above Case A or B

Google Sheet [Template]; Choose anyone from either Case A or B above

Case D: 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 D - 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 can 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: Breakdown your monthly budget into daily targets

To start distributing the budget linearly from monthly to daily, first you need to use one of the budget templates (Case A or B) containing data on a monthly level, make sure the column which defines the month is a date type, and the first day of that month. The second step would be to use a custom function that we have made available with the template that would create another sheet and convert them to daily levels, called "SplitMonthToDaily" which is already set up here. The function requires (budget sheet name with the data range, Month column name, and budget column name) that might need to be adjusted if you are using a different name for your sheet.

Option i: When you want only one metric (e.g Budget) to daily targets

You can use a formula like '=SplitMonthToDaily('[Template] Case A - Monthly Budget'!A:D,"First day of the month","Budget")'

Option ii: When you want to distribute multiple metrics (e.g Budget, Revenue, MQL etc.) to daily targets

You can use a formula like (note the curly braces at the end) '=SplitMonthToDaily('[Template] Case B - Monthly-Traffic Source Budget'!A:D,"First day of the month",{"Budget","Revenue"})'

Once the function is done calculating below is how your result might look like, now this sheet is ready to be imported into Funnel

Using a custom function with a large number of rows can sometimes end up an error, to make the setup more robust and also handle a larger amount of data we recommend using google sheets inbuilt scheduler called a trigger. See the section below about scheduling a function.

Case D: 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 to use a campaign identifier like 'campaign name/id' and 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")' if you just want to split your budget between these dates. In case you want to distribute multiple metrics like Budget and Revenue, the formula would be '=SplitToDaily('Campaign Budget'!1:1000,"Start date","End date",{"Budget","Revenue"})' 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

Schedule a function to run on a time interval

For the budget date which spans more than a year our recommendation to schedule the function instead of calling it directly from the Google Sheet, so it runs in the background.

Follow below to schedule a function:

  1. Go into your copy of the Google Sheet -> Tools - >Script editor to open AppScript editor

  2. Click on "Triggers" in the left Menu

  3. Click on "Add Trigger" to add a new one

  4. Select the function "schedulerSplitDateRangeToDaily" if your budget has a time period or 'schedulerSplitMonthlyToDaily' if your budget is based on a monthly level and choose how frequently the function should be updated.

The function "schedulerSplitDateRangeToDaily" is set up to work for Case D which required that you provide both the start and end dates for this to work. Depending on the function you have chosen for scheduling you have to edit only this section of the code and provide values for:

  • Source sheet name: where the budget data is stored

  • Destination sheet name: where you want to store your result for daily levels

  • Start/End date: for working with monthly you just need the first day of the month

  • Target columns: name of the columns which you want to split to daily levels

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 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?