Google Analytics SpotApp

SpotApps are ThoughtSpot’s out-of-the-box solution templates built for specific use cases and data sources. They are built on ThoughtSpot Modeling Language (TML) Blocks, which are pre-built pieces of code that are easy to download and implement directly from the product.

The Google Analytics SpotApp mimics the Google Analytics data model. When you deploy it, ThoughtSpot creates several Worksheets, Answers, and Liveboards, based on your Google Analytics data in your cloud data warehouse.

This is a sample Liveboard, created after you deploy the Google Analytics SpotApp:

Google Analytics SpotApp Liveboard

Use the Google Analytics SpotApp to analyze data about customer engagement with your website and any ads on the website. Understand how people are using the website, and track the number of clicks and impressions generated by ads on the website.

Prerequisites

Before you can deploy the Google Analytics SpotApp, you must complete the following prerequisites:

  • Review the required tables and columns for the SpotApp.

  • Ensure that your columns match the required column type listed in the schema for your SpotApp.

  • Sync all tables and columns from Google Analytics to your cloud data warehouse. You can sync only the required tables and columns, but ThoughtSpot recommends syncing all tables and columns from Google Analytics to your CDW. The columns can be Google Analytics’s out-of-the-box columns, or any custom columns that you are using instead of the out-of-the-box columns.

    If you are using an ETL/ELT tool or working with another team in your organization to move data, our recommendation is that you sync all columns from the tables listed in the SpotApp.
  • Obtain credentials and SYSADMIN privileges to connect to your cloud data warehouse. The cloud data warehouse must contain the data you would like ThoughtSpot to use to create Answers, Liveboards, and Worksheets. Refer to the connection reference for your cloud data warehouse for information about required credentials:

  • The connection name for each new SpotApp must be unique.

  • Administrator access to Google Analytics

  • Access to the following Google Analytics tables in your cloud data warehouse. Refer to Google Analytics SpotApp schema for more details.

    • ga_ad_analytics

    • ga_traffic_source

    • ga_geo_audience

    • ga_page_behaviour

    • ga_site_statistics

  • Run the required SQL commands in your cloud data warehouse. Refer to Run SQL commands.

Run SQL commands

The following SQL commands create 5 views based on your Google Analytics data. This standardizes data types and column names. Replace the database name and schema name with your specific information.

The following SQL example is for the Redshift cloud data warehouse. You may need to modify the code for the SQL requirements of your specific cloud data warehouse.

SQL commands:

Open the dropdown menu to view the SQL commands.
create or replace view FIVETRAN.GOOGLE_ANALYTICS_THOUGHTSPOT.GA_AD_ANALYTICS(
"ga:date",
"ga:adGroup",
"ga:adContent",
"ga:adDistributionNetwork",
"ga:campaign",
"ga:adSlot",
"ga:impressions",
"ga:adClicks",
"ga:adCost",
"ga:CPM",
"ga:CPC",
"ga:transactionRevenue",
"ga:sessions",
"ga:bounces",
"ga:users",
"ga:pageviews"
) as
select
"DATE" as "ga:date",
"AD_GROUP" as "ga:adGroup",
"AD_CONTENT" as "ga:adContent",
"AD_DISTRIBUTION_NETWORK" as "ga:adDistributionNetwork",
"CAMPAIGN" as "ga:campaign",
"AD_SLOT" as "ga:adSlot",
"IMPRESSIONS" as "ga:impressions",
"AD_CLICKS" as "ga:adClicks",
"AD_COST" as "ga:adCost",
"CPM" as "ga:CPM",
"CPC" as "ga:CPC",
"TRANSACTION_REVENUE" as "ga:transactionRevenue",
"SESSIONS" as "ga:sessions",
"BOUNCES" as "ga:bounces",
"USERS" as "ga:users",
"PAGEVIEWS" as "ga:pageviews"
from "FIVETRAN"."GOOGLE_ANALYTICS_THOUGHTSPOT"."GA_ADWORDS";

create or replace view FIVETRAN.GOOGLE_ANALYTICS_THOUGHTSPOT.GA_SITE_STATISTICS(
"ga:date",
"ga:time",
"ga:hostname",
"ga:pagePath",
"ga:country",
"ga:browser",
"ga:operatingSystemVersion",
"ga:deviceCategory",
"ga:operatingSystem",
"ga:language",
"ga:sessions",
"ga:pageviews",
"ga:avgPageLoadTime",
"ga:avgPageDownloadTime",
"ga:avgRedirectionTime",
"ga:avgServerConnectionTime",
"ga:serverResponseTime"
) as
select
"DATE" as "ga:date",
NULL as "ga:time",
"HOSTNAME" as "ga:hostname",
"PAGE_PATH" as "ga:pagePath",
"COUNTRY" as "ga:country",
"BROWSER" as "ga:browser",
"OPERATING_SYSTEM_VERSION" as "ga:operatingSystemVersion",
"DEVICE_CATEGORY" as "ga:deviceCategory",
"OPERATING_SYSTEM" as "ga:operatingSystem",
"LANGUAGE" as "ga:language",
"SESSIONS" as "ga:sessions",
"PAGEVIEWS" as "ga:pageviews",
"AVG_PAGE_LOAD_TIME" as "ga:avgPageLoadTime",
"AVG_PAGE_DOWNLOAD_TIME" as "ga:avgPageDownloadTime",
"AVG_REDIRECTION_TIME" as "ga:avgRedirectionTime",
"AVG_SERVER_CONNECTION_TIME" as "ga:avgServerConnectionTime",
"SERVER_RESPONSE_TIME" as "ga:serverResponseTime"
from "FIVETRAN"."GOOGLE_ANALYTICS_THOUGHTSPOT"."GA_STATISTICS";

