Page MenuHomePhabricator

New list pull from Civi
Closed, ResolvedPublic

Description

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!

Event Timeline

LeanneS created this task.Jun 4 2020, 8:02 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 4 2020, 8:02 PM

@EYener actually just volunteered to pull this. Fr-tech will need to do the import. Let us know when you need our help!

DStrine assigned this task to EYener.Jun 4 2020, 8:28 PM
DStrine moved this task from Triage to Blocked or not fr-tech on the Fundraising-Backlog board.

Cool! Thanks so much @EYener!

@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.

Sounds great! Thanks @EYener.

@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).

@EYener Thank you! I was able to open it easily. Appreciate your help!

EYener closed this task as Resolved.Jun 18 2020, 4:05 PM

Great, thanks! I will mark this as resolved (I think I can do that...)