Business Intelligence Developer (MCSE)

Real time click stream analysis with Googles Analytics

For the majority outside marketing/digital, click stream analytics may be foreign or irrelevant. In my opinion, it’s one of those cool things that has real commercial value but at the same time is underutilised and not well understood. In a nutshell, click stream analytics is the process of collecting, analysing and reporting aggregate data about which pages a website visitor visits, for how long, where the viewer came from, the referring sites, the device of the viewer, the operating system, etc, etc. It really has something for everyone.

How does it work? Well, search engines indexes just about everything, really fast (like instantly). But that’s not to say your site will show up on searches just because it exists, you first need to register the site as the owner. Once done, this will get the search engines to start indexing and returning results, immediately if you’ve done it right and have a bit of luck. (the challenge then is to outrank others for the same search criteria, type Sam Tran or Sam Tran business intelligence in Googles or Bing from within Australia..I’ll be in the top 10, same search in Yahoo, probably nothing-I didn’t register with Yahoo). You can also target the region(s) of your results, such as Australia as I have (I’ll rank higher in Australia, North Koreans as an audience in general are not exactly a priority for me at the moment 🙂 )

But to then to actually start using Google Analytics (GA) which is a website statistics service, you first need to subscribe to it. Upon registration, GA will give you some JavaScript codes to embed into your master page so as to allow them to track the requests for your pages. Once you do that, you’re all good to go (simplified but that’s the gist of it)

For the business, it gives you a lot of useful information. This is available without any coding and even with the basic freebie accounts, there’s a lot to digest (there are options to upgrade which gives you more). To keep this short, I’ll show you just some of the data that is collected for my site and the tools available to do the analysis. Everything is in real time.

This is one of the standard tools to write the query

This is the results (for time, you can go down to the hour):

This is another view/tool- has plenty of inbuilt graphs/dashboards-just showing one of the real time geographical views.

For the visualizers, there’s Datastudio. I could write an entire blog on this truly innovative and remarkable product but it is still buggy (still Beta) and Googles have acknowledged this so will wait until they fix it(the most promising bit don’t work as advertised yet). If you’re keen to explore, it still works and also with non GA data (but has to be on Googles platform like Big Query, YouTube etc)-I built this dashboard below in under three minutes the first time I used it, the source data is Google Analytics-I didn’t need to read one line of instruction on how, it’s that intuitive. Again, real time. People are already requesting this as an iFrame to embed into their apps and sites-imagine the time saved.

To get a feel of the dashboard above, visit this site. You’ll need a Googles account to view-that’s the bug, it should be publicly view-able without a Googles account as that’s the way I set it up to work (or editable if I as the author have granted you such permissions via your Gmail account). It’ll eventually work like this, the author sets the permission for their reports/pages and these are then available for the entire web-no html, coding or maintenance required.

For the report/apps lover who simply can’t get enough, there’s even phone Apps for it. Everything is in real time. Always up, always available and fully maintained by Googles. Speaking of Apps, if you go to the end, I’ve posted Microsoft’s offering for Azure-mega useful (think reboot the server!)-it’s not exactly Google Analytics but still showing it simply because it is cool-it sits on my iPhone.

Finally for the coders who likes rolling their own- API heaven. Data as they say is king and capturing your own and having the ability to blend and mould with your other sets is power.
Googles have pretty much all the major languages covered. The hardest bit is authentication (not very well documented as anyone who’s used it will tell you-especially if you’re not from a Java background) which is outside the scope here as there are several options depending on what you want to do. The good thing is you only need to do it once and can use the same tokens everywhere- embedded within websites with your Javascripts, C# in SSIS, etc. The actual coding is fairly straight forward and for C#, the library is available from Nuget (Nuget is like the CRAN libraries in R). Notice how the query (in brown) is identical to the first screen shot for the the metrics and dimensions.

What coding brings to the table is customisation, like better visualisation (Tableau, Highcharts, Telerik, etc), data blending with other data sources, aggregations, etc.

The key point is, you don’t need to be a coder, java ninja or big data miner to use this fantastic tool, it’s completely free and easy to use. I work with some very smart people, pretty sure they would love to explore our bank’s online visitors behavior and how it affects their BU.

There are great articles out there on how to do all this and there’s a lot more to it than this, I’ve only scratched the surface on what’s available and possible. But one very interesting developments I’ve read but not tried yet is Google Analytics now also works for your intranet.

Like most modern servers nowadays, Power BI has a fairly good set of web APIs that developers can leverage to extend functionalities of the product and further automate many processes. The developer’s section of the documentation provides an easy to follow set of instructions on how to get started and showcases some very useful feature … More Power BI REST API

Came across a few little extensions in Visual Studio Code marketplace recently that has and will further save me considerable time when I need to work with JSON. … More Serializing and Deserializing JSON

Whether you're a manager, developer or DBA, SQL Auditing is a powerful tool that can help you understand what is happening within your platform and its underlying data. Auditing on SQL Server allows you to monitor your server from two different aspects: at the server level and the database level. … More Auditing Database Servers with SQL Server-A Primer

In my last blog on partition tables, I talked about the benefits from an archiving and management perspective. In this one, I'll go over the query performance that can be gained. If partitioning is something new to you, it's worth thinking it as dividing large tables into smaller ones … More Partitioned for Performance

If you do any development beyond the most basic on the .Net Framework, understanding and resolving DLL dependencies is a necessity and a fact of life and without the right tools to guide you, this can be a very time consuming and frustrating exercise. … More Untangle and Understand DLL Dependencies with NDepend

It’s fairly common knowledge amongst database professionals partitioned tables can help considerably with performance in various situations but my guess is not many would have considered the enormous benefits when it comes to archiving, a necessity when it comes to massive tables. … More Horizontal Partitioning for Big Tables

Despite its reputation, I'm still a massive fan of linked servers. Microsoft developed it for a reason and it's ongoing presence on the platform since day one says it all. No one told you to pull back a gazillion rows on an inner join with a local table. So, when I saw it's now possible ... … More Linked Servers for Azure’s NoSQL Cosmos DB