Hi All, Starting this phab task for the new list pull for TargetSmart. Could you help us pull a list with the same fields as previous pulls? Criteria is below:
Donated gifts of $1.00-$4.99 in the past 3 fiscal years
Individual contact type
US based with mailing address
Exclude 2020 TargetSmart Update and TargetSmart2019 groups
Thank you!
@EYener actually just volunteered to pull this. Fr-tech will need to do the import. Let us know when you need our help!
@EYener if you are outputting a csv just be mindful it needs a few BOM characters at the start for utf-8 support in mysql. I can fish out the details fo you
Adding query for transparency and any future needs:
SELECT DISTINCT c.contact_id, contact.nick_name, contact.first_name, contact.last_name, a.street_address, a.supplemental_address_1, a.city, a.county_id, a.state_province_id, a.postal_code, a.country_id FROM civicrm.civicrm_contribution AS c LEFT JOIN civicrm.wmf_donor AS wmf ON c.contact_id = wmf.entity_id LEFT JOIN civicrm.civicrm_contact AS contact ON c.contact_id = contact.id LEFT JOIN civicrm.civicrm_address AS a ON (c.contact_id = a.contact_id AND a.is_primary = 1) WHERE c.receive_date >= '2016-07-01' /*Pull 3 prior years*/ AND c.total_amount >= 1 AND c.total_amount <= 4.99 /*Limits based on donation tiers*/ AND c.contribution_status_id = 1 /*Completed donations only*/ AND contact.contact_type = 'Individual' AND a.country_id = 1228 /*US Only*/ AND a.street_address IS NOT NULL /*Must have a street address*/ AND c.contact_id NOT IN /*Not in Civi lists associated with prior TS imports/exports*/ (SELECT DISTINCT g.contact_id FROM civicrm.civicrm_group_contact AS g WHERE g.group_id IN (802,522,534,535,560,669,791))
@EYener also exclude based on opt in & out?
This is what we used last time
INSERT INTO civicrm_group_contact (group_id, status, contact_id)
SELECT $groupID, 'Added', wmf.entity_id
FROM wmf_donor AS wmf
INNER JOIN civicrm_contact AS c ON c.id = wmf.entity_id
INNER JOIN civicrm_address AS a ON (c.id = a.contact_id AND a.is_primary = 1)
LEFT JOIN $communicationPreferencesTableName cv ON cv.entity_id = wmf.entity_id
INNER JOIN civicrm_email e ON e.contact_id = c.id AND e.is_primary = 1 AND e.on_hold IS NOT NULL
WHERE
c.contact_type = 'Individual'
AND a.country_id = 1228
AND a.street_address IS NOT NULL
# This complex clause gets the earliest of endowment_first_donation_date & first_donation_date
# and ensures it is greater than 1 Jul 2019
# effectively 'first gave this financial year'
AND IF(wmf.first_donation_date IS NULL OR wmf.endowment_first_donation_date IS NULL,
COALESCE(wmf.first_donation_date, wmf.endowment_first_donation_date),
LEAST(wmf.first_donation_date,wmf.endowment_first_donation_date)) >= '2019-07-01'
AND c.is_deleted = 0
# Yep 3 forms of opt in / out & we have to be careful about NULL
AND c.is_opt_out = 0
AND (cv.do_not_solicit = 0 OR cv.do_not_solicit IS NULL)
AND (cv.opt_in = 1 OR cv.opt_in IS NULL)
# total of the 2 fields is greater than 5.
AND (wmf.total_2019_2020 + wmf.endowment_total_2019_2020 ) >= 5
# I took a look to see if there were many that needed deduping and
# this list stood out as being intentionally invalid addresses.
AND email NOT IN (
'na@na.com',
'name@domain.com',
'no@gmail.com',
'no@no.com',
'noemail@gmail.com',
'noemail@noemail.com',
'noemail@yahoo.com',
'none@none.com',
'noneofyourbusiness@gmail.com',
'nope@nope.com',
'nothanks@gmail.com',
'a@b.com'
)
"I chatted with @LeanneS about it and it seems that keeping all communication preferences is okay for now (please correct me if this changes!)
I'm also going to change the address fields to something more human-readable and re-submit a final version once Leanne approves.
@LeanneS is able to access the list through Superset. I did an update to make the export fields more human-readable (see below). I'll do another glance through tomorrow to make sure I'm not missing anything.
SELECT DISTINCT c.contact_id, contact.nick_name, contact.first_name, contact.last_name, a.street_address, a.supplemental_address_1, a.city, state.name AS state, a.postal_code, country.iso_code AS country FROM civicrm.civicrm_contribution AS c LEFT JOIN civicrm.wmf_donor AS wmf ON c.contact_id = wmf.entity_id LEFT JOIN civicrm.civicrm_contact AS contact ON c.contact_id = contact.id LEFT JOIN civicrm.civicrm_address AS a ON (c.contact_id = a.contact_id AND a.is_primary = 1) LEFT JOIN civicrm.civicrm_country AS country ON a.country_id = country.id LEFT JOIN civicrm.civicrm_state_province AS state ON a.state_province_id = state.id WHERE c.receive_date >= '2016-07-01' /*Pull 3 prior years*/ AND c.total_amount >= 1 AND c.total_amount <= 4.99 /*Limits based on donation tiers*/ AND c.contribution_status_id = 1 /*Completed donations only*/ AND contact.contact_type = 'Individual' AND a.country_id = 1228 /*US Only*/ AND a.street_address IS NOT NULL /*Must have a street address*/ AND c.contact_id NOT IN /*Not in Civi lists associated with prior TS imports/exports*/ (SELECT DISTINCT g.contact_id FROM civicrm.civicrm_group_contact AS g WHERE g.group_id IN (802,522,534,535,560,669,791))
Note that the way to make MS Excel (except Leanne's copy) interpret special characters is to add some hex to the start of the file -
https://en.wikipedia.org/wiki/Byte_order_mark#UTF-8
In php that looks like `echo "\xEF\xBB\xBF";` before any other output
The table above is in frdb1003: analytics_ad_hoc.targetsmart_export_june_2020. However, I ended up running a tunnel through R, creating a query, and writing that query output to a CSV on the file server via RStudio. Thanks to @Dwisehaupt for the help figuring out how to connect to a volume from a local application!
I'm happy to talk through this more @Eileenmcnaughton; I'm not sure how things like this have happened in the past and if this is a process improvement.
@LeanneS the CSV is on the file server; I tested it and it opens well for me (after a few minutes).