Google third-party OAuth using Service Accounts

Note

• For other options when authenticating Google third Party services, read Google Query Authentication Guide.
• Additional guides include Google Ad Manager Query authentication guide and Google Custom Search Query authentication guide.

Overview

Users can perform ETL/ELT on their data from numerous Google services:

• Gmail
• Google Ad Manager (previously known as DoubleClick for Publishers)
• Google Ads
• Google Analytics
• Google BigQuery
• Google Custom Search
• Google Drive
• Google Sheets
• YouTube Analytics

Google allows OAuth flows using user credentials as well as service accounts.

This page explores using a service account for OAuth instead of a user account to pull data from Google Analytics using the Google Analytics Query component.

The section titled Google Configuration describes steps taken on the Google Cloud Platform (GCP) to create the service account and gather required information.

The section titled Matillion ETL Configuration then focuses on using the information gathered to configure an example Google Analytics Query component to use the service account created in Google Configuration.

---

Google configuration

Enable the Google Analytics API

1. Log in to the Google Cloud Platform console.
2. Click ☰ in the upper-left of the console UI.
3. Click APIs and services.
4. Click Library.
5. Use the search bar to find "Google Analytics API". Click the search result of the same name.
6. You should now see the product details page for the Google Analytics API. Click Manage.
7. If you see a button that reads Disable API, ignore it. This means you have already enabled this API. Alternatively, click Enable if this button is available.

Create a service account

1. Create a service account and note the email address.
2. Download the .JSON file for this service account.
3. Ensure the service account has access to relevant services and roles. For example, to give the service account access to Google Analytics, log in to Google Analytics and add the service account email address to the allowed users.

Create an OAuth app

1. Click ☰ in the upper-left of the console UI.
2. Click APIs and services.
3. Click Credentials.
4. Click Create credentials.
5. Click OAuth client ID.
6. Set the application type.
7. Complete the fields and click Create.
8. Once created, gather your app's Client ID and Client Secret credentials.

---

Matillion ETL configuration

1. Copy the .JSON file for your created service account to the Matillion ETL server. For example, to the /etc/tomcat/ folder or any other folder on the Matillion ETL server that the Tomcat user has access to. Ensure the Tomcat user has Read access to this file.
2. Create a new Google OAuth entry in Matillion ETL.
   1. Click Project → Manage OAuth.
   2. Click + to add a new OAuth entry.
   3. Select Google from the drop-down menu of services.
   4. Click OK. The OAuth entry's status will read "Not configured".
   5. Click OK.
   6. Click Project → Manage OAuth.
3. Add a new Google Analytics Query component to the job canvas.

4. In the Connection Options parameter, set the following connection options:

   • InitiateOAuth: Set this to GETANDREFRESH.
   • OAuthClientId: The Client ID in your app settings.
   • OAuthClientSecret: The Client Secret in your app settings.
   • OAuthJWTIssuer: Email address of your service account.
   • OAuthJWTCertType: Set this to GOOGLEJSON.
   • OAuthJWTCert: The path to the .JSON file on the Matillion ETL server.
   • OAuthJWTCertPassword: Enter notasecret.
   • OAuthJWTCertSubject: Set this to * to pick the first certificate in the certificate store.
   • Profile: The Google Analytics profile or view you want to connect to. This value can be retrieved from the Profiles table. If this is not specified, the first profile returned will be used.

   Note

   • The Profile connection option is specific to Google Analytics. If you're using a different service account, Profile isn't required.
   • If you're using a .p12 file instead of a JSON file, see below to configure the following attributes:
     • OAuthJWTCertType: Set this to PFXFILE.
     • OAuthJWTCert: The path to the .p12 file on the Matillion ETL server.
     • OAuthJWTCertPassword: The password of the .p12 file.

5. Configure the remaining parameters of the Google Analytics Query component and then run the job. Matillion ETL will use the connection options to authenticate using the service account instead of the user-based OAuth entry.