Connect GA4 to Google Sheets

Getting Your GA4 Data into Google Sheets

Looking to improve your GA4 reporting with Sheets?
  • Analyze your top landing pages
  • Discover where users are engaged
  • Analyze paid and organic traffic sources
  • Improve on the limited reporting in the GA4 web interface?

Example: Health Check on your Top Landing Pages

  • See how organic traffic is growing
  • Understand what keywords are driving traffic and how much traffic is available in search
  • Quickly understand how pages are driving goal completions:
notion image
Ready to Get Started?

Google Analytics V4 API to Google Sheets via SyncWith

We’re going to the SyncWith Addon for Google Sheets (click to install in Google Workspace)
  • Over 100,000 users syncing their data
  • ⭐️⭐️⭐️⭐️⭐️ with over 470 reviews

How does SyncWith Work?

notion image

Let’s Get Started

In this reference we’ll look at how to setup a Google Analytics v4 API connection using SyncWith. The image to the right shows how to connect to over 1000 sources using SyncWith.
We’ll use SyncWith to get your GA4 Data into Google Sheets.
In SyncWith if you search for google analytics you’ll get a few results, including our GA v3 connector and various Google Analytics templates.
Once you get your GA connected to Sheets you’ll also be able to pair other data sources such as:
  • Add Google Search Console data
  • Google Ads, Facebook Ads, Tik Tok Ads and other ads data sets
notion image

Choose a Reporting Time Frame

Already have SyncWith Installed?
 
Lets get started, once you’ve installed SyncWith and started to create a Google Analytics Connection we’ll need to pick a reporting time frame for the data we want synced.
You can choose from a wide range of default reporting time frames as well as dynamic ones. Some marketers and agencies might report on the last 21 weeks of data. You may or may not want to see the current day in the reporting period.
All are possible with the date range picker. Popular options include:
  • Last 30 days
  • Last 90 days
  • Last Month
  • Month to Date
  • Last 5 Months
notion image

Choosing Your Dimensions

There are a LOT of dimensions available for the Google Analytics so we’ll take a look at some of the more popular ones.
One way to think about dimensions is every time you add one you create more rows.
For example if you have 30 days and 20 countries. If you request date you’ll get 30 rows, if you request country you’ll get 20 rows if you request country and date you’ll get 600 rows (as long as there is data for every day for every country).
For this reason adding just a few dimensions can really explode the number of rows returned.
notion image

Major Differences in Dimensions between GA3 vs GA4

If you’re moving from GA3 you’re going to notice that there are dimensions missing, and new dimensions you haven’t seen. One major difference is GA4 enables much better tracking across multiple properties and web / app and multiple visits. Often as a marketer you’ll wonder how to do better attribution. GA4 helps solve some of these questions by letting you differentiate between:
  • The source of the user
  • The source of a session
  • The source of an event
By doing so you can better understand attribution around:
  • What is driving conversations
  • What is attracting new users
  • What is brining existing users back
As example you’ll see dimensions like campaign be broken out into many options:
  • Campaign
  • First user campaign
  • Session campaign
This breaks out the campaign that drove an event conversion, the campaign that brought the user in for the first time and the campaign that drove a given session.
Because of this you’ll notice a trend of dimensions that are event sourced, session sourced and user sourced - keep this in mind when deciding what you want to report on and what your goals are.
 
