Change 634392 had a related patch set uploaded (by Ejegg; owner: Ejegg):
[wikimedia/fundraising/tools@master] All donation counts are int unsigned

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

Change 634392 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] All donation counts are int unsigned

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

Escalating the priority bc of the short window to fix and confirm the fix before these sends go out Sunday. Thanks team.

I just ran this on on staging to reflect the change

alter table silverpop_export MODIFY column donation_count INT UNSIGNED NOT NULL DEFAULT 0, MODIFY column endowment_number_donations INT UNSIGNED NOT NULL DEFAULT 0;

If it's done before the job kicks off it should alter it in time

& this would be the other query to implement @Ejegg's patch

alter table silverpop_export_stat MODIFY column foundation_donation_count INT UNSIGNED NOT NULL DEFAULT 0, endowment_number_donations INT UNSIGNED NOT NULL DEFAULT 0; to do after

OK - those queries ran - so check tonights update & see how it compares

The recurring jobs ran successfully and so I checked the database and at a glance it looks like most contacts still have both_funds_donation_count in decimal format. I think the fix may have only applied to recent contacts, so we will need a full file push of _all_wikimedia to correct the format across the board. Could this be deployed today?

I just replaced the view

CREATE OR REPLACE VIEW silverpop_export_view AS
SELECT ContactID,
IsoLang,
all_funds_donation_count,
all_funds_first_donation_date,
all_funds_highest_donation_date,
all_funds_highest_usd_amount,
all_funds_latest_currency,
all_funds_latest_currency_symbol,
all_funds_latest_donation_date,
all_funds_latest_native_amount,
contact_hash,
country,
email,
employer_id,
employer_name,
endowment_first_donation_date,
endowment_highest_donation_date,
endowment_highest_native_amount,
endowment_highest_native_currency,
endowment_highest_usd_amount,
endowment_last_donation_date,
endowment_latest_currency,
endowment_latest_native_amount,
endowment_number_donations,
firstname,
foundation_donation_count,
foundation_first_donation_date,
foundation_has_recurred_donation,
foundation_highest_donation_date,
foundation_highest_native_amount,
foundation_highest_native_currency,
foundation_highest_usd_amount,
foundation_latest_currency,
foundation_latest_currency_symbol,
foundation_latest_donation_date,
foundation_latest_native_amount,
foundation_lifetime_usd_total,
foundation_total_2014,
foundation_total_2015,
foundation_total_2016,
foundation_total_2017,
foundation_total_2018,
foundation_total_2019,
foundation_total_2020,
gender,
lastname,
latest_optin_response,
postal_code,
prospect_birth_date,
prospect_charitable_contributions_decile,
prospect_disc_income_decile,
prospect_estimated_net_worth,
prospect_family_composition,
prospect_income_range,
prospect_occupation,
prospect_party,
state
FROM silverpop_export_view_full

& now running

 nohup run-job silverpop_emails_export_only
silverpop_emails_upload_files

The view will be overwritten back to incremental next run

@KHaggard there should hopefully be a new full output uploading.... It would be an additional file

@KHaggard that first one failed, but I kicked it off again and it uploaded correctly the second time.

the first 10 or so rows all have integers with no decimal point in the 'all_funds_donation_count' column

hi @Ejegg , just a quick note: we're looking for integers with no decimal point for both_funds_donation_count field

doing a quick search of the database (note: not a query) within Acoustic and i still see a handful of folks that have x.00 but much less than before.

search results for 1.00 showed 7 contacts
2.00 showed 33 contacts
3.00 showed 32 contacts
4.00 showed 18 contacts

it's a lot less than before so I feel happy about proceeding with the send later today (21:00 UTC Oct 18 // 10:00 local time New Zealand Oct 19), and the sends going out on Monday.

Thanks everyone!

@MNoorWMF - it's a little confusing but I want to verify that "all_funds_donation_count" is the same as "both_funds_donation_count". Civi's field names are correctly mapped to Acoustic, but they have slightly different names.
Civi names:
all_funds_; foundation_; endowment_; prospect_
Acoustic equivalent:
both_funds_; AF_; endowment_: TS_

Thanks for the files @Eileenmcnaughton and @Ejegg - I'll dive into the database today.

@MNoorWMF - Can you show me how you checked this? I unfortunately cannot create a query to check for formatting changes.

In T265700#6558238, @MNoorWMF wrote:

doing a quick search of the database (note: not a query) within Acoustic and i still see a handful of folks that have x.00 but much less than before.

search results for 1.00 showed 7 contacts
2.00 showed 33 contacts
3.00 showed 32 contacts
4.00 showed 18 contacts

I manually checked the first 100 contacts of every en6C base query and it looks like the formatting for both_funds_donation_count is correctly displaying whole numbers instead of decimal. I also exported a CSV of one of the segments we sent to today and searched values for anything with a "x.00" and did not find any. I think this ticket is solved, but I want to check with @MNoorWMF about the above statement^ before we close out.

ohh thanks for the clarification @KHaggard re: all_funds_ and both_funds_. you mentioned that before and I forgot the two systems have different nomenclature. will chat with you on Slack about where I found those numbers.

Thanks @MNoorWMF for sharing how you found those contacts in the database. I looked at the 1.00 and 2.00 donation count contacts and most of them are old donors with both_funds_latest_donation_date predating 2015, which we don't target for emails anymore. There are a few that we've sent to fairly recently, so I'm dropping some CIDs below for fr-tech to investigate. These contacts are getting emails from us, but did not get updated with the full file push and I'm having trouble figuring out why.

39304535
22660624
28077406
18203378
43893568
45911670
39916155
29396677
25388232
39385003

So looking at the first one - he has 2 references in silverpop

This is for his current primary address
https://engage4.silverpop.com/searchRecipient.do?action=edit&recipientId=482245963665&listId=9644238

And this is for is old primary address (with the old style numbers)
https://engage4.silverpop.com/searchRecipient.do?action=edit&recipientId=310967082458&listId=9644238

The old primary address would be added to the suppression list I believe (@Ejegg @mepps) - even though he has what looks like another duplicate record in 24487031

This seems like this issue was fixed as expected but there are still some unclear things around the suppression list still (this might not be the Phab to dig into that on)

The second one

contact id 22660624

https://engage4.silverpop.com/lists.do?action=listSummary&listId=9644238

has been merged in the silverpop export into 42965336 - so there are no updates going out on 22660624
(they are not actually merged in civi)

The record in silverpop has a different - presumably old - email - which is the same as 16773866

@Eileenmcnaughton I'm trying to read from this if the primary issue is resolved now?

@mepps Hi! The primary issue is resolved yes, but there's some straggler contacts that didn't get the update that we're trying to figure out. I think we could probably close this one out and then figure out which phab to put these edge cases in. I'll do some digging around!

Thanks for the quick response @KHaggard! I'm trying to figure out where to direct my efforts :).

For now, I'm moving this to "Done" on the workboard because it looks like we might open another task for final cleanup. Feel free to move it if I'm wrong.

The conversation about the few un-updated contacts has been moved to this phab T254304 so we can "officially" close this one out. Thanks!