Build a tailored and free reporting for your startup’s traffic acquisition team (Adwords, Facebook,…)
In a startup, as soon as your marketing team starts acquiring some traffic from a few different sources (and they will quickly, if only to test the traction of your product/service), they will spend more an more time monitoring their KPIs (impressions, clicks, budget spent, sessions, conversions, …) from the reporting tools of each channel that they use.
Even for low amounts of money spent in online advertising, the effort and time required to monitor and optimize their ROI grow quickly, as well as the time needed to aggregate the information they read from the various reporting applications (Facebook Business Ads Manager, Adwords, Google Ads Express, MailChimp, Google Analytics…). Very quickly, they will want something landing directly in their favorite tool : a spreadsheet (and if possible in near real-time…).
In this post, we describe how we quickly provided a flexible and totally free solution to our marketing team for them, to analyse their traffic acquisition metrics exactly in the format that they want.
TL;DR Google Spreadsheet, Apps Script, and APIs
The basic idea is to query APIs from the various channel providers with a Google Apps Script that populates and updates a Google Spreadsheet. This script(s) can be scheduled and monitored to be executed on a regular basis from within the Google Apps suite. A nice bonus is that there is no hosting to manage for these scripts as they are executed (for free) on the Google Apps infrastructure.
As we need to keep this convenient to maintain, and easy to use for your traffic acquisition team, we use the organization that we describe here.
Structure the spreadsheet
We provide in a single spreadsheet the data for all the acquisition channels, and want to offer the possibility to analyze each of them separately with their own specifics or together with their shared indicators.
Thus we organize the spreadsheet with the following worksheets :
- for each channel, a raw-data worksheet : this is where your scripts will append data every time they run (in fact they should update the data of the current and previous day, and only append data the time they run on a given day, assuming the time granularity you choose to report on is the day). The goal is to generate here data that can be easily analyzed with pivot tables. So you will have rows with dimensions and measures columns. For dimensions, you will probably have at least a marketing campaign identifier and the date (for convenience reason, you may want to add more dimensions columns like week number, day of week, week of year, month…, or any other attributes of the campaign that you will pull from a reference worksheet). For measures, you will probably have a number of impressions, clicks, the total spend for those clicks, a number of sessions on your web site, and maybe some conversion values. Avoid calculated values (such as the click through rate (CTR), cost per click (CPC), … as you cannot aggregate averages ; better to let the spreadsheet do for you in pivot tables with their calculated values feature.
- a reference data worksheet : this is where your marketing team will add attributes of campaigns that cannot be pulled from the channel APIs, and could be used as dimensions in the raw-data worksheet. For example, it could be a column describing the purpose of a campaign. These values will be read and integrated in a column of the raw-data sheet with a vlookup.
- an all channels raw-data worksheet : this is where we concatenate the content of all the raw-data sheets (at least the shared columns). This sheet is also protected (updated only by the scripts), and a support for your users to build pivot tables across all channels (and answer questions like how did my Adwords campaign perform compared to Facebook this week compared to last week…)
- at last an updates log sheet will indicate at what time the last update of data was made by your scripts for each of the channels
Some channels that cannot be automated may be filled manually by the users, provided you give them the right structure, so that your scripts can also merge the data in the aggregated raw-data worksheet.
All this enables your users to create pivot tables, graphs, or other worksheets/spreadsheets that pull data from those raw-data sheets with spreadsheet formulas to build the ad-hoc analysis or reports they need. For specific reports that are well specified, it may be more efficient to generate once for all those reports with some scripts or better, with something like Google Data Studio.
You Apps scripts should not take too long to execute or they will be killed by Google before they complete (at least when they are scheduled).
A few tips :
- one script with its own scheduling per channel : each script may do several API calls, but their number should be minimized as we cannot control how much time is needed for each of them
In some cases, you may also have to choose between setting a value that was computed by your script in a cell, or to inject a spreadsheet formula that will be calculated at runtime use of the spreadsheet. The first solution makes faster and more reactive spreadsheet documents (less formulas to (re)compute dynamically) but removes some flexibility for the user. A use case where it makes sense to inject formulas is for updating columns with values based on a reference table the user can update (such as custom attributes of a campaign).
As the developer of the script, you will probably have to monitor that it runs successfully on its schedule. Google Apps provides you convenient email alerts when the script fails. You may also log events to Stackdriver, but we did not experiment that.
For your users, make sure for each data update to insert somewhere in your spreadsheet the last successful data update timestamp, so that at least, in doubt, they may verify whether the data they are looking at is up to date before calling you for support.
Regarding the external APIs, it may be good to implement a simple retry with exponential back-off, just to make sure your scripts do not fail too easily if there is a connectivity problem on the API you query.
Google Adwords (and Adwords Express) specifics
To move Adwords reporting data into a Google Spreadsheet, Google seems to encourage us to script from within the Adwords environment. There is also an API for querying directly Adwords. This works fine in most cases, but we did not use those solutions for a few reasons :
- pushing from Adwords to a spreadsheet : this was not very consistent with the design we would have to apply for other channels (pull channel data from a spreadsheet script) and we would have less information available than by querying Google Analytics directly as described below
- all the data we need is available in Google Analytics, because it is natively pushed here by our Adwords account (that was linked to our Google Analytics account)
- all the data means also the data for the ads that were configured in Adwords Express (and the KPIs for those ads did not seem to be available using the Adwords API using the “push” scripts)
So in summary, our Google Apps script only queries the Google Analytics API to get our Adwords and Adwords Express KPIs. Building the right query is straightforward using the Google Analytics Query Explorer. You will probably run a query similar to this one (and you can easily verify that the numbers match what you would read in the Adwords console reports) :
Even if it’s sometimes difficult to find where to start in their documentation, Facebook provides nice APIs for getting the data you’ll need, using the Marketing API. The best is to start experimenting and refine your API queries using the Graph Explorer. You will easily get the number of impressions per campaign, clicks, budget spent, and a lot more…
However, in order to get sessions (which will differ from clicks), we had to go back to querying Google Analytics as described above ; this only works though if your Facebook ads are configured properly with well defined utm parameters that you can select (for example utm_source=facebook, utm_medium=paid), you will have to align with your marketing team for that or make you scripts configurable.
A tricky part is the authentication : your Google Apps script will need to be authorized to query your Facebook data with no interactive sign-in or temporary token. Fortunately this script will help you to setup the Oauth2 authentication, without having to spend too much time understanding how this works…
So, to sum-up : we created a Facebook app, that our Google Apps script uses to query the Facebook marketing API and collect metrics on the campaigns (like impressions, clicks, link clicks, budget spent per day and per campaign). Our script also queries Google Analytics to collect the number of sessions generated by those clicks (the “join” is made using the utm parameters that are configured on the Facebook Ads target URLs).
While we have not yet implemented other automated updates, we could most likely follow the same process for for other channels like emailing, SMS, banners, retargeting as long as they provide an API (for impressions, clicks and budget spend or equivalent metrics) and the URLs they link to are configured with correct utm parameters (for the join with Google Analytics).
In some cases, it might just not be worth automating and better to let the marketing team fill in a templated worksheet, from where a script will pull numbers to integrated in a cross-channel analysis.
There are some obvious limitations with this model : scalability (supporting more data in a Google spreadsheet will become more and more tricky, and a database may be needed), depth and complexity of data (we really only use a subset of the data each channel makes available), … With growth, your traffic acquisition team may want more and more advanced data analysis tools and no longer be happy with the kind of raw data that this solution provides ; this is when a switch will have to made from custom in-house solution to a more advanced one (unless you want to reimplement one, but is this really what your tech resources should focus on ?), that they will select, and take ownership of.