Popular Google GA v4 Dimensions
Name
Description
Date
Useful for tracking trends over time. Formatted YYYYMMDD
Date hour
Combines the Date and Hour field into a single field. Formatted YYYYMMDDHH
Week
Numeric value of the week. Between 01 and 53. Weeks start on a Sunday and January 1st is always in Week 01. The first and last week can have less than 7 days and all other weeks have 7 days.
Year
Year formatted YYYY
Campaign
Useful for tracking your various marketing paid and un paid marketing campaigns, set via the utm_campaign variable
Source
This is the source that contribute to the conversion event and is used for event reporting such as a purchase. This is different than in GA3 / Universal analytics where the Source dimension is used for the session - use Session source if you want the same effect. This will help you understand which sources drive the most conversions. Eg Google drives more conversions than Reddit.
First user source
The source by which the user was first acquired. For example perhaps we first acquired the user via a paid search ad. In this case the First user source would be Google
Session source
The source by which the session was started. For example a user who we’d initially required via a paid ad and signed up for our newsletter now returns on our newsletter - Session source would be newsletter
Medium
This is the medium that contributed to the conversion event and is used for event reporting such as a purchase. This is different than in GA3 / Universal analytics where the Medium dimension is used for the session - use Session medium if you want the same effect. Medium refers to the type of channel, eg social, search, direct, email, etc. For example you might have medium=search for traffic from bing, google and duckduckgo. This would help you understand which mediums drive the most conversions.
Session medium
The medium by which the session was started. For example a user who we’d initially required via a paid ad and signed up for our newsletter now returns on our newsletter - Session medium would be email
First user medium
The medium that first acquired the user to your website or app for example if you first acquired a user via paid google search ads the medium would be cpc.
Page referrer
The full referring URL including the hostname and path. This referring URL is the user's previous URL and can be this website's domain or other domains. Must be populated via the page_referrer event parameter.
Campaign
The name of the marketing campaign. Present only for conversion events.
First user campaign
Name of the marketing campaign that first acquired the user. Includes Google Ads and manual campaigns utilizing the utm_campaign query parameter.
Session Campaign
The marketing campaign name for a session. Includes Google Ads and manual campaigns utilizing the utm_campaign query parameter.
First user google ads campaign type
You can report on Google Ads campaign types by first user, session and conversion event. In this instance we’re looking at the the campaign type for the ad that caused us to first acquire the user. Campaign types determine where customers see your ads and the settings and options within Google Ads for example Search Display Shopping Video Discovery App Smart Hotel Local Performance Max
Country
Name of the country the user is from, example values could include: United States India Canada Brazil United Kingdom Japan France Germany
Landing Page
This breaks things out by the first page the user started their session on, it includes the path and query string of the first pageview. This is very useful in terms of understanding the pages that are attracting users to your site for instance / - the root of your website /support - your support page /pricing - your pricing page
Page Title
Page is Simply the title of the pages. For example Pricing - Acme Services Pricing Plans this might correspond to a page location of https://www.example.com/pricing/plans?query_string=value
Page location
The protocol, hostname, page path, and query string for web pages visited; for example https://www.example.com/about/contact-us?query_string=value Populated by the event parameter 'page_location'.
Full page URL
Excludes protocol, includes hostname, page path and query string, for example https://www.example.com/about/contact-us?query_string=value
Page path + query string
Captures the portion of the URL after the hostname, for example https://www.example.com/about/contact-us?query_string=value
Page path
Captures the portion of the URL between the hostname and query string, for example https://www.example.com/about/contact-us?query_string=value
Event name
The name of the event, pair with metrics such as Event count and Event value to understand the performance of your custom events
Ad unit
Name of the ad unit that is used to display ads to the user, pair with ad unit exposure or Publisher ad impressions. This is app specific.

Choosing Your Metrics

