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 triaged this task as Unbreak Now! priority.Nov 19 2019, 8:52 PM

Some sample CIDs:
24041039
30066653
5396632

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

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.

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 claimed this task.
DStrine subscribed.

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