Where to find Visit Duration and Latest Date Visited in Code Behind

Question

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.

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

<pipelines>
   <latest-visitors>
      <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"/>
   </latest-visitors>
</pipelines>

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)
      return;
   contact.LatestVisitLocationCityDisplayName = visit.WhoIs.City;
   contact.LatestVisitLocationCountryDisplayName = visit.WhoIs.Country;
   contact.LatestVisitLocationRegionDisplayName = visit.WhoIs.Region;
   contact.LatestVisitLocationId = visit.LocationId;
}