Adding just a few CID examples we saw discrepancies for, but there are a lot. All had 2019 contributions in EUR but AF_latest_currency was showing up in USD:
7787199
16384762
5037882

Also worth it to note that AF_highest_native_currency reflected the correct currency.

Hey just jumping in to confirm, yes it's looking like the following Acoustic fields are not populating accurately:
AF_latest_currency (civi equivalent = foundation_latest_currency)
AF_latest_currency_symbol (civi equivalent = foundation_latest_currency_symbol)

It also looks like AF_latest_native_amount is rendering in USD.

Please note, when email team references "AF_" we mean Annual Fund fields (aka the "foundation_" fields from the civi export)

Thanks!

I just checked & this IS incorrect in Civi - so the issue is with the triggers.

I think we should consider not having last currency fields in civi & just calculating them in the silverpop export - I feel like we only calculate them in Civi to support the silverpop export & I'm not sure it's giving us benefits

@NNichols @LeanneS @RLewis do you use Last Currency at all

Note as a short term fix I think we'll calculate the field in silverpop regardless but since it seems inaccurate in Civi now I lean towards dropping rather than fixing

Change 621068 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/tools@master] Calculate latest currency within silverpop not civi

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

Change 621076 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/tools@master] Add sql to rebuild the table for latest foundation donations

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

@Eileenmcnaughton Last Currency is used in exports for MGF&E appeal mailings when sending letters to non-US donors that include last donation info. I think that might be the only use case, but the field is used.

Change 621068 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Calculate latest currency within silverpop not civi

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

Change 621076 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add sql to rebuild the table for latest foundation donations

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

@KHaggard @MNoorWMF there should be a full DB upload (maybe more than one... ) - the most recent one should have this data correct. Can you try importing again. Note there is one more field added to the end -modified_date. That's mostly for our purposes but you might choose to map it

I checked Acoustic and Civi this morning, and both are still showing the incorrect value for the latest_currency field.

Jumping in - the nightly recurring data jobs ran several times this morning, but it looks like there's another file (maybe more) in the queue that I missed. I'm manually running it now. Sorry for any confusion. I'll update this phab again when it's finished.

Just ran the latest import and there's a lot of bad data. @Eileenmcnaughton the error is saying: Date provided is not a valid date/format. Valid format: MM/dd/yyyy Could someone fix the date formatting and send the file over again?

Data Job Report:
Contact Source Name: _all_Wikimedia
File Name: DatabaseUpdate-20200819092037.csv

Job ID: 170127475
Job Type: Recurring Contact Source Import @ Wednesday, August 19, 2020 at 2:57:45 PM GMT
Job Status: Complete
Total Rows: 20966335
Total Valid Rows: 12309334
Total Invalid Addresses: 1339
Total Duplicate Addresses: 2
Total Disallowed Addresses: 10732
Total Bad Data: 8644930

I'm wondering if it's either in this line: https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/tools/+/621094/2/silverpop_export/update_table.sql#517 where I'm not sure what e.modified_date is doing
or this line: https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/tools/+/621068/2/silverpop_export/update_table.sql#127 where it looks like we removed the select for d.last_donation_date and I'm wondering either of those are triggering the errors.

Change 621326 had a related patch set uploaded (by Mepps; owner: Mepps):
[wikimedia/fundraising/tools@master] Add back last_donation_date

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

Change 621326 abandoned by Mepps:
[wikimedia/fundraising/tools@master] Add back last_donation_date

Reason:
Not the solution here.

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

Change 621330 had a related patch set uploaded (by Ejegg; owner: Ejegg):
[wikimedia/fundraising/tools@master] Remove modified_date from filtered view

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

Change 621330 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Remove modified_date from filtered view

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

The file @Eileenmcnaughton sent over processed successfully with bad data back to what it normally is. See aggregate row totals below:

Contact Source Name: _all_Wikimedia
File Name: DatabaseUpdate-20200819202333.csv
Job ID: 170143392
Job Type: Recurring Contact Source Import @ Wednesday, August 19, 2020 at 9:34:00 PM GMT
Job Status: Complete
Total Rows: 20966335
Total Valid Rows: 20954223
Total Invalid Addresses: 1339
Total Duplicate Addresses: 12
Total Disallowed Addresses: 10732
Total Bad Data: 41

