Skip to main content

Google Analytics_4

KORE Team
  • Updated

Overview

Google Analytics is a service that tracks and reports website traffic. It is part of the Google Marketing Platform. This integration provides you with the most important information to better understand and manage your customer behavior in a user-friendly format.

Setup

This integration uses a service account to access the Google Analytics Reporting API.

During initial setup, Two Circles will pull 12 months of historical data. (More can be pulled upon request.) Afterward, the integration performs an incremental pull each day. The incremental pull captures data from the past 30 days to ensure none is missed if the integration fails for any reason. 
Please follow the below steps to have the Google Analytics service enabled

  • Client:

    1. Sign-in to Google using a Google Account that has owner or admin rights for your Analytics account. (Specifically, you must have the iam.serviceAccountAdmin role.)

    2. The steps are defined below to know/create/enable -

      1. Google Cloud Project Name

      2. Service Account for Two Circles

      3. Google Analytics Data API service

      4. JSON file containing credentials

      5. Email Address of Service Account

      6. Google Analytics Property

      7. Addition of the user [Two Circles service account’s email address] to the Google Analytics Property

Find Google Cloud Project name and service account detail that has been already created for Two Circles.

  1. You can skip this section and move to “Add the service account email address to the property" section, if following checklist are already known or completed

    1. Google Cloud Project

    2. Service Account created for Two Circles under this project with Basic Viewer role

    3. Downloaded the JSON file containing credentials of this service account

  2. To know the project name, service account and download JSON file, open this - https://console.cloud.google.com/welcome

  3. Click on “Project drop down” [top - center]

  4. a7edebd9-2b47-4b81-95a1-684eee2d57d4 (1).png
    Click on “All” to see all projects that have been created in the past

  5. If no projects are visible then Create Google Cloud Project section in this guide.

  6. Else, select a project and search Service Accounts from the search box and select the same.

  7. 64a0a0cd-bf83-4b3a-9609-14ee6aa0897c (1).pngCheck if any service account is visible in the list of service accounts containing “Two Circles” in its email address.
  8. bb2c6966-73b3-4d98-9bfe-6758840a87ad (1).png

  9. If Two Circles service account is not found in any of the project then to create a Two Circles specific service account, either you can utilize one of the existing project or create a new one.

    1. If you are planning to utilize existing project then follow Create service account section of the user guide.

    2. If you are planning to create new project then follow Create Google Cloud Project section of the user guide.

  10. Else, If found, keep a note of this Two Circles service account
  11. 63796a9d-8f5e-4956-ab01-5614ddd7d7c8 (1).pngOn the left hand panel, Click on IAM

  12. Then click on View by Roles. If above service account is listed in viewer role then you can jump to Create and download JSON file section of the user guide.

  13. 3ad12f0c-b50b-405d-9976-4d5049391a84 (1).pngIf above Two Circles service account is not visible in viewer role then click on Grant Access. On the right side panel, under the Add Principals, Type Two Circles and select the service account.
  14. dd1cd01c-4849-485a-8d9b-6ec05913614f.pngUnder Under the Assign roles and “select a role” drop down, Select basic viewer role.
  15. f1b4dfe5-1d8c-46ea-ae56-78b9889c3c3d.pngClick Save

  16. Note down this project name and service account name.

  17. Navigate to Create and download JSON file section.

Create Google Cloud Project (If not available)

  1. Open https://console.cloud.google.com/welcome.

  2. Follow this step - https://developers.google.com/workspace/guides/create-project

  3. Enable the IAM API - https://console.cloud.google.com/flows/enableapi?apiid=iam.googleapis.com

  4. Make sure that you can see the recently created project in top banner new search box.

  5. 5f4c5de0-929b-421c-b903-3c0f42d99377.pngClick Next
  6. 2ca9db2b-7371-4a4b-967d-2d172c28c97a.pngClick Enable.

  7. Move to next section to create new service account

Create a new service account for Two Circles. (Skip if you already have service account for Two Circles)
  1. Create a new service account for KORE. (Skip if you already have service account for KORE)
     a. Set the service account's name to "KORE Google Analytics Integration".
      85039fb6-99d9-4b41-a4e3-dd287d84be6a.png
    b.

    Click on CREATE AND CONTINUE

    c. 8fb867b6-ef26-49b6-89a5-68f4df25cb59.png

    Click in “Select a role” drop down

    d. 7a1079ba-7235-4d05-862e-63d87dab7d04.png

    Click on Basic and Viewer

    e. Click to Continue
    f. 7e24e25e-f931-4d33-989a-42b70748d68e.png
    Click on Done
  2. 9dfafc23-12ad-47eb-aa64-077d21848e69.png
    Now you have created project and service account for Kore with Basic viewer role.

  3. Next step is to create and download JSON file

Create and download JSON file

  1. Go to https://console.cloud.google.com/welcome

  2. In the search box, search and open Service Accounts

  3. a1d10ab0-ffed-4194-b47a-c3b7f87d546d.pngFrom the list of service accounts, select the one created for Two Circles.

  4. Click on Keys in top panel
    79358d85-1e50-4449-a8a9-56e6e01f276d.png

  5. Click on Add Key and select Create new key

  6. Keep JSON selected and click Create. A JSON file will get automatically created and downloaded (you may get prompt to define the download location of the file).

  7. 25abfc91-f8f3-43f1-a727-d3a918d0ed54.pngClick Close.

  8. Keep this file handy and safe. You will need to provide this file to Two Circles after following below steps.

  9. Open this file in notepad and find “client_email” and the email address present in it.

  10. Keep it handy as you will required this email address to be added to Google Analytics property in next sections.

