KORE Tags

Overview

Often, a marketing department will request a list of contacts matching certain criteria. Business intelligence analysts then write SQL queries to produce that list. If the same criteria are used frequently, you can create a tag. A tag is essentially a saved query whose results are updated nightly. Marketing and other users can download the list of Golden Records associated with a tag at any time without using SQL.

To add or edit tags, you must be a Power User or Admin. Tags are refreshed hourly.

Data dictionary

The data dictionary for this integration can be found here.

Key Fields

• customerid: unique identifier for the customer, this is the foreign key to the tags.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

This section shows how to create SQL queries to integrate KORE Tags with other KORE’s products:

Searching for customer records

Here is an example query on how to search for a customer and all their associated records

SELECT t.customerid, t.firstname, t.lastname, t.email, t.source, cg."sourceid" 
FROM insightsx.tags t
JOIN insights.customer_group cg
  ON t.customerid = cg.customerid 
WHERE t.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 of this linkage are the 'sourceid' and '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, the following is recommended:

insights.customer_group.sourceid = eloqua.contact.contactid
AND insights.customer_group.source = ‘eloqua’

SQL Example - Displaying Eloqua Email sends in the last 30 days

SELECT t.customerid, t.firstname, t.lastname, 
   COUNT(distinct activityid) email_sends
FROM insightsx.tags t
JOIN insights.customer_group cg
   ON t.customerid = cg.customerid
JOIN eloqua.contact ec
   ON cg.sourceid = ec.contactid
LEFT JOIN eloqua.emailsend es
   ON ec.contactid = es.contactid
   AND activitydate < current_date - 30
WHERE 
  t.email = 'example@example.com'     /* Email address who registered the Tag */
  AND cg.source = 'eloqua'            /* Tag Source name */
  AND t.tag_name='nba_resale_buyers_-_spent_$250+_(2019-20)'
GROUP BY 1, 2, 3;

KOREPSS Example

For an example of how to link back to korepss, the following JOIN is recommended, then you 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 DISTINCT t.customerid, t.firstname, t.lastname,
   sa.activitydate, sa.subject, sa.activitytype
FROM insightsx.tags t
INNER JOIN insights.customer_group cg
   ON t.customerid = cg.customerid
INNER JOIN korepss.syncedcontacts sc
   ON cg.sourceid = sc.contactid
LEFT JOIN korepss.syncedactivities sa
   ON sc.contactid = sa.contactid
   AND sa.koreactivitydate < current_date - 30
WHERE t.email = 'example@example.com'   /* Email address who registered the Tag */
   AND cg.source = 'korepss'            /* Tag Source name */
   AND t.tag_name='nba_resale_buyers_-_spent_$250+_(2019-20)'
ORDER BY sa.activitydate;

DWA Archtics Ticketing Example

For an example of how to link back to Archtics ticketing datasource, the following JOIN is recommended. Note for this integration is necessary a custom JOIN logic to link the sourceid field:

• On archtics.cust
  archtics.cust.acct_id + '-' + archtics.cust.cust_name_id = insights.customer_group.sourceid

• On archtics.ticket
  archtics.cust.acct_id = archtics.ticket.acct_id

SQL Example - Displaying Active Sold Tickets for Full Season plan

SELECT DISTINCT t.customerid, cg.source, t.tag_name,
   tt.event_name, tt.section_name, tt.row_name, tt.num_seats
FROM insightsx.tags t
INNER JOIN insights.customer_group cg 
   ON t.customerid = cg.customerid
INNER JOIN archtics.cust c 
   ON c.acct_id + '-' + c.cust_name_id = cg.sourceid
INNER JOIN archtics.ticket tt
   ON c.acct_id = tt.acct_id
WHERE t.tag_name IN ('lapsed_ticket_purchasers_without_plans')
   AND t.source = 'archtics'
   AND tt.ticket_status= 'A'             /* A: Active tickets, R: Returned tickets */
   AND tt.plan_event_name LIKE '%FS'     /* FS: Full Season tickets */
;

DWA Ticketing Example

For an example of how to link back to DWA ticketing datasource is very similar to the Archtics example, the following JOIN is recommended. Note for this integration is also necessary a custom JOIN logic to link the sourceid field:

• On archtics.cust
  archtics.cust.acct_id + '-' + archtics.cust.cust_name_id = insights.customer_group.sourceid

• On archtics.ticket
  archtics.cust.acct_id = archtics.ticket.acct_id

SQL Example - Displaying Active Sold Tickets for Full Season plan

SELECT DISTINCT t.customerid, cg.source, t.tag_name,
   p.event_desc, p.manifest_desc, tt.seatsid,tt.season_year
FROM insightsx.tags t 
INNER JOIN insights.customer_group cg
   ON t.customerid = cg.customerid
INNER JOIN archtics.cust c 
   ON c.acct_id + '-' + c.cust_name_id = cg.sourceid
INNER JOIN dwa.fact_ticket_transaction tt
   ON c.acct_id = tt.contact_id
INNER JOIN dwa.dim_product p
   ON tt.productsid = p.productsid 

WHERE t.tag_name in ('lapsed_ticket_purchasers_without_plans')
	AND t.source = 'archtics'
	AND tt.ticket_status= 'A'							/* A: Active tickets, R: Returned tickets */
	AND tt.season_year = 2021
	AND p.eventcode LIKE '%FS'            /* FS: Full Season tickets */