In this Quickstart, we will investigate how to use the Snowflake Connector for Google Analytics Raw Data to emulate data ingestion from Google Analytics to BigQuery into Snowflake.

What You Will Build

• A BigQuery dataset with Google Analytics data.
• A Streamlit application to visualize the data.

What You Will Learn

You will learn how to:

• Create a Google Analytics Project.
• Create a BigQuery dataset.
• Install and configure the Snowflake Connector for Google Analytics Raw Data.
• Visualize the BigQuery data in a Streamlit application.

Prerequisites

• A Google Account to access Google Analytics and BigQuery.
• Familiarity with basic Python and SQL.
• Familiarity with data science notebooks.
• Go to the Snowflake sign-up page and register for a free account. After registration, you will receive an email containing a link that will take you to Snowflake, where you can sign in.

Overview

You will use Snowsight, the Snowflake web interface to create Snowflake objects (warehouse, database, schema, role).

Creating Objects and Loading Data

1. Navigate to Worksheets, click + in the top-right corner to create a new Worksheet, and choose SQL Worksheet.
2. Copy and paste the setup script code to create Snowflake objects (warehouse, database, schema) and click Run All at the top of the Worksheet.

Overview

In this section, we will create a Google Analytics Account and Property in Google Analytics.

Create Account

1. To create an Account, navigate to Google Analytics and click on Start measuring.

2. Enter an account name such as Snowflake Connector Account and click Next.

3. Enter the Property Name as Snowflake Connector Property and click Next.

4. Select the industry category and business size and click Next.

5. Select the business objectives and click Create.

6. Accept the terms and conditions and click I Accept.

Overview

In this section, we will create a BigQuery dataset and table to store Google Analytics data.

Create a BigQuery Project

1. Navigate to Google Cloud Console and click on Create or select a project button to create a new project.

2. Select New Project then No organization and click Create.

3. In the popup notification window on the top right, click SELECT PROJECT.

4. The page will automatically load to the new project. Hover over the left sidebar to expand it and in the Resources section, click on BigQuery.

5. In the Explorer, enter bigquery- in the Search BigQuery resources box and toggle to Search all projects. Click on the star next to bigquery-public-data to star the dataset.

6. Click X in the Search BigQuery resources to view your project and the starred project.

7. Expand the bigquery-public-data dataset to view the datasets.

8. Scroll down to find and expand the ga4_obfuscated_sample_ecommerce dataset and select the only table that's in this dataset. It should start with events_. Click COPY.

9. After copying, select BROWSE for the project and select your project that was created earlier.

10. Click on the Dataset dropdown, select CREATE NEW DATASET, and enter analytics_20210131 and click CREATE DATASET.

11. Select the newly created dataset and enter events_20210131 for the table name and click COPY.

12. Click GO TO TABLE on the toast that appears on the bottom of the page.

13. View the table by clicking PREVIEW.

Overview

In this section, we will link Google Analytics to BigQuery.

Link Google Analytics to BigQuery

1. Navigate to Google Analytics and enter BigQuery Links in the search bar and select BigQuery Links.

2. Click the Link button.

3. Click on the Choose a BigQuery project button.

4. Select the BigQuery Project and click Confirm.

5. Click Next and select Include advertising identifiers for mobile app streams, Streaming (best-effort), and Daily. Click Next again.

6. Click Submit.

7. A new link is created.

Overview

In this step, we will configure the OAuth Consent Screen.

Create Service Account Key

1. Navigate back to BigQuery and hover over the left sidebar to expand it and click on APIs & Services then Credentials.

2. Click on Create Credentials and select Service account.

3. Enter a Service account name such as Connector Service Account and click CREATE AND CONTINUE.

4. Add all 3 of these roles one at a time by clicking on the dropdown list to select a role, and then click + ADD ANOTHER ROLE to add the next role then click DONE: BigQuery Data Viewer, BigQuery Read Session User and BigQuery Job User.

5. Click on the newly created service account and click on ADD KEY then Create new key.

6. Select JSON and click CREATE.

7. The JSON key file will be downloaded to your computer.

Overview

In this section, we will configure the OAuth Consent Screen.

Configure OAuth Consent Screen

1. Hover over the left sidebar to expand it and click on APIs & Services then OAuth consent screen.

2. Select External and click CREATE.
3. Note if you have the GO TO NEW EXPERIENCE button, click the button to proceed to the next step.

4. Navigate to Clients and click on GET STARTED.

5. Enter the App name as Snowflake Connector for Google Analytics Raw Data and click NEXT.

6. Click External and then NEXT.

7. Enter an email address (preferably the same one entered for the User Support Email) and click NEXT.

8. Select to Agree and click CONTINUE.

9. Click CREATE.

Overview

In this section, we will enable the Cloud Resource Manager API.

Cloud Resource Manager API

1. Inside BigQuery, enter Cloud Resource Manager API in the search bar and click on Cloud Resource Manager API.

2. Click ENABLE.

Overview

In this section, we will install and configure the Snowflake Connector for Google Analytics Raw Data.

Clean Up Script

1. Navigate to the Snowflake Marketplace and type in Snowflake Connector for Google Analytics Raw Data and click on the Connector.

2. Click GET and then GET to install the Connector.

3. Click Configure.

4. Click Mark all as done and then click Start configuration.

5. Click Configure.

6. Upload the JSON file that was previously downloaded and upload it to this page and click Connect.

7. Click Define data to sync.

8. Select the data to sync and click Start sync.

9. When the data is syncing, the UI will indicate with Last sync: x minutes ago.

Overview

In this section, we will create a Snowflake Notebook to view the data: both the raw un-flattened and flattened data.

Create a Snowflake Notebook

1. Download the Snowflake Notebook
2. Navigate to Snowsight, make sure you're using the ACCOUNTADMIN role and go to Projects and Notebooks.
3. Click on the dropdown arrow portion of the + Notebook button.
4. Click on Import .ipynb file.
5. A popup window will appear to upload the Notebook file.
6. In the Name section, name the Notebook, select GOOGLE_ANALYTICS for the database and RAW_DATA for the schema.
7. Select GOOGLE_ANALYTICS_DS_WH for the warehouse.
8. Click Create.
9. Click Run all to run the Notebook.

Overview

In this section, we will create a Streamlit application to visualize the data.

Visualize Data

1. Copy the Streamlit code.
2. Navigate to Snowsight, make sure you're using the ACCOUNTADMIN role and go to Projects and Streamlit.
3. Click on the + Streamlit App button.
4. In the App title section, name the app, select GOOGLE_ANALYTICS for the database and RAW_DATA for the schema.
5. Select GOOGLE_ANALYTICS_DS_WH for the warehouse.
6. Paste in the Streamlit code into the code editor and click Run.

Overview

When you're finished with this Quickstart, you can clean up the objects created in Snowflake.

Clean Up Script

Navigate to the last cell in the Snowflake Notebook labeled clean_up to uncomment and run it to drop all objects created in this Quickstart.

Congrats! You're reached the end of this Quickstart!

What You Learned

With the completion of this Quickstart, you have learned how to:

• Use the Snowflake Connector for Google Analytics Raw Data to ingest data from Google Analytics/BigQuery into Snowflake.
• Visualize the data in a Streamlit application.

Resources

• Snowflake Connector for Google Analytics Raw Data
• Snowflake Notebooks
• Streamlit in Snowflake