How to Analyse, Optimise & Automate Multi-Channel ROI Calculations
As marketing tactics become more and more complex, the needs of marketing managers and executives to understand and monitor Cost Per Conversion (and ROI) from every channel are increasing. With so many different areas of marketing, marketing services are commonly provided by multiple agencies, resulting in marketing managers and executives receiving numerous disjointed reports, and crucially, no universal view across all channels.
Marketing analysts spend days producing, manipulating and syndicating information from different reports into a single view. An ever increasing variety of these activities adds further pressure for executives all while increasing costs for businesses, all just to have a universal view of their overall marketing spend.
Wouldn’t it be great if you could automate production of universal reports and spend the money saved from this automation on developing business?
Now, thanks to Google Apps Script, it’s easier than ever to integrate data from services that provide data over an API. Services such as Google Analytics or Google AdWords can now integrate with Google Docs, dramatically decreasing the time spent on report creation. You can perform extensive data manipulation, calculations, create charts and have data automatically updated.
I am fortunate enough to work in a forward-thinking agency where automation of time consuming tasks is an integral part of the agency process. There are many ways to syndicate data and create universal reports, but recently at Delete we have started using Google Apps Script, Google Analytics, Google Docs and Google Sites to our advantage. Together, these web services allow us to build easy to read, syndicated reports that update automatically for clients, providing them with a single view of CPC from all of their marketing activities.
In this post I will show you how to create a report with data from Google Analytics. This report will perform some basic cost calculations to output CPA for every marketing channel, all of which is updated automatically. The process I am going to describe is simple to understand and does not require involvement of a developer.
1. Setting up the Google Script
The first step is to create a new Google spreadsheet where you will export the data to.
Create a new Google Spreadsheet to export data to.
Go to Tools -> Script Gallery
Type ‘Google Analytics Report Automation (magic)’ in the search box, find the script in the results and and click ‘Install’.
In this video, Nick Mihailovski of the Google Analytics team demos how to quickly install the script, create a Google Spreadsheet and connect it to Google Analytics:
2. Importing data into your Google Spreadsheet
For this example I am going to configure the report to retrieve goal completions by source and medium, filter out email, Facebook, PPC and organic completions for the last 30 days and publish it in a sheet called ‘CPA’.
I will also retrieve the same data but for the last eight months and publish it on a sheet called ‘CPA trend’ (we will need it later).
Run the report. Go to Google Analytics -> Get Data and wait for the report to finished.
Output will look like this:
4. Manipulating the data in your Google Spreadsheet
As you can see on the previous screen grab, there are many sources with the same type of medium.
Now it’s time for some manual data entry. Create another worksheet where you will count goal completions by unique source/medium. This table groups goal completions by marketing channel using a simple SUMIF() function. Add marketing spend for each channel during the relevant period of time and calculate Cost Per Conversion using a simple formula CPC = spend / number of goals:
You can quickly see that Paid Search brings conversions at a better cost compared with Facebook advertising and email marketing is cheaper per conversion than both paid search and Facebook advertising.
Create a graph to visualize the result.
Now let’s take the data retrieved for the past eight months published on the sheet called ‘CPA trend’ and calculate CPA in the same way:
And then plot the results on a graph.
This trend graph shows that the marketing strategy and spend has not been optimal on some channels.
Cost per conversion from Facebook advertising kept increasing for six months, which means that Facebook campaigns need to be looked at very closely. Email marketing performed well every month except September, so campaigns done during that month need to be reviewed and lessons learned.
This trend also help optimise distribution of spent between different channels. If paid search advertising brings cheaper conversions, it may be beneficial to redirect funds from Facebook advertising into paid search advertising. Conversions from organic search are by far the cheapest, so it may be worth directing more funds into this channel also.
5. Automate your updates
To make reporting automated you need to set up the trigger to export data. For daily automation, go to Tools -> Script Editor. Then go to Resources -> All your triggers. Choose the getData function, select Time-driven, specify your preferred timer and save your changes.
6. Moving tables and charts into Google Sites
You are now ready to create a pretty and universal view of your tables and charts in Google Sites.
Go to Google sites https://sites.google.com and create a new site using the following instructions:
On a new page, click Edit page in the top right corner
Go to Insert -> Chart
All available Google Documents will then be displayed. Choose the document where you created the tables and charts, select the table or chart you wish to insert and save changes.
The beauty of using Google Docs and Google Sites is that you can share your reports with anyone by simply sending them a link and defining the levels of access you want them to have.
Summary
If done right, you will be pleasantly surprised how well this way of syndicating reports and analysing marketing spent by channel will be received by a client. It will show the client how every penny is being spent, helping you to optimise or re-prioritise marketing funds where possible.
So, next time you panic about the overwhelming number of disjointed reports and lack of clear picture of what works and what doesn’t, try to syndicate your data following the instructions in this post and automate the process to save yourself time and money.
Polly Pospelova
Polly Pospelova is a passionate online marketing professional who thrives on delivering unique value-driven search solutions. As well as managing Delete Agency’s natural and paid search teams, Polly works closely with both technical developers and UX specialists to maximise customer experience and conversions.
State of Digital
The post How to Analyse, Optimise & Automate Multi-Channel ROI Calculations appeared first on Network Building Asia.