Google Analytics Historical Data Extractor

Why Extract Google Analytics Historical Hit Level Data?

Google Analytics Standard is a free service provided by Google. Of course, it will have many limitations. Even though technically you can extract data out of Google Analytics there are few problems.

a. You need to write a build a custom solution using Google Analytics APIs to export data.
b. The event/hit data does not provide a Unique Visitor Id hence the data cannot be integrated with other omnichannel marketing data sources such as Mailchimp, Shopify and other CRM systems.

Google Analytics Historical Hit Level Data Extractor by Electrik.AI is designed to extract historical hit level data as old as your google analytics account and load it in a database.

How to add a Google Analytics Historical Data Source in Electrik.AI?

Step 1: Ensure you have following prerequisites to create a Google Analytics Historical Data Connection in Electrik.AI

a. Access to Google Analytics Account.

b. Google Analytics Account User Id and Password.

c. Google Analytics Property Id; View Id details.

Step 2: Sign-in into your Electrik.AI account

Step 3: Select IO in Electrik.AI dashboard

Select IO in Electrik.AI dashboard-ElectrikAI

Step 4: Select the Connections tab in the left most panel.

Step 4 Select the Connections Icon in the left most panel-ElectrikAI

Step 5: Click on “Add” action button.

Step 5 Click on “Add” button the top-ElectrikAI

Step 6: The Source default tab is selected just click on “Google Analytics Historical Data” connection.

Step 6 Google Analytics Historical Data-ElectrikAI

Step 7: Enter all the details.

a. Select your analytics account from the drop down
b. Select your analytics account property from the drop down
c. Select your account property view from the drop down

Step 7 Enter all the details-ElectrikAI

Step 8: Click on Save button on the top.

Step 8 Click on Save button on the top-ElctrikAI

Step 9: Congratulations, you have successfully created Google Analytics Historical Data connection in Electrik.AI.

Step 9 Congratulations successfully created Google Analytics Historical Data connection-ElctrikAI

What are the supported Data Destinations?

Electrik.AI supports the following databases to export Google Analytics Historical Data. It is important to note that Electrik.AI itself is not a destination. A destination is required to export data from various marketing data sources using Electrik.AI.

Google Big Query Data Destination-Elecrik.AI
PostgreSQL Data Destination-Eletrik.AI
MySQL Data Destination-Electrik.AI
AzureSQL Data Destination-Eletrik.AI

How to export Google Analytics Historical Data?

Electrik.AI creates a data flow (or ETL process) that exports Google Analytics data to a target database or data warehouse.

Follow these steps to export Historical Google Analytics Data to Google Big Query.

Step 1: Ensure you have the following prerequisites.
1. Access to Google Analytics Account.
2. Google Analytics Account User Id and Password.
3. Google Analytics Property Id; View Id details.
4. Admin Access to Google BigQuery.
Step 2: Sign-in into your Electrik.AI account.
Step 3: Select IO in Electrik.AI dashboard.

Select IO in Electrik.AI dashboard-ElectrikAI

Step 4: Click on Add button to create a Google Analytics Historical Data Extractor Flow.

Step 4 Flow Add Button-ElectrikAI

Step 5: Select Google Analytics Historical Data Connection present in the list of available sources.

Step 5 Select Google Analytics Historical Data Connection-ElectrikAI

Step 6: Select your existing Google Analytics Historical Data connection in Electrik.AI.

Step 6 Select your existing Google Analytics Historical Data-ElectrikAI

To Configure a new connection, refer to this link.

Step 7: Click on a destination of your choice from list of available destinations.
In this example we are choosing Google BigQuery. 

Step 7 Click on a destination of your choice from list of available destinations-ElectrikAI

Step 8:  Select your existing Google BigQuery connection in Electrik.AI.
To Configure a new connection, refer to this link.

Step 8 Select your existing Google BigQuery connection-ElectrikAI

Step 9:
a. Electrik.AI now supports 4 reports, choose the desired type to get the appropriate data. In this example we are choosing Campaign Performance.
b. Use existing/create a new Data set from/in your BigQuery bucket.
c. Select from and to month-year for which you wish to extract the data
d. Provide Success Notification Email, Failure Notification Email.
e. Click “Save and Proceed” to continue.