create or replace view FIVETRAN.GOOGLE_ANALYTICS_THOUGHTSPOT.GA_TRAFFIC_SOURCE(
"ga:userType",
"ga:date",
"ga:campaign",
"ga:source",
"ga:medium",
"ga:keyword",
"ga:adContent",
"ga:socialNetwork",
"ga:channelGrouping",
"ga:sessions",
"ga:users",
"ga:bounces",
"ga:sessionDuration",
"ga:pageviews",
"ga:organicSearches"
) as
select
"USER_TYPE" as "ga:userType",
"DATE" as "ga:date",
"CAMPAIGN" as "ga:campaign",
"SOURCE" as "ga:source",
"MEDIUM" as "ga:medium",
"KEYWORD" as "ga:keyword",
"AD_CONTENT" as "ga:adContent",
"SOCIAL_NETWORK" as "ga:socialNetwork",
"CHANNEL_GROUPING" as "ga:channelGrouping",
"SESSIONS" as "ga:sessions",
"USERS" as "ga:users",
"BOUNCES" as "ga:bounces",
"SESSION_DURATION" as "ga:sessionDuration",
"PAGEVIEWS" as "ga:pageviews",
"ORGANIC_SEARCHES" as "ga:organicSearches"
from "FIVETRAN"."GOOGLE_ANALYTICS_THOUGHTSPOT"."GA_TRAFFIC";

create or replace view FIVETRAN.GOOGLE_ANALYTICS_THOUGHTSPOT.GA_GEO_AUDIENCE(
"ga:date",
"ga:country",
"ga:region",
"ga:latitude",
"ga:longitude",
"ga:channelGrouping",
"ga:userType",
"ga:source",
"ga:medium",
"ga:sessions",
"ga:users",
"ga:bounces",
"ga:sessionDuration",
"ga:pageviews"
) as
select
"DATE" as "ga:date",
"COUNTRY" as "ga:country",
"REGION" as "ga:region",
"LATITUDE" as "ga:latitude",
"LONGITUDE" as "ga:longitude",
"CHANNEL_GROUPING" as "ga:channelGrouping",
"USER_TYPE" as "ga:userType",
"SOURCE" as "ga:source",
"MEDIUM" as "ga:medium",
"SESSIONS" as "ga:sessions",
"USERS" as "ga:users",
"BOUNCES" as "ga:bounces",
"SESSION_DURATION" as "ga:sessionDuration",
"PAGEVIEWS" as "ga:pageviews"
from "FIVETRAN"."GOOGLE_ANALYTICS_THOUGHTSPOT"."GA_GEO";

create or replace view FIVETRAN.GOOGLE_ANALYTICS_THOUGHTSPOT.GA_PAGE_BEHAVIOUR(
"ga:date",
"ga:hostname",
"ga:pagePath",
"ga:landingPagePath",
"ga:secondPagePath",
"ga:exitPagePath",
"ga:pageTitle",
"ga:source",
"ga:country",
"ga:sessions",
"ga:entrances",
"ga:pageviews",
"ga:timeOnPage",
"ga:exits",
"ga:pageValue"
) as
select
"DATE" as "ga:date",
"HOSTNAME" as "ga:hostname",
"PAGE_PATH" as "ga:pagePath",
"LANDING_PAGE_PATH" as "ga:landingPagePath",
"SECOND_PAGE_PATH" as "ga:secondPagePath",
"EXIT_PAGE_PATH" as "ga:exitPagePath",
"PAGE_TITLE" as "ga:pageTitle",
"SOURCE" as "ga:source",
"COUNTRY" as "ga:country",
"SESSIONS" as "ga:sessions",
"ENTRANCES" as "ga:entrances",
"PAGEVIEWS" as "ga:pageviews",
"TIME_ON_PAGE" as "ga:timeOnPage",
"EXITS" as "ga:exits",
"PAGE_VALUE" as "ga:pageValue"
from "FIVETRAN"."GOOGLE_ANALYTICS_THOUGHTSPOT"."GA_PAGE_TRACKING";

Deploy the Google Analytics SpotApp

After you complete the prerequisites, you are ready to deploy the Google Analytics SpotApp and begin leveraging its pre-built content.

To deploy the Google Analytics SpotApp, refer to Deploying SpotApps.

Google Analytics SpotApp schema

The following table describes the schema for the Google Analytics SpotApp.