Enable Google Analytics Data API Service

  1. Go to https://developers.google.com/analytics/devguides/reporting/data/v1/quickstart-client-libraries

  2. ac8e3344-fa01-40db-844e-d75caffd56f4 (1).pngClick on “Enable the Google Analytics Data API v1”.

  3. In the popped up window, click ”BACK”.

  4. 4f576e1c-4f41-4bcb-86cc-550d50825593.png

  5. In the next window, use dropdown to select the project [as identified from the above sections] that contained the service account created for Two Circles.
    55bd99bb-b98f-4bd4-81a8-57a1c6999def.png

  6. Click Next.

  7. You will see a similar window -

  8. 81f1cc10-f373-43e8-bf26-7c2d429d72fb.png
    Click on DONE.

  9. Move to next section to add service account email address to the property id.

Add the service account email address to the property

  1. It is assumed that on the Google Analytics portal, you have already created a single GA4 property that is already capturing analytics data of the your organization’s required website.

  2. If you have multiple websites, you can create one GA4 property for each website.

  3. It is also assumed that you have the email address of Two Circles service account handy.

  4. Go to admin section of the Google Analytics portal,
    def595d9-be45-48be-8b37-5028866f5174.png


  5. Click the desired Account and the property. And, then click Property Access Management.
    2b0a658d-9ba5-4c44-88f9-25fce89454a3.png

    1. From the right side, a sliding panel will appear, click on plus sign on right top corner (+). It is meant for adding user to the property.

    2. Click "Add Users"

    3. Paste the Two Circles service account email address.

    4. In the roles section, choose "Viewer"

    5. Click on "Add" present on right top corner.

  6. Note down this property ID. You will need to share this with Two Circles. If you have created multiple ga4 properties, then repeat the same process of adding the email address of the service account across all properties. One property will result into one schema object on your Two Circles DWA instance.

  7. Please refer below section.

Provide Two Circles with the below information required for source connectivity

    1. JSON file containing credential. It will contain below attributes -

      1. type

      2. project_id

      3. private_key_id

      4. private_key

      5. client_email

      6. client_id

      7. auth_uri

      8. token_uri

      9. auth_provider_x509_cert_url

      10. client_x509_cert_url

    2. Property ID(s)

      1. If you are providing multiple property ids, then provide a short friendly name of each of the property as well, so that same can be suffixed in the schema name to differentiate between data of different properties.

    3. Ask your Two Circles Success Manager for a secure location to upload the JSON file. (Alternatively, you can encrypt the file with a password and email it. Then use our secure information portal to provide the password.)

Data dictionary and table descriptions

Note: No ERD is provided because the tables in this schema do not have any relationships to each other.

 

Table Description

page_tracking
Returns metrics page views related to page title and actual link of the page.

advertising_traffic_sources
Contains information of sessionCampaignName, sessionDefaultChannelGroup, metrics of a advertiserAdClicks, sessions, costs, number the users, avg duration, page views.

adwords_metrics
Contains adwords metrics such as advertiserAdCostPerClick, advertiserAdClicks and adveristerAdCost.

audience_demo
Returns detailed information of audiences such as user age range, gender, number of users, sessions, avg session duration, page views, and transaction revenue.

audience_interest_market
Contains brandingInterest categories and number of visits by date.

channel_grouping
Returns channel-group categories by users, sessions, avg session duration, page views, and transaction revenue.

device_type
Shows details about end-users device type such as browser, data source, number of users, sessions, avg session duration, page view, and device category.

ecommerce_product_purchase_value
Returns details about e-commerce products, brands, revenue per item, quantity, and unique purchases.

event_tracking
Contains detailed information related to events such as event category, event label, total events, sessions, and transactions.

geo_network
Returns geolocation such as latitude and longitude and details of the number of users, sessions, location details(metro), region, revenue, and number of transactions by date.

marketing_attribution
Returns marketing information related to campaigns, ad contents, source as well as number of clicks, transaction revenue and number of transactions.

time
Returns metrics such as users, sessions, average session duration, page views, by date, hour, days of the week.

topline
Contains information related to number of users, new users, sessions per users, sessions, bounces, bounce rate, hits by date.

traffic_sources
Returns details of the traffic including information such as source, medium, platform, number of organic searches, sessions, number of users, page views.

page_tracking_traffic_source

Returns pageviews for distinct combinations of date, page (URL), and source / medium.

page_conversion_rates

Object returns the page details with revenues,transactions and page views.

 

Example queries

Show the top 25 metro areas your site visitors come from in the past week:

SELECT metro, SUM(CAST(users AS INT)) AS visitors
FROM google_analytics.geo_network
WHERE "date" > DATEADD(WEEK, -1, CURRENT_DATE)
  AND metro NOT LIKE '(not set)'
GROUP BY metro
ORDER BY visitors DESC
LIMIT 25;

Show the top 25 traffic sources your visitors came from in the past week, distinguishing organic links from referrals:

SELECT medium, "source", SUM(users) AS total_users
FROM google_analytics.marketing_attribution ma 
WHERE "date" > DATEADD(WEEK, -1, CURRENT_DATE)
GROUP BY medium, "source"
ORDER BY total_users DESC
LIMIT 25;