0

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.

Improve this question
2
  • 2
    There is a table called Fact_PageViewsByLanguage - that might be a good place to start.
    – Richard Seal ♦
    Feb 6 '17 at 18:38
  • I do not see the "Fact_PageViewsByLanguage" table in the "reporting" database. Is it available for Sitecore 8.0 ?. I have another instance of 8.2 & that table is present in that DB
    – Qwerty
    Feb 7 '17 at 5:06
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.