Google Analytics API Integration

Query Google Analytics APIs via Retool

Retool's Google Analytics integration provides a convenient UI for connecting to and querying Google Analytic's Management API v3 and the Analytics Reporting API v4 .

The Reporting API v4 can be used to programmatically access report data and user activity from Google Analytics properties using Universal Analytics. The Management API v3 can be used to retrieve data about your Google Analytics accounts, properties and views, as well as manage user permissions and account settings.

Connecting to Google Analytics

To build Retool apps with your Google Analytics data you'll first need to create a resource for querying the Google Analytics API. Resources sit on top of our integrations and store the authentication and metadata fields required for you to connect to a data source.

Get started by navigating to the Resources page, clicking Create new and then selecting Google Analytics as your resource type.

Retool hosted setup

Retool uses OAuth to connect to Google Analytics, allowing you to authenticate with just a few clicks.

First, Enter a name for your resource that you’ll use when querying it in the editor (e.g. “Marketing Site Google Analytics”). Next, you’ll need to decide whether you want to grant Read and write or Read only access to Retool. Retool uses the option you select here to determine the scopes passed with the OAuth request.

Click Connect to Google Analytics to begin Google's authorization flow. After completing the authorization flow, you should see a message indicating a successful connection.

Self hosted setup

If you host Retool yourself, you'll need to provide credentials for a Google OAuth2 client. If you do not already have a client configured follow our instructions for creating a Google OAuth2 client.

Enter your client ID and client secret, and confirm your Google auth client has the correct callback URL. Click Create Resource.

You can now select your newly-created Google Analytics resource from the Resource dropdown in in the in-app Query Editor, as well as the Query Library.

Advanced settings

For resources using OAuth 2.0 authentication scheme Retool provides the option to share credentials between users or to require each user to authenticate using their own credentials. The Google Analytics resource defaults to sharing credentials between users. If you would like each app user to login with their own Google account uncheck Share Google Analytics credentials between users in the Advanced section.

To learn more about how OAuth 2.0 credentials work in Retool refer to our API Authentication docs.

Querying Google Analytics

To query the APIs select your Google Analytics resource from the Resource dropdown. Then select the API you want to query from the API dropdown. Next select the desired API operation from the Operation dropdown. Note you can search through available operations by typing in the Operations dropdown field.

Once you have selected an API operation a form will display the query and request body parameters available for the selected operation.

You can display the results of Google Analytics API queries as with any other query in Retool. You will have to examine the response to see which part of it you actually want to display. A good place to start looking is always in the top-level data field - {{yourQuery.data}}.

If you're getting UNAUTHORIZED errors while building apps with this resource, it means you haven't authenticated your session. You can do this by pressing the Re-authenticate API button in the query editor and trying to run the query again.

Tips for the Reporting API v4

The /reports:batchGet endpoint of the v4 Reporting API allows you to build almost any report, but can be confusing to figure out. We suggest reviewing the Fundamentals section of the Google Analytics API docs, especially their overview of how the Request Body needs to be formatted, before you get started. Google also provides an interactive Request Composer for building requests you can copy and paste into Retool.

Example API request for a list of landing pages with conversion data, filtering out sessions from paid sources.Example API request for a list of landing pages with conversion data, filtering out sessions from paid sources.

Example API request for a list of landing pages with conversion data, filtering out sessions from paid sources.

Handling the /v4/reports:batchGet endpoint response

The /v4/reports:batchGet endpoint returns an array of Report objects. While the Report object contains a lot of useful metadata, it takes a bit of wrangling to get the data into format useful for rendering results in a chart or table. We recommend enabling the transformer on your query and adding the following JS snippet to parse results into a useable table.

const transformedReports = data.reports.map(report => {
  const { columnHeader } = report;
  const dimensionEntries = (columnHeader.dimensions || []).map(name => name);
  const metricHeaderEntries = (
    columnHeader.metricHeader.metricHeaderEntries || []
  ).map(o => ({ ...o, key: o.name }));
  const rows = report.data.rows || [];

  const formattedData = rows.reduce((arr, row) => {
    const dimensions = row.dimensions || [];
    const flatDims = dimensions.reduce((obj, value, index) => {
      const key = dimensionEntries[index];
      return { ...obj, [key]: value };
    }, {});
    const flatMetrics = row.metrics[0].values.reduce((obj, value, index) => {
      const { key } = metricHeaderEntries[index];
      return { ...obj, [key]: Number(value) };
    }, {});
    arr.push({ ...flatDims, ...flatMetrics });
    return arr;
  }, []);
  return { ...report, formattedData };
});

return { ...data, reports: transformedReports };

Here's an example response after the transformer has been applied:

[ {
        "ga:landingPagePath": "/",
        "ga:users": 147825,
        "ga:goal1ConversionRate": 5.254646535406078,
        "ga:goal1Completions": 14102
      },
      {
        "ga:landingPagePath": "/contact",
        "ga:users": 32371,
        "ga:goal1ConversionRate": 0.0040,
        "ga:goal1Completions": 130
      }, {...}, 
]
{ "columnHeader": {
        "dimensions": ["ga:landingPagePath" ],
        "metricHeader": {
          "metricHeaderEntries": [
            { "name": "ga:users", "type": "INTEGER" },
            {  "name": "ga:goal1ConversionRate","type": "PERCENT" },
            { "name": "ga:goal1Completions",  "type": "INTEGER" }
          ]
        }
      },
      "data": {
        "rows": [
          { "dimensions": [ "/" ],
            "metrics": [
              {  "values": [ "148876", "5.270975823347485", "14265" ]  }
            ]  },
          {
            "dimensions": [ "/blog/salesforce-for-engineers/" ],
            "metrics": [
              { "values": [  "31963",  "0.0",  "0"  ] }
            ]
          }, {...}], 
     ...}

Did this page help you?