Google Analytics to MySQL: 2 Easy Methods for Replication

Are you attempting to gain more information from your Google Analytics by moving it to a larger database such as MySQL? Well, you’ve come to the correct place. Data replication from Google Analytics to MySQL is now much easier.

This article will give you a brief overview of Google Analytics and MySQL. You will also explore 2 methods to set up Google Analytics to MySQL Integration. In addition, the manual method’s drawbacks will also be examined in more detail in further sections. Read along to see which way of connecting Google Analytics to MySQL is the most suitable for you.

Prerequisites

You will have a much easier time understanding the ways for setting up the Google Analytics to MySQL connection if you have gone through the following aspects:

• An active Google Analytics account.
• An active MySQL account.
• Working knowledge of SQL.
• Working knowledge of at least one scripting language.

Introduction to Google Analytics

Google Analytics is the service offered by Google to get complete information about your website and its users. It allows the site owners to measure the performance of their marketing, content, and products. It not only provides unique insights but also helps users deploy machine learning capabilities to make the most of their data. Despite all the unique analysis services provided by Google, it is sometimes required to get the raw clickstream data from your website into the on-premise databases. This helps in creating deeper analysis results by combining the clickstream data with the organization’s customer data and product data.

To know more about Google Analytics, visit this link.

Introduction to MySQL

MySQL is a SQL-based open-source Relational Database Management System. It stores data in the form of tables. MySQL is a platform-independent database, which means you can use it on Windows, Mac OS X, or Linux with ease. MySQL is the world’s most used database, with proven performance, reliability, and ease of use. It is used by prominent open-source programs like WordPress, Magento, Open Cart, Joomla, and top websites like Facebook, YouTube, and Twitter.

To know more about MySQL, visit this link.

Methods to Set up Google Analytics to MySQL Integration

This article delves into both the manual and using Hevo methods in depth. You will also see some of the pros and cons of these approaches and would be able to pick the best method to export google analytics data to MySQL based on your use case. Below are the two methods to set up Google Analytics to MySQL Integration:

Method 1: Using Hevo to Set up Google Analytics to MySQL Integration

Hevo, a No-code Data Pipeline, helps you directly transfer data from 150+ data sources including 30+ Free sources like Google Analytics to a Database such as MySQL, Data Warehouses, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo Data takes care of all your Data Preprocessing needs and lets you focus on key business activities and draw a much more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always has analysis-ready data in your desired destination. 

Hevo Data provides a simple click-and-execute interface to get all your data from Google Analytics to MySQL in just 2 steps.

Step 1: Configure and authenticate Google Analytics source.

To get more details about Configuring Google Analytics with Hevo Data, visit this link.

Step 2: Configure the MySQL database where the data needs to be loaded.

To get more details about Configuring MySQL with Hevo Data, visit this link.

Hevo does all the heavy lifting, masks all ETL complexities, and delivers data in MySQL in a reliable fashion.

Here are more reasons to try Hevo to connect Google Analytics to MySQL database:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
• Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
• Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Bringing in Hevo was a boon. Our data moves seamlessly from all sources to Redshift, enabling us to do so much more with it

– Chushul Suri, Head Of Data Analytics, Meesho

Simplify your Data Analysis with Hevo today! 

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

Method 2: Manual ETL process to Set up Google Analytics to MySQL Integration

Below is a method to manually set up Google Analytics to MySQL Integration:

Step 1: Getting data from Google Analytics

Google Analytics makes the click event data available through its Reporting API V4. Reporting API provides two sets of Rest API to address two specific use cases.

1. Get aggregated analytics information on user behavior on your site on the basis of available dimensions – Google calls these metrics and dimensions. Metrics are aggregated information that you can capture and dimensions are the terms on which metrics are aggregated. For example, the number of users will be a metric and time will be a dimension.
2. Get activities of a specific user = For this, you need to know the user id or client id. An obvious question then is how do you know the user id or client id. You will need to modify some bits in the client-side google analytics function that you are going to use and capture the client id. Google does not specifically tell you how to do this, but there is ample documentation on the internet about it. Please consult the laws and restrictions in your local country before attempting this, since the legality of this will depend on the privacy laws of the country. You will also need to go to the Google Analytics dashboard and register the client id as a new dimension.

Google Analytics APIs use oAuth 2.0 as the authentication protocol. Before accessing the APIs, the user first needs to create a service account in the Google Analytics dashboard and generate authentication tokens. Let us review how this can be done.

1. Go to google service accounts page and select a project. If you have not already created a project, create a project.
2. Click on Create Service Account.
3. You can ignore the permissions for now.
4. On the ‘Grant users access to this service account’ section, click Create key
5. Select JSON as the format for your key.
6. Click Create a key and you will be prompted with a dialogue to save the key in your local computer. Save the key.

