A while back I noticed that we were spending too much time making reports for clients. I solved that problem by automating most of the reporting process. Here’s how you can do the same.
A while back I noticed that we were spending a lot of time making reports for clients. I started having the team track their hours specific to reporting and found that we were spending over ten hours a month on reporting alone. That was way too much time, but reporting is important and helps us prove to our clients that we’re worth what we are paid, so we couldn’t stop doing it. It was a prime opportunity for automation.
First, and I’d recommend anyone else do this as well, I looked at what our process was and broke down what data we were trying to show and why. It turns out we had some legacy data in there that wasn’t relevant to our clients anymore, so I removed that. We also had some extra steps with an outside tool that was useful for us as a benchmark, but didn’t need to go to our clients. I automated that to send to us, and cut it from the client reporting system.
With a clean process and knowing exactly what data we needed to get, I started to look into automating as much of it as possible. I started with Add-ons.
In Google Sheets, between the ‘Tools’ and ‘Help’ sections of the top menu, there’s something called ‘Add-ons.’ There are a bunch of useful ones, but for this purpose I only needed one: Google Analytics.
There are a lot of good tutorials for setting this up, but I think this video overview from Google is the simplest and best:
So, I connected my sheet with our analytics property (actually, with 40 of them.) I plugged in what data I needed (sessions & % new sessions / month, sessions / channel grouping & month, and sessions with event / event category & channel grouping & month,) I set it for the last 30 days and I ran a report. Surprise! The Data wasn’t organized the way I need it to be and there is more than I need.
In the end, I would still have to pull the data into it’s own styled report. Still, this narrows it down to two moving parts. First is the sheet pulling data from analytics, and second is the sheet styling and organizing that data for client consumption.
So I’ve got the analytics data I need and it’s time to pull it into a new sheet for a report and style it so I can show it to my clients. I’m going to start by organizing my sheet as I need it, giving headers. After that, I’m going to use an incredibly useful function: =IMPORTRANGE()
With the Import Range function, I can tell google sheets to pull data from another location in my Drive and it’ll populate my current sheet, as long as I have the relevant permissions. The function has two parts. First is the key; this tells sheets what file you’re pulling from – it’s this part of your url:
Next is the worksheet’s name and the range you’re pulling. In my case, I called the report I pulled from Analytics ‘sessions’ – so now I’m using a sheet called ‘sessions’ and I’m pulling the organic sessions, which are in C24:C25.
So my complete Import Range function will look like this:
Now my data is linked and pulling into the sheet I want to use as my report.
To schedule this, I need to go back into the worksheet pulling the data and click on ‘add-ons’ and then ‘google analytics’ again. That will open a drop-down with the option to ‘schedule reports.’ When I click that, a box pops up; when I check ‘Enable reports to run automatically,’ it looks like this:
With that, I set my reports to run daily and I’m set.
There’s one final problem with this setup – I still have to manually change the ranges in my client-facing sheet’s Import Range function to make sure it’s pulling the right data. I ended up fixing that by using the query function along with Import Range, but you’ll have to check in later to see how as that’s another blog post.
Did this work for you? Do you have any other analytics add-on tips or tricks? Leave a comment below and let us know!
Website © Hudson Creative 2018