Sorting Sitecore Items by Popularity Using Sitecore Analytics

using System;
using Sitecore.Analytics;
using Sitecore.Diagnostics;
using Sitecore.Pipelines;
using Website.Logic.Common.Extensions;
using Sitecore.Data;
using Newtonsoft.Json;

namespace Website.Logic.Analytics.Views
{
    public class ViewPageEventProcessor
    {
        public void Process(PipelineArgs args)
        {
            Assert.ArgumentNotNull(args, "args");
            Assert.IsNotNull(Tracker.Current, "Tracker.Current is not initialized");
            Assert.IsNotNull(Tracker.Current.Session, "Tracker.Current.Session is not initialized");
            Assert.IsNotNull(Tracker.Current.Session.Interaction, "Tracker.Current.Session.Interaction is not initialized");
            if (Tracker.Current.Session.Interaction == null)
                return;
            var currentPage = Tracker.Current.CurrentPage;
            if (currentPage == null || currentPage.IsCancelled)
                return;

            var item = Sitecore.Context.Item;
            if (item == null)
                return;

            var pageEventData = currentPage.Register(new Sitecore.Analytics.Data.PageEventData("View")
            {
                ItemId = item.ID.ToGuid(),
                Text = string.Format("Template ID: {0}", item.TemplateID),
                DataKey = item.TemplateID.ToString(),
                Data = item.TemplateID.ToString()
            });
        }
    }
}

using Sitecore.Analytics.Aggregation.Data.Model;

namespace Website.Logic.Analytics.Views
{
    public class ViewFact : Fact<ViewKey, ViewValue>
    {
        public ViewFact() : base(ViewValue.Reduce)
        {
            
        }

        public override string TableName
        {
            get { return "Views"; }
        }
    }
}

using System;
using Sitecore.Analytics.Aggregation.Data.Model;

namespace Website.Logic.Analytics.Views
{
    public class ViewKey : DictionaryKey
    {
        public Guid ItemId { get; set; }
        public DateTime Date { get; set; }
    }
}

using Sitecore.Analytics.Aggregation.Data.Model;
using System;

namespace Website.Logic.Analytics.Views
{
    public class ViewValue : DictionaryValue
    {
        internal static ViewValue Reduce(ViewValue left, ViewValue right)
        {
            var viewValue = new ViewValue();

            viewValue.Count = left.Count + right.Count;
            viewValue.TemplateId = left.TemplateId
            
            return viewValue;
        }

        public long Count { get; set; }
        public Guid TemplateId { get; set; }
    }
}

using System;
using System.Linq;
using Sitecore.Analytics.Aggregation.Pipeline;
using Sitecore.Diagnostics;

namespace Website.Logic.Analytics.Views
{
    public class ViewAggregationProcessor : AggregationProcessor
    {
        protected override void OnProcess(AggregationPipelineArgs args)
        {
            Assert.ArgumentNotNull(args, "args");

            if (args.Context.Visit.Pages == null)
                return;

            foreach (var page in args.Context.Visit.Pages)
            {
                if (page.PageEvents != null)
                {
                    var fact = args.GetFact<ViewFact>();
                    foreach (var pageEvent in page.PageEvents.Where(p => p.Name == "View"))
                    {
                        var viewKey = new ViewKey {
                            Date = pageEvent.DateTime.Date,
                            ItemId = pageEvent.ItemId
                        };

                        var viewValue = new ViewValue
                        {
                            Count = 1,
                            TemplateId = new Guid(pageEvent.Data)
                        };

                        fact.Emit(viewKey, viewValue);
                    }
                }
            }
        }
    }
}

public static List<Item> GetMostViewed(int count, int daysToInclude, List<Guid> templateIDs, List<Guid> mostViewedExcludeGuids)
{
    var sqlServerDataApi = new SqlServerDataApi(Sitecore.Configuration.Settings.GetConnectionString("reporting"));

    // Base Query
    var query = @"SELECT TOP " + count.ToString() + @"
                    A.ItemId,
                    A.DailyCount,
                    A.LastViewDate
                FROM
                (
                SELECT	ItemId, 
                        SUM([Count]) AS DailyCount,
                    Max(Date) AS LastViewDate
                FROM    Fact_Views 
                WHERE   ViewDate >= DATEADD(DAY, -" + daysToInclude.ToString() + @", GETDATE()) ";

    // Restrict To Search Templates
    if (templateIDs != null && templateIDs.Any())
    {
        query += @"AND     TemplateId IN (" + string.Join(",", templateIDs.Select(x => string.Format("'{0}'", x.ToString()))) + @") ";
    }

    // Exclude Specific ItemIDs
    if (mostViewedExcludeGuids != null && mostViewedExcludeGuids.Any())
    {
        query += @"AND     ItemId NOT IN (" + string.Join(",", mostViewedExcludeGuids.Select(x => string.Format("'{0}'", x.ToString()))) + @") ";
    }

    // Group And Sort
    query += @"GROUP BY ItemId
                ) AS A
                ORDER BY A.DailyCount DESC,
                    A.LastViewDate DESC";

    var result = new List<Item>();

    var reader = sqlServerDataApi.CreateReader(query);
    while (reader.Read())
    {
        var itemID = reader.InnerReader.GetGuid(0);
        var scItem = Sitecore.Context.Database.GetItem(new ID(itemID));
        if (scItem != null)
        {
            result.Add(scItem);
        }
    }
    return result;

}