As mentioned in Part I, one of our clients wanted to go beyond general web site statistics and traffic. In this article we will talk about what we did for advanced reporting statistics and how we did it (with code samples). Image 1
A windows service downloads data from Google Analytics and inserts it into a RavenDB NoSQL database. This allowed us to create a wide variety of advanced reports. A Windows service was configured to run every night to download daily activity log from Google Analytics site using their download API.
Before you can download the data you need to:
- Register your application using Google APIs console .
- After you’ve registered, go to the API Access tab and copy the “Client ID” and “Client secret” values, which you’ll need later. Click here to get more info on how to get Client ID and “Client secret” key
- Following code snippet shows how to authenticate Google API and call Get method to get data from Google Analytics. 4) Once you have the data as JSON objects next step is to insert it into database. We chose RavenDB to store JSON object for further data processing
RavenDB is a relatively new, open source document database for .NET. As we are storing JSON objects in Google Analytics it made sense to keep data in JSON format and save it in document database. It made retrieval of information fast and easy (more detail below). We had a choice in another popular document database – MongoDB – but we chose RavenDB over MongoDB because:
- RavenDB is built in C#
- RavenDB supports batch transaction
- Optimistic concurrency
- Supports full-text query
- Support static and ad-hoc indexes
- Supports triggers
- Rest API
Setting up RavenDB: The RavenDB server instance can be instantiated in several ways:
- Running the Raven.Server.exe console application (located under /Server/ in the build package).
- Running RavenDB as a service.
- Integrating RavenDB with IIS on your Windows based server.
- Embedding the server in your application.
A great source of information on setting up RavenDB database is at:
Retrieving data from RavenDB:
- Connection to RavenDB: There are 4 modes to set up RavenDB data source. We configured the RavenDB in server mode. Following code snippet shows how to connect to RavenDB in server mode.
- Querying database to retrieve data: The built-in Linq provider implements the IQueryable interface. This makes it very easy to retrieve data by writing Linq queries. Here is an example of getting all the Companies From RavenDB database using Linq (visit RavenDB knowledge base for more details)
- Creating Custom reports: Criteria for filtering is based on user input and data from the database is accessed and parsed into c# object using Linq queries. Data calculations and graph was generated at runtime.
- Creating graphs: We used Rickshaw toolkit for creating interactive graphs.
For the client, we were able to use this data to create Timeline reports by pulling past usage data and comparing it to current data over a variety of time periods for practically every action a user can perform on the site.
Thus, by using JSON and Knockout for binding and Rickshaw toolkit for graphing, we were able to create scalable, customizable and interactive report. RavenDB played a key role by providing us fast and reliable DB source.
Let me know if you have questions or comments on our experience!
- Google APIs console
Picture cortesy of Gosa Postoronca