We will be using the information from this step when we actually access the API.

Step 2: Accessing Google Reporting API V4

Google provides easy-to-use libraries in Python, Java, and PHP to access its reporting APIs. It is best to use these APIs to download the data since it would be a tedious process to access these APIs using command-line tools like CURL. Here we will use the Python library to access the APIs. The following steps detail the procedure and code snippets to load data from Google Analytics to MySQL.

• Use the following command to install the Python GA library to your environment.

sudo pip install --upgrade google-api-python-client

This assumes the Python programming environment is already installed and works fine.

We will now start writing the script for downloading the data as a CSV file.

• Import the required libraries.

from apiclient.discovery import build from oauth2client.service_account import ServiceAccountCredentials

• Initialize the required variables.

SCOPES = [‘https://www.googleapis.com/auth/analytics.readonly’] KEY_FILE_LOCATION = ‘<REPLACE_WITH_JSON_FILE>’ VIEW_ID = ‘<REPLACE_WITH_VIEW_ID>’

The above variables are required for OAuth authentication. Replace the key file location and view id with what we obtained in the first service creation step. View ids are the views from which you will be collecting the data. To get the view id of a particular view that you have already configured, go to the admin section, click on the view that you need, and go to view settings.

• Build the required objects.

credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SCOPES) #Build the service object. analytics = build(‘analyticsreporting’, ‘v4’, credentials=credentials)

• Execute the method to get the data. The below query is for getting the number of users aggregated by country from the last 7 days.

response = analytics.reports().batchGet(       body={         ‘reportRequests’: [         {           ‘viewId’: VIEW_ID,           ‘dateRanges’: [{‘startDate’: ‘7daysAgo’, ‘endDate’: ‘today’}],           ‘metrics’: [{‘expression’: ‘ga:sessions’}],           ‘dimensions’: [{‘name’: ‘ga:country’}]         }]       }   ).execute()

• Parse the JSON and write the contents into a CSV file.

import pandas as pd from pandas.io.json  import json_normalize reports = response[‘reports’][0] columnHeader = reports[‘columnHeader’][‘dimensions’]  metricHeader = reports[‘columnHeader’][‘metricHeader’][‘metricHeaderEntries’] columns = columnHeader for metric in metricHeader:   columns.append(metric[‘name’])  data = json_normalize(reports[‘data’][‘rows’])  data_dimensions = pd.DataFrame(data[‘dimensions’].tolist())  data_metrics = pd.DataFrame(data[‘metrics’].tolist())  data_metrics = data_metrics.applymap(lambda x: x[‘values’])  data_metrics = pd.DataFrame(data_metrics[0].tolist())  result = pd.concat([data_dimensions, data_metrics], axis=1, ignore_index=True) result.to_csv(‘reports.csv’) Save the script and execute it. The result will be a CSV file with the following columns Id , ga:country, ga:sessions

• This file can be directly loaded to a MySQL table using the below command. Please ensure the table is already created.

LOAD DATA INFILE’products.csv’ INTO TABLE customers FIELDS TERMINATED BY ‘,’  ENCLOSED BY ‘“’ LINES TERMINATED BY ‘rn’ ;

That’s it! You now have your google analytics data in your MySQL. Now that we know how to get the Google Analytics data using custom code, let’s look into the limitations of using this method.

Challenges of Building a Custom Setup

1. The method even though elegant, requires you to write a lot of custom code. Google’s output JSON structure is a complex one and you may have to make changes to the above code according to the data you query from the API.
2. This approach will work for a one-off data load to MySQL, but in most cases, organizations need to do this periodically merging the data point every day with seamless handling of duplicates. This will need you to write a very sophisticated import tool just for Google Analytics.
3. The above method addresses only one API that is provided by Google. There are many other available APIs from Google which provide different types of data from the Google Analytics engine. An example is a real-time API.  All these APIs come with a different output JSON structure and the developers will need to write separate parsers.

A solution to all the above problems is to use a completely managed Data Integration Platform like Hevo.

Conclusion

This article provided a detailed step-by-step tutorial for setting up your Google Analytics to MySQL Integration utilizing the two techniques described in this article. The manual method although effective will require a lot of time and resources. Data migration from Google Analytics to MySQL is a time-consuming and tedious procedure, but with the help of a data integration solution like Hevo, it can be done with little work and in no time.

VISIT OUR WEBSITE TO EXPLORE HEVO

Businesses can use automated platforms like Hevo to set this integration and handle the ETL process. It helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you with a hassle-free experience.

SIGN UP and move data from Google Analytics to MySQL instantly.

Share your experience of connecting Google Analytics and MySQL in the comments section below!