Most Popular Pages in Sitecore (Again)

This post shows some code snippets for returning the most popular items (pages) in Sitecore based on the data gathered in the DMS. The code base is taken from a different post that shows how to gather the most viewed pages in Sitecore. The difference between most viewed pages and most popular pages is significant because a list of most viewed pages doesn’t really give you a good comparison since some pages may have just been published compared to pages that have been around for years and collected a lot of visits.

In order to determine the most popular pages you need some sort of time frame to level the playing field. For example, you could determine the most popular pages in the past month which is really just a list of Sitecore items with the most page views in the last month. This will ensure that the most popular pages are a good representation of what visitors are currently looking at based on time sensitive data.

The first example returns a number of popular posts (parameter 1) based on a timespan in days (parameter 2).

private static IEnumerable<Item> GetMostPopularPages(int numberOfItems, int timespan)
{
    string query =
        string.Format(
            "SELECT TOP {0} ItemId, count(*) as cnt FROM Pages WHERE DateTime > DATEADD(DAY, -{1}, GETDATE()) GROUP BY ItemId ORDER BY cnt DESC",
            numberOfItems, timespan);

    IEnumerable<Guid> itemIDs = DataAdapterManager.Sql.ReadMany(query,
        reader => new
        {
            ItemId = DataAdapterManager.Sql.GetGuid(0, reader),
        },
        new object[0]).Select(a => a.ItemId);

    return itemIDs.Select(id => Sitecore.Context.Database.GetItem(new ID(id))).Where(item => item != null).ToList();
}

And the function can be called like this to return the top 5 most popular pages from the past 30 days.

var popularPages = GetMostPopularPages(5,30);

The first example works great but does not take multi-site solutions into account since it will return the most popular pages across all sites. The second example returns a number of popular posts (parameter 1) based on a timespan in days (parameter 2) and only for the specified website name (parameter 3).

private static IEnumerable<Item> GetMostPopularPagesPerSite(int numberOfItems, int timespan, string siteName)
{
    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);

    IEnumerable<Guid> itemIDs = DataAdapterManager.Sql.ReadMany(query,
        reader => new
        {
            ItemId = DataAdapterManager.Sql.GetGuid(0, reader),
        },
        new object[0]).Select(a => a.ItemId);

    return itemIDs.Select(id => Sitecore.Context.Database.GetItem(new ID(id))).Where(item => item != null).ToList();
}

And the function can be called like this to return the top 10 most popular pages from the past 90 days for the current context site.

var popularPages = GetMostPopularPagesPerSite(10, 90, Sitecore.Context.Site.Name);

I don’t know of any way to get this sort of data without using custom SQL queries so let me know if you have any other ideas or are using similar code for different purposes.

Advertisements
Posted in Sitecore
6 comments on “Most Popular Pages in Sitecore (Again)
  1. […] Most Popular Pages in Sitecore (Again) – Shows how to get a list of the most viewed pages in Sitecore within a certain time frame based on Analytics data. […]

  2. […] Most Popular Pages in Sitecore (Again) – Shows how to get a list of the most viewed pages in Sitecore within a certain time frame based on Analytics data. […]

  3. shailesh says:

    Thank for your code . But i am using your code with master database and getting following “Sitecore Could not find configuration node dataAdapterManager”

  4. Ganesh R says:

    Hi

    I’m getting the following error,

    Message :
    Invalid object name ‘Pages’.

    Inner exception :
    Error executing SQL command: SELECT TOP 10 ItemId, count(*) as cnt, MultiSite FROM Pages INNER JOIN Visits ON Pages.VisitId = Visits.VisitId WHERE DateTime > DATEADD(DAY, -90, GETDATE()) AND MultiSite = ‘MyStarSanofi’ GROUP BY ItemId, MultiSite ORDER BY cnt DESC

    Thanks

  5. Ganesh says:

    Hi

    I’m getting the following error,

    Message :
    Invalid object name ‘Pages’.

    Inner exception :
    Error executing SQL command: SELECT TOP 10 ItemId, count(*) as cnt, MultiSite FROM Pages INNER JOIN Visits ON Pages.VisitId = Visits.VisitId WHERE DateTime > DATEADD(DAY, -90, GETDATE()) AND MultiSite = ‘TestSite’ GROUP BY ItemId, MultiSite ORDER BY cnt DESC

    Please let me knw the work around .

    Thanks

Leave a Reply to Ganesh R Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s