Recently I participated in a webinar about Reporting Automation. The curious thing, that was mentioned was, that on average a specialist spends 1h per week per client on reporting. I took this number and run our internal time tracking tools to check how do we compare and I must say time could be used better optimizing the campaigns.
Reporting, unfortunately, is not a glorious task. A lot of people just don’t like it. Creating the same spreadsheet or presentation on a monthly basis can be monotonous. In PPC as in SEO this is a task that is repeatable. We were eager to find a solution.
We discussed the possibilities with Anchor.team, our partner company. The goal was simple reports automatization. In SEO reports parts can be completely automatized, so an idea was born. We have already done similar projects for other companies but this time we wanted to completely automate the reporting process for SEO.
The tricky thing is that SEO requires not one but several data sources. Position data for keywords, Organic traffic data, information about published articles for backlinks. We decided that the best end result for the agency and the client is a presentation based report. It’s not as limited as a PDF and at the same time creating a PDF from a spreadsheet is really easy if needed.
Creating manual reports for clients takes a lot of valuable time that specialists can use for optimizing the campaigns. Especially, if we have more than 50 clients. The challenge was to find a solution that could facilitate the reporting process and speed it up with automatization.
Planning the pipeline
All of the sources were essential and at the same time, all of them had separate reporting engines. We wanted to take this data and put it all into one report. The diagram below shows how we have done it.
As we don’t have access to some APIs we needed to make a workaround using Supermetrics. In this way, we created a workflow Data->Spreadhseet->Presentation. For other data sources, we could access the data directly.
We included the following sources:
- Google Search Console – Through Supermetrics
- Seo Station – a keyword position tracking tool – through their API
- Google Analytics – through the spreadsheets Analytics Add-on
All of the above were parsed and the results were put into a presentation using brand guidelines.
The final result was a presentation in Google Slides created from a spreadsheet using Google Apps Scrips. The data was processed to the spreadsheet using a custom Script from Seo Station, Google Analytics Add-On and Supermetrics.
The whole report refreshes on a monthly basis automatically.
You can see the results below.
The report is filled with dummy data.
If your agency is also wasting a lot of time on repeating tasks that can be automated please don’t hesitate to send us a message. We would be happy to help you.