0

I'm using Sitecore 9 update 1 and SXA 1.7, I want to sort Sitecore pages by popularity, I opened the Reporting database and found many tables like Fact_Visits, Fact_PageViewsMetrics and Fact_PageViewsByLanguage. I don't know which to use and what's the difference by Page count, visits and views.

2

You will want to use Fact_PageViews in the Reporting database, which should contain an Item Id, representing the page that someone has visited. Keeping in mind that this data will always be old and not real time, since data is collected by xDb and then aggregated into this data store.

https://sitecoreenthusiast.wordpress.com/2016/06/27/getting-sitecore-pages-based-on-page-views/

public Dictionary<ID, int> GetAllItemsFromAnalytics()
{
    Dictionary<ID, int> dictionary = new Dictionary<ID, int>();
     var getAllPageViewsQuery =
        @"SELECT ItemId, Sum(Views) FROM Fact_PageViews GROUP BY ItemId";

     var results = DataAdapterManager.Sql.ReadMany(getAllPageViewsQuery, reader =>
       new
       {
        ItemId = DataAdapterManager.Sql.GetGuid(0, reader),
        PageViews = DataAdapterManager.Sql.GetInt(1, reader)
       }, new object[0]).ToDictionary(i => ID.Parse(i.ItemId), i => i.PageViews);

    return results;
}

From the article, this code snippet will be the basis of what you need to pull. You need to use a Sql Query to pull the data from the Reporting database. Keeping in mind, not all environments will have direct access to the reporting database.

The article also takes the data returned a step further, but running that query in a custom computed field, so that all items will have a PageViews field that can then be used for sorting. I will share from experience, that if this is your intent, that you may experience issues with using a computed field. The reporting data is old, and so is the data in the computed field. Secondly, sorting on a field that is just a number, with no limitation on the traffic in a set timeframe, it'll lead to you always having the same content in the block sorted by PageViews. I'd suggest sorted by PageViews and posts created in a set time frame, that way new content is always being added to the block. Unless you want to always show the same content, but in that case, your best bet is to just use personalization to force the content to always be the same.

  • Thank you so much for your help, I just have another question, the data is collected by xDb and then aggregated, what is the time of aggregation ? like at the end of the day or something ? and is this configurable ? – Mohamed Mosa Jan 6 at 7:32

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.