Google Analytics to SQL Server: 2 Easy Methods

on Data Integration • August 12th, 2021 • Write for Hevo

Google Analytics helps organizations track user activity and the effectiveness of their content. It is widely used by marketers and advertisers around the world to gain critical insights from their marketing campaigns. The data obtained from Google Analytics is further transferred to SQL server to successfully backup significant business data to be used for further analysis. This blog post aims at introducing you to two approaches that can be used for data transfer from Google Analytics to SQL Server.

The constituents of this post are listed below. The navigation links below will help you quickly traverse to the point of your interest:

Introduction to Google Analytics

GA Logo
Image Source

Google Analytics helps companies keep track of user details and advertising campaigns using a simple javascript plugin. It provides an excellent reporting dashboard and the ability to run various analyses on the user activity data. It even supports advanced machine learning capabilities and helps organizations to deploy this capability to make better decisions without having to know the concepts or implementation details of machine learning algorithms.

Google Analytics also makes raw user activity data available to the companies that want to run custom algorithms and reports. This requires the raw clickstream data to be loaded into the organization’s own data store. 

Here are a few key benefits of Google Analytics:

  • Understanding User Behavior: The key advantage of having behavioral metrics is that it provides you valuable information on what pages get the most engagement and traction. By having a better understanding of User Behavior, you can change the way you can interact with your users in the most optimized way.
  • Easy to Find Your Target Audience: With the help of Google Analytics, you can easily define your target audience. The audience can then be used to optimize the content of your website along with its offerings to increase engagement. Thus, an engaged audience is one of the best ways to improve a website’s potential.
  • Data Reports and Customization: Google Analytics allows you to customize dashboards, alerts, and reports to analyze data that can fit every company’s different needs. Google Analytics provides an extensive library of user-generated reports and dashboards that can help you make data-driven decisions to improve efficiency.
  • Track Online Traffic: Google Analytics allows you to track traffic from all sources since having an understanding of where your audience comes from is a crucial aspect of running a business online. Pinpointing different traffic sources and understanding why and how much traffic comes to your website allows you to track the gains of your strategies.
  • Improved Search Engine Optimization and Content Marketing: With Google Analytics, you can identify the best-performing pages of your website to gain insights on the type of content to invest in. Google Analytics allows you to improve the tracking of the success of your Content Marketing and Search Engine Optimization strategy. By analyzing every part of your content strategy, you can create a stable and solid plan with clearly defined steps to reproduce successful pages.

Introduction to SQL Server

SQL Server Logo
Image Source

SQL Server is a relational database system provided by Microsoft. It can hold petabytes of data and have 100s of parallel running cores. It has a comprehensive querying layer that aids in business analysis and tightly integrates with reporting tools like Power BI. Recent versions have spark support built-in; which means analyzing large amounts of data using Spark SQL without much additional setup needed.

It supports ANSI SQL, the standard SQL (structured query language) language. SQL Server comes with its implementation of the proprietary language called T-SQL (transact SQL). SQL server management studio (SSMS) is the main interface tool, consisting of a client and server. A client is an application for sending requests to the SQL Server installed on a given machine, whereas a server is capable of processing input data as requested.

SQL Server provides enterprise-grade security and support which makes it a favorite among organizations with strict compliance requirements. This post is about loading data from Google Analytics to SQL Server.

Relational databases store and manage data in a traditional table format. Businesses use relational databases to handle data from their applications and ensure they always have access to critical information.

Here are a few key features of SQL Server:

  • The Database Engine: This segment handles data storage, fast exchange processing, and data security.
  • The SQL Server: This assistance begins, pauses, stops, and continues the dataset process. 
  • The Server Agent: It plays out the job of the task scheduler. It tends to be set off by any occasion or according to request. 
  • The SQL Server Browser: This receives approaching queries and connects with the ideal SQL server instance. 
  • The SQL Server Full-Text Search: This permits the client to run a full-text search against Character information in SQL Tables. 

Prerequisites

The following checklist will help you stay prepared for the data transfer:

  • A valid Google Analytics account with admin privileges. Create an account here.
  • Basic knowledge of Python and how to use Python libraries.
  • An SQL Server installation with write permission. Find the SQL server installation guide here and click on this link to learn how you can configure account permissions.

