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 response to a query. Marketing and other users can use it to download the associated list of Golden Records without using SQL themselves.

The records associated with each tag are calculated nightly, but this can be changed to every 2 hours if new data is received frequently. These updates can be used in Helix immediately, but the changes will still only be pushed into DWA nightly regardless of this setting.

To add or edit tags,

• you must be a Power User or Admin, and
• there must be a category for the tags.

Create or edit a tag

Tags can be created based on the DWA fact tables as well as any other data sources you have connected to DWA, but currently not the Golden Records.

To begin, open the Tags page in the Insights Portal. Any existing tags will be displayed here. Click the New button to create a new tag.

In the editor, fill in the required fields and any optional ones as desired. When finished, click the Save button. The new tag will be queued for processing—the queue time varies, but this typically takes around five minutes.

To edit an existing tag, click anywhere on the desired line except the Actions column. This will open the editor.

To delete an existing tag, open the editor and click the ellipsis (…) button at the top to find the Delete button.

Example queries

You can use these examples as templates to create your own tags.

SELECT DISTINCT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
INNER JOIN dwa.vwdim_product vp ON vp.productsid = vtt.prodplansid
   AND vp.season_year ='2021' AND vp.event_desc ='2021 Season Tickets'
WHERE vtt.ticket_status = 'Active';

SELECT DISTINCT vmt.contact_id AS customerid
FROM dwa.vvwfact_manifest_transaction vmt
WHERE vmt.productsid='0001' AND vmt.seatstatus = ‘Sold’;

SELECT vat.contact_id AS customerid
FROM dwa.vwfact_attendance_transaction vat
WHERE vat.resultcode ='0' AND vat.scantypesid = 'A' AND vat.season_year = '2020'
GROUP BY vat.contact_id
HAVING COUNT(DISTINCT vat.productsid) = 5;

SELECT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
GROUP BY vtt.contact_id
HAVING SUM(vtt.purchase_price) > 5000;

SELECT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
INNER JOIN dwa.vwdim_product vp ON vp.productsid = vtt.productsid
WHERE vp.season_year ='2021' AND vtt.prodplansid IS NULL;

SELECT tt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction tt
INNER JOIN dwa.vwdim_product p ON tt.productsid = p.productsid
   AND tt.season_year = '2021'
WHERE p.event_desc LIKE 'KORE Knights vs%' AND tt.ticket_status = 'Active'
GROUP BY tt.contact_id
HAVING COUNT(DISTINCT p.eventcode) > 20
EXCEPT
  SELECT vtt.contact_id
  FROM dwa.vwfact_ticket_transaction vtt
  INNER JOIN dwa.vwdim_product vp ON vp.productsid = vtt.prodplansid
     AND vp.season_year ='2021' AND vp.event_desc ='2021 Season Tickets'
  WHERE vtt.ticket_status = 'Active';

SELECT DISTINCT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
LEFT JOIN dwa.vwdim_price p ON p.pricesid = vtt.pricesid
WHERE vtt.ticket_status = 'Active'
   AND vtt.productsid = 'f0e035f9-9b90-eb11-82e4-cc1f053c5f52'
   AND p.parentpricecode IN ('Season Tickets');

SELECT DISTINCT vmt.contact_id AS customerid
FROM dwa.vvwfact_manifest_transaction vmt
WHERE vmt.productsid='0001' AND vmt.seatstatus = ‘Sold’;

SELECT vat.contact_id AS customerid
FROM dwa.vwfact_attendance_transaction vat
WHERE vat.resultcode ='Succeeded' AND vat.scantypesid = 'FirstScan'
   AND vat.season_year = '2020'
GROUP BY vat.contact_id
HAVING COUNT(DISTINCT vat.productsid) = 5;

SELECT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
GROUP BY vtt.contact_id
HAVING SUM(vtt.purchase_price) > 5000;

SELECT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
INNER JOIN dwa.vwdim_product vp ON vp.productsid = vtt.productsid
WHERE vtt.ticket_status = ‘Active’ AND vp.season_year ='2021'
   AND vtt.prodplansid IS NULL;

SELECT tt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction tt
INNER JOIN dwa.vwdim_product p ON tt.productsid = p.productsid
   AND tt.season_year = '2021'
WHERE p.event_desc LIKE 'KORE Knights vs%' AND tt.ticket_status = 'Active'
GROUP BY tt.contact_id
HAVING COUNT(DISTINCT p.eventcode) > 20
EXCEPT
   SELECT sgc.crmid 
   FROM dwa.vwdim_account account
   LEFT JOIN seatgeek.subscription subscription
      ON account.accountsid = subscription.purchaserclientid
   INNER JOIN seatgeek.customer sgc ON sgc.id = subscription.purchaserclientid
   WHERE subscription.name = 'Season Tickets';

View tagged records

To begin, go the the Golden Records page and select a tag from the dropdown menu.

Next, use the Export Manager to generate a CSV (comma separated values) file containing the Golden Records that match your current filters (tag and quick search). The CSV file can be imported into Excel.

Refresh tags

Tags automatically refresh according to each tag’s configuration, but you may also click the refresh icon if you require fresher data. The request will be queued right away, but it may take some time before processing begins. Once processing begins, it typically takes about five minutes for the results to become available. Refreshing this list only updates which contacts are included, not their Golden Records. (Golden Records are only updated nightly, so very recent changes to the underlying data may not be reflected yet.)

Query the tag results in DWA

After creating a tag, the list of source matches are stored in tag specific tables. These tables are maintained on the tag’s schedule. Nightly the changes to the consolidated tag results are pushed into a single DWA table. You can query the results directly.

You can also map the tag results to the source they came from. The helix.mappings table contains these key fields:

• component: unique identifier, use as foreign key to the helix.grrecords.id field
• helix_source: the source system the record came from
• helix_sourceid: the source system's unique identifier for the customer

Examples:

• Eloqua
  • helix.mappings.helix_sourceid= eloqua.contact.contactid
  AND helix.mappings.helix_source = ‘eloqua’
• KOREPSS (CRM-integrated products)
  • helix.mappings.helix_sourceid= korepss.syncedcontacts.contactid
  AND helix.mappings.helix_source = ‘korepss’
• DWA with Archtics
  • archtics.cust.acct_id + '-' + archtics.cust.cust_name_id = helix.mappings.helix_sourceid
  AND helix.mappings.helix_source = ‘archtics’
    -OR-
  • archtics.cust.acct_id = helix.mappings.helix_sourceid
AND helix.mappings.helix_source = ‘archtics’

Data dictionary

Example queries

Search the tagged records for a customer using their email address:

SELECT t.id, g.first_name, g.last_name, g.email, t."tag"
FROM helix.tags t
JOIN helix.grrecords g ON t.id = g.id 
WHERE t.email = 'customer@example.com';

Show the number of Eloqua emails sent to each tagged customer in the past 30 days:

SELECT t.id, COUNT(distinct activityid) email_sends
FROM helix.tags t
JOIN helix.mappings m ON t.id = m.component 
JOIN eloqua.contact ec ON m.helix_sourceid = ec.contactid
  AND m.helix_source = 'eloqua' 
LEFT JOIN eloqua.emailsend es ON ec.contactid = es.contactid
   AND activitydate < current_date - 30
WHERE 
  t.email = 'customer@example.com'
  AND t."tag" = 'example_tag_name'
GROUP BY 1, 2, 3;