Step 9 Review the Data Source, Data Destination-ElectrikAI

Success Notification Email: Enter a valid Email ID to receive the Flow Success Email Notification.

Failure Notification Email: Enter a valid Email ID to receive the Flow Failure Email Notification.

1. Let’s say if your GA account is created on Dec 2018 then avoid choosing staring month less than Dec 2018.
2. If payment method is not added only first 7 days (of selected date range) of historical hit level data will be exported.
3. By Default, Electrik.AI uses the signed-in user’s Email Id.
4. By Default, Electrik.AI chooses an existing Dataset to load your GA data. You can create a new dataset in your bucket by selecting the “Create Data Set” radio button.

Step 10: Congratulations, Flow is created. Please wait while you are redirected to Flow Details Screen.

Step 10 Congratulations, Flow is created-ElectrikAI

You have now successfully setup Google Analytics Historical Data to Google BigQuery flow in Electrik.AI

Successfully setup Google Analytics Historical Data-ElectrikAI

Your Google Analytics Historical Data will start loading after 24 hours in your Google BigQuery Dataset.

Google Analytics Historical Data will start loading after 24 hours-ElectrikAI

Data Schema

The following is an overview of the dimensions and metrics Electrik.AI can extract from Google Analytics.

The eai_ dimensions (e.g. eai_session_id) are captured by Electrik.AI. All Google Analytics dimension and metrics are renamed from ga:<name> to ga_<name>. For example, ga_pagepath is the renamed version of ga:pagePath.