Moving Data from Google Analytics to SQL Server

There are various methods for transferring data from Google Analytics to SQL Server. In this post, you will peruse through the following approaches.

Method 1: Using Manual ETL Scripts to Connect Google Analytics to SQL Server

To write custom scripts to transfer data from Google Analytics to SQL Server, you would have to first generate account keys, install required python libraries and finally build a Python script to load data from Google Analytics to SQL Server. Using custom scripts can be a time-consuming process and needs continuous monitoring of the written scripts.

Method 2: Export Google Analytics data to SQL Server Using Hevo Data

Get Started with Hevo for Free

Using Hevo, you can seamlessly move data from 100+ Data Sources like Google Analytics (among 30+ Free Sources) to SQL Server without writing a single piece of code. Since Hevo is fully managed by a team of experts, you would not need to monitor scripts regularly, leaving you with enough time to focus on critical business activities. The process of transferring data from Google Analytics to SQL server will be completely smooth.

Simplify your Data Analysis with Hevo today! 

Sign up here for a 14-Day Free Trial!

Understanding the Methods to Connect Google Analytics to SQL Server

You can use the following methods to establish a connection from Google Analytics to SQL Server in a seamless fashion:

Method 1: Using Manual ETL Scripts to Connect Google Analytics to SQL Server

This section of the post will help you build a custom script to migrate data from Google Analytics to SQL Server.

Google Analytics provides data through Reporting API V4. These APIs provide aggregate analytics information on the basics of metrics and dimensions. Dimensions are terms based on which metrics are aggregated. The number of users is a typical example of a metric. The time window is one of the dimensions on which the user count is aggregated. The API uses the OAuth authentication protocol and requires a service account to access the data.

Below are the steps involved in developing custom scripts to load data from Google Analytics to SQL Server.

Step 1: Generating Account Keys on Google Analytics
Step 2: Installing the Required Python Libraries
Step 3: Building a Python Script
Step 4: Loading Data from Google Analytics to SQL Server

Step 1: Generating Account Keys on Google Analytics

Go to the Google API console and create a project. In the ‘credential’ section of the project click ‘Create Credentials’. Select ‘Service Accounts’ and click ‘Create Key’. Select JSON and save the key on your computer. We will be using this key in our next steps.

Google Analytics to SQL server: Account creation
Image Source

Step 2: Installing the Required Python Libraries

Google Analytics provides tools in Python, Java, Javascript, etc to access their APIs. Using command-line utilities like CURL is a tedious process because of the complexity of the JSON structure and the authentication process. 

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

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

The above command needs a Python environment to be pre-installed. Let us now proceed to build the script.

Step 3: Building a Python Script

This section deals with building a python script using the installed Google API client library. We will start by importing the required libraries. The various stages involved in this stage are as follows:

  1. Import the Required Libraries
  2. Initialize the Keys and Configuration Parameters
  3. Request the Reports JSON from the API
  4. Parse the JSON to Extract the Relevant Fields

1. Import the Required Libraries

Use the following command to import the Python libraries.

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

2. Initialize the Keys and Configuration Parameters


      SCOPES = ['https://www.googleapis.com/auth/analytics.readonly']
                        KEY_FILE_LOCATION = '<JSON_FILE_WITH_KEY>'
                        VIEW_ID = '<VIEW_ID>'

 credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SCOPES)

  # Build the service object.
  analytics = build('analyticsreporting', 'v4', credentials=credentials)

The JSON file is the one that was saved in the first step. The VIEW_ID can be found in the admin section. Go to the Admin section, under the ‘views’ column, click view settings. Under basic settings, you will find the VIEW_ID.

3. Request the Reports JSON from the API


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

The above code snippet accesses the reports for a specific date range. The metric we access here is the session count and the dimension is ‘country’.

4. Parse the JSON to Extract the Relevant Fields

import pandas as pd from pandas.io.json 
import json_normalize

ga_reports = response['reports'][0]
columnHeader = ga_reports['columnHeader']['dimensions'] 
metricHeader = ga_reports['columnHeader']['metricHeader']['metricHeaderEntries'] report_columns = columnHeader for metric in metricHeader:  
   report_columns.append(metric['name']) 
