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
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;
}