Loading Google Analytics Data into SAP BW
One of the most challenging parts of Data Warehousing is to find the right way of extracting external data whatever the source is. Recently we got a requirement about consuming Google Analytics data into SAP BW. The Purpose of this Blog is to shed light on extracting Google Analytics data into SAP BW and exploring two different methods how to solve that. Fortunately, Google Analytics provides a robust API that enables to tap into the data programmatically, meaning you can conventionally pull and package data in ways that not be easy to do on the web. Google Analytics provides statistics about for instance, visitors activities like time, location etc. Additionally, statistics about E-commerce like sales, revenue and transactions. The Idea of extracting Google Analytics data into SAP BW is gather all available business data into one source as a single point of truth beside CRM data for instance, so you can get a better monitoring over all your business activities form all existing channels.
Google Analytics is a free Web analytics service that provides statistics and basic analytical tools for search engine optimization (SEO) and marketing purposes. The service is available to anyone with a Google account.
Google Analytics features include:
- Data visualization tools including a dashboard, scorecards and motion charts, which display changes in data over time.
- Segmentation for analysis of subsets, such as conversions.
Furthermore, some security experts have raised concerns about privacy issues in Google Analytics. Through the Google Analytics Dashboard, users can collect information on people whose websites link to social networking sites such as Facebook and Twitter.
2 Get started with Google Analytics
2.1 Set up Google Analytics Account
To begin tracking a website, you have to
- Create a new google analytics account
- Set up a property in the account you have created either web tracking or mobile app tracking (Properties are where you send data and set up reporting views)
- Configure your account, property or view
- Add reporting view that filter out internal traffic from your own company. Allow other users to see reports and give them permission to make configuration changes.
- Set up Goals so that you can see conversion rates in your reports and measure how well your site or app fulfills your target objectives. Link your account to your AdWords account
2.2 Report and Analyze – API
In order to be able to retrieve google analytics data and integrate it with your own business data for deeper insights, you have to use the google analytics Reporting APIs to automate complex reporting tasks. The following steps explain how to access google analytics account, query the analytics APIs and get results.
Step 1: Enable the Analytics API
Each request to an API that is represented in the Google Developers Console must include a unique identifier. Unique identifiers enable the Developers Console to tie requests to specific projects in order to monitor traffic, enforce quotas, and handle billing. Google supports two mechanisms for creating unique identifiers: OAuth 2.0 client IDs is for applications that use the OAuth 2.0 protocol to call Google APIs, you can use an OAuth 2.0 client ID to generate an access token. The token contains a unique identifier; and API key which is a unique identifier that you generate using the Developers Console. It can be either a server key or a browser key. To use OAuth 2.0 in your application, you need an OAuth 2.0 client ID, which your application uses when requesting an OAuth 2.0 access token. To create an OAuth 2.0 client ID in the Google Developers Console, do the following:
First, create or select a project in the google developers console and enable the API.
Second, create a client ID under the OAuth heading to create your OAuth 2.0 credentials.
Third, add the newly created service account to Google Analytics account which includes email address to add user to the google analytics account that you want to access the API.
Authorizing requests with OAuth 2.0
All requests to the Analytics API must be authorized by an authenticated user.
The details of the authorization process, or “flow,” for OAuth 2.0 vary somewhat depending on what kind of application you’re writing. The following general process applies to all application types:
- 1. When you create your application, you register it using the Google Developers Console. Google then provides information you’ll need later, such as a client ID and a client secret.
- 2. Activate the Analytics API in the Google Developers Console. (If the API isn’t listed in the Developers Console, then skip this step.)
- 3. When your application needs access to user data, it asks Google for a particular scope of access.
- 4. Google displays a consent screen to the user, asking them to authorize your application to request some of their data.
- 5. If the user approves, then Google gives your application a short-lived access token.
- 6. Your application requests user data, attaching the access token to the request.
- 7. If Google determines that your request and the token are valid, it returns the requested data.
Install the google client library
To install the google analytics API Java Client, you must download a zip file containing all of the jars you need to extract and copy into your Java classpath. Second, add all of the JARs within the libs directory to your classpath. Third, Add the google-api-services-analytics-v3-[version].jar jar to your classpath.
3 Extraction via Generic Datasource based on FM
The idea of this solution is to create a generic data source based on function module in SAP BW. The function module communicates google analytics API and queries the data.
There are some components that have to been installed firstly. I refer to SAPLINK for this purpose.
- ABAP JSON Document Class
- Install OAUTH2 (Setting: Table ZOAUTH2_CONSUMER)
- Install SAP Cryptographic Library
- Import Google Certificate
- Download and install Nugg file and install via SAPLINK
- Activate Google API https://code.google.com/apis/console/b/0/
- Create a Client-ID für local application
- Enter log in Data and requests into table ZOAUTH2_CONSUMER and enter the appropriate Service under API_HOST otherwise it would not be possible to access the data.
Import Google Certificate
- Download Goggle Internet Authority via http://pki.google.com/
- Use T-Code to import the certification in the folder SSL client (Anonymo and SSL client SSL Client Standar
- For abap2GAPPS, the certificate muss be under folder Anonymo and for under folder standard for Report RSICFCLTST01
abap2gapps is an API prototype that gives you a very easy framework to communicate and operate with Google services that use new OAuth2 authentication.
Consider this Google Document folder, it shows 2 spreadsheets
- Use this https://sap.assembla.com/spaces/abap2gapps/wiki to install Nugg file via SAPLINK
- At first run for abap2GAPPS or any application that uses OAUTH2 API, Consumer name and UserID must be specified. Enter Consumer name in the table ZOAUTH2_CONSUMER with the appropriate OAUTH entries. UserID will be registered in the API via an ABAP Program at first run.
- The browser will be opened, accept and copy the code.
Access on google analytics API is made then via http client that undertakes the communication with the API.
4 Extraction via Java Program
The activated API can be communicated by means of Java program. This program is designed so that the characteristics and key figures can be dynamically changed in a configuration file. The call of the program is executed by means of a script on the (HP UX) Operation system level of the SAP System. The program generates a CSV file which contains the queried data (characteristics and key figures specified in the configuration file) and save it on the application server. That File can be easily then loaded into SAP BW. In the configuration file, there are some parameters that must be defined, Measures or metrics which are key figures in BW, Dimensions which are characteristics in BW, Filter on the key figures and the characteristics, start date the defines from what time the selection should begin and the data is extracted and the path where the CSV file should be saved (Application server or local station).
5 BW Data model
The structure of the data model is based on the EDW data modeling:
- Data Entry Layer ( DSO)- in this layer, all data which are supplied from the source (Google) are saved one to one without any manipulation and provided with a time stamp, so that later can still be traced.
- Data Harmonization layer (DSO) – in this layer the data for reporting purpose are prepared. Specifically, a split is to be performed for the original feature “source / Medium” on the two characteristics “source” and “medium”. Company code and controlling area are based on the extracted Google profile page (company’s profile). This data is based on the master data of derived profile.
- Data Reporting Layer (InfoCube) – the reporting layer represents the analysis of the available data. In this layer data will be transferred 1:1 from the data harmonization layer.
For further questions or if you’re interested in implementing the suggested solution, please contact me directly via LinkedIn.