Connecting to Google Analytics

Sisense enables easy and quick access to databases, tables, and views contained in Google Analytics databases.

Note:

  • If you are connecting to a Google service remotely, and the address of Sisense is something other than localhost, Google requires that you connect using the OAuth 2.0 protocol. See Google Authentication in Linux for more information.

To create a connection to Google Analytics, you need to:

  1. Download a CDATA Google Analytics driver

  2. Install the CDATA Google Analytics driver

  3. Configure the CDATA Google Analytics driver

  4. Connect to Google Analytics using the CDATA Google Analytics driver

Connecting to Google Analytics Using a CDATA Driver

There are two ways to use a CDATA connector:

  • Option 1 involves using the CDATA connector's internal generic clientId and clientSecret. It requires less effort but is also less customizable.

  • Option 2 requires creating an app in Google to generate credentials (clientId and clientSecret). Although this option is more customizable, it requires more effort.

Option 1: Less Customized, Less Effort

  1. Download the driver from Sisense Connectors | Sisense Data Connectivity | CData Software.

  2. Install the driver locally in the customer’s environment.

  3. Navigate to (if installed in the default location):

    • /Applications/CData/CData JDBC Driver for Google Analytics 2022/lib if using MacOS.

    • C:\Program Files\CData\CData JDBC Driver for Google Analytics 2022\lib if using Windows.

  4. Execute cdata.jdbc.googleanalytics.jar.

  5. Add the following parameters to make it work:

    • AuthScheme=OAuth;

    • OAuth=GETANDREFRESH;

    The resulting connection string looks as follows: jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH

  6. Click Test Connection. You will be redirected to the web browser to log in to your Google account. Once authenticated, the connection successful message will appear in the JAR and the connection string will be updated.

  7. Click Copy to Clipboard to copy the connection string.

    OAuthSettings.txt will be generated containing the connection parameters in the following location:

      • %APPDATA%\\CData\\GoogleAnalytics Data Provider\\OAuthSettings.txt for Windows

      • ~/Library/Application Support for macOS

      • If you can’t find the file, set the OAuthSettingsLocation in the JAR file.

  8. Copy OAuthSettings.txt to the /opt/sisense/storage/data directory in the Sisense server.

  9. Copy cdata.jdbc.googleanalytics.jar to the /opt/sisense/storage/data/GoogleAnalytics directory in the Sisense server.

  10. Connect to Google Analytics using Sisense:

    • Use the connection string (from above) but change the OAuthSettingsLocation to:

      jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthSettingsLocation="/opt/sisense/storage/data/OAuthsettings.txt";_persist_oauthrefreshtoken=oauthRefreshToken;_persist_oauthexpiresin=3599;_persist_oauthaccesstoken=oauthAccessToken;_persist_oauthtokentimestamp=XXXXXXXXXXXXX;

    • Enter the JARs folder: /opt/sisense/storage/data/GoogleAnalytics.

    • Provide the driver class name: cdata.jdbc.googleanalytics.GoogleAnalyticsDriver.

