How to get popular pages by language and site

Question

I'm trying to get the most popular pages by language & site for Sitecore 8.0.

This one I could find, is for an older version and the table names have changed since then.

string query = string.Format(

"SELECT TOP {0} ItemId, count(*) as cnt, MultiSite

FROM Pages

INNER JOIN Visits ON Pages.VisitId = Visits.VisitId

WHERE DateTime > DATEADD(DAY, -{1}, GETDATE()) AND MultiSite = '{2}'

GROUP BY ItemId, MultiSite

ORDER BY cnt DESC",

numberOfItems, timespan, siteName);

Also in the 8.0 DB, the "Items" table does not have any mappings for Site/language. Could someone please help me with the right query.

Answer 1

I'd have a look at the Fact_Traffic table. It seems to contain all the data you're looking for (Site, Item, Language and Visits) and according to the documentation (which can be found here: https://doc.sitecore.net/sitecore_experience_platform/setting_up_and_maintaining/xdb/platform/reporting_database_reference)

The Fact_Traffic table contains statistical data about interactions. The Fact_Traffic table is optimized for inserts and date range queries.

It does seem like the Fact_PageViewsByLanguage has been added only since 8.1.