Great news - so far from my side I checked some contacts and the error is fixed in the above cids that @MNoorWMF listed above. I also ran a query to check for mixed data and the count was small. @MNoorWMF would you be able to cross check some more cids to confirm that it looks right from your side?

@KHaggard in addition to the fixes this ticket relates to the Endowment latest currency data should now be back-populated so can you or @MNoorWMF check on that too?

@Eileenmcnaughton Do you mean the all_funds_latest_currency and all_funds_latest_currency_symbol from ticket T257597 ? I'll check now

@KHaggard yep

Got it, thanks. I ran a query to check for any contacts in en6C and FR who donated within the last 5 years and are not opted out that have blanks for those fields ^. The count wasn't huge, but it returned a bit over 6k records. I'm pasting a few cids from that query below for you to look at @Eileenmcnaughton . Let me know what you think.

1768225
1769498
1765981

Should we break this out into a new task?

@KHaggard so for the first cid the first values rows I see for him look like this

                       ContactID: 1768225
                         IsoLang: en
        all_funds_donation_count: 6.00
   all_funds_first_donation_date: 12/05/2011
 all_funds_highest_donation_date: 12/05/2011
    all_funds_highest_usd_amount: 50.00
       all_funds_latest_currency: USD
all_funds_latest_currency_symbol: $
  all_funds_latest_donation_date: 01/01/2020
  all_funds_latest_native_amount: 20.00

Is that different to silverpop

the csv seems to have the same

1768225,en,6.00,12/05/2011,12/05/2011,50.00,USD,$,01/01/2020,20.00

Ah, ok when I search the database for that contact without restrictions, CID 1768225 has two email address tied to it. Contact hash is also the same. I can only find one email address out of the two in civi. They have different AF_latest_donation_dates

Does this mean it's dedupe?

@KHaggard I think this is a variant of https://phabricator.wikimedia.org/T254304 of some sort - maybe update these findings onto there?

Or create a new phab if it's clearer - it's definitely worth it's own investigation but not strictly related to this

(I think the issue is we aren't culling no-longer-should-be-there rows - needs some thought)

Final thought - if his other email had been re-assigned it probably would have been updated. For better or worse that second record is almost certainly on the suppression list

I agree and yes, sorry that other email address is in fact on the suppression list when I double checked. It's difficult to search within the suppression list because it takes forever to load my search results. I wish there was a way in IBM to filter out suppression list contacts from my queries so it's not as confusing for everyone - may be a future phab in that.

I agree - moving comment to the other task you pinged. Thanks @Eileenmcnaughton

I think there is actually another phab that relates - I thought it was this - https://phabricator.wikimedia.org/T256522 but less sure. @CCogdill_WMF was going to check with legal about using in-silverpop filtering rather than the suppression list

@Eileenmcnaughton @mepps - Hey all updating this task again - looks like the nightly import is showing tons of bad data again, I can track it that it's been doing this over the weekend so I think we need another full file push. Are you able to see on your end if modified date is back in the export? All I can see on my end for the bad data error is: "Date provided is not a valid date/format. Valid format: MM/dd/yyyy"

Ah shoot @KHaggard I'll take a look at the file generated this morning and see if I can track down the problematic field.

Looks like the patch to fix the problematic modifed_date added last week still needed deploying so I'll push that out now and kick off another export job.

Thanks @jgleeson - I ran the export job you sent over and it the bad data issue seems to be fixed. Pasting in rows below. The 1 bad data seems to be a row that did not have an email address. Since the export covers all contact updates in the last 7 days, we should be fine now to continue forward with the frFR send tomorrow.

Contact Source Name: _all_Wikimedia
File Name: DatabaseUpdate-20200824142535.csv

Job ID: 170384902
Job Type: Recurring Contact Source Import @ Monday, August 24, 2020 at 2:43:20 PM GMT
Job Status: Complete
Total Rows: 229160
Total Valid Rows: 229107
Total Invalid Addresses: 2
Total Duplicate Addresses: 0
Total Disallowed Addresses: 50
Total Bad Data: 1

Awesome!