Most popular sitecore items for each language.

Question

I am currently working with Sitecore 8 Update 2

I am looking for a way to get the most popular elements from sitecore analytics for each language.

While I can get the most popular elements, this is part of the language, which turns out to be a bit more complicated.

This post explains very well how to get popular items: https://sitecorecontextitem.wordpress.com/2014/07/08/most-popular-pages-in-sitecore-again/

However, my current project is a site with 4 languages, and not every element has a version in all languages. (This is as intended!) So I would like to get a sql statement that will retrieve it at a time.

If you do not want to read the article here, 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 bit, but the functionality has remained the same. But if someone wants to test this in sitecore 8, the request will look like this:

 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 cannot do this out of the box with a single request. I do not see the page-language link in the report database.

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