0

Could someone share the sample analytics api to retrieve the analytics data to get the total Visits and unique visits from last 30 days.

2 Answers 2

1

You can leverage Reporting API to retrieve total visits and unique visits from the [Fact_ChannelGroupMetrics] table inside the Reporting table as shown below.

In order to retrieve visits and total visits for last 30 days, we can run below query which joins the Fact_ChannelGroupMetrics, DimensionKeys and Fact_SystemMetrics table to retrieve corresponding results.

    using Sitecore.Xdb.Reporting;
    using System.Web.Mvc;
    using Sitecore.Configuration;
    
    public static Tuple<int,int> GetTotalVisits(){
      var provider = (ReportDataProvider)Factory.CreateObject("reporting/dataProvider", true);
      var query = new ReportDataQuery("SELECT sum([Fact_ChannelGroupMetrics].Visits) as Visits, sum(Fact_SystemMetrics.TotalVisits) as TotalVisits FROM [sitecorehackaton2024_Reporting].[dbo].[Fact_ChannelGroupMetrics] INNER JOIN [DimensionKeys] on [DimensionKeys].DimensionKeyId = Fact_ChannelGroupMetrics.DimensionKeyId LEFT OUTER JOIN (SELECT * FROM Fact_SystemMetrics WHERE [SegmentId] IN ('424110F4-2B2B-4453-9E2B-635E1DE75F20')) As Fact_SystemMetrics ON Fact_ChannelGroupMetrics.[SiteNameId] = Fact_SystemMetrics.[SiteNameId] AND Fact_ChannelGroupMetrics.[Date] = Fact_SystemMetrics.[Date] AND Fact_ChannelGroupMetrics.[FilterId] = Fact_SystemMetrics.[FilterId]  Where [Fact_ChannelGroupMetrics].SegmentId = '56649EAC-3CCF-4EAB-88B0-C74862DB862B' and [Fact_ChannelGroupMetrics].Date > DATEADD(day, -30, GETDATE())");
      string datasource = "reporting";
      ReportDataResponse response = provider.GetData(datasource, query, new CachingPolicy { NoCache = true });
      var datatable = response.GetDataTable();
      if (datatable != null && datatable.Rows.Count > 0)
      {
         var visits = datatable.Rows[0]["Visits"];
         var totalVisits = datatable.Rows[0]["TotalVisits"];
         return Tuple.Create(visits, totalVisits);
      }
      return null;
     }

Using above query we can also fetch total time on site,total page views, monetary value etc.

You can find more details on the reporting api here:

https://doc.sitecore.com/xp/en/developers/93/sitecore-experience-platform/reporting-api.html

Hope this helps!!!

11
  • thanks for the answer, I get the exception. Column 'UniqueVisits' does not belong to table
    – sns
    Commented Apr 19 at 19:56
  • DId you checked the datatable response..is it containing UniqueVisits column in the response...Also is it working at your end for 1st case i.e. for TotalVisits?
    – ckhanna
    Commented Apr 20 at 5:06
  • Thank you I see it, but the query doesnt seem to get unique visits? . Also the total visits doesnt match the total visits inside the launch pad's experience analytics. Any thoughts
    – sns
    Commented Apr 21 at 20:18
  • 1
    @sns I have updated the query above to fetch the visits and total visits.. Let me know in case you face any issues.
    – ckhanna
    Commented Apr 29 at 16:10
  • 1
    @sns I just checked 'Visits' in the above query return visits through online channel and 'Total Visits' returns visits through online as well as offline channel. You can find more details on visits(doc.sitecore.com/xp/en/users/92/sitecore-experience-platform/…) and channels(doc.sitecore.com/xp/en/users/92/sitecore-experience-platform/…) here. Hope this solves the issue you raised!!!
    – ckhanna
    Commented May 1 at 17:28
0

Using the Sitecore reporting database the Sitecore analytics API comes with some handy features to achieve this with the reporting database.

There are lot's of out of the box report queries that you can find under this location.

/sitecore/system/Settings/Content Testing/Report Queries

Here is the sample query to get the visits from the OOTB report queries.

DECLARE
    @LanguageCode int = null,
    @DeviceCode int = null
    
IF @LanguageName != ''
BEGIN
    SELECT @LanguageCode = LanguageId
    FROM [Languages]
    WHERE Name = @LanguageName
END

IF @DeviceName != ''
BEGIN
    SELECT @DeviceCode = DeviceNameId
    FROM [DeviceNames]
    WHERE DeviceName = @DeviceName
END

SELECT
    SUM([Visits]) AS [Visits]
FROM
    dbo.[Fact_PageViewsByLanguage]
WHERE
    [ItemId] = @ItemId 
    AND [LanguageId] = COALESCE(@LanguageCode, LanguageId)
    AND [DeviceNameId] = COALESCE(@DeviceCode, DeviceNameId)
    AND [Date] >= @Start 
    AND [Date] <= @End

You can modify this as per your need.

Also you can follow this article to know more about querying them.

https://theroks.com/use-sitecore-analytics-show-number-pageviews/#:~:text=Second%20is%20the%20query%20field,to%20be%20a%20negative%20number.

Hope this helps.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.