Load Google Analytics Data into SAP BW - PART V: Query and Excel

Load Google Analytics Data into SAP BW is possbile

Download the Full Visual Studio Solution with the Source Code

In this post series, I show you the process of building a prototype for a Google Analytics Extractor to SAP Business Warehouse. You can replicate the source code by reading the posts. If you want to save time, you can also download the ready-to-use Visual Studio solution file with the source code. The posts explain my code in all the needed details.

Overview

In the previous post, I have initialized our Analytics Client with OAuth. As I promised, we are over the hard part of the authentication. This is where things get interesting. In this post I will be implementing the remaining 2 steps in our workflow. As a result, we will be able to Execute a Query and Save it to a CSV file. By the end of this post, we will have a working prototype of our Google Analytics Extractor that can generate a file interface for uploading it to BW.

# Workflow Step Method Signature
1 Initialize Analytics Client AnalyticsService InitializeAnalyticsClient()
2 Execute Query GaData ExecuteQuery(AnalyticsService analyticsclient)
3 Save Result to CSV void SaveResultToCSV(GaData data)

Let's start our work with the implementation of the ExecuteQuery method!

Code Snippet: Execute Query

As you can see the solution below, the implementation is very simple. We have to prepare a Request first. Creating a request is simple if you followed my post about the Google Analytics Query Explorer Here we need to fill the required parameters for the query: the ids, start-date, end-date and the metrics.

If you want to group your data by a dimension, like a date, you need to add the list of dimensions to the Dimensions property of the request. Once the request is ready, you can call its Execute method. This method will go to Google Analytics and send our query request. As a result, the Google Analytics Client will parse the result into a GaData type.

And this is our method was about. We prepare and send our request and we have our data in memory. The next step is about to convert this data from memory to a CSV file.

Code Snippet: Save Result to CSV

Creating a CSV file is also very easy. As you can see I have created 3 helper methods in order to read and maintain our code better.

You can read its workflow:

  • StringBuilder: First, it creates a string. This will store the data in CSV format with the separator ";".
  • AppendHeaderRow: After that, it creates the first line as a header row. It's possible because the data has a property, called ColumnHeaders. This is a list of headers and all of the headers has a Name property.
  • AppendRows: It appends all of the rows from the in memory data. The data parameter has a Rows property that contains the list of the rows and each row is a list of data corresponding to the columns of the request.
  • CreateFile: And finally it saves the generated data to a file. Once the string is ready, it can be saved to a file. I have saved it to the "analytics.csv" file. If the file already exists, I delete it and recreate it from scratch. The file will be saved into the same folder as the application file of this Console application. If you run this application in Debug mode, you will find the file here: "PATH_TO_YOUR_SOLUTION\GoogleAnalyticsExtractor\GoogleAnalyticsExtractor\bin\Debug"

Summary

As you can see, the logic of the Google Analytics client is simple. We completed our prototype, you can try it and play with different queries and parameters. Until now, I discussed how to create such a client, but I didn't answer the main questions:

How can I automate it ?

Why console application with a CSV file instead of writing ABAP with direct download?

How does Google Analytics Dashboard look like in SAP BW ?

I have more questions, but now it's your turn. Try out and test the application. Add more questions here below the post to the comments. Or answer this: Why and how do YOU want to use this extractor ?

If you had trouble with C# and with this application, I have a good news for you. I will change and develop this code further and I am working on a ready to use extractor that I will upload to my next post. Stay tuned!

blog comments powered by Disqus