Metrics can be thought of as the data for every row. Using our example above if you have country and date as dimensions you still need to specify what you want to know about them, for example show me my Views and Sessions by date by country.
The metrics have changed a lot between GA3 and GA4, because GA4 is more focused on a user paradigm you’ll have metrics you would have seen in Firebase (GA4 is Google’s effort to standardize the future of analytics between Firebase, Universal Analytics and measurement protocol for OTT/IoT). You’ll see things like MAU - monthly active users and DAU - daily active users. You’ll also see metrics like Engaged sessions that leverage the advanced measurement baked into GA4.
notion image
Popular Page Insight Metrics
Metric
Description
Sessions
Sessions measure the number of visits to your website`
Engaged sessions
The number of sessions that lasted longer than 10 seconds, or had a conversion event, or had 2 or more screen views.
Bounce rate
The percentage of sessions that were not Engaged sessions (Sessions - Engaged sessions )/ Sessions This is different from GA3 where a bounce meant a users who didn’t click to a second page which wasn’t a good measure of non-engagement, so this can be considered an improvement.
Average session duration
The average duration (in seconds) of users' sessions. Unlike GA 3 you can’t get session duration to do you’re own aggregations so you would need helper columns to recalculate session duration via Average session duration * Sessions
Sessions per user
Sessions / Active users gives you the average number of sessions per user.
Total users
The number of distinct users who have logged at least one event, regardless of whether the site or app was in use when that event was logged.
New users
The number of users who interacted with your site or launched your app for the first time (event triggered: first_open or first_visit).
28 day active users
Number of users on your site or app within a 28 day period. The 28 day period includes the last day in the report's date range.
DAU / MAU
The rolling percent of 30-day active users who are also 1-day active users. For example if 1000 of your 30-day users are 1-day active and you have 10,000 30-day active users your DAU / MAU = 1,000/10,000 or 10% of your monthly active is also daly active
Views
The number of app screens or web pages your users viewed. Repeated views of a single page or screen are counted.
Views per session
The number of app screens or web pages your users viewed per session. Repeated views of a single page or screen are counted. Views / Sessions
Add to carts
The number of times users added items to their shopping carts. You could create a custom event to track unique add to carts which could be useful to compare people who’ve added at least one item vs Checkouts
Checkouts
The number of times users started the checkout process
Ad unit exposure
The time that an ad unit was exposed to a user, in milliseconds. This is useful to understand if your ads are being seen long enough to get a click or to understand if you’re showing an add for too long and should look at refreshing your in app ads more frequently (or if refresh is broken)
Total ad revenue
App only. The total advertising revenue from both Admob and third-party sources.
Organic google search average position
Allows search console position ranking to be reported on in GA4, shows the average ranking of your website URLs for the query reported from Search Console. Requires an active Search Console link.
Organic google search clicks
The number of organic Google Search clicks reported from Search Console.
Organic google search impressions
The number of organic Google Search impressions reported from Search Console.
It should also be noted that if you choose more than 10 metircs Google will through a 400 error:
notion image
{ "error": { "code": 400, "message": "Requests are limited to 10 metrics within a nested request.\n This request is for 11 metrics.", "status": "INVALID_ARGUMENT" } }
 

Sorting By Specific Metrics or Dimensions

You can choose multiple metrics and dimensions to sort by for example:
  • Sort first by Date Ascending
  • Sort second by Sessions Descending
 
notion image

Setting a Row Limit

If you want to limit to say the first 20,000 rows you can set a row limit, but we will attempt to get all the rows you need if no limit is set.

Setting the Update Mode

SyncWith supports 3 update modes:
  1. Replace contents of sheet
  1. Append rows to end of sheet
  1. Update or add rows as needed
The first two options are simple in nature, the last option to update or add rows as needed is more advanced.
notion image

Replace Contents of Sheet

PREFERRED MODE
Every time you refresh or SyncWith refreshes based on your predefined schedule, all the cells will be deleted and the new data will be inserted.
  • Any computational rows that you’ve added will not get deleted as long as they are outside of the range being updated. If you’ve inserted a computational row in between other data rows it will get over written
  • Formatting you apply will stay in tact, eg if you specify a percentage, number, date that formatting for the column will persist
  • This mode is ideal for when you don’t want to collect or store previous data. For example you just want the last 30 days of data.

Append Rows to End of Sheet

NOT COMMONLY USED OR RECOMMENDED
Every time you refresh or SyncWith refreshes based on your predefined schedule SyncWith will append the new rows to the last line with data
  • The entire sheet will not be re-sorted based on any sort you have but the appended data will be sorted and appended according to the sort you specified.
  • It will NOT prevent duplicate rows from being created
  • It will NOT update existing rows
  • If you want to only append new rows or properly update existing rows then you need to use Update or Add Rows as Needed
  • This is not a popular updating mode

Update or Add Rows as Needed

USED for use cases where you need to collect historical data
  • This mode uses the date as a key to determine if a row is unique.
  • If the row is unique (we don’t have data from that date yet) then it will add a new row
  • If the row is not unique it will update the metrics if they’ve changed, eg you pulled a partial day the data was updated the most recent pull will update the metrics for the days where the data changed.
This update mode is a great option if you want to collect historical page data, by doing many successful 90 day period pulls of your data across older date ranges you can collect all your historical data, then change the connector to just get the last 7 days data on a daily basis.

Scheduling Your Google Analytics Data to Google Sheets

Manual Refresh

Manual Refresh Is the default scheduling for a connection. Data will not be updated until you actively refresh it. In order to refresh the data you must either:
  • Refresh your data from in the SyncWith addon side bar via the refresh button - all connections can be manually refreshed
notion image
  • Refresh via the Add-on menu, allowing you to refresh all connections setup, or all connection(s) pushing data to the current active sheet
notion image
  • Refresh via the check box (located in the header above the data table)
notion image

Scheduled Refreshes

There are 3 options for scheduled refreshes:
  • Automatically refresh daily will refresh the data every day at the same time. The time is set based on when the connection was last updated. If you wish to set the refresh to happen at a specific time you would need to open the connection and save it at that time.
  • Automatically refresh hourly will refresh the data every hourly
  • Automatically refresh every 5 minutes will refresh the data every 5 minutes. This is not typically used for google analytics - unless you’re looking in detail at the current day. A maximum of 100 connections can use 5 minute refresh.