Option 2: More Customized, More Effort

  1. Download the driver from Sisense Connectors | Sisense Data Connectivity | CData Software.

  2. Install the driver locally in the customer’s environment.

  3. Navigate to (if installed in the default location):

    • /Applications/CData/CData JDBC Driver for Google Analytics 2022/lib if using MacOS.

    • C:\Program Files\CData\CData JDBC Driver for Google Analytics 2022\lib if using Windows.

  4. Execute cdata.jdbc.googleanalytics.jar.

  5. Add the following parameters to make it work:

    • AuthScheme=OAuth;

    • OAuth=GETANDREFRESH;

    • OAuthClientId=clientId;

      Note:

      If you are a new customer, see Google Authentication in Linux to create a Google app. If you already use our native Google Analytics driver, you need to specify the clientId, which can be retrieved from manifest.json from the old connector in one of the following possible locations:

      • /opt/sisense/storage/connectors/googleanalytics/manifest.json

      • C:\Program Files\Sisense\DataConnectors\DotNetContainer\Connectors\GoogleAnalytics\manifest.json

      • C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors\GoogleAnalytics\manifest.json

    • OAuthClientSecret=clientSecret;

      This is the same as OAuthClientId retrieved from manifest.json.

      The resulting connection string looks as follows:

      jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=clientId;OAuthClientSecret=clientSecret;

  6. Before clicking Test Connection in the JAR file, navigate to your Google application using Google API Console and add http://localhost:33333 to the ‘Authorized redirect URIs’ field.

  7. Click Test Connection. You will be redirected to the web browser to log in to your Google account. Once authenticated, the connection successful message will appear in the JAR file. The connection string will be updated to incorporate the tokens obtained from Google.

  8. Click Copy to Clipboard to copy the connection string. (You will need it to set up the connection in Sisense.)

    OAuthSettings.txt will be generated containing the connection parameters in the following location:

      • %APPDATA%\\CData\\GoogleAnalytics Data Provider\\OAuthSettings.txt for Windows

      • ~/Library/Application Support for macOS

      • If you can’t find the file, set the OAuthSettingsLocation in the JAR file.

  9. Copy OAuthSettings.txt to the /opt/sisense/storage/data directory in the Sisense server.

  10. Copy cdata.jdbc.googleanalytics.jar to the /opt/sisense/storage/data/GoogleAnalytics directory in the Sisense server.

  11. Connect to Google Analytics using Sisense:

    • Use the connection string (from above) but change the OAuthSettingsLocation to:

      jdbc:googleanalytics:AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthSettingsLocation="/opt/sisense/storage/data/OAuthsettings.txt";_persist_oauthrefreshtoken=oauthRefreshToken;_persist_oauthexpiresin=3599;_persist_oauthaccesstoken=oauthAccessToken;_persist_oauthtokentimestamp=XXXXXXXXXXXXX;

    • Enter the JARs folder: /opt/sisense/storage/data/GoogleAnalytics.

    • Provide the driver class name: cdata.jdbc.googleanalytics.GoogleAnalyticsDriver.

Note:

The CDATA table and column structure is different from Sisense’s native drivers. Therefore, you might need to redesign your data models. To learn about the CDATA table and column structure, and the nuances of using the connector, see the following CDATA documentation.

Importing Custom Tables from Google Analytics with CData Drivers

If you have custom tables in Google Analytics that you want to add to your existing data model in Sisense, perform the following procedure for each table you need to add to the data model:

  1. From the Import Query page (seeImporting Data with Custom Queries), run the CreateCustomSchema stored procedure (a CData capability) to generate an RSD file for the table in the target ElastiCube. (Ensure that the structure of the schema is similar to the one in Google Analytics.)

    Example:

    Stored procedure:

    EXEC CreateCustomSchema @TableName='newTestGA4', @Dimensions='pagePath,date', @Metrics='sessions', @PropertyId=<yourPropertyId>

  2. Using file management (see Uploading Files to Customize Your Linux Deployment), upload the RSD file to the Linux server, placing it in the <domain name>/app/explore/files/connectors/framework/<connector name> folder.

When the procedure has been done for all the custom tables you want to add to the data model:

  1. Manually update the connection settings for all existing Google Analytics tables in the ElastiCube.

    Example:

    Connection string:

    jdbc:googleanalytics:AuthScheme=OAuth;Schema=GoogleAnalytics4;PropertyId=<yourPropertyId>;InitiateOAuth=GETANDREFRESH;_persist_oauthrefreshtoken=<yourRefreshToken>;_persist_oauthexpiresin=3599;_persist_oauthaccesstoken=<yourAccessToken>;_persist_oauthtokentimestamp=1678195165150;Location=/opt/sisense/storage/data/ga_schemas;

  2. Preview the table data to verify that the data model is as you would expect.

  3. Rebuild the ElastiCube.