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 data sets’ data structures.
Tags can be created based on the DWA fact tables as well as any other data sets 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) |
| Data Set | (Dynamic tags only) The data set 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 data set records. If the results contain data set IDs that are not returned by the configured data set, 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 sets in DWA (not the Golden Records themselves), the information from a data set could match the tag’s criteria even though the Golden Record selection rules selected a different data set 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 sets 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.)