Setting up data ingestion for your Snowflake Connector for Google Analytics Raw Data

This topic describes how to access Snowflake Connector for Google Analytics Raw Data in your Snowflake account.

Setting up data ingestion using Snowsight

To set up data ingestion using Snowsight, do the following:

  1. Sign in to Snowsight as a user with the ACCOUNTADMIN role.

  2. In the navigation menu, select Data Products » Apps.

  3. Search for the Snowflake Connector for Google Analytics Raw Data, then select the tile for the connector.

  4. In the page for the Snowflake Connector for Google Analytics Raw Data, navigate to the Data Sync section.

    This displays a list of all the Google Analytics properties.

  5. Select the properties you want to ingest:

    1. Search for the property you want to ingest.

    2. Select the checkbox in the Status column next to the property you want to select.

    3. Repeat these steps for each property you want to ingest into Snowflake.

  6. Select the heading of the Status column to see the properties you have currently selected.

  7. Select Start sync to begin ingesting data into your Snowflake account.

Selected properties appear in the properties list.

Data Ingestion status will be displayed in the right top corner of the Manage data synchronization section.

Data sync for each property will begin with an initial load, which ingests all historical data for a property and then proceeds to the current sync. If you wish to only sync current data, you can do so via a worksheet.

Modifying data ingestion using Snowsight

To modify the Google Analytics tables to be ingested or the synchronization schedule for the tables, do the following:

  1. Sign in to Snowsight as a user with the ACCOUNTADMIN role.

  2. In the navigation menu, select Data Products » Apps.

  3. Search for the Snowflake Connector for Google Analytics Raw Data, then select the tile for the connector.

  4. In the page for the Snowflake Connector for Google Analytics Raw Data, navigate to the Data Sync section.

  5. Select Edit properties.

  6. Modify the tables you want to ingest:

    1. Search for the table you want to ingest.

    2. Select the checkbox in the Status column next to the table you want to select or deselect.

  7. Select Update data sync.

Setting up data ingestion using SQL statements

To set up data ingestion using SQL statements, do the following:

Note

To configure these settings, use stored procedures that are defined in the PUBLIC schema of the database that serves as an instance of the connector installation database.

Before calling these stored procedures, select that database as the database to use for the session.

For example, if that database is named snowflake_connector_for_google_analytics_raw_data, run the following command:

USE DATABASE snowflake_connector_for_google_analytics_raw_data;
Copy

Listing the properties available for ingestion

To list all the available properties in a given GCP project, call the following stored procedure:

CALL LIST_GA_PROPERTIES();
Copy

The result displays all the available projects and properties to ingest by an authorized account. If no results are returned please check:

  • If the data export from Google Analytics to BigQuery is configured.

  • If exported data is visible in BigQuery.

  • If proper roles are assigned to the used Service Account / authenticated user.

Please be advised that it can take up to 24 hours between setting up the data export and storing data in BigQuery. This delay can be a cause for the LIST_GA_PROPERTIES procedure producing no results.

Turning the Google Analytics export off does not mean the property is ommited by LIST_GA_PROPERTIES. Even though the export was turned off, data can still persist in BigQuery and can be synchronized by the connector.

Preparing destination database

Before enabling the ingestion, you need to grant the connector access to creating tables and views inside your destination database and schema.

GRANT USAGE ON DATABASE <destination database> TO APPLICATION <application name>;

GRANT USAGE ON SCHEMA <destination database>.<destination schema> TO APPLICATION <application name>;

GRANT CREATE TABLE ON SCHEMA <destination database>.<destination schema> TO APPLICATION <application name>;

GRANT CREATE VIEW ON SCHEMA <destination database>.<destination schema> TO APPLICATION <application name>;
Copy

Enabling or disabling the ingestion of a property

To enable or disable the synchronization of data for a specific property in Google Analytics, call the ENABLE_PROPERTIES stored procedure with the following arguments:

CALL ENABLE_PROPERTIES('<gcp_project>', ['<properties_to_configure>'], <enable_initial_load>);
Copy

Where:

gcp_project

Specifies the GCP project of the enabled properties.

properties_to_configure

Specifies a comma-delimited list of Google Analytics properties names in single quotation marks.

Use the property name without the analytics_ prefix.

enable_initial_load

A boolean indicating whether to enable or disable the initial data load, which ingests all historical data for a property and then proceeds to the current sync.

This is an optional argument and the default value for it is true.

For example, to enable the synchronization of the properties named property1, property2, and property3 in the project gcp_example_project, run the following command:

CALL ENABLE_PROPERTIES('gcp_example_project', ['property1','property2','property3']);
Copy

To disable initial data loading, use an ENABLE_PROPERITES query similar to:

CALL ENABLE_PROPERTIES('gcp_example_project', ['property1','property2','property3'], false);
Copy

To prevent these properties from being ingested, run the following command:

CALL DISABLE_PROPERTIES('gcp_example_project', ['property1','property2','property3']);
Copy

Disabling the property stops its synchronization. When the property synchronization is disabled, the whole ingestion that started is removed from the destination database.

The ENABLE_PROPERTIES procedure adds the specified property names to the ENABLED_PROPERTIES view.

Initial load

After enabling a new property, the connector enters initial load mode. It means that all the historical data found in BigQuery will be ingested before collecting new events.

Switching Google analytics export to a different project.

As long as the connector has access to the project where the new data is stored, the connector keeps operating regularly and download the data. No additional steps are required.