Automation — Analytics Canvas

By: Analytics Canvas  09-12-2011

ETL stands for “Extract, Transform and Load”. ETL is a key process in the business intelligence and data warehousing arena- it’s all about automating data movement and building repeatable, robust processes that take raw data, clean it, fix it, enrich it, and then load it into a location where it can be easily queried and analyzed.

The opposite of ETL is referred to as “CPALAICES” which stands for “Cut, paste and link a lot in crazy excel spreadsheets”. OK. I made that up. But it’s what happens.

Another process is “CPLICADERIE” which is “Cut, paste, load into crazy Access Database, export and reload into Excel”. Yeah, I also made that up. But tell me you’ve never seen it done.

Short term sacrifice for long term win

What is hard about automating data tasks with data from Google Analytics is that at first glance, it always seems easier to do in Excel, maybe you’ve even got one of the many very useful excell addins that use the Google Analytics API. And often, it is pretty quick… but think about it- for many tasks you don’t do them just once, you do them every month, or every week- so while you saved time at first, week after week you’re doing the same “faster” manual steps. Step back and it’s not faster at all. In fact, as they add up, you are spending more time every month pulling data out of Google Analytics and messing around with it in spreadsheets.

On the other hand, ETL jobs are designed to be run again and again- scheduled in fact, regularly. They might take more time to build at first, because they have to handle lots of cases (no lovely “I’ll just edit the data in Excel” fixes are available). But once built the effort to run each period- ZERO.

But even if automating that weekly task takes three times as long as the manual steps you do every week- well, you can do the math, by week four, suddenly, you’re ahead. Every week after that? Pure gravy.

Stop building new problems with each new manual analysis

When you automate, you’ll be amazed at how much more you can do- drop that crazy spreadsheet, take the time to do it right.

And if you are in an agency, with multiple customers, then that automation goes right to the bottom line. You can generate reports and results for more customers for less. Doesn’t take a financial wizard to know that adding revenue without adding cost makes happiness sprout about.

Other products and services from Analytics Canvas


Google Analytics Premium — Analytics Canvas

The service guarantees in the form of SLAs, the more formalised arrangements with certified partners for support, attribution analytics, but perhaps most importantly, more data and less sampling. Google Analytics, for a very long time, as been a remarkably powerful tool, particularly for its price. Google Analytics premium offers a number of key advantages.


Analytics Canvas Tool — Analytics Canvas

In a more sophisticated scenario, you can cross reference each page to a given member of a product hierarchy, so that pages that are overviews, or dealing with an entire product group, are accurately mapped. The higher end editions will be capable of pulling hundreds of thousands of rows of data from multiple accounts and profiles, as well as data from multiple databases and files.


Keyword Analysis — Analytics Canvas

Because you can pull huge amounts of data from Google Analytics easily, you can include ALL your keywords, and calculate segmentation across the enter data set, with a day by day breakdown, if you like. There are lots of keyword tools available to help you pick which keywords to target, and who is searching for what, but what is actually happening on your site- which keywords are converting.


Google Analytics API — Analytics Canvas

If you have significant search from, and if that traffic is different than your existing search traffic, all your metrics could have a step change for the search category. The next time you see a step change in your data, it doesn’t hurt to consider the possibility that your traffic hasn’t changed, but the way that Google Analytics is reporting it has.


Analytics Canvas Tool — Analytics Canvas - analytics canvas tool

In Analytics Canvas you go to the “Add data” toolbar menu, then select Google Analytics, and pick which of the profiles you want to do the analysis on. For some reason, when google sends image search visits, they get marked as referrals, not organic- and the referral path is set to “/imgres”. What we want to do is organize this data into more meaninful groups by defining the rules for that segmentation block.