We have confirmed the criteria for the list pull from Civi. There are a few different segments we would like to bring into one list. There may be overlap and we would need to figure out how to dedupe the list for those cases.
Universal criteria:
- Individual contact type
- Not opted out
- US based with mailing address (don’t include those with invalid addresses)
ie
FROM civicrm_contact c INNER JOIN civicrm_address a ON a.id = c.id AND a.is_primary = 1 AND a.country_id = 1228 AND a.street_address <> '' WHERE contact_type = 'Individual' AND is_deceased = 0 AND is_deleted = 0 AND is_opt_out = 0
Segments:
- Gift of $50+ cumulatively in the last 5 years (Jan 1, 2018 - today)
- HNWI MG Audience (Donors who have a HNWI of $1M based on the TSmart data and have donated between $50 and $249 in the FY2122) - there is a group created by Major Gifts here. select * FROM civicrm_group_contact WHERE group_id = 1204 AND status = 'Added';
- Given $250+ ever (Data Axle 250+ single donation group ) select * FROM civicrm_group_contact WHERE group_id = 1678 AND status = 'Added';
- DAF donors of any threshold who gave in last 5 years (gift source = Donor Advised Fund) select * FROM civicrm_group_contact WHERE group_id = 1609 AND status = 'Added';
- QCD donors any threshold who gave in last 5 years (gift source = Retirement fund / retirement fund - endowment specified / retirement fund - annual fund specified) civicrm_value_1_gift_data_7.campaign IN (''Retirement Fund - Endowment Specified'', ''Retirement Fund')
- Given at least $20+ each year for the last 3 years
The format for the list requested by Data Axle is:
- CSV or Excel file
- 1 list file
Separate columns for:
- First Name
- Last name
- Address (123 Main St)
- City
- State
- Zip
- Phone(if available)
- DOB
I know this can get complicated, so please follow up for any clarification needed. Thank you for your help pulling this list together and exporting from Civi! Any idea on how long this might take to create and pull would be appreciated so we can let Data Axle know.