Wednesday, January 16, 2013

Web Analytics with Google BigQuery



So I’m a business owner who has had a website for over a decade and has recently decided to do some analytics. A consultant tells me that I should start using Google’s analytics services, he says the word dashboard and I’m off in lala land thinking of my 1967 stingray’s dashboard.  After 20 minutes you realize his solution is perfect going forward, and I even somewhat understand the concept of a dashboard. He states
I will have enough data in a few weeks that should be enough to start some trending analysis.

Knowing nothing of the technology I start to wonder about the years my website has already been operational? When we had some issues in years past our technician explained to me about web logs and I am certain they can be used to analyze past data but how would I go about it? A call to the technician, and he says we can restore from backup 7 years worth of complete web logs by the end of the week. It turns out there are 75 billion lines within all of the logs.  Some students from a local university tried loading the data into a database and running some simply queries on it but it was too many lines. What now?

Google BigQuery Service allows you to run SQL-like queries against very large datasets, with potentially billions of rows. This can be your own data, or data that someone else has shared for you. BigQuery works best for interactive analysis of very large datasets, typically using a small number of very large, append-only tables. You can use BigQuery through a web UI called the BigQuery browser tool, the bq command-line tool.


I will have to have the queries generated by someone who understands but I would like to have them modeled after the results I will be getting from Google Analytics so they can be compared.  

Now I have 7 years worth of data analyzed and it is clearer than ever before where I should be leading my company.

References
Google BigQuery - https://developers.google.com/bigquery/docs/overview