Page MenuHomePhabricator

Latest currency fields in silverpop export are incorrect
Closed, ResolvedPublic4 Estimated Story Points

Description

In recent frFR emails, DS flagged that donors were seeing USD instead of EUR. No issues reported with how our dynamic content is set up in emails.

When I looked at the contact records in Acoustic and Civi, I noticed that it does say USD, however Mariana noticed that the last donations for contacts who donated last year are all wrong. They are showing up as USD when in fact they had donated in EUR. A quick glance at all the folks we sent to within a single segment mailing shows a large mix of EUR and USD when we usually see predominantly EUR as the latest currency.

Seems that the database has incorrect data in the AF_latest_currency field.

Event Timeline

MNoorWMF created this task.Aug 18 2020, 4:18 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 18 2020, 4:18 PM
MNoorWMF triaged this task as Unbreak Now! priority.Aug 18 2020, 4:18 PM
Restricted Application added a subscriber: Pcoombe. · View Herald TranscriptAug 18 2020, 4:18 PM
MNoorWMF added a comment.EditedAug 18 2020, 4:51 PM

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.

KHaggard added a comment.EditedAug 18 2020, 5:27 PM

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!

DStrine renamed this task from Latest currency fields in Civi are incorrect to Latest currency fields in silverpop export are incorrect.Aug 18 2020, 8:09 PM

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

mepps added a subscriber: mepps.Aug 19 2020, 4:17 PM

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 added a comment.EditedAug 19 2020, 11:23 PM

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

Eileenmcnaughton closed this task as Resolved.Thu, Aug 27, 4:40 AM

Adding 4 story points - was considering 2 but it's one of those days when things seem hard. Maybe it's the 2 chattering tweens

Eileenmcnaughton set the point value for this task to 4.Thu, Aug 27, 4:40 AM
Eileenmcnaughton set Final Story Points to 4.