To get the answers we want, sometimes it’s desirable to rolled up or aggregate a overly detailed dimension. This can apply to any data source including Google Analytics, PIWIK, Facebook, Instagram, Twitter, YouTube, SQL Queries and more.

To demonstrate the value of rolling up detailed dimensions, a good example would be web site referers. Referers are the sites that send traffic to your site. Both Google Analytics and PIWIK refer to referring sites as “referers” and so do the social media engines.

A practical example occurs on most peoples’ web sites, because Google sends traffic from a large number of variations of Google.com.

piwik-referer-events-uncombined

If we want to know just the total value of referrals from Google, we have several steps of analysis to perform.

First, there can be multiple entries for each variation of Google referer.

We would want those combined and their numeric values to be summed. For those who are savvy in SQL, this is similar to a Count Distinct expression.

After the combine is performed, you can see that there were 850 instances of different kinds of Google referring traffic.

piwik-referers-as-logged

Although this is easier to read, it’s not yet what we want. We want to know the total for each type of referer. Google is an obvious example, but there are going to be redundant referals coming from other popular sources too, such as Yandex, Yahoo, LinkedIn, Bing, Facebook, and others.

The next level of analysis is to combine all referer entries that are alike. NEXT Analytics FREE and PRO software lets you specify the rules to apply when combining your referer traffic in a spreadsheet.  Matching entries will be combined, with their numeric values summed.

piwik-referer-fixes

The script is easy. Look at Row 9 and you can see it is simply saying “if the referer_url contains “google” then re-write it as just “Google”.  This instruction converts, for example, google.com, google.co.in, google.co.uk, and so on to being combined all as one, in this case “Google”.

This produces a much more concise report!

piwik-referers-after-fixes-applied

If you were to do this in SQL, it would require a sophisticated Count Distinct expression. I’m not even sure you can do it!

You would be reliant on the SQL programmer to create this expression. If it changed in the future, you’d have to wait till for them to make the change.

In contrast, having your analytics in a spreadsheet means that almost any Excel user can make the change and see the result within a few seconds.

It is also worthwhile to note that this technique applies to any dimension.  It would be useful to prepare a similar analysis for:

  • goals with similar names and purpose
  • cities and countries, to redefine regions into perhaps sales territories
  • landing pages with similar names and purpose
  • operating systems
  • screen sizes

All of this is easily achievable using NEXT Analytics’ FREE or PRO version of software.

It works equally well for Google Analytics or PIWIK, as well as all the social media data sources including but not limited to:

  • Facebook Group Discussions
  • Facebook Posts
  • Facebook Insights
  • Instagram Posts, Accounts, Hashtags
  • Twitter users and Tweets (aka Statuses)
  • YouTube
  • Adwords
  • Bing
  • and others

Again, all this works with our Free version, as well as our PRO version.

This spreadsheet is available as part of the free download. It will be in a folder named dashboards\piwik-piwik-log-analysis.xlsx.