Page MenuHomePhabricator

Civi to IBM export: Ensure CIDs ordered in reverse chronological order
Closed, ResolvedPublic

Description

After speaking with Donor Services, we want to make sure the Civi>IBM export file has CIDs ordered in reverse chronological order from when they were created. This is to ensure we have the most recent donation history for donors who have multiple un-merged CIDs under the same email address.

Setting this as an Unbreak Now due to influx of angry donors receiving emails based on non-recent donation history.

Event Timeline

KHaggard created this task.Nov 19 2019, 8:52 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 19 2019, 8:52 PM
KHaggard triaged this task as Unbreak Now! priority.Nov 19 2019, 8:52 PM
Restricted Application added subscribers: Pcoombe, Liuxinyu970226. · View Herald TranscriptNov 19 2019, 8:52 PM

Some sample CIDs:
24041039
30066653
5396632

These were all merged today by Sandra after she fielded donor complaints.

MBeat33 added a comment.EditedNov 19 2019, 9:24 PM

Many thanks, @CCogdill_WMF and @KHaggard !

Ejegg added a subscriber: Ejegg.Nov 19 2019, 9:53 PM

We can't see anything wrong with the data in the silverpop export table or in the Acoustic UI for 24041039

The merged record for cid 5396632 had a slight discrepancy in the spelling of the email address.
The merged record for 30066653 had the email address recently edited to fix a misspelling.

Since the email addresses associated with the newer donations were misspelled, our export script can't do any deduplication. We're looking deeper into that first one.

Pcoombe removed a subscriber: Pcoombe.Nov 19 2019, 9:54 PM
Ejegg added a comment.Nov 19 2019, 9:56 PM

OK, looks like the merged records for 24041039 had different versions of the email address (but which the email service treats as equivalent).

Regarding the text of this ticket: we only export one row to silverpop for each email address in our database, and we export it with the name address, and contact id of the most recently created contact record that has that email address.

To calculate the latest donation, we look across all the wmf_donor table entries for contacts with that address, and take the donation date, currency, and amounts from the record with the most recent entry in latest_donation_date.

Okay thanks for digging into this and explaining the current prcess! @MBeat33 please have your team chime in if they have other examples they wanted us to look at. It sounds like these were cases of email address typos.

Thank you @CCogdill_WMF

OK, looks like the merged records for 24041039 had different versions of the email address (but which the email service treats as equivalent).

I think it's the Gmail "." error that fed into that case, I will have my team keep an eye out for the occasional bulk email complaints that may be caused by that, and treat them as known small bugs.

MBeat33 lowered the priority of this task from Unbreak Now! to Medium.Nov 20 2019, 11:58 PM
DStrine closed this task as Resolved.Nov 21 2019, 5:09 PM
DStrine claimed this task.
DStrine added a subscriber: DStrine.

I'm going to close this for now. Let us know if there are more reports or something similar.