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
Let's continue where I left my previous post: (
Load Google Analytics Data into SAP BW - Part I: The Data Flow)
I have already prepared a simple data flow with the Data Flow Wizard in SAP BW with an Excel source.
Any source that can provide tha same schema that my Excel table follows can be uploaded to SAP BW for further riporting and analytics.
In this post I want to explore the capabilities of our source system called Google Analytics. It's very important to get familiar with the source system in order to develop automated data extraction.
Google API Client Libraries
Google has a strong API that helps us to develop any query tools and represent Google data in other systems. Yes, not only Google Analytics is part of this API but other open Google services like Google+, Google Calendar etc. can be used for our scenarios. Google API provides access to his systems through web service calls, so you can choose any languages that you are confortable with to work.
Of course, we don't need to reinvent the wheel and there are already many implementations in the most popular programming languages for accessing Google Analytics data easier. We call these implementations Client Libraries and you can explore them here:
Google Analytics Client Libraries
In my solution for creating Google Analytics extractor for SAP BW, I have tried and used the C# Client Library. I have created a simple Console Application that connects to my websites Google Analytics data and queries the number of sessions and number of users for a selected time period by visit date and saves it as a CSV file.
Any language preference you chose and before implementing your extractor client, it's important to get familiar with the terms of Google Analytics API and I found the Query Explorer very helpful for that.
Google Analytics Api Query Explorer
This is a web testing tool of the Google Analytics API that you can access from this link:
Google Analytics API Query Explorer
It allows you to play with the API and you can query the web statistics of your website that is registered with Google Analytics. After playing with this tool, it was much easier to develop the extractor, so take your time and try this useful tool.
Playing Around with the Parameters
There are several parameters that we need to get familiar with in order to formulate a correct Google Analytics Query:
- ids: This is the ViewID of your Google Analytics Dashboard. For me it was the hardest parameter because I didn't know where to find it because Google uses several ID's related to your web assets. The id is linked to a combination of account-property-view. If you play with the Query Explorer, first you need to select a view and it shows your id so you don't need to search it. I found this feature very useful. You can access all of the statistics through this id.
- start-date: The start date of your query based on the day of the visits. It allows you to use a date or a dynamic parameter. I used '30daysAgo'.
- end-date: The end date of your query based on the day of the visits. It allows you to use a date or a dynamic parameter. I used 'yesterday'.
- metrics: The list of the key figures you want to query. You get value help in this parameter that shows the list of possible key figures. I have chosen 2 key figures: 'ga:users' and 'ga:sessions' because I am curious about the number of the visitors and their sessions.
- dimensions: You can use one or several characteristics to show the context of your key figures. This parameter is not obligatory, if you don't fill this, you will get the total of your chosen key figures for the selected visit period. I filled it with 'ga:date' because I want to see my visits by the visit date.
Google Analytics: Query Parameters and sample values
| Parameter |
Value |
Required ? |
| ids |
ViewID of your Google Analytics Dashboard |
Required |
| start-date |
30daysAgo |
Required |
| end-date |
yesterday |
Required |
| metrics |
ga:users , ga:sessions |
Required |
| dimensions |
ga:date |
Optional |
Run the Query and Enjoy the Result!
You can play with other parameters but this is enough for our requirement. After pushing the Run button you will get an HTML table with the results of your query about your website's visits. The Query Explorer demonstrates also how you need to formulate the API URL in order to get this result through an API client:
API Sample URL:'https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A86587428&start-date=30daysAgo&end-date=yesterday&metrics=ga%3Ausers%2Cga%3Asessions&dimensions=ga%3Adate'
Summary
As a result we got an API URL that solves the extraction logic of our datasource. If we can create a Rest Client that can Authenticate to Google Analytics and can call the API with the query URL that we prepared now then we can generate a CSV file that contains our visits and ready to upload to SAP BW. In the coming post I am going to implement our extractor, first with a Console Application using the Client Library in C#.