The Google Analytics Data API is a powerful tool that allows you to access and analyze your Google Analytics data from anywhere, including Google Sheets. By using the API, you can pull data from Google Analytics 4 into a Google Sheets spreadsheet, where you can manipulate and visualize it in any way you need.
To pull data from Google Analytics 4 into a Google Sheets spreadsheet using the Google Analytics Data API, you will need to:
- Enable the Google Analytics API. To do this, go to the Google Developers Console and select your project. Then, click the APIs & Services tab and search for Google Analytics Data API. Click the Enable button next to the API.
- Create a Google Sheets spreadsheet. Once the API is enabled, you can create a new Google Sheets spreadsheet.
- Create a new Apps Script. In the spreadsheet, go to Extensions->Apps Script.
- Add the Google Analytics Data API client library to your script. In the Apps Script editor, click on the plus (+) sign next to the Services Headers to Add a Service. Scroll to find the Google Analytics Data API. Select the service and click the Add button.
- Write a script to pull data from Google Analytics 4. The following is a simple script that you can use to pull data from Google Analytics 4 into a Google Sheets spreadsheet:
/* Copyright 2023 Optimization, LLC (o10n)
* version 1 - Modified Dimensions & Metrics for this example and Added OrderBy functionality
*
* Derived from Google LLC, Copyright 2021
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
function pullDataFromGA4() {
// Set the Google Analytics 4 property ID.
const propertyId = 'YOUR_GA4_PROPERTY_ID';
try {
// Construct Dimensions Array
const dimension = AnalyticsData.newDimension();
dimension.name = 'date';
// Construct Metrics Array
const metricsArray = [];
const totalUsers = AnalyticsData.newMetric();
totalUsers.name = 'totalUsers';
metricsArray.push(totalUsers);
// Construct Date Range
const dateRange = AnalyticsData.newDateRange();
dateRange.startDate = '7daysAgo';
dateRange.endDate = 'yesterday';
// Construct Order By
const orderBy = AnalyticsData.newOrderBy();
orderBy.desc = false;
const dimOrderBy = AnalyticsData.newDimensionOrderBy();
dimOrderBy.dimensionName = dimension.name;
dimOrderBy.orderType = 'NUMERIC';
orderBy.dimension = dimOrderBy;
// Construct Report Request
const request = AnalyticsData.newRunReportRequest();
request.dimensions = [dimension];
request.metrics = [metricsArray];
request.dateRanges = dateRange;
request.orderBys = orderBy;
// Run the report.
const response = AnalyticsData.Properties.runReport(request, 'properties/' + propertyId);
// Get Active Sheet and Clear All Data
const sheet = SpreadsheetApp.getActiveSheet();
sheet.clear();
// Construct Header
const dimensionHeaders = response.dimensionHeaders.map(
(dimensionHeader) => {
return dimensionHeader.name;
});
const metricHeaders = response.metricHeaders.map(
(metricHeader) => {
return metricHeader.name;
});
const headers = [...dimensionHeaders, ...metricHeaders];
// Write Header to Sheet
sheet.appendRow(headers);
// Construct Row Data
const rows = response.rows.map((row) => {
const dimensionValues = row.dimensionValues.map(
(dimensionValue) => {
return dimensionValue.value;
});
const metricValues = row.metricValues.map(
(metricValues) => {
return metricValues.value;
});
return [...dimensionValues, ...metricValues];
});
// Write Row Data to Sheet
sheet.getRange(sheet.getLastRow(), 1, response.rows.length, headers.length)
.setValues(rows);
} catch (e) {
console.log('Failed with error: %s', e.error);
}
}
To use the script, replace YOUR_GA4_PROPERTY_ID with the ID of your Google Analytics 4 property. You can find the property ID in the Google Analytics 4 interface (it’s the ID listed in Admin->Property Settings).
Once you have replaced the property ID, you can run the script by clicking the Run button in the script editor. The script will pull data from Google Analytics 4 and write it to the spreadsheet.
You can modify the script to pull different dimensions and metrics, or to filter the data. You can also use the script to pull data from multiple Google Analytics 4 properties or to pull data from different date ranges. Visit the Google Apps Script Analytics Data Service API Reference, for more information.
Example use cases
There are many different ways that you can use the Google Analytics Data API to pull data from GA4 into a Google Sheets spreadsheet. Here are a few examples:
- You can pull data on your website traffic, such as the number of sessions, page views, and unique visitors.
- Track e-commerce sales, such as the number of orders, revenue, and conversion rate.
- Analyze marketing campaign performance by tracking cost per click, click-through rate, and conversion rate.
- Understand customer behavior by tracking the most popular pages visited and the average time spent on site.
- Identify zero-converting organic search traffic by analyzing acquisition, landing page, and order data to find products with the most organic search traffic and no sales.
Benefits of using the Google Analytics Data API
There are many benefits to using the Google Analytics Data API to pull data from GA4 into a Google Sheets spreadsheet:
- Automated Data Retrieval: Automate the data pull process, so that you don’t have to manually export data from Google Analytics 4 into Google Sheets.
- Data Aggregation: Easily combine data from multiple sources like Google Analytics 4, CRM, or ERP into one spreadsheet.
- Data Flexibility: Re-Organize, manipulate and filter data in any way you need to.
- Tailored Reporting: Create custom reports and visualizations that are tailored to your specific needs.
Google Sheets and the Google Analytics Data API are both powerful tools for marketers, analysts, and website owners. Combining them will help you unlock the full potential of your GA4 data.