Using Sitecore Report Queries to get analytics data in your code

Sitecore comes with dozens of predefined report queries which you can use to get any type of data you need from reporting database. Learn how to use predefined Sitecore Queries to get any analytics data or write your own Report Query to get the data you need.

Where are Sitecore Queries in content tree

Sitecore already comes with default Report Queries which you can use or you can custom write your own. You can find them at /sitecore/system/Settings/Analytics/Report Queries in your Sitecore instance. Take some time and review all query items there and see what kind of data is available. You use any of the already existing Report Queries to retrieve data you need. Please don't hard code SQL queries in your code to pull data. For example, if you need to get page views, just use Page Visits report query and extract TotalVisits.

How do I use Sitecore Report Query?

In example here we are going to use New Page Visitors report query to get total number of new visitors for a specific page. Here is how report query item looks like in Sitecore content tree.

Data Source field has reporting entered which means to use the reporting database.

Query field has the SQL query which will be run against the reporting database. Here is the full SQL query since it is cut-off in the screenshot above.

SELECT COUNT(ContactID) AS [TotalVisitors]
  FROM [dbo].[Fact_Visits]
  where 
  [ItemId] = @Id AND
  [FirstVisit] = 1 AND 

  [Date] BETWEEN @StartDate AND @EndDate

 So, how do we use a Report Query to get reporting data? I've set this up in a Helix solution in Sitecore.Foundation.Analytics project. Here is the code for the NewPageVisitorsQuery class which you can use to get the data. NewPageVisitorsQuery is derived from ItemBasedReportingQuery which handles all the internal work for you.

using System;
using System.Collections.Generic;
using Sitecore.Analytics.Reporting;
using Sitecore.Data;

namespace Sitecore.Foundation.Analytics.ReportQueries
{
    public class NewPageVisitorsQuery : ItemBasedReportingQuery
    {
        public NewPageVisitorsQuery(ReportDataProviderBase reportProvider = null)
        : base(Constants.ReportingQueries.NewPageVisitors, reportProvider)
        { }

        public ID ItemId { get; set; }

        public long TotalVisitors { get; protected set; }

        public override void Execute()
        {
            // you can pass @StartDate and @EndDate as parameters if you need to specify date range
            var parameters = new Dictionary<string, object>
            {
                { "@Id", ItemId },
                { "@StartDate", DateTime.MinValue.ToShortDateString() },
                { "@EndDate", DateTime.Today.ToShortDateString() }
            };

            var dt = this.ExecuteQuery(parameters);

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

Finally, this is how you can use the NewPageVisitorsQuery class to get your data.

protected ReportDataProviderBase ReportDataProvider = null;

var query = new NewPageVisitorsQuery(this.ReportDataProvider)
{
    ItemId = Sitecore.Context.Item.ID
};

query.Execute();

var visits = query.TotalVisitors;

Summary

The advantage of using report queries is that we don't have to write our own SQL statements to retrieve the data we need. Anything you would need is most likely already present in one of the predefined report queries and if you cannot get the data you need in one of the predefined queries we can always add your own Report Query item and then adjust the code sample above accordingly.