Powerful ETL
I've been on the beta group and v1.1 should be available by the time you read this [1] - James Standen and the team at Analytics Canvas know their stuff! Basically, Analytics Canvas powerful visual query interface allows you, among other things, to:- intuitively build complex queries and apply advanced ETL rules on the data (transformation, filtering, join, sort, segment, summarize, etc.)
- extract more data than the 10k per call imposed by GA API
- combine multiple GA profiles, even from different accounts
- easily extract more than the 10 metrics constraint imposed by GA API
- work with sampled or full data sets and visualize the results
- combine with other data sources, be it Excel, MS SQL, Oracle, mySQL, etc.
- export to flat files, Excel or databases
Why is Analytics Canvas important?
The future of web analytics is dim... if we want to bring business value, we need to be business focused, not merely looking at website visits. That's why I think "web analytics" as we know it today will give way to "business analysis" and "business intelligence" - and to get there we need to achieve higher level of online analytics maturity - and that's where tools like Analytics Canvas become essential.A real-world scenario
I want to merge online behavior data with back-office data. I want to look at visits grouped by date and hour, days since last visit (recency), visit count (frequency), pageviews, time on site for each visit, as well as 5 different goals for each of the authenticated users to my site [2]. This data will then be merged with a SQL data source containing demographics info and monetary values of past customers (without any PII, so we comply with GA TOS - see my previous post on this topic). The end result is a nice RFM analysis model I can use to analyze customer behavior and make much better recommendations that speaks to business stakeholders.
The challenge
- the volume of data can be substantial and break the 10k rows limit per GA call,
- I have more than 10 metrics,
- I want to group by date/time and get only the rows where there is a known user,
- data needs to be joined with back-office data stored in a SQL database,
- and of course, I want to export the result to an Excel file, or back into SQL, for further analysis.
The solution
 |
| The final canvas |
 |
| Blocks library to build complex ETL |
 |
| Simple yet powerful query builder |
 |
| The Join block |
 |
| The Filter block |
 |
| Sample output |
My take
This simple example took only a few minutes to build. Doing adjustments is a snap and doesn't require a single line of programming or tedious testing rounds. The Analytics Canvas approach is different from Excel plugins like Tatvic, Shufflepoint or Next Analytics (which I'm using for a super-cool dashboard to be presented here in the coming days as part of the "The math behind web analytics" series). Those coming from a database or even stats background will probably be more comfortable with Analytics Canvas than, say, purely marketing people. Analytics Canvas definitely offer very strong capabilities unprecedented in any other GA API tools I've seen.If you want to extract massive amount of GA data and merge with back-office sources, Analytics Canvas is definitely worth a look!
Analytics Canvas will be exhibiting at eMetrics Toronto, April 25-29 - I will be there too!
---
[1] other than being on the beta list, I have received no compensation for this article.
[2] as per GA TOS, you are not allowed to store any PII info [wikipedia] in GA - but you can use a unique user id (not an email!) or in a similar scenario, you could use the e-commerce transaction id to reconcile with back-office data as long as it's not merged with any PII.