Skip to content

Instantly share code, notes, and snippets.

@keyan1603
Created September 20, 2023 18:25
Show Gist options
  • Save keyan1603/e32c861fdf5470d4f9923d00b793df8c to your computer and use it in GitHub Desktop.
Save keyan1603/e32c861fdf5470d4f9923d00b793df8c to your computer and use it in GitHub Desktop.
Get top visited pages in Sitecore per site with multi-site setup
public IEnumerable<Dictionary<string, object>> TopViewedPages(int topPagesToGet, string siteName, int daysInterval, string language)
{
var provider = (ReportDataProvider)Factory.CreateObject("reporting/dataProvider", true);
var query = new ReportDataQuery(string.Format(@"SELECT TOP {0}
ItemId,
F.LanguageId,
SUM(Views) PageViewsCount,
SUM(Visits) PageVisitsCount
FROM Fact_PageViewsByLanguage F
INNER JOIN SiteNames S ON S.SiteNameId = F.SiteNameId
INNER JOIN Languages L ON L.LanguageId = F.LanguageId
WHERE S.SiteName = '{1}'
AND F.Date BETWEEN DATEADD(DAY,-{2},GETDATE()) AND GETDATE()
AND L.Name = {3}
GROUP BY ItemId, F.LanguageId
ORDER BY PageViewsCount DESC, PageVisitsCount desc",
topPagesToGet, siteName, daysInterval, language));
var response = provider.GetData("reporting", query, new CachingPolicy { NoCache = true }); // You can set your own caching policy as per requirement
var datatable = response?.GetDataTable();
return datatable?.Select()?.Select(row => row.ItemArray
.Select((column, count) => new { Name = datatable.Columns[count].ColumnName, Value = column })
.ToDictionary(column => column.Name, column => column.Value));
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment