Google Analytics to Azure: 2 Best Ways to Replicate Your Data

“Torture the data, and it will confess to anything.”– Ronald Coase, the Nobel prize Laureate

Table of Contents

Well, a quote that would only be applicable in the field of data analytics, but very powerful, do you agree? It’s pretty relevant while extracting data from Google Analytics to Azure. Because it’s very tricky. The APIs don’t permit us to extract event-level data. 

Just imagine a free direct Google Analytics to Azure connector. Ahh, wishful thinking! But, it’s not that seamless in reality. In this blog, we’ll go over two ways to replicate data from Google Analytics to Azure. I will also discuss when you should go for each of these methods. 

Let’s get started!

How to Replicate Data from Google Analytics to Azure

The first method is to convert the data from Google Analytics to CSV and then to Azure.

Using CSV 

Step 1: Convert to CSV

To export a report in Google Analytics into CSV:

• Take a look at the report you want to export.
• Ensure that you select the date range and apply report parameters. Because, Analytics exports the report exactly as it is now appearing on your screen.
• Select Export (across from the report title).
• Choose CSV from the following export file types:
  • CSV
  • TSV
  • TSV for Excel
  • Excel (XLSX)
  • Google Sheets
  • PDF
• Your file will be automatically created and accessible in your computer’s download location.

Now, the next step is sharing the report.

Point to note: Google Analytics has put a cap of 400 scheduled emailed reports per user per view. 

To send the report:

• Open the report and select Share (across from the report title).
• The From field contains a listing of the email address you used as your login.
• Enter a list of email addresses separated by commas in the To field.
• Choose the attachment format and frequency, then provide a subject.
• Click ADVANCED OPTIONS and choose an Active for period if you chose a frequency other than Once in the previous step. 
• For instance, if you choose Everyday in the previous step and “1 month” for the Active for term, Google Analytics will send the email daily for that duration. 
• To enter the email body, use the text field and select Send.

Information provided in the email depends on the time zone you choose in the view settings. Google Analytics can’t guarantee a specific delivery time. But, the tool sends emails sometime after midnight in the chosen time zone.

What data is in the report also depends on the frequency that you choose. For instance, if you choose daily, the report will contain information for the previous day.

Now, let’s deep dive into the next step— converting CSV files into Azure.

Step 2: Import Data from CSV File into Azure

You can import data from a CSV file into an Azure SQL Database or an Azure SQL Managed Instance using the bcp command-line.

The required prerequisites for the step are,

• A database in Azure SQL Database 
• The command-line utility installed by bcp
• The sqlcmd command-line tool

You can download the bcp and sqlcmd utilities from the Microsoft sqlcmd Documentation.

Since bcp does not support UTF-8, you need to encode your data in ASCII or UTF-16. 

1. Make a destination table

In SQL Database, choose a table as the destination table. The data in each row of your data file must match the columns in the table. Open a command prompt, then enter the following command using sqlcmd.exe to create a table:

sqlcmd.exe -S <server name> -d <database name> -U <username> -P <password> -I -Q "
    CREATE TABLE DimDate2
    (
        DateId INT NOT NULL,
        CalendarQuarter TINYINT NOT NULL,
        FiscalQuarter TINYINT NOT NULL
    )
    ;
"

2. Create a source data file

You need to copy the following data into a new text file in Notepad. Then, save to your local temporary directory., C:\Temp\DimDate2.txt. This data is in ASCII format.

20150301,1,3
20150501,2,4
20151001,4,2
20150201,1,3
20151201,4,2
20150801,3,1
20150601,2,4
20151101,4,2
20150401,2,4
20150701,3,1
20150901,3,1
20150101,1,3

Open a command prompt and use the following command to export your own data from a SQL Server database. Substitute your own information for TableName, ServerName, DatabaseName, Username, and Password.

bcp <TableName> out C:\Temp\DimDate2_export.txt -S <ServerName> -d <DatabaseName> -U <Username> -P <Password> -q -c -t ","

3. Load the data

Open a command prompt and enter the following command to load the data, substituting the server name, database name, username, and password values with your own data.

bcp DimDate2 in C:\Temp\DimDate2.txt -S <ServerName> -d <DatabaseName> -U <Username> -P <password> -q -c -t ","

You can use this command to verify Google Analytics loaded the data correctly.

