Sooner or later, every digital marketer will face this very basic question: how to get Google Analytics data to Google Sheets on a regular basis? In this article, I will answer that question.
You have basically 3 options:
- Manual exports from Google Analytics UI
- You can use an add-on for Google Sheets made by Google.
- You can use Supermetrics for Google Sheets
#1 Manual Exports from Google Analytics UI
I don’t want to spend much time on this because this solution is not replicable at all – you cannot schedule the report refresh to run e. g. on a daily basis. So let’s go very quickly over this.
In GA UI, you’ll likely create a “Custom Report” which you will try to EXPORT to Google Sheets:
You click the “Google Sheets” option, you’ll be asked to authenticate and then the deal is done. The data will be exported to Google Sheets. The downside of this method is that you cannot schedule this process so it’s all kind of useless.
#2 Google Analytics Add-On for Google Sheets
A slightly better method but still not quite there yet. While you can schedule the downloads relatively easily, changes in configurations are pretty complicated for average GA users.
Let’s look at the process. You can get the add-on here. Install the add-on and find it in your target Google Sheet:
A sidebar will open:
You can go ahead and fill out the inputs: report name, GA account from which to pull the data, GA Property, GA View, Metrics, Dimensions, possibly Segments. Notice you cannot specify date range (yet).
After clicking “Create Report”, this “Report Configuration” sheet will be created:
Now you can change the report configuration by directly editing the cell values. You can change Start Date, End Date, Order, Filters, Metrics, Dimensions… The “only small” problem is that you need to know what exactly to enter. So you cannot write “30 days ago” but you need to write “30daysAgo” to get the last 30 days of data or you need to know that “campaign” should be referred as “ga:campaign”. You can read more info on what exactly to enter here: https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on. As you can see, the edits are not very user friendly.
Another bad thing about this approach is that you cannot prevent sampling. GA loves to sample all the data especially in a big GA accounts, basically rendering the data absolutely useless. A side note: if you want to read more about major issues (not just sampling) in GA head to this article.
When you are finally done with your configuration, you can run the report:
When the report finishes running, you’ll get something like this:
Rows 1-14 are occupied with the information which cannot really be removed, the results only start from row 15.
If you want to schedule the report, you can do it in the add-ons menu:
And that’s about it for the Google Analytics add-on. It’s not very user friendly but at least it’s free unlike Supermetrics.
#3 Supermetrics for Google Sheets
By far the most user friendly option but you gotta pay for it 🙂 Get Supermetrics for Google Sheets here (don’t worry, they have a free trial):
When you install the add-on, launch it in your Google Sheet:
Open the “Data source” section:
And find Google Analytics (either standard GA or the new GA4) on the list. Authenticate if needed:
Configure the parameters of the report: view where to pull from (you can select more than 1), dates, metrics, dimensions, segment, filters, and options. Pay special attention to options because you can ask to avoid sampling and also add the sampling note. Keep in mind that Supermetrics is not always successful in avoiding sampling. It’s not really their fault. It because GA is shit product in the very first place.
Once your configuration is ready, press “Get Data to Table” and the report will run. My results look like this:
Now, let’s say you need to edit the report. Unlike with the Google Analytics add-on, the process is simple with Supermetrics. You just “Modify” the query and the very same sidebar options will pop out:
You don’t really have to deal with any weird names… It’s all nice and easy.
By now, you are probably wondering whether you can schedule the queries. Of course you can:
And that’s it for Supermetrics. As you can see, it’s quite easy to operate, you don’t worry about any API nonsense. Is Supermetrics worth the money? Depends on how much you value your time and piece of mind 🙂 For me, it’s definitely worth it.
Conclusion
In this article, I showed 3 methods of getting GA data into Google Sheets. So which one to choose?
I’d say this:
- If you need one time export, you just do it via GA UI.
- If you are short on budget, you can use Google Analytics add-on for Google Sheets but expect some configuration hassles on the way.
- If you are not a geeky person, you should go for Supermetrics to save time and your nerves 🙂
And that’s all for today!