Sorting Pages by most views

Question

I have a requirement in our 8.1 update 2 solution to provide a sorting mechanism for pages based on the number of views. This data lives in the analytics database and I'm looking for a way to retrieve the data without querying the database directly. Is there an API in place out of the box that I can use to get an item's view count?

I've verified that the data I need exists in the [Sitecore_Analytics].[dbo].[Fact_PageViews] table. From a best practices standpoint, what is the best way to retrieve this data and use it in our components?

Answer 1

Here is how you can do this:

1. Create reporting query item

Create somewhere an item of template /sitecore/templates/System/Analytics/ReportQuery

Example fields configuration:

Write down an item ID somewhere, we will need it later.

2. Create item based reporting query class

Example code:

Note: This Constants.ReportingQueries.ItemVisits is an item ID of your reporting query item, put this into constant or pull from configuration.

public class ExampleVisitsQuery : ItemBasedReportingQuery
{
    public ExampleVisitsQuery(ReportDataProviderBase reportProvider = null)
        : base(Constants.ReportingQueries.ItemVisits, reportProvider)
    {
    }

    public ID ItemId { get; set; }

    public long Visits { get; protected set; }

    public override void Execute()
    {
        var parameters = new Dictionary<string, object>
        {
            {"@ItemId", ItemId}
        };

        var dt = this.ExecuteQuery(parameters);

        if (dt != null && dt.Rows.Count > 0)
        {
            var result = dt.Rows[0]["Visits"];
            if (result != null && result != DBNull.Value)
                Visits = (long)dt.Rows[0]["Visits"];
        }
    }
}

3. Get your data

This is where we get the data.

var query = new ExampleVisitsQuery (this.ReportDataProvider)
{
    ItemId = id
};

query.Execute();

long itemViews = query.Visits;

Note: this.ReportDataProvider is an instance of ReportDataProviderBase type.

In this can be null

As a output you get number of views.

4. Sort your data

Once you get Visits for all items you can easily sort them.

Assuming you have got a list of items ids as an input here is what you can do:

if (entryIds.Any())
{
    var views = new Dictionary<ID, long>();

    foreach (var id in entryIds)
    {
        var query = new ExampleVisitsQuery(this.ReportDataProvider)
        {
            ItemId = id
        };

        query.Execute();

        var itemViews = query.Visits;

        if (itemViews > 0)
            views.Add(id, itemViews);
    }

    var ids = views.OrderByDescending(x => x.Value).Take(maxCount).Select(x => x.Key).ToArray();
}

source: WeBlog

Answer 2

I'm not sure if that's the best solution, but yes, you can get the information you're looking for using Fact_PageViews table.

You can use query like that:

SELECT TOP 10 ItemId, count(*) as c FROM Fact_PageViews GROUP BY ItemId ORDER BY c DESC

or with some more options:

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