@LeanneS where should the data pull live on the file server once I gather the data? I'll be working on this tomorrow.

Thank you @EYener! I started a 2021 folder here: Fundraising/Tech/Major Gifts/TargetSmart Update/2021 Update

The below query pulls a total of 832,108 donors. @LeanneS I realize this is lower than expected: there are a number of US individuals who are first time donors in FY2021 who are opted in (criteria mentioned above) who nonetheless do not have a listed street address, mostly due to matching gifts (at first glance).

I've exported the full list in a series of 8 files into the file serve path mentioned above: Fundraising/Tech/Major Gifts/TargetSmart Update/2021 Update

Query below for QA and input; this is also in gerrit.

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,
    LEAST(COALESCE(wmf.first_donation_date, wmf.endowment_first_donation_date),
        COALESCE(wmf.endowment_first_donation_date,wmf.first_donation_date)) AS all_funds_first_donation_date
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 >= '2020-07-01' /* Pull FY2021 donations only */
    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*/
    /* First donation from any fund is in FY2021 */
    AND LEAST(COALESCE(wmf.first_donation_date, wmf.endowment_first_donation_date),
        COALESCE(wmf.endowment_first_donation_date,wmf.first_donation_date)) >= '2020-07-01'
    AND contact.is_opt_out = 0 /* no opt-outs */;

@EYener Awesome, thanks so much for the quick turnaround on this list!!

@DStrine @Eileenmcnaughton As a heads up, we're expecting the amended files by Thursday!

@DStrine @Eileenmcnaughton I've just added the amended files to the same spot on the server - Fundraising/Tech/Major Gifts/TargetSmart Update/2021 Update/Amended files. The fields are all the same as last year, except for one new one, which I just created in Civi: IRA and 401K Decile.

Please let me know if you have any questions, and thank you so much!

I've split off the first 10 & am trying a UI import for them to check out the mappings - the UI selection looks like - note the word 'tab' is written in for the separator, contains headers is checked & it is set to update

@LeanneS - I just imported the first 9 rows using import

https://civicrm.wikimedia.org/civicrm/group/search?force=1&context=smog&gid=1048

perhaps we can connect tomorrow to go through this - - I think they mostly went in ok - I'm gonna check up on 'supplemental address 3' which is not in the import ui

Ok we don't use supplemental_address_3 so that part is fine. For some reason we filled up the data with 'NA' for supplemental_address_1 - that seems to be our doing & I guess we can strip it out of the csvs

Just a note as I saw this pop up - can we change the task title to New FY2021 Donors to avoid confusion? I think I understand from context that this is to be used in FY2122, but my two cents is that it will be hard to tell in the future what links these donors to FY2122. Thanks!

@Eileenmcnaughton Thank you! I'm around my afternoon/your morning to review together. I'll put some time on our calendars.

Some notes from my initial review:

• Most contacts seem to have a "NA" under supplemental_address_1 if it was blank. This shows up in the old address. Could we have the import ignore that value?
• The zero is missing on zip codes that start with one due to a common Excel issue. Example: CID 1378988. I have an Excel trick to update I can show if it's easier to do within the file.

@EYener That was just a typo there. I guess I was already looking toward the next fiscal! :) I've updated the task to reflect FY2021.

@Eileenmcnaughton Also want to note that in the past we've needed to use null or something of the sort to prevent the old supplemental address 1 from showing up in the address block of the new/primary address.

Change 702324 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Prune out 'NA' from tsmart

https://gerrit.wikimedia.org/r/702324

Change 702324 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Prune out 'NA' from tsmart

https://gerrit.wikimedia.org/r/702324

Change 702498 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Use same address setting on dev sites as live

https://gerrit.wikimedia.org/r/702498

Change 702499 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Update our custom field create to use preferred action

https://gerrit.wikimedia.org/r/702499

Change 702582 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Fix custom field create to correctly create option_values

https://gerrit.wikimedia.org/r/702582

I tried to run this today but hit an issue

drush @wmff cvapi TargetSmart.import_job csv=/path-to-civi/files/targetsmart/targetsmart_export_june_2021_1_first10_rev.csv batch_size=1 mapping_name=2021-targetsmart  add_to_group_name=818 identifier=1 
Row failed to import Invalid value for field(s) : field ID; ; field  [error]
ID; ; field ID; ; field ID; ; field ID; ; field ID; field ID; field
ID; Array
(
    [0] => Invalid value for field(s) : field ID; ; field ID; ; field
ID; ; field ID; ; field ID; ; field ID; field ID; field ID;

I've been trying to replicate on my local dev but struggling with inconsistencies in dev set up which are hopefully addressed with https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/702582 and https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/702498

Change 702583 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Update targetsmart references for 2021

https://gerrit.wikimedia.org/r/702583

Change 702587 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Add new dev field to support targetsmart testing

https://gerrit.wikimedia.org/r/702587

Change 702587 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add new dev field to support targetsmart testing

https://gerrit.wikimedia.org/r/702587

Change 702583 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Update targetsmart references for 2021

https://gerrit.wikimedia.org/r/702583

This is now done - the data is in & the jobs are off

Change 702498 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Use same address setting on dev sites as live

https://gerrit.wikimedia.org/r/702498

Change 702582 merged by Eileen:

[wikimedia/fundraising/crm@master] Fix custom field create to correctly create option_values

https://gerrit.wikimedia.org/r/702582

Change 702499 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Update our custom field create to use preferred action

Reason:

I think this was otherwise merged

https://gerrit.wikimedia.org/r/702499