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.

Note:
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.

Index Category Electrik.AI Column Name Description
1 Electrik.AI eai_client_id GA Client ID
2 Electrik.AI eai_event_in_utc_dt Date of Hit in UTC timezone
3 Electrik.AI eai_event_in_visitor_tz Timestamp of Hit in Visitor’s timezone
4 Electrik.AI eai_hit_type Type of hit (pageview,event,transaction, etc.)
5 Electrik.AI eai_session_id Unique Session Identifier
6 App Tracking ga_appname ga:appName
7 App Tracking ga_screenname ga:screenName
8 Channel Grouping ga_channelgrouping ga:channelGrouping
9 Custom Variables ga_dimension1 ga:dimensionXX
10 Custom Variables ga_dimension10 ga:dimensionXX
11 Custom Variables ga_dimension11 ga:dimensionXX
12 Custom Variables ga_dimension12 ga:dimensionXX
13 Custom Variables ga_dimension13 ga:dimensionXX
14 Custom Variables ga_dimension14 ga:dimensionXX
15 Custom Variables ga_dimension15 ga:dimensionXX
16 Custom Variables ga_dimension16 ga:dimensionXX
17 Custom Variables ga_dimension17 ga:dimensionXX
18 Custom Variables ga_dimension18 ga:dimensionXX
19 Custom Variables ga_dimension2 ga:dimensionXX
20 Custom Variables ga_dimension3 ga:dimensionXX
21 Custom Variables ga_dimension4 ga:dimensionXX
22 Custom Variables ga_dimension5 ga:dimensionXX
23 Custom Variables ga_dimension6 ga:dimensionXX
24 Custom Variables ga_dimension7 ga:dimensionXX
25 Custom Variables ga_dimension8 ga:dimensionXX
26 Custom Variables ga_dimension9 ga:dimensionXX
27 Ecommerce ga_ecommerceType The type of this e-commerce activity.
28 Ecommerce ga_products Details of the products in this transaction.
29 Ecommerce ga_shoppingStage ga:shoppingStage
30 Ecommerce ga_transactionid ga:transactionId
31 Ecommerce ga_transactionrevenue ga:transactionRevenue
32 Ecommerce ga_transactionshipping ga:transactionShipping
33 Ecommerce ga_transactiontax ga:transactionTax
34 Event Tracking ga_eventaction ga:eventAction
35 Event Tracking ga_eventcategory ga:eventCategory
36 Event Tracking ga_eventlabel ga:eventLabel
37 Event Tracking ga_eventvalue ga:eventValue
38 Event Tracking ga_totalevents ga:totalEvents
39 Goal Conversions ga_goal10completions ga:goal10Completions
40 Goal Conversions ga_goal10name ga:goal10Starts
41 Goal Conversions ga_goal10value ga:goal10Value
42 Goal Conversions ga_goal11completions ga:goal11Completions
43 Goal Conversions ga_goal11name ga:goal11Starts
44 Goal Conversions ga_goal11value ga:goal11Value
45 Goal Conversions ga_goal12completions ga:goal12Completions
46 Goal Conversions ga_goal12name ga:goal12Starts
47 Goal Conversions ga_goal12value ga:goal12Value
48 Goal Conversions ga_goal13completions ga:goal13Completions
49 Goal Conversions ga_goal13name ga:goal13Starts
50 Goal Conversions ga_goal13value ga:goal13Value
51 Goal Conversions ga_goal14completions ga:goal14Completions
52 Goal Conversions ga_goal14name ga:goal14Starts
53 Goal Conversions ga_goal14value ga:goal14Value
54 Goal Conversions ga_goal15completions ga:goal15Completions
55 Goal Conversions ga_goal15name ga:goal15Starts
56 Goal Conversions ga_goal15value ga:goal15Value
57 Goal Conversions ga_goal16completions ga:goal16Completions
58 Goal Conversions ga_goal16name ga:goal16Starts
59 Goal Conversions ga_goal16value ga:goal16Value
60 Goal Conversions ga_goal17completions ga:goal17Completions
61 Goal Conversions ga_goal17name ga:goal17Starts
62 Goal Conversions ga_goal17value ga:goal17Value
63 Goal Conversions ga_goal18completions ga:goal18Completions
64 Goal Conversions ga_goal18name ga:goal18Starts
65 Goal Conversions ga_goal18value ga:goal18Value
66 Goal Conversions ga_goal19completions ga:goal19Completions
67 Goal Conversions ga_goal19name ga:goal19Starts
68 Goal Conversions ga_goal19value ga:goal19Value
69 Goal Conversions ga_goal1completions ga:goal1Completions
70 Goal Conversions ga_goal1name ga:goal1Starts
71 Goal Conversions ga_goal1value ga:goal1Value
72 Goal Conversions ga_goal20completions ga:goal20Completions
73 Goal Conversions ga_goal20name ga:goal20Starts
74 Goal Conversions ga_goal20value ga:goal20Value
75 Goal Conversions ga_goal2completions ga:goal2Completions
76 Goal Conversions ga_goal2name ga:goal2Starts
77 Goal Conversions ga_goal2value ga:goal2Value
78 Goal Conversions ga_goal3completions ga:goal3Completions
79 Goal Conversions ga_goal3name ga:goal3Starts
80 Goal Conversions ga_goal3value ga:goal3Value
81 Goal Conversions ga_goal4completions ga:goal4Completions
82 Goal Conversions ga_goal4name ga:goal4Starts
83 Goal Conversions ga_goal4value ga:goal4Value
84 Goal Conversions ga_goal5completions ga:goal5Completions
85 Goal Conversions ga_goal5name ga:goal5Starts
86 Goal Conversions ga_goal5value ga:goal5Value
87 Goal Conversions ga_goal6completions ga:goal6Completions
88 Goal Conversions ga_goal6name ga:goal6Starts
89 Goal Conversions ga_goal6value ga:goal6Value
90 Goal Conversions ga_goal7completions ga:goal7Completions
91 Goal Conversions ga_goal7name ga:goal7Starts
92 Goal Conversions ga_goal7value ga:goal7Value
93 Goal Conversions ga_goal8completions ga:goal8Completions
94 Goal Conversions ga_goal8name ga:goal8Starts
95 Goal Conversions ga_goal8value ga:goal8Value
96 Goal Conversions ga_goal9completions ga:goal9Completions
97 Goal Conversions ga_goal9name ga:goal9Starts
98 Goal Conversions ga_goal9value ga:goal9Value
99 Goal Conversions ga_goalCompletionLocation ga:goalCompletionLocation
100 Goal Conversions ga_goalPreviousStep1 ga:goalPreviousStep1
101 Goal Conversions ga_goalPreviousStep2 ga:goalPreviousStep2
102 Goal Conversions ga_goalPreviousStep3 ga:goalPreviousStep3
103 Page Tracking ga_hostname ga:hostname
104 Page Tracking ga_landingPagePath ga:landingPagePath
105 Page Tracking ga_pagepath ga:pagePath
106 Page Tracking ga_pagetitle ga:pageTitle
107 Platform or Device ga_mobiledevicebranding ga:mobileDeviceBranding
108 Platform or Device ga_mobiledevicemodel ga:mobileDeviceModel
109 Platform or Device ga_datasource ga:dataSource
110 Platform or Device ga_devicecategory ga:deviceCategory
111 Platform or Device ga_operatingsystem ga:operatingSystem
112 Traffic Sources ga_campaign ga:campaign
113 Traffic Sources ga_keyword ga:keyword
114 Traffic Sources ga_medium ga:medium
115 Traffic Sources ga_source ga:source