This document outlines the relevant sections for the Entity Resolution or Golden Record feature in DWA. Entity resolution (ER) is the task of disambiguating records that correspond to real world customer records across and within datasets.
The data dictionary for this integration can be found here
Link: Data dictionary.xlsx
The "customer" object is the single source of truth for a customer once it has been feed into KORE's entity resolution processing. This represents a single customer from all of the relevant systems across your DWA product which are included in the merging process
customerid: unique identifier for the customer. This is not a static field and changes every time the process runs
source: indicates the source system the record came from. if this is a record combined from multiple systems it will say "kore"
The "customer_group" table represents the link back to the source system where the customer was derived. If a customer record was a combined output of korepss and eloqua, there will be a record identifying the korepss customer id and the eloqua customer id in this table.
customerid: unique idnetifier for the customer, this is the foreign key to the customer.customerid field.
source: indicates the source system the record came from
sourceid: this is the unique id from the source system which can be used to link back to the source system
Searching for customer records
Here is an example query on how to search for a customer and all their associated records
SELECT c.customerid, c.firstname, c.lastname, c.email, cg."source", cg."sourceid" FROM insights.customer c JOIN insights.customer_group cg ON c.customerid = cg.customerid WHERE c.email = 'firstname.lastname@example.org' ORDER BY "source"
Linking To Source Data
Linking customer records back through to the source data can be achieved by using the “customer_group” table as the bridge back to the customer information in the source system
The key elements are to this linkage is the the “sourceid” and the “source” fields, as they help you to define fields you need to use to use to link back to the source system.
For an example of how link back to eloqua, I would join the following
insights.customer_group.sourceid = eloqua.contact.contactid and insights.customer_group.source
SQL Example - Displaying Eloqua Email sends in the last 90 days
SELECT c.customerid, c.firstname, c.lastname, COUNT(DISTINCT activityid) email_sends FROM insights.customer c JOIN insights.customer_group cg ON c.customerid = cg.customerid JOIN eloqua.contact ec ON cg.sourceid = ec.contactid and cg.source = 'eloqua' LEFT JOIN eloqua.emailsend es ON ec.contactid = es.contactid and activitydate < current_date - 90 WHERE c.customerid = '1abc2d3e-1234-1234-1234-123ab4567890' GROUP BY 1, 2, 3
For an example of how to link back to korepss, I would join the following fields, then I can find any KOREPSS related data
insights.customer_group.sourceid = korepss.syncedcontacts.contactid and
insights.customer_group.source = ‘korepss’
SQL Example - Displaying KOREPSS activities in the last 30 days
SELECT c.customerid, c.firstname, c.lastname, sa.activitydate, sa.subject, sa.activitytype FROM insights.customer c JOIN insights.customer_group cg ON c.customerid = cg.customerid JOIN korepss.syncedcontacts sc ON cg.sourceid = sc.contactid and cg.source = 'korepss' LEFT JOIN korepss.syncedactivities sa ON sc.contactid = sa.contactid and sa.koreactivitydate < current_date - 30 WHERE c.customerid = '1abc2d3e-1234-1234-1234-123ab4567890' ORDER BY sa.activitydate