HOW WE INCREASED EFFICIENCY BY AUTOMATING OUR REPORTS
Data is the lifeblood of atom42; gathering it, studying it, reporting on it and acting on it are all critical parts of our day. But something which had always bothered me was how long the gathering and reporting parts took…
We pulled performance data daily for all of our clients, reports from Analytics, Adwords, Bing Ads, Facebook ads and any other platforms, multiplied by the number of clients a team had. Then we pasted it into Word docs, adding commentary and actions, or presentations on a monthly basis, swallowing up hours of time we wanted to spend optimising..
But earlier this year after looking at ways we could become more efficient we started automating our daily reports, creating self-updating dashboards for client reports, and linking our presentations to these dashboards, so we could spend our time analysing and actioning, rather than copying and pasting. Here’s how we did it…
Automating data gathering
Google Sheets has been a saviour for us when it comes to increased reporting efficiency.
We started by setting up a Google Sheet for each client and using the Google Analytics and Supermetrics add-ons to gather all the data we would normally pull from Google Analytics, Bing Ads and Facebook Ads.
These reports can be configured however you like and can be scheduled to update daily at a time that’s convenient for you. Ours update at 2 am, so the data is ready for us to review as soon as we get to the office.
We use Google Analytics to gather pretty much everything we need, including Adwords cost/click/impression data, but for Facebook and Bing spend/impressions/clicks we use Supermetrics.
The data dump these provide looks a little ugly, but we’ll get to that next…
Creating usable reports
Now we have our data we need to create a usable report which collates data and gives us a lay of the land.
While Excel has superior functionality over Google Sheets, we found that for 95% of our reporting needs Google Sheets was sufficient and saved us time, so we stopped using Excel completely at this point and rebuilt our reporting dashboards in Google Sheets.
Using SumIfs we’re able to gather all the data we need from the automated data dumps and show it in these dashboards. If we want to build a sheet which shows how branded campaigns compare to non-brand campaigns we simply use a sumif to look for a campaign which has ‘brand’ in the title, or not, and sum the stats.
Using a lot of SumIfs can slow a sheet down, so we copy and paste as values on historic data every few months to keep our sheets speedy.
Creating client-friendly dashboards
One we were happy with our details reports, we wanted to create something more topline for clients, which we could simply export and send to them.
These dashboards use offset formulas to pull the right data once you select the week number or start date from the drop-down, meaning live data is only a couple of clicks away.
They can be easily exported to PDF through Google Sheets, meaning no copying and pasting into Word docs, then battling with the formatting.
Automating monthly presentations with linked graphs and tables
Once our reporting sheets were complete, the final step was to link our monthly presentations graphs and tables to the sheet, so all we needed to do when updating the data in our presentations was hit ‘refresh’.