sqlcmd.exe -S <server name> -d <database name> -U <username> -P <password> -I -Q "SELECT * FROM DimDate2 ORDER BY 1;"

The results should look like this:

DateId	CalendarQuarter	FiscalQuarter
20150101	1	3
20150201	1	3
20150301	1	3
20150401	2	4
20150501	2	4
20150601	2	4
20150701	3	1
20150801	3	1
20150801	3	1
20151001	4	2
20151101	4	2
20151201	4	2

To migrate a SQL Server database, see SQL Server database migration. Remember that technology like Google Analytics is constantly changing. So, what worked yesterday might not work today.

CSV is a very useful way for transferring data from Google Analytics to Azure. Let’s see some use cases:

• When you need reports for one time: This is the best way when your business teams need Google Analytics reports on a one-time basis. Why waste your money for single time requirements?
• When you don’t require data transformation:  In situations when you don’t need to perform any complex analysis, data in your spreadsheets would suffice. Just organize and present it in an appropriate manner.
• When you have small files: It takes time to download and create SQL queries to submit several CSV files. If you need to create a 360-degree perspective of the business and combine spreadsheets with data from several departments around the company, it can be especially time-consuming. So, go for them only when you are dealing with small files.

We’ve only begun to scratch the surface of how you can use Google Analytics to obtain raw data. If you wish to combine data from many sources, things could become even more challenging.

So instead of building and maintaining your own solution…

Use A Fully Automated Data Pipeline Tool

Let’s get into the details of how a fully automated data pipeline helps you connect Google Analytics to Azure.

• Reproducible patterns: When you think of data processing from Google Analytics as a network of pipelines, you may reuse and repurpose certain pipes for other data flows because you can perceive them as examples of patterns in a larger design.
• Enables quick integrations of new data sources: It is simpler to plan for the ingestion of new data sources along with Google Analytics. It takes less time and money to integrate them when there is a common concept and set of tools for how data should flow through analytics systems.
• Increases the accuracy of the data: You can increase the quality of the reports from GA and decrease the possibility of pipeline breaches going unnoticed by viewing your data flows. Because, the pipelines are monitored.
• Ensures robust security: Through the use of repeating patterns and a common understanding of tools and architectures, companies ensure the security from end to end of the pipeline. 
• Allows scalability: You can scale your dataflows in GA progressively by considering them as pipelines. You can start early and start seeing results right away by starting with a small, controllable slice of data from a data source to a user.
• Provides agility and flexibility: The structure provided by pipelines allows you to adapt quickly to updates in the Google Analytics or the demands of your data users.

The benefits of using a data pipeline for Google Analytics Azure integration is fascinating, right? Indeed. It’s an upcoming destination for Hevo Data. In the next section, let me explain the benefits of Google Analytics to Azure integration.

What are the Benefits of Transferring Your Data from Google Analytics to Azure?

Here are a few questions your data analysts can answer by replicating data from Google Analytics to Azure:

• Which message would move a customer through the lifecycle?
• How can you tell which creatives have a good ROAS are worth investing more in?
• Which copy and creatives are most effective for your target market?
• How can you make your web page more conversion driven?
• How does your customer LTV change as a result of various targeting, creatives, or products?

That’s it about the benefits of replicating data from Google Analytics to Azure. Now, let’s wrap up the different ways of Google Analytics to Azure migration.

Key Takeaways

Replicating data from Google Analytics to Azure is not a cakewalk. One way of doing that is through converting to CSV and then to Azure. The second method is using an automated data pipeline. Your data analytics team needs to go for a data pipeline solution when you require the periodical use of replication of data from Google Analytics to Azure. 

The main benefit of using a data pipeline for Google Analytics to Azure is replicable patterns. Others are, trust in the accuracy of the data, agility and flexibility, and belief in the pipeline’s security. Consider your priorities and choose the option that fits your requirements.

You can enjoy a smooth ride with Hevo Data’s 150+ plug-and-play integrations (including 40+ free sources. Hevo Data is helping thousands of customers take data driven decisions through its no-code data pipeline solution. We are happy to announce that we have launched Azure Synapse as a destination. 

Visit our Website to Explore Hevo

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!

Share your experience of learning about data replication from Google Analytics to Azure! Let us know in the comments section below!