Free Google Sheets add-on to pull Google Analytics 4 data
Sometimes it’s better to pull and transform your data from Google Analytics to Google Sheets than to Looker Studio. Especially, if you want to merge the data from Universal Analytics and GA4, you can use add-ons that can help you. In this article I am going to tell you about some free Google Sheets add-on.
Watch the video-review along to understand better what is going on:
Installation
Mostly, add-ons are paid but I will show you a free Google Sheets add-on that I really like. Its an add-on by Michele Pisani called “GA4 Magic Reports”. There are two options of installation – admin install and individual install. I chose an individual install, after that succeeded an authorisation process. Now we’re done! The add-on is installed!
In the list of add-ons in the Extensions menu we can find GA4 Magic Reports. It gives us an option to create a new report, run reports, schedule reports, watch a video tutorial, donate and get help.
Setting up a report
Let’s click “Create a new report”. This extension creates a new page that is called GA4 reports configuration. Also we have a right panel with manual setup.
First of all, you can input the name of the report and only then choose an account. I have my personal account and Google Analytics demo-account. In this case I will choose my own account because it is not compatible with Google Analytics demo-accounts. To change properties you don’t need to create a new report – just open your Google Analytics account and input your property name and id manually.
The next thing we do is select a date range – I choose from May till yesterday. Followingly, we have an opportunity to choose metrics and dimensions. In our case:
Metrics: Active Users, New Users, Bounce Rate, Sessions.
Dimensions: Date, First user source/medium
The thing is that not all the fields that are available in Google Analytics 4 are available in add-ons. There are 2 reasons for that:
- API restrictions. Some metrics are closed and Google doesn’t allow you to pull this data anywhere via API.
- Different connector providers have different sets of metrics and dimensions. That is why we have various connectors. Some of them, like Power My Analytics and Supermetrics, pull a lot of different data, whereas the others are quite limited.
Turning back to the setup, beside all the things that we’ve already set up, you can also add metrics and dimensions filters and sorting. I would like to sort it by Date that you can find in the Time section. After that I click “Create report”.
Report review
We have such rows as Report name, Property ID, Property name, Start Date, End date, Compare Start date, Compare End date, Metrics, Dimensions, Metric Filters, Dimension filters, Order by, Limit, Currency code and include empty rows. Now we see that there is data in column B now. In this table we can use formulas when it’s necessary, for example in End Date we can write a formula today()-1 or simply write yesterday.
So, you can pull your data as it is now, or you can change it a bit manually. When you want to create a report, in the Extensions menu click “Run reports”.
The script has already created a new tab for us that I’ve called GA4 daily. It contains the table with the data we wanted to see which means we can already use this table for our work.
Tips and tricks
You can set up an automatic update of the table by running every two hours, every day, every week or every month. For this you just need to set up updating time, for example, between 2 and 3 a.m.
When the report updates, all the formulas and your calculations on this page will be removed. What should you do in this case?
You should create a new tab and make a link to the previous tab to have access to the tables and data. On this page you can name the fields whatever you want. Here you can try different formats of data, experiment with calculations, join data from other tabs and so on.
If you want to create one more report, the data will be added to our very first page with configurations, to the column C. If you want to update your existing report, you still can do it on the same configurations tab.
I’ve used this connector to create my Combine UA & GA data plus a 12-month traffic forecast report template. Check it out and purchase it in my template store.
Hope you liked this report! Share your impressions in the comments section!