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.

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: Break down 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 Case 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

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 change the values to reflect your budget, now this new sheet is ready to be imported into Funnel

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.

Once you have adjusted the values of different fields to reflect your budget/target, then this new sheet is ready to be imported into Funnel.

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 can be used to convert them to daily levels, called "SplitMonthToDaily" which can be set up as explained below. 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.

Follow the below step:

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 the new budget will be distributed on a daily basis, now this new sheet is ready to be imported into Funnel

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'.

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.

Start by creating and new sheet, and on the first cell paste below formulas '=SplitToDaily('[Template] Case D - Campaign Budget'!A:F,"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('[Template] Case D - Campaign Budget'!A:F,"Start date","End date",{"Budget","Revenue"})'. In case you have named your columns differently this formula might needs adjustments to reflect the correct column names.

Once the function is done calculating the new budget will be distributed on a daily basis, now this new sheet is ready to be imported into Funnel

[Expert tip] 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.

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:

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. For inspiration follow this demo dashboard on how to visualize and compare your actuals against targets.