Import your marketing budgets and targets from Google Sheets into Funnel. You can then compare your budget with your actual results.
Before you begin
Choose the budget case that matches your data.
Make a copy of the relevant Google Sheets template.
Prepare the budget values and any dimensions that you want to compare in Funnel.
Choose a budget template
Companies organize budgets in different ways. The following cases cover common budget structures.
Case A: Compare a total monthly budget with monthly cost
Use this case to compare monthly spending with the total budget for that month.
Google Sheets template: Budget [Templates] - Case A - Monthly Budget
Case B: Compare a monthly budget by dimension with monthly cost
Use this case when you have a separate monthly budget for each dimension value, such as Traffic Source.
Google Sheets template: Budget [Templates] - Case B - Monthly-Traffic Source Budget
Case C: Split a monthly budget into daily targets
Use this case to compare a monthly budget or target with daily actual results. The custom function distributes each monthly value equally across the days in that month.
Case D: Compare a campaign budget with cost for a custom date range
Use this case for campaigns that have different start dates and end dates. The custom function splits each campaign budget into daily values.
Google Sheets template: Budget [Templates] - Case D - Campaign Budget
Prepare the budget sheet
Case A: Compare a total monthly budget with monthly cost
A budget sheet can represent a month in several ways, such as January 2020, Jan 2020 or 202001. To make the date consistent, enter the full monthly budget on the first day of the month. Funnel maps this date to the correct month.
Change the template values to match your budget. Then follow the steps in Import your budget into Funnel.
Case B: Compare a monthly budget by dimension with monthly cost
Enter the full monthly budget on the first day of the month. Add a field for the dimension that divides the budget. This example uses Traffic Source. Make sure that each dimension value exactly matches the corresponding value in Funnel.
Change the field values to match your budget or target. Then follow the steps in Import your budget into Funnel.
Case C: Split a monthly budget into daily targets
Make a copy of the Case A or Case B budget template.
Change the field values to match your budget.
Make sure that the month column uses the date type and contains the first day of each month.
Click + in the lower-left corner to add a sheet.
Copy the relevant formula into cell A1 of the new sheet.
The SplitMonthToDaily function converts monthly values to daily values. The function requires the budget sheet name, data range, month column name and budget column name. If your sheet or columns use different names, update these arguments in the formula.
Split one metric into daily targets
To split one metric, such as Budget, use =SplitMonthToDaily('[Template] Case A - Monthly Budget'!A:D,"First day of the month","Budget").
Split multiple metrics into daily targets
To split multiple metrics, such as Budget and Revenue, use =SplitMonthToDaily('[Template] Case B - Monthly-Traffic Source Budget'!A:D,"First day of the month",{"Budget","Revenue"}). Keep the curly braces around the metric names.
After the function finishes, the new sheet contains daily budget values. Then follow the steps in Import your budget into Funnel.
Case D: Compare a campaign budget with cost for a custom date range
Store each campaign budget with a campaign identifier and a date range. For example, use Campaign name or Campaign ID as the identifier. Use Start date and End date for the date range.
The custom function distributes the budget equally across all campaign days. For example, a campaign with a budget of 100 USD that runs for 10 days gets a daily budget of 10 USD.
Add a sheet.
To split only the Budget metric, paste
=SplitToDaily('[Template] Case D - Campaign Budget'!A:F,"Start date","End date","Budget")into the first cell.
To split multiple metrics, such as Budget and Revenue, paste
=SplitToDaily('[Template] Case D - Campaign Budget'!A:F,"Start date","End date",{"Budget","Revenue"})into the first cell.
If your columns use different names, update the column names in the formula.
After the function finishes, the new sheet contains daily budget values. Then follow the steps in Import your budget into Funnel.
Note: A custom function can fail when it processes many rows.
Import your budget into Funnel
Use the Google Sheet that you prepared to upload your budget to Funnel. Configure the fields as follows:
Set the date field to the
DATEtype and use theYYYY-MM-DDformat.
Set the Budget Value unit type to
MONETARYand the aggregation toSUM.
Set the date attribution to
Data by daily date.
If your budget contains extra fields, such as
Traffic Source, edit the matching custom dimension. Add a rule that uses the dimension from the sheet. The following example adds sheet data to the existingTraffic Sourcedimension.
What to expect
Your budget data is available in Funnel with the date, monetary value and dimension settings that you configured. You can compare the budget with your actual results.
To learn how to compare the values, see How to create a forecast for your monthly advertising spend in Data Studio.
You can also use the budget comparison demo dashboard as an example.