A challenging business intelligence issue with subtotals

[larryexcel]

Hi everyone,

• I am currently working on a dashboard made with Excel and PowerPivot. This dashboard is meant to centralize all the data I have about my website and apps. One of these databases is a website analytics database which consists of the number of pages views and the number of unique browsers for a specific country and a specific language (my website is available in several languages).

If the user of my dashboard selects one country (for example, France), a specific language (for example, english) and a specific data type (for example, pages views), he will get the number of pages views in France for the english version of the website.

Then, if the same user selects only one country (for example, France), he will get the number of pages views in France, whatever the language of the website is. Indeed, Excel sums up the number of pages views in France by language to get the total number of pages views in France. Everything is right for the moment.

• Here is the issue: if the user wants to get the number of unique browsers in France, it is tricky. Indeed, even if you live in France, you can visit the website in French and then visit it in English. Thus, if we are interested in the number of unique browsers for French and English, a user located in France who visited the website in both languages should be only counted once: there is a duplication between french and english languages.

That's why Excel can't calculate by itself the subtotals, and we need to export the right subtotals from our web analytics database.

My question is: how should I present my web analytics database ? My first idea was to present it like that:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Language[/TD]
[TD]Data type[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]english[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]french[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]english + french[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]english[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]french[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]english + french[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]UK + France[/TD]
[TD]english[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]UK + France[/TD]
[TD]french[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]UK + France[/TD]
[TD]english + french[/TD]
[TD]Unique browsers[/TD]
[TD]xxx[/TD]
[/TR]
</tbody>[/TABLE]

The lines in red refer to the subtotals I have to get from my website analytics database, and the "xxx" are numbers.

• The problem with this display is that on my dashboard, when the user will select a country in a slicer, the list of countries will be:

- France
- UK
- France + UK

and the list of languages:
- english
- french
- english + french

I think it is counter intuitive to have "France + UK" considered as a single country, and "english + french" as a single language. Indeed, the hierarchy of my countries is:

Continent (for example, Europe)
Subregion (for example, Western Europe)
Country (France, UK, France+UK)

Then if the user only selects "Western Europe", Excel will give him the number of unique browsers in France + unique browsers in the UK + unique browsers in France and the UK, which would be wrong.

How can I solve this issue ?

Best,

Larry.

 

[mole999]

if you are analysing visitors then I would suggest capturing the IP

 

[larryexcel]

if you are analysing visitors then I would suggest capturing the IP

Indeed, it would be a solution but I have other databases with other kind of data that can't be summed up. I'd only like to know how to deal with non-calculated subtotals in my databases ...

 

[RoryA]

If you have a field that uniquely identifies the visitor, you can perform a distinct count on it within the dataset matching your filters. There is no need to combine countries etc.

 

[scottsen]

What Rory said (though, I totally read "Browser" as like IE9... Roty is clearly a better reader ) Err, UniqueVisitors := DISTINCTCOUNT(DataTable[BrowserId])

Depending on what you want on the Grand Total cell, you may need to wrap that in a SUMX (choices are just unique vistors across everything... OR... something that adds up correctly based on counts above the grand total).

 

[greg kramer]

+1 on the browser

 

[larryexcel]

+1 on the browser

Hi,

Thanks for the answer. The issue is that if I get the database with the list of all the IPs, I'll have millions of rows every month and you can imagine that if I want to aggregate this data each month, Excel may be a bit slow doing the calculation at the end of the year ...

This is why I was thinking of extracting the subtotals from the web analytics database and inserting them into my dashboard database. Isn't it possible according to you ?

 

[scottsen]

Ya can certainly throw some GROUP BY clauses in your SQL query to aggregate the data...

 

[RoryA]

I can't help but wonder whether the number of occurrences of this sort of scenario is actually likely to be statistically significant anyway.