Managing online campaigns can be a tedious task. Save time and energy by leveraging the different integration tools available on Google SpreadSheet:
- Import Data easily from Google Analytics or Google AdWords
- Create advanced automated tracking systems
- Filter data and create powerful pivot tables
- Create readable and understandable reports
To get started with Google Spreadsheet, we’ll show you an easy way to track your monthly budget on Google AdWords. The template of this report will be shared at the end of this article.
Before getting started
Even though it is now possible to integrate Google AdWords directly on Google Spreadsheet, we will still be using Google Analytics add-on for three main reasons: Google AdWords Add-on is still in Beta phase, the report is not flexible (e.g. formulas for periods) and finally it can’t be scheduled yet.
Configuring your report
We will be assuming that you’ve downloaded and implemented Google Analytics Spreadsheet Add-on. If not, you can find the required steps on the following link.
What would you need to configure your report?
Even though our introduction to Google Spreadsheet will remain straightforward in terms of customization, Google gives tools to build a report suiting to your needs.
Naming your report
Bear in mind that the name you choose will be used later as part as the tab integration. We would advise you to add a meaningful name. For instance, we strongly suggest that you use either the named account or client or the subcategory of this one and the period taken into consideration (e.g. PM standing for Previous Month.)
Selecting the View ID
The second important step consists in picking the most relevant Google Analytics view for you. Depending on your strategy, you should have at least a few you can integrate (e.g. Filtered view or not). No matter the one you choose, it needs to be linked to Google AdWords.
Picking a period range
For the purpose of this blog article, we’ll be only considering the results of the current month in comparison to the previous one. Besides, in order to have accurate figures, we’ll be excluding the current day. In other terms, all the formulas are meant to be set at D-1.
Formulas used in the report:
Current Month
First day of the current Month: =TODAY()-DAY(TODAY())+1
Yesterday: =TODAY()-1
Previous Month
The first day of previous Month:=EOMONTH($B$4,-2)+1 [$B$4 referring to the cell used to add the formulas related to the first day of the current month]
Yesterday (previous month) : =TODAY()-1-DAY(EOMONTH(TODAY(),-1))
Metrics & Dimensions
While the first three steps could be considered as the foundations of your report, this stage let you the possibility to be more creative. Indeed, Google let you import a wide range of Metrics and Dimensions (Link to the resource).
Which Metrics & Dimensions are necessary to build our report?
Metrics:
ga:adCost ~ How much did Google AdWords cost on the defined period
Dimensions:
Ga:day ~ Allowing to break down results per day
Filters & Segments
These elements might be necessary in few cases if you wish to go more granular in specific areas. In the present example, we’ve added “ga:sourceMedium==google / cpc” to only consider traffic coming to Google AdWords.
Once all the steps are completed you’re good to run your report and build your budget tracker.
Creating your budget tracker
Simplifying your management formulas
Before even building your budget tracker, you need to think about all the formulas/recurrent elements you might need to use later. Even though this example is quite straightforward, grouping them will save you a huge amount of time with more an advanced structure.
Building your budget Tracker
As part of managing your budget, we do believe crucial to track at least these three elements:
- Average spend for a certain period (3 or 7 days)
- Outstanding daily budget
- Spend projection
Please bear in mind that you might need to be familiar with these elements to fully understand the structure of this report:
~ “INDIRECT” allows us to returns a cell reference specified by a string
~ “CONCATENATE” needs to be used on Google Spreadsheet.
~ “SUMIF” allows adding rows if they match a specific criterion
Let’s break down each column:
In order to display your Google AdWords spend, it is necessary to import it from the report Google Analytics add-on produced. To avoid using repetitively the name of each tab, we simplified the integration with “INDIRECT”. Doing so, you’ll be able to add more rows without having to change any formulas.
Account:
Refer to the name of the report you chose (See above)
Budget
The allocated budget for the account. The currency used in this report should ideally be the same than the one on Google Analytics.
Spend
=INDIRECT(CONCATENATE(“‘”,A2,”‘!B12″))
This formula helps to import the monthly spend from the tab Google Analytics add-on produced.
Average Spend for the last 7 days
=SUMIF(INDIRECT(CONCATENATE(A2,”!$A$16:$A$44″)),”>=”&DAY(TODAY())-7,INDIRECT(CONCATENATE(A2,”!$B$16:$B$44″)))/7
This formula allows knowing your average daily spend over the selected period. We chose to show the results for the last 7 days. However, it could be used for shorter or longer period of time.
For instance, for only 3 days,
=SUMIF(INDIRECT(CONCATENATE(A2,”!$A$16:$A$44″)),”>=”&DAY(TODAY())-3,INDIRECT(CONCATENATE(A2,”!$B$16:$B$44″)))/3
Outstanding daily budget
=(B2-C2)/(EOMONTH(TODAY(),0)-(TODAY()-1))
Knowing how much you spend on a specific period is important only if you can compare it to the outstanding daily budget for the month. With this formulas, you can indeed to know easily how much you can afford to spend daily for the rest of the month.
Spend Projection
=Iferror((C2/Formulas!$B$6)*Formulas!$B$4,”-“)
This last formula allows you to have a clear idea of how you risk spending if you keep spending in the same way.
To go one step further
This introduction to Google Spreadsheet is meant to be accessible and straightforward. Even though an analysis at Account level is necessary, it won’t replace the optimization at more granular levels (Campaigns, Adgroups, KeyWords…).
You can download the template of the report on the following link.
If this kind of reports is interesting you, feel free to get in touch to discover what Green T Digital can do for you.
Comments are closed.
