Accessing fetched Google Analytics data

Overview

For each report that is configured for synchronization, the connector creates the following table and view in the destination database and destination schema:

  • report_name__RAW table containing data in a raw form, where each row contains a Google Analytics record in a single VARIANT column.

  • report_name view containing flattened data, where each row contains a Google Analytics dimension or metric in a separate column.

Accessing the raw data

For each synchronized Google Analytics report, the connector creates a new table with the report_name__RAW name in the Snowflake database and schema used to store the Google Analytics data.

For example, if you configured the connector to store the Google Analytics data in the dest_db database and dest_schema schema, and if you configured the connector to synchronize the my_report report, the connector creates the table dest_db.dest_schema.my_report__raw.

This table contains raw data ingested from Google Analytics, and contains the following columns:

Column

Data Type

Description

DATE

DATE

The value of the date dimension for a record from Google Analytics.

RAW

VARIANT

The data for a record from Google Analytics in raw form.

LAST_UPDATE_DATE

TIMESTAMP_NTZ

The last time when a record was updated in Snowflake.

The following example SELECT statement retrieves data from the dest_db.dest_schema.my_report__raw table:

SELECT * FROM DEST_DB.DEST_SCHEMA.MY_REPORT__RAW;
Copy

Accessing the flattened data

In addition, for each table that contains data, the connector creates a flattened view of the raw data. The name of the view is the name of the table without the __RAW suffix. For example, for the table named dest_db.dest_schema.my_report__raw, the connector creates the view named dest_db.dest_schema.my_report.

The view contains flattened records from Google Analytics, where each dimension and metric is stored in a separate column.

The following is an example of a SELECT statement that retrieves data from the dest_db.dest_schema.my_report view:

SELECT * FROM DEST_DB.DEST_SCHEMA.MY_REPORT;
Copy

Note

The flattened view is created only after entire dataset is fetched from GA API. For large report this can take some time.