[ Updated Sep 18, 2023 ]
In today’s data-driven world, the ability to extract data from your website or app analytics platform is crucial to gaining insights and making better business decisions. What’s more, you’ll have ownership of your data, allowing you to further transform, blend or manage it how you need to.
While Google Analytics 4 (GA4) and Universal Analytics (UA) offer powerful tools to track user behavior, analyzing this data can be challenging without the right tools. Luckily, Google Sheets gives a powerful data analysis and visualization platform, enabling you to create custom calculations, slice and dice your data, and gain deeper insights.
Google Analytics 4 is the latest version of Google Analytics (GA) platform. It’s the successor of the Universal Analytics version, which will replace UA on July 1, 2023.
In this blog post, we’ll show you how to import data from GA4 or UA into Google Sheets so that you can unlock the full potential of your analytics data.
Skip ahead >>
- Importing Google Analytics 4 data to Google Sheets
- Getting started with importing Google Analytics 4 data in Google Sheets
- Google Analytics report templates for Google Sheets
Methods to import Google Analytics 4 data to Google Sheets
There are two methods to import your data from Google Analytics to Google Sheets.
Manual Data export
You can get your Google Analytics 4 data into Google Sheets through a manual export straight from the GA4 interface.
However, there are a couple of downsides to this method.
Firstly, the dataset you export is static. So, if you need to make reports daily or weekly, you’ll have to repeat the same steps manually each time you want to refresh your data. This could be especially frustrating if you have a complex configuration on the interface or if you need to export multiple reports.
On top of that, the exported dataset isn’t exactly user-friendly for data analysis. Additional elements like summary rows, total counts, and time series data make it difficult to work with or manipulate.
However, if you would like to manually export data, here’s how it works: To export your Google Analytics data to Google Sheets, customize your report on the GA interface, download a spreadsheet file, or report data directly into Google Sheets using built-in functionality. Once done, click the Export button at the top bar, choose the file type, and your data is extracted. You’ll now have a Google Sheet populated with your exported GA data.
Using Google Sheets add-ons
The second method is using a Google Sheets add-on. Let’s look at two add-ons to get data from Google Analytics to Google Sheets: Google’s own GA add-on and Supermetrics.
To get started with add-ons, you need to install them from the extension menu in Google Sheets. Both Google’s own GA add-on and Supermetrics add-on use the Google Analytics reporting API to pull the data and have similar functionality for importing Google Analytics data to Google Sheets, but they differ in terms of their usability and accuracy.
The Supermetrics add-on has more advanced options available within the interface. With Supermetrics, you can apply filters without typing them into the report configuration sheet. The add-on also has the option to try and avoid Google’s data sampling, which can lead to more accurate results. Supermetrics allows you to configure your report to include metrics and dimensions from the multi-channel funnels reporting API. You can also receive notifications if any queries fail when the report is refreshed.
You can also edit your queries easily in the Supermetrics sidebar and change the metrics and dimensions on the go. Advanced options allow you to combine old results with new ones, which is especially handy if there are metrics that don’t support date dimensions. Also, there’s an option to format the data for Looker Studio if that’s where you want to visualize your results.
The sidebar shows its strengths especially when you start to combine multiple data sources together. For example showcasing your Google Analytics data side by side with your paid media results to see the full impact of your campaigns. Or if you’re tracking conversions from social media channels and want to learn which channel is performing the best in engagement, followers, and conversions.
Google’s own Analytics Add-on
On the other hand, the native Google Analytics add-on for Sheets is free to use but has limited options in the user interface. You can only choose metrics, dimensions, and segments. Other features, like filters, need to be manually entered according to specific formatting rules. The data set also requires some extra formatting before it’s ready for analysis. Plus, if you have a large dataset, the API requests might not give you the most accurate data due to data sampling.
Important: If you’re starting your Google Analytics to Google Sheets journey now, it’s very important to note that Google’s own Google Analytics add-on doesn’t support Google Analytics 4 (GA4), as it will soon replace the Universal Analytics (UA).
Importing Google Analytics 4 data in Google Sheets with Supermetrics
Step 1: Install the add-on
To import data from Google Analytics 4 or Universal Analytics to Google Sheets, you need to install the Supermetrics add-on.
Open a fresh Google Sheet to create a new report. On your new Google Sheets file:
- Open the “Extensions” menu
- Look for the “Add-ons” section
- Go to “Get add-ons”
- Search for “Supermetrics”
- And click “Install”
Now if you want to just get data from Google Analytics, Google gives only Google Analytics add-on, which does not support GA4.
You can install Supermetrics directly from the Google Workspace Marketplace.
Next, sign in with your Google account and give Supermetrics all necessary permissions.
Step 2: Connect Google Analytics 4 to Google Sheets
The first step is done. Now it’s time to get some data into Google Sheets.
Launch the Supermetrics sidebar by clicking on “Extensions”> “Supermetrics”> “Launch.”
Once the sidebar is launched, you can choose to get data from 100+ sales and marketing tools and start extracting data straightaway. For this task, let’s search and select ‘Google Analytics 4’.
Once you select the data source (Google Analytics 4), sign in to connect your account.
Step 3: Export Google Analytics 4 data to Google Sheets
Once you’ve configured the Google Analytics account, a new sidebar will open. This sidebar lets you control your data to import to Google Sheets.
You need to create a query to get the data in the spreadsheet. A query is a request for a data result based on the parameters defined. In our context, it simply means defining the parameters below to import the data in the spreadsheet.
So let’s start building the first query. Choose the relevant values from each drop-down menu:
- Data source: Choose the source from which you want to pull data.
- Select views: Select the Google Analytics 4 account, properties, or views from which you want to get data.
- Select dates: From here, you can set the date range for your data. You can choose one of the preset dynamic date ranges, set custom dates to range like week or month, or even compare the dates.
- Select metrics: Select the metrics you want to add to import to your spreadsheet. For example, sessions, pageviews, bounce rate, etc.
- Split by dimensions: Choose how you want to split your metrics. For example, you can break down your metrics by date, campaign, etc.
You can also play with segments, filters, and options to pull more granular data.
For example, my query setup is:
- Data source: Google Analytics 4
- Select view: Supermetrics blog
- Select date: Last year
- Select metrics: Session, views, and event count
- Split by dimensions: Page path vs. Year & month
- Filter: Country EQUALS Canada
- Option: Try to avoid Google Analytics’ data sampling
Happy with your query? Great. Now click “Get data to table”, and your data will appear in the spreadsheet.
Step 4: Modify your data queries
If the query data didn’t turn out to be as you wanted, you can modify the same query and fetch it again. No need to delete and re-do all over. You can modify a single query or multiple queries.
Modify a single query
If you want to modify your query, simply click any cell that contains data from the query, and the sidebar will reopen. Then click “Modify” to make any changes you want.
Now you can change any value in the query, like dates, dimensions and metrics, and more.
Once you’ve made your changes, click ‘Apply changes’ to reflect the changes on the data.
Modify bulk query
If you want to modify multiple queries simultaneously, you can use the bulk update. Search for the ‘SupermetricsQueries’ sheet from the bottom sheets menu. This will give you the tabular form of all the queries in the same place.
Now, you can search for the queries you want to update in the table and simply update the parameters you want to update (like date, metrics, dimensions, etc.).
💡Pro tip: If you cannot find the ‘SupermetricsQueries’ sheet from the bottom tabs, then it might be the case that the sheet is hidden. Follow the screenshot above and click the hamburger button and click the ‘SupermetricsQueries’ — this will unhide the sheet.
Once you’ve updated all the parameters, you need to refresh all the queries to reflect the changes — click the ‘Refresh all queries’ button in the toolbox on the same sheet. It will take a few seconds to update all the queries — once everything is updated, the popup will close automatically, and you’ll have the updated data in the columns.
Step 5: Duplicate query
Now, once you’ve run the query successfully and want to get the same table elsewhere, then don’t worry — you don’t need to select the data source, dimension, metrics, dates, etc. again or copy and paste the data. Select any cell with the query data and click ‘Duplicate’ in the sidebar. Now go to the cell where you want the data and click ‘Get the data’, and you have your data in place now.
Pro tip: If you want similar data, for example, you’ve pulled data for a website in GA4, and you want to pull the same data for a different property in GA4, then you can duplicate the query and change the GA4 property. Once that’s done, just click ‘Get the data’. This will give you the same data from a different GA4 property.
Step 6: Set up automatic refreshes and triggers
Instead of running the same query every day, week, or month, you can set up scheduled report refreshes to get new data to your Google spreadsheet whenever needed.
With the scheduled refreshing and emailing features, you can:
- Set up automatic data refreshes in Google Sheets
- Send periodic emails to your inbox
- Trigger emails from Google Sheets based on a cell value
- Manage all your triggers in one place
Now, let’s see how you can set up a trigger.
On the Supermetrics sidebar, click “Schedule” > “Add trigger”
The “Trigger” view will appear. From here, you can refresh your data daily, weekly, or monthly. If you want to get an email every time the data refreshes, choose one of the “Refresh & email” options.
You can also set alerts for failed queries. For instance, if the query doesn’t refresh at the specified time or day, it’ll send you an email notification to the assigned email.
To manage the triggers across different files, click “Manage triggers in all your files” in the “Schedule” window.
Manual data refreshes
If you don’t want to trigger automatic refreshes, you can manually refresh the queries using the Google Sheets add-on menu.
Click the ‘Extensions’ menu > ‘Supermetrics’ > ‘Refresh all’.
This will refresh all the queries in the spreadsheet. If you want to refresh the active sheet, click ‘Refresh active sheet’; if you want to refresh a single query, use the Supermetrics sidebar and click the ‘Refresh’ option.
Google Analytics report templates for Google Sheets
Since you’ve completed the crash course on using Supermetrics for Google Sheets, I’ve got a graduation gift: some free plug-and-play report templates.
All the templates below are based on Google Analytics Universal Analytics version (UA). To get this data in the Google Sheet, you just need to change the Data Source in Step 3, and the rest of the process would be similar as outlined above. Alternatively, you can skip all the steps of building a new report and use one of our pre-built templates to kickstart your reporting.
You can either look at the Google Analytics data or even combine the Google Analytics data with other sources and answer more complex data problems.
Single source templates
You can start with a simple website analysis with the data from Google Analytics alone. Some of the example use cases you can get started with using the templates are:
- Cohort analysis template
- Traffic heatmap template
- Traffic source bubble chart template
- Year-on-year website performance
Using Google Analytics data, you can monitor the performance of your online store with this e-commerce reporting template.
Determine whether your ad spend produces a positive ROI and see how your paid ads perform compared to other channels using the post-click analysis template.
Multi-source templates (with Google Analytics)
Using Supermetrics, you can combine data from 100+ sales and marketing tools to see a complete picture of your marketing efforts.
Combine data from Google Ads, Facebook Ads & Google Analytics to get a good understanding of your online store’s performance using our ecommerce performance template.
Paid channel mix reporting
See how your paid campaigns are performing across Google Ads, Bing Ads, Facebook Ads, and Twitter Ads with our Paid channel mix template.
SEM vs. SEO data comparison
Monitor the traffic distribution between paid and organic channels with this handy template by OIKIO.
Organic traffic & keywords
Keep track of your website’s organic traffic and the keywords that drive that traffic using our Organic keyword template.
Google AdSense & Google Analytics reporting
Quickly determine which traffic sources are bringing in AdSense revenue using our Google AdSense & Google Analytics reporting template.
Want to see more templates? Browse through our Google Sheets marketing template gallery.
Goodbye manual work
Alright, we did it! We eliminated the most boring part of marketing reporting: manual data collection.
Now it’s up to you to decide how to analyze your data and what kinds of reports you want to build.
Turn your marketing data into opportunity
We streamline your marketing data so you can focus on the insights.