Retrieving Interactions Data from MongoDB using Sitecore Analytics API

Posted 07/05/2016 by Subramanian Ramanathan

Being a Sitecore Developer sometimes means that you have implemented or are going to need to implement Sitecore Analytics in your project soon. 

Frequently, when performing this implementation, we need to create Reports with Charts/Tables using the Analytics Interactions Data. 

The first thought to do that is to query the Reporting Database to get the interaction details from tables like Fact_PageViews or Fact_Visits.

If we need some customization in that, we would directly query the MongoDB using FindAll() or Find() methods and would get the entire collection. Then we could filter it and convert it into List,DataTable etc. 

//Accessing the Server & Database 
using MongoClient 
string connectionString = ConfigurationManager.ConnectionStrings["analytics"].ConnectionString; 
MongoUrl mongoUrl = new MongoUrl(connectionString); 
MongoServer server = (new MongoClient(connectionString)).GetServer(); 
MongoDatabase database = server.GetDatabase(mongoUrl.DatabaseName); 
//Get the Interactions Collection 
var collection = database.GetCollection("Interactions").FindAll().ToList();

We are still not done here though, since we have to filter & convert these details, which is a huge task.

This got me thinking... why is Sitecore not providing some APIs to fetch the Analytics Data? After a lot of research I was surprised to see that Sitecore DOES already have some APIs in it to do just that, which made my job much simpler.

Sitecore.Analytics.Reporting.MongoDbReportDataSource mongoDBSource = new Sitecore.Analytics.Reporting.MongoDbReportDataSource("analytics"); 
string query = "{collection: \"Interactions\",query: {_t: \"VisitData\"},fields: [\"_id\",\"ContactId\",\"StartDateTime\",\"EndDateTime\",\"Value\",\"VisitPageCount\"]}"; 
ReportDataQuery reportQuery = new ReportDataQuery(query); 
DataTable interactions = mongoDBSource.GetData(reportQuery); 

The query here would have the following details,

  • MongoDB Collection Name
  • Query for filtering the Collection
  • Fields that needs to be retrieved from MongoDB

With these lines of code you are actually ready with the datasource(as DataTable) for creating reports. I usually use the Telerik Charting controls (that comes out of box with Sitecore) for creating reports.

I have got this done using Sitecore 7.5 and it should also work in versions above 7.5. This will have major performance improvement in your Mongo DB Querying. 

You should also not find a major impact when you upgrade your sitecore version, because Sitecore will take care of the API changes that might happen in the new MongoDB dlls.


Add your comment




  • Suresh Rayarakula said:
    4/13/2017 12:50 AM

    Thanks for this post, very use full.

  • Suresh Rayarakula said:
    4/13/2017 12:54 AM

    Please provide the sample query for join the two different collections.

  • Alan said:
    6/6/2017 6:54 AM

    Could you help me how can i use {$unwind} in this code ?

  • Pavan said:
    7/25/2018 4:30 AM

    Hi, thanks for the article. Is there a way to get visited page names apart from the count of pages. When i pass "pages", to collection the response is empty. I believe pages is a collection object. Can you please provide inputs on how it can be done.