The Advanced DWA Querying is available for teams with DWA, available from both the Fan Finder tool.
All screens shown are from Fan Finder.
- Open Fan Finder, and start a new search for Contacts:
- On the Search Criteria page, drag the Advanced DWA Query option to the Include block:
- This will open the Advanced DWA Query window. Paste in a condition that supposes you are working from the WHERE clause of a query (These can be simple, or quite advanced).
- You can also test the query using Aginity or SQL Toolbelt or other Redshift query tools, by pasting the query to the account dimension below:
select count(*)
from dwa.vwdim_account
where <paste query here> - Simple example:
middlename ilike 'sarah'
- More advanced sample, using a subquery to look for customers that have purchased full season tickets:
accountsid in (select top 100 distinct tt.accountsid from dwa.vwfact_ticket_transaction tt inner join dwa.vwdim_product r on tt.productsid = r.productsid where r.eventcode ilike '%fs%') - More advanced sample, using a subquery to reference a custom table with Archtics IDs, joining back through the KORE links to the Account dimension:
lower(accountsid) in
(
select lower(sc.sf_contactid)
from custom.appended_contacts_cust c
inner join korepss.externalsystemtocontact estc
on c.archtics_id = estc.externalcontactid
inner join korepss.syncedcontacts sc
on estc.crmcontactid = sc.contactid
where discretionary_income_index_cd ilike '04%'
)
- You may choose to validate the query if you wish. If the syntax is invalid a red exclamation will be displayed, or a green checkmark will be displayed if valid. When ready, click OK to add the criteria to your shopping cart:
- Click Next to view the results and proceed with the campaign. WARNING: for larger lists, this may cause a timeout on query execution, as it returns all results to the Fan Finder before limiting to 1k, so applying limits in your subquery can yield immense performance benefits.
