Deprecated: This page describes the older Insights Portal. Contact your Success Manager to ask about switching to the new Helix platform.
Overview
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.
Data dictionary
The data dictionary for this integration can be found here
Link: Data dictionary.xlsx
Customer
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
Key Fields
-
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"
Customer Group
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.
Key Fields
-
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
Example queries
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 = 'example@example.com'
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.
Eloqua Example
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
= ‘eloqua’
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
KOREPSS Example
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