Table Column Column type Required column

ga_ad_analytics

ga:adClicks

INT64

Y

ga_ad_analytics

ga:adCost

INT64

Y

ga_ad_analytics

ga:impressions

INT64

Y

ga_ad_analytics

ga:users

DOUBLE

N

ga_ad_analytics

ga:transactionRevenue

DOUBLE

Y

ga_ad_analytics

ga:bounces

DOUBLE

N

ga_ad_analytics

ga:cpc

DOUBLE

N

ga_ad_analytics

ga:cpm

DOUBLE

Y

ga_ad_analytics

ga:pageviews

DOUBLE

Y

ga_ad_analytics

ga:sessions

DOUBLE

Y

ga_ad_analytics

ga:date

DATE

Y

ga_ad_analytics

ga:adGroup

VARCHAR

Y

ga_ad_analytics

ga:adContent

VARCHAR

Y

ga_ad_analytics

ga:adSlot

VARCHAR

Y

ga_ad_analytics

ga:campaign

VARCHAR

Y

ga_ad_analytics

ga:adDistributionNetwork

VARCHAR

Y

ga_traffic_source

ga:userType

BOOL

Y

ga_traffic_source

ga:users

INT64

Y

ga_traffic_source

ga:organicSearches

INT64

Y

ga_traffic_source

ga:bounces

INT64

Y

ga_traffic_source

ga:sessions

INT64

Y

ga_traffic_source

ga:sessionDuration

DOUBLE

Y

ga_traffic_source

ga:pageviews

DOUBLE

Y

ga_traffic_source

ga:date

DATE

Y

ga_traffic_source

ga:campaign

VARCHAR

N

ga_traffic_source

ga:source

VARCHAR

Y

ga_traffic_source

ga:medium

VARCHAR

Y

ga_traffic_source

ga:keyword

VARCHAR

N

ga_traffic_source

ga:adContent

VARCHAR

N

ga_traffic_source

ga:socialNetwork

VARCHAR

Y

ga_traffic_source

ga:channelGrouping

VARCHAR

Y

ga_geo_audience

ga:userType

BOOL

Y

ga_geo_audience

ga:users

INT64

Y

ga_geo_audience

ga:bounces

INT64

Y

ga_geo_audience

ga:sessions

INT64

Y

ga_geo_audience

ga:sessionDuration

DOUBLE

Y

ga_geo_audience

ga:pageviews

DOUBLE

N

ga_geo_audience

ga:date

DATE

Y

ga_geo_audience

ga:source

VARCHAR

Y

ga_geo_audience

ga:medium

VARCHAR

N

ga_geo_audience

ga:channelGrouping

VARCHAR

Y

ga_geo_audience

ga:country

VARCHAR

Y

ga_geo_audience

ga:region

VARCHAR

N

ga_geo_audience

ga:latitude

DOUBLE

Y

ga_geo_audience

ga:longitude

DOUBLE

Y

ga_page_behaviour

ga:entrances

INT64

Y

ga_page_behaviour

ga:exits

DOUBLE

Y

ga_page_behaviour

ga:pageValue

DOUBLE

Y

ga_page_behaviour

ga:sessions

INT64

Y

ga_page_behaviour

ga:pageviews

INT64

Y

ga_page_behaviour

ga:timeOnPage

DOUBLE

Y

ga_page_behaviour

ga:date

DATE

Y

ga_page_behaviour

ga:source

VARCHAR

Y

ga_page_behaviour

ga:country

VARCHAR

Y

ga_page_behaviour

ga:hostname

VARCHAR

N

ga_page_behaviour

ga:pagePath

VARCHAR

N

ga_page_behaviour

ga:secondPagePath

VARCHAR

N

ga_page_behaviour

ga:pageTitle

VARCHAR

N

ga_page_behaviour

ga:landingPagePath

VARCHAR

Y

ga_page_behaviour

ga:exitPagePath

VARCHAR

Y

ga_site_statistics

ga:avgPageDownloadTime

DOUBLE

Y

ga_site_statistics

ga:avgPageLoadTime

INT64

Y

ga_site_statistics

ga:pageviews

INT64

Y

ga_site_statistics

ga:sessions

INT64

Y

ga_site_statistics

ga:avgRedirectionTime

DOUBLE

N

ga_site_statistics

ga:avgServerConnectionTime

DOUBLE

N

ga_site_statistics

ga:serverResponseTime

DOUBLE

N

ga_site_statistics

ga:time

TIME

Y

ga_site_statistics

ga:date

DATE

Y

ga_site_statistics

ga:country

VARCHAR

Y

ga_site_statistics

ga:hostname

VARCHAR

N

ga_site_statistics

ga:pagePath

VARCHAR

N

ga_site_statistics

ga:browser

VARCHAR

Y

ga_site_statistics

ga:operatingSystemVersion

VARCHAR

N

ga_site_statistics

ga:language

VARCHAR

N

ga_site_statistics

ga:deviceCategory

VARCHAR

N

ga_site_statistics

ga:operatingSystem

VARCHAR

N