Export Google Analytics Data into SQL Server

Exporting your Google Analytics data is helpful for keeping backups of your website statistics, and since you have the data stored in a DB you can manipulate it as you see fit. We will demonstrate a quick way on how you can export your visits, day, and unique visitors into the SQL Server without using any external libraries.

Although the application is written using a Console Project it can easily be migrated to a web app with a few code changes.

We would like to point out that this is a very simple application, and it was required for this website to automatically import Google Analytics data into the SQL Server.

Requirements

  • Visual Studio For Desktop (Express is fine, 2010, 2012, 2013)
  • SQL Server (Express is fine, we tested this with 2012)
  • A Google Analytics account

The first step is to go to your Google Analytics dashboard and go to the audience overview section.

Google Analytics Exporting To TSV

On the right hand side choose a date range, and then on the left click export as TSV. The file will be downloaded to your computer; rename it to the current date using a format you’re comfortable with. Then move the file to a location (such as C:\ drive) so you can easily reference it.

Create a new Console Application in Visual Studio, name it Google Analytics and then add the following namespaces:

using System.Data.SqlClient;

using System.Globalization;

using System.IO;

using System.Text.RegularExpressions;

In the main method add the following code:

string fileLocation = @"C:\30-03-2014.tsv";

if (File.Exists(fileLocation))
{

  string[] delimiter = { "\r", "\n", "\t" };
  string pattern = "[\",]";
  //if the file exists parse the file.
}
else
{
  Console.WriteLine("File does not exist");
}
  Console.ReadLine();

Specify the location of the file along with the file format you have chosen (for example, our file is 30-03-2014.tsv in the C:\ drive). The delimiter is the character we use to separate the values and TSV stands for Tab Separated Values, so each value (or cell) is separated by a Tab. (\t). We also separate it by a carriage return and new line. The pattern will be used for regex to remove commas and speech marks from large numbers.

using (StreamReader reader = new StreamReader(fileLocation))
{

  string content = null;
  int i = 0;

  string connection = @"Add Your Data Source Connection String Here..";
  using (SqlConnection conn = new SqlConnection(connection))
  {
    conn.Open();

    while ((content = reader.ReadLine()) != null)
    {

     if (!content.StartsWith("#") && !content.EndsWith("-") && !string.IsNullOrWhiteSpace(content))
     {

        if (!content.StartsWith("Day"))
        {

          string[] final = content.Split(delimiter, StringSplitOptions.None);

          #region database insert

          string command = "INSERT INTO GoogleAnalytics (Date, Visits, UniqueVisits) VALUES(@Date, @Visits, @UniqueVisits)";
          CultureInfo ci = new CultureInfo("en-US");
          SqlCommand cmd = new SqlCommand();
          cmd.Connection = conn;
          cmd.CommandType = System.Data.CommandType.Text;
          cmd.CommandText = command;

             DateTime date;


             if (!string.IsNullOrEmpty(final[0]))
             {


                  DateTime.TryParse(final[0], ci, DateTimeStyles.None, out date);
                  cmd.Parameters.Add("@Date", System.Data.SqlDbType.Date).Value = date;
                  string Visits = Regex.Replace(final[1], pattern, string.Empty);
                  string UniqueVisits = Regex.Replace(final[2], pattern, string.Empty);
                  cmd.Parameters.Add("@Visits", System.Data.SqlDbType.Int).Value = Visits;
                  cmd.Parameters.Add("@UniqueVisits", System.Data.SqlDbType.Int).Value = UniqueVisits;

                  try
                  {
                    cmd.ExecuteNonQuery();
                    i += 1;
                    Console.Write("\rWriting...Google Analytics Data: " + i);
                    }
                    catch (Exception ex)
                    {
                     Console.WriteLine(ex.Message);
                     break;

                    }

                  }
                                    #endregion
                                }

                            }

                        }
                    }

                }

Although it seems confusing, the steps can be simply broken down like this:

  • Using the StreamReader class read the file
  • Using the SqlConnection class open a connection to our db (make sure you specify connection)
  • Using the While Statement read each line skipping out the first view (the first views lines are comments and headers which are not necessary)
  • Split the data using the delimiters and store it in an array so each value (day, visits and visitors) will be stored in the final array
  • Insert the content into the database (make sure you have created the table)

The CultureInfo is required if you are outside the USA because the default dates for Google Analytics are in the format (MM/DD/YYYY), which will throw an invalid date exception if your computer’s date format is different. We must use culture info to parse the date and store it in the date variable. We also need to remove commas and speech marks from the visits and unique visitors columns.

The i+=1 is something we added to see how many rows were being added J.

Now you’re done! As you can see it is a very simple app, and you can now retrieve the data from the database and create your own charts!

Notes

The Google Analytics exporting tool only allows 500 rows per file; however if you do a monthly schedule you will only have 30/31 rows, unless you do a batch export/import (in this case each file will need to be done in turn).

The process is not fully automated, but if you wish to make it fully automated you would need to access your email, get the attachment and save it, and then run this app on a scheduled basis. We experimented with this but gave up because we would not do this regularly. However if you wish to make it automated have a look at this C# Mail library.

When creating the database table make sure the date column is a date column and the other two are int columns. ALL columns should allow null values.

The total summary which is given in the Analytics is not inserted into the database, however you can do this using the SUM(column_name) feature in SQL.