I am creating a process that reads in the Sitecore analytics contact collection and gets information needed for a report. It is read and inserted in SQL Server. Not sure how to get some of the fields though. Particularly Visit Duration and Latest Date Visited. I was able to get Visits Count, Email, First Name, Last Name etc..., but can't seem to find these fields. Update these fields I believe can be retrieved from the interactions collection. However not sure how to get a dataset from contacts and interactions to get these fields.

1 Answer 1


That data is in the sitecore_analytics_index. It is not in the analytics model for you to pull from. You are going to have to make a call into the index to get that data for each of your contacts.

When you look at Experience Profile, the data you see if coming from an API call to /sitecore/api/ao/v1/aggregates/latest-visitors.

Tracking that back to the config, the pipeline constructor for that API is

      <processor type="Sitecore.Cintel.Reporting.Aggregate.Visitors.Processors.ConstructVisitorsDataTable, Sitecore.Cintel"/>
      <processor type="Sitecore.Cintel.Reporting.Aggregate.Visitors.Processors.QueryLatestVisitorsFromSearch, Sitecore.Cintel"/>
      <processor type="Sitecore.Cintel.Reporting.Processors.ApplySorting, Sitecore.Cintel"/>
      <processor type="Sitecore.Cintel.Reporting.Processors.ApplyPaging, Sitecore.Cintel"/>

The one you really care about is Sitecore.Cintel.Reporting.Aggregate.Visitors.Processors.QueryLatestVisitorsFromSearch. This is the dll that gets the data you require.

Gather the latest record on your contact by ID. Sort by date descending and pick one.

var ctx = ContentSearchManager.GetIndex(CustomerIntelligenceConfig.ContactSearch.SearchIndexName).CreateSearchContext();

var visit = ctx.GetQueryable<IndexedVisit>().Where(iv => iv.ContactId == contact.ContactId).OrderByDescending(iv => iv.StartDateTime).Take(1).FirstOrDefault();

IContactSearchResult contact;

Pick out the details and send it to SQL as you requested.

Then you can send a contact and the result into this function to the the details you need out.

private void PopulateLatestVisit(IndexedVisit visit, ref IContactSearchResult contact)
   contact.LatestVisitId = visit.InteractionId;
   contact.LatestVisitStartDateTime = visit.StartDateTime;
   contact.LatestVisitEndDateTime = visit.EndDateTime;
   contact.LatestVisitPageViewCount = visit.VisitPageCount;
   contact.LatestVisitValue = visit.Value;
   contact.ValuePerVisit = Calculator.GetAverageValue(contact.Value, contact.VisitCount);
   if (visit.WhoIs == null)
   contact.LatestVisitLocationCityDisplayName = visit.WhoIs.City;
   contact.LatestVisitLocationCountryDisplayName = visit.WhoIs.Country;
   contact.LatestVisitLocationRegionDisplayName = visit.WhoIs.Region;
   contact.LatestVisitLocationId = visit.LocationId;
  • Thanks Chris! I am going to give this a try and let you know what happens. I will keep you posted.
    – RReilley
    Aug 29, 2017 at 12:06
  • Thanks again. This seems to work for me. I marked this as the answer.
    – RReilley
    Aug 29, 2017 at 19:37
  • Great to hear @RReilley. Glad to help.
    – Chris Auer
    Aug 29, 2017 at 19:38

Your Answer

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

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