report_data = json_normalize(ga_reports['data']['rows']) 
g_dimensions = pd.DataFrame(report_data['dimensions'].tolist()) 
g_metrics = pd.DataFrame(report_data['metrics'].tolist()) 
g_metrics = data_metrics.applymap(lambda x: x['values']) 
g_metrics = pd.DataFrame(data_metrics[0].tolist()) 
result = pd.concat([g_dimensions, g_metrics], axis=1, ignore_index=True)
result.to_csv('reports.csv')

The above code snippet uses a pandas data frame to process the JSON and save it as a CSV with metrics and dimensions. Execute the script to generate the CSV file. The resulting CSV will have the below columns:

id, ga: country, ga: sessions
123x, India, 23
341a, Germany, 34

Step 4: Loading Data from Google Analytics to SQL Server

 You will now load the above CSV to SQL Server using the below command.

BULK INSERT GaSessions
FROM 'C:CSVDatareports.csv' WITH 
( FIRSTROW = 2,
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = 'n',
 TABLOCK )

That concludes the steps to load data from Google Analytics to SQL Server. The task required us to build a custom script, generate a CSV using it, and then load the CSV to SQL Server. In reality, there are a lot more challenges in executing such a load process. The below section will give you a good idea about the limitations of this approach.

Limitations of using Manual ETL Scripts to Connect Google Analytics to SQL Server

There are few limitations of using Manual ETL Scripts to Connect Google Analytics to SQL Server:

  • The first and foremost one is that this method requires you to write a lot of custom code. If you have a transformation requirement before loading to SQL Server, be prepared to spend even more time writing custom code.
  • This approach is suitable for a one-time bulk insert. In most cases, you will need to execute a continuous load process to ensure that SQL Server always has the latest data. In that case, you will need logic to handle the deletes and duplicates.
  • The above approach only deals with one API. Google Analytics provides a large amount of data in a variety of APIs, each of which follows different JSON structures. The developer will need to be familiar with all these and write custom code to accommodate them.
  • Google APIs are rate-limited. Hence the logic has to work around the thresholding and extract data without triggering the rate violation exceptions.

An alternative to spending all this effort in building a custom script is to use a Cloud-based ETL tool like Hevo which can handle such load jobs as exporting data from Google Analytics to SQL server in a matter of few clicks. 

Method 2: Export Google Analytics data to SQL Server Using Hevo Data

Hevo Logo
Image Source

Hevo, a No-code Data Pipeline, provides an easy-to-use, completely managed cloud-based service that can execute the load operation from Google Analytics  (among 30+ Sources) to SQL server in a few clicks. Hevo also helps you transform your data and automatically map schemas in databases. The data migration from Google Analytics to SQL server can be carried out in 2 simple steps:

Step 1: Connect Hevo to Google Analytics to set it up as your source by filling in the Pipeline Name, Account Name, Property Name, View Name, Metrics, Dimensions, and the Historical Import Duration.

GA Source Config
Image Source

Step 2: Configure SQL Server as a destination and start moving data instantly by entering the Destination Name, Database Host, Database Port, Database User, Database Password, Database Name, Database Schema.

SQL Server Destination Config
Image Source

You have successfully Exported Google Analytics data to SQL Server using Hevo Data.

Key Highlights of Hevo

  • Easy-to-use Platform: Hevo has a straightforward and intuitive UI to configure the jobs.
  • Data Transformation: Hevo supports complex transformations for the data on the move.
  • Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
  • Connectors: Hevo supports 100+ integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.  
  • Real-time Data Transfer: Support for real-time synchronization across a variety of sources and destinations. 
  • Fully Managed: Completely managed service which spares the users of all infrastructure and maintenance activities. 
  • Automatic Schema Mapping: Hevo can automatically detect your source’s schema type and match it with the schema type of your destination. 

Conclusion

This blog talks about the two methods you can deploy to connect Google Analytics to SQL Server in a seamless fashion. It also gives a brief introduction to Google Analytics and SQL Server that includes its salient features and benefits. Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day!

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 100+ sources including Free sources like Google Analytics for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources. You can use it to transfer data from multiple data sources into your Data Warehouse, Database, or a destination of your choice such as an SQL server. It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up  for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

How do you load data from Google Analytics to SQL server? Share your thoughts in the comments section below.

No-code Data Pipeline For SQL Server