Most popular items in sitecore per language.

Question

I'm currently working with Sitecore 8 Update 2

I'm looking for a way to get the most popular items from sitecore analytics per language.

So far i can already get the most popular items, it's the per language part that is proving to be a bit harder.

In this post it is explained very well how to get the popular items: https://sitecorecontextitem.wordpress.com/2014/07/08/most-popular-pages-in-sitecore-again/

However my current project is a website with 4 languages, and not every item has a version in every language. ( This is as intended! ) So i would like to get an sql statement that will retrieve this in one single go.

If you don't want to read the article here's the most important line:

string query = string.Format("SELECT TOP {0} ItemId, count(*) as cnt FROM Pages WHERE DateTime > DATEADD(DAY, -{1}, GETDATE()) GROUP BY ItemId ORDER BY cnt DESC", numberOfItems, timespan);

Note that in sitecore 8 the names have changed a little but the functionality is the same. But if someone would want to test this in sitecore 8 the query becomes as follows:

string query = string.Format("SELECT TOP {0} ItemId, count(*) as cnt FROM Fact_PageViews WHERE Date > DATEADD(DAY, -{1}, GETDATE()) GROUP BY ItemId ORDER BY cnt DESC", numberOfItems, timespan);

Answer 1

It looks like you can't do it out of the box by one query. I don't see relation page-language in reporting DB.

But you can create your own dimension by language and page, write your own aggregation and create your own report. You can read high-level description here.