Clariba website

View Original

BI and Social Media - A Powerful Combination (Part 1: Google Analytics)

If you take a look at the latest Business Intelligence (BI) trends you will see a great deal of mentions revolving around the “Social Media” topic. There are a lot of interesting ideas out there and it seems to be certain that this will become even more prominent in the near future since it continues growing and it is becoming an intrinsic part of our society. This also means that the information related to Social Media becomes more valuable with each passing day, becoming the new “gold mine” for Business Intelligence consultants when handled correctly. No wonder there are a lot of us that want to start digging in!

In this blog post, I will share the research I recently made with the objective to determine the possible alternatives to extract and use information from Google Analytics to develop SAP BusinessObjects reports and dashboards. An important note is that I did not consider SAP BusinessObjects Data Integrator’s Unstructured Data and Text Analysis capabilities on purpose, in order to see what other options were out there.

Extracting data from Google Analytics

The first thing you must know is that in December 2011, Google released the Google Analytics Core Reporting API (in replacement of its old Data Export API) which allows us to extract data from its application. Although it is not rare to see the old API still being used, if you are starting a fresh development and have the opportunity to choose, then go with the new version of course. Considering these interfaces, I found four different strategies that stood above the rest.

Programmed Google Docs spreadsheet: It is a Google Docs spreadsheet available free of charge, which contains embedded Google Apps Script code to connect to the Google Analytics Data Export API. Once authenticated, it allows the user to define the dimensions, metrics and filters filling in the fields of the document with the desired values. Subsequently, this spreadsheet can be exported as a .CSV file and use it as a data source to be reported with several tools of SAP BusinessObjects such as Web Intelligence or Data Integrator, for example. Credits go to Mikael Thuneberg who developed this. 1

GA Data Grabber: It offers a 7-day trial version, the cost to purchase the Google  Analytics module is 299USD per year (at the present date). It consists of an Excel file containing Macros and Visual Basic code to run queries with any of the dimensions, metrics or filters available in the Google Analytics Core Reporting API. This is one of the best options I found so far, since it has a user interface that is very easy to use, good flexibility to select metrics, dimensions and filters, besides having a fairly comprehensive structure that facilitates the understanding of the type of information that can be obtained from the API. By having the data available in an Excel file there are more SAP BusinessObjects tools that can be involved in addition to Web Intelligence and Data Integrator, such as LiveOffice that allows direct integration with Xcelsius dashboards. 2

Reporting Utility of Google Analytics: This functionality is already included in the Google Analytics application. It is a relatively manual process, which requires the user to have some prior knowledge of the dimensions and metrics that can be obtained. However, custom reports can be created with specific information that can be exported to .CSV files so they can be incorporated into the reporting capabilities of SAP BusinessObjects.

Custom Application Development: If third party tools are not the desired option, then the best strategy to use is to develop a custom application in any common programming language, such as PHP, Ruby, Python, Java and JavaScript. The Google Analytics Core Reporting API lets you connect with these type of applications. In spite of representing the option that requires more effort to be implemented, it is also the one that permits to have a perfectly tailored, automatable and free solution. The data can be potentially stored in text files or custom databases from where any SAP BusinessObjects tools can draw information.

Official Documentation always does the trick

I did not want to get into too much technical detail because they are very susceptible to change through time, and it is always best practice to look at the official documentation published by Google. In this sense, I will leave you with a few links that will be very useful to deepen the technical knowledge necessary to develop an integrated solution with Google Analytics.

  • Google Analytics Core Reporting API Overview:

https://developers.google.com/analytics/devguides/reporting/core/v3/

  • Dimensions & Metrics Reference (to understand what information can be retrieved):

https://developers.google.com/analytics/devguides/reporting/core/dimsmets

  • Google Analytics Core Reporting & Data Export API Change Log:

https://developers.google.com/analytics/community/export_changelog

  • Google Analytics Core Reporting API Client Libraries:

https://developers.google.com/analytics/devguides/reporting/core/v3/gdataLibraries

  • Google APIs Console:

https://code.google.com/apis/console

Final Thought

As with all things that become “the latest trend” and  develop at a hectic pace, a lot of changes are expected to happen, meaning that sooner rather than later there will be more and better options available. So my final thought is to keep an inquisitive eye for all that is moving around Social Media and its most popular networks, because this is the future that is already happening.

If you have any questions or anything to add to help improve this post, please feel free to leave your comments.

References

1 For more info: http://www.automateanalytics.com/2010/04/google-analytics-data-to-google-docs.html

2 For more info: http://www.gadatagrabbertool.com/