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), set it for the last 30 days and ran the report.
Surprise! The Data wasn’t organized the way I needed it to be and there was more than I needed.
In the end, I would still have to pull the data into its own styled report. Still, this narrowed it down to two moving parts. First was the sheet pulling data from analytics, and second was the sheet styling and organizing that data for client consumption.
I’ve got the analytics data I needed – it was time to pull it into a new sheet for a report and style it so I could show it to my clients. I decided to start by organizing my sheet as I needed it, giving headers. After that, I used an incredibly useful function: =IMPORTRANGE()
With the Import Range function, you can tell google sheets to pull data from another location in Drive and it will populate your current sheet as long as you 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 looks like this:
Now my data was linked and pulling into the sheet I wanted to use as my report.
To schedule this, I had to go back into the worksheet pulling the data and click on ‘add-ons’ and then ‘google analytics’ again. That opened a drop-down with the option to ‘schedule reports.’ When I clicked that, a box popped up; when I checked ‘Enable reports to run automatically,’ it looked like this:
With that, I set my reports to run daily and I was 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.
Website © Hudson Creative 2020