IndexCategoryElectrik.AI Column NameDescription
1Electrik.AIeai_client_idGA Client ID
2Electrik.AIeai_event_in_utc_dtDate of Hit in UTC timezone
3Electrik.AIeai_event_in_visitor_tzTimestamp of Hit in Visitor’s timezone
4Electrik.AIeai_hit_typeType of hit (pageview,event,transaction, etc.)
5Electrik.AIeai_session_idUnique Session Identifier
6App Trackingga_appnamega:appName
7App Trackingga_screennamega:screenName
8Channel Groupingga_channelgroupingga:channelGrouping
9Custom Variablesga_dimension1ga:dimensionXX
10Custom Variablesga_dimension10ga:dimensionXX
11Custom Variablesga_dimension11ga:dimensionXX
12Custom Variablesga_dimension12ga:dimensionXX
13Custom Variablesga_dimension13ga:dimensionXX
14Custom Variablesga_dimension14ga:dimensionXX
15Custom Variablesga_dimension15ga:dimensionXX
16Custom Variablesga_dimension16ga:dimensionXX
17Custom Variablesga_dimension17ga:dimensionXX
18Custom Variablesga_dimension18ga:dimensionXX
19Custom Variablesga_dimension2ga:dimensionXX
20Custom Variablesga_dimension3ga:dimensionXX
21Custom Variablesga_dimension4ga:dimensionXX
22Custom Variablesga_dimension5ga:dimensionXX
23Custom Variablesga_dimension6ga:dimensionXX
24Custom Variablesga_dimension7ga:dimensionXX
25Custom Variablesga_dimension8ga:dimensionXX
26Custom Variablesga_dimension9ga:dimensionXX
27Ecommercega_ecommerceTypeThe type of this e-commerce activity.
28Ecommercega_productsDetails of the products in this transaction.
34Event Trackingga_eventactionga:eventAction
35Event Trackingga_eventcategoryga:eventCategory
36Event Trackingga_eventlabelga:eventLabel
37Event Trackingga_eventvaluega:eventValue
38Event Trackingga_totaleventsga:totalEvents
39Goal Conversionsga_goal10completionsga:goal10Completions
40Goal Conversionsga_goal10namega:goal10Starts
41Goal Conversionsga_goal10valuega:goal10Value
42Goal Conversionsga_goal11completionsga:goal11Completions
43Goal Conversionsga_goal11namega:goal11Starts
44Goal Conversionsga_goal11valuega:goal11Value
45Goal Conversionsga_goal12completionsga:goal12Completions
46Goal Conversionsga_goal12namega:goal12Starts
47Goal Conversionsga_goal12valuega:goal12Value
48Goal Conversionsga_goal13completionsga:goal13Completions
49Goal Conversionsga_goal13namega:goal13Starts
50Goal Conversionsga_goal13valuega:goal13Value
51Goal Conversionsga_goal14completionsga:goal14Completions
52Goal Conversionsga_goal14namega:goal14Starts
53Goal Conversionsga_goal14valuega:goal14Value
54Goal Conversionsga_goal15completionsga:goal15Completions
55Goal Conversionsga_goal15namega:goal15Starts
56Goal Conversionsga_goal15valuega:goal15Value
57Goal Conversionsga_goal16completionsga:goal16Completions
58Goal Conversionsga_goal16namega:goal16Starts
59Goal Conversionsga_goal16valuega:goal16Value
60Goal Conversionsga_goal17completionsga:goal17Completions
61Goal Conversionsga_goal17namega:goal17Starts
62Goal Conversionsga_goal17valuega:goal17Value
63Goal Conversionsga_goal18completionsga:goal18Completions
64Goal Conversionsga_goal18namega:goal18Starts
65Goal Conversionsga_goal18valuega:goal18Value
66Goal Conversionsga_goal19completionsga:goal19Completions
67Goal Conversionsga_goal19namega:goal19Starts
68Goal Conversionsga_goal19valuega:goal19Value
69Goal Conversionsga_goal1completionsga:goal1Completions
70Goal Conversionsga_goal1namega:goal1Starts
71Goal Conversionsga_goal1valuega:goal1Value
72Goal Conversionsga_goal20completionsga:goal20Completions
73Goal Conversionsga_goal20namega:goal20Starts
74Goal Conversionsga_goal20valuega:goal20Value
75Goal Conversionsga_goal2completionsga:goal2Completions
76Goal Conversionsga_goal2namega:goal2Starts
77Goal Conversionsga_goal2valuega:goal2Value
78Goal Conversionsga_goal3completionsga:goal3Completions
79Goal Conversionsga_goal3namega:goal3Starts
80Goal Conversionsga_goal3valuega:goal3Value
81Goal Conversionsga_goal4completionsga:goal4Completions
82Goal Conversionsga_goal4namega:goal4Starts
83Goal Conversionsga_goal4valuega:goal4Value
84Goal Conversionsga_goal5completionsga:goal5Completions
85Goal Conversionsga_goal5namega:goal5Starts
86Goal Conversionsga_goal5valuega:goal5Value
87Goal Conversionsga_goal6completionsga:goal6Completions
88Goal Conversionsga_goal6namega:goal6Starts
89Goal Conversionsga_goal6valuega:goal6Value
90Goal Conversionsga_goal7completionsga:goal7Completions
91Goal Conversionsga_goal7namega:goal7Starts
92Goal Conversionsga_goal7valuega:goal7Value
93Goal Conversionsga_goal8completionsga:goal8Completions
94Goal Conversionsga_goal8namega:goal8Starts
95Goal Conversionsga_goal8valuega:goal8Value
96Goal Conversionsga_goal9completionsga:goal9Completions
97Goal Conversionsga_goal9namega:goal9Starts
98Goal Conversionsga_goal9valuega:goal9Value
99Goal Conversionsga_goalCompletionLocationga:goalCompletionLocation
100Goal Conversionsga_goalPreviousStep1ga:goalPreviousStep1
101Goal Conversionsga_goalPreviousStep2ga:goalPreviousStep2
102Goal Conversionsga_goalPreviousStep3ga:goalPreviousStep3
103Page Trackingga_hostnamega:hostname
104Page Trackingga_landingPagePathga:landingPagePath
105Page Trackingga_pagepathga:pagePath
106Page Trackingga_pagetitlega:pageTitle
107Platform or Devicega_mobiledevicebrandingga:mobileDeviceBranding
108Platform or Devicega_mobiledevicemodelga:mobileDeviceModel
109Platform or Devicega_datasourcega:dataSource
110Platform or Devicega_devicecategoryga:deviceCategory
111Platform or Devicega_operatingsystemga:operatingSystem
112Traffic Sourcesga_campaignga:campaign
113Traffic Sourcesga_keywordga:keyword
114Traffic Sourcesga_mediumga:medium
115Traffic Sourcesga_sourcega:source