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
Note: To create or edit tags, you should already be familiar with writing SQL queries and your sources’ data structures.
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.
Field | Notes |
Display Name | Short title for the tag, up to 50 characters. |
API Name | Simplified version of the display name that the system uses when sending to other integrations. Only letters, numbers, dashes, and underscores are allowed. This cannot be changed later. |
Category | High level grouping of Tags; categories are managed from the Categories tab. |
Tag Type | Dynamic: Assigned using a SQL query. Static: Assigned from a manually uploaded file. Ask your Success Manager for SFTP or S3 access. |
Description | (Optional) Up to 500 characters. |
External Link | (Optional) |
Source | (Dynamic tags only) The data source to evaluate. |
Schedule | (Dynamic tags only) How frequently to refresh the data: nightly, every 2 hours, or paused. |
Connection | (Dynamic tags only) The service to pull data from. Currently, DWA is the only option. |
SQL | (Dynamic tags only) The query used to find source records. If the results contain source IDs that are not returned by the configured source, they are still counted but ignored otherwise. |
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.
2021 Season ticket holders:
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';
Active ticket buyer for a particular concert:
SELECT DISTINCT vmt.contact_id AS customerid
FROM dwa.vvwfact_manifest_transaction vmt
WHERE vmt.productsid='0001' AND vmt.seatstatus = ‘Sold’;
Attended 5 games in the 2020 season:
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;
Lifetime spend over $5000:
SELECT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
GROUP BY vtt.contact_id
HAVING SUM(vtt.purchase_price) > 5000;
Primary individual game/event buyer:
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;
Purchased over 20 individual tickets this season:
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';
2021 Season ticket holders:
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');
Active ticket buyer for a particular concert:
SELECT DISTINCT vmt.contact_id AS customerid
FROM dwa.vvwfact_manifest_transaction vmt
WHERE vmt.productsid='0001' AND vmt.seatstatus = ‘Sold’;
Attended 5 games in the 2020 season:
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;
Lifetime spend over $5000:
SELECT vtt.contact_id AS customerid
FROM dwa.vwfact_ticket_transaction vtt
GROUP BY vtt.contact_id
HAVING SUM(vtt.purchase_price) > 5000;
Primary individual game/event buyer:
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;
Purchased over 20 individual tickets this season:
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.
Warning: It is possible for the list of Golden Records you download to contain records that appear not to meet the selection criteria. Because tags are assigned based on data sources in DWA (not the Golden Records themselves), the information from a data source could match the tag’s criteria even though the Golden Record selection rules selected a different data source for that information.
For example, suppose that Deidra Banks recently moved from Townsville to Shelbyville. If a tag selects contacts who live in Townsville using data from SeatGeek, Deidra’s record would still be included if she hasn’t updated her address there. This is true even if other data sources are up-to-date and the Golden Record correctly lists her city as Shelbyville. Since the CSV file contains Golden Records, Deidra’s town would be correctly listed as Shelbyville.
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.
Note: If multiple source records with a tag have matched into a single Golden Record, only one GR-Tags record is pushed into DWA. Additionally, if a Golden Record is already connected to a tag, and another matched source records gains the tag, Helix does not see that as a change since state of the final Golden Record did not change.
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 thehelix.grrecords.id
fieldhelix_source
: the source system the record came fromhelix_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
Table name | Column name | Data type | Notes |
tags_{api-name} | source | varchar(50) | the source api-name |
tags_{api-name} | sourceid | varchar(50) | the id of the source |
tags_{api-name} | tag_name | varchar(50) | the tag api-name |
tags_{api-name} | processed_date | timestamp | the time the last schedule executed |
tags | id | varchar(100) | Id of the Golden Record |
tags | tag | varchar(50) | api-name of the tag |
tags | helix_processedon | timestamp | the latest time the helix engine detected a change for this mapping |
tags | processed_date | timestamp | The time when the ETL pushed the change from helix into DWA |
grrecords | first_name | varchar(100) | |
grrecords | last_name | varchar(100) | |
grrecords | varchar(100) | ||
grrecords | company_name | varchar(100) | |
grrecords | work_phone | varchar(100) | |
grrecords | home_phone | varchar(100) | |
grrecords | cell_phone | varchar(100) | |
grrecords | other_phone | varchar(100) | |
grrecords | address_line_1 | varchar(100) | |
grrecords | address_line_2 | varchar(100) | |
grrecords | city | varchar(100) | |
grrecords | state | varchar(100) | |
grrecords | country | varchar(100) | |
grrecords | zip | varchar(100) | |
grrecords | id | varchar(100) | |
grrecords | helix_processedon | timestamp | the latest time the helix engine detected a change for this record |
grrecords | processed_date | timestamp | The time when the ETL pushed the change from helix into DWA |
mappings | helix_recordid | varchar(100) | a composite key of source and it’s id |
mappings | helix_sourceid | varchar(50) | the source record id |
mappings | helix_source | varchar(50) | The api-name of the source |
mappings | component | varchar(50) | Id of the Golden Record |
mappings | helix_maxprocessedon | timestamp | the latest time the helix engine detected a change |
mappings | processed_date | timestamp | The time when the ETL pushed the change from helix into DWA |
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;