Page MenuHomePhabricator

Civi x Acoustic Database QA Flags
Closed, ResolvedPublic

Description

Starting today and over the next 5 business days, six people are running QA in the database to ensure things look good before we launch Big English soon. Creating a ticket to keep track of findings.

I ran a pretty thorough QA and found some interesting flags that I want to mention below, but overall I think everything looks fantastic.

New Status showing as blank in Civi
These are correctly labeled in Acoustic as "New" but Civi UI doesn't show anything for status.
CIDs: 64257386, 64261641, 61327965, 56092642, 64263346, 64262193, 32187153, 64263066, 64273884, 64214308

Civi x Acoustic data mismatches
A few strange cases of mismatching data:
35918419 - Labeled as Recurring Donor in Acoustic, but not a recurring donor in Civi.
59250832 - Labeled as a Major Donor in Acoustic, but has no contributions in Civi. Should NULLs in Civi overwrite Acoustic?

Some segment/status outliers
Searched for anyone who doesn't have any of the 20 possible labels, and got a scoop of donors that for whatever reason got stuck and haven't updated since Jun 21, 2024, even though we ran a full db overwrite the other day. Dropping all of them below:

61936037
8090444
12522325
1840065
12884388
41953210
42034634
58590405
18999430
50320738
3254995
35027618
52089835
62768725
62667164
53940107
54488102
23498514
58570274
62632871
54107090
61102307
44639205
50193811
61980745
62996739
60845717
62825957
33622530

Event Timeline

Thanks @KHaggard - please keep adding findings and FR-Tech will continue to review!

Thanks @AKanji-WMF ! I just updated the task description removing four cids as they were small and resolvable on my end. :) Thanks for keeping an eye on this !

  1. New Status showing as blank in Civi - this was just a problem with the label not being attached to the new number (25) correctly & is fixed now
  1. Acoustic mismatches

35918419 - contact was correctly showing recurring as they were being 'Acoustic merged' into another version of them who had recurrings - I have merged the contact in Civi now

bobby is picking up Major Donor from his foundation that shares his email - probably a bigger discussion but the 2 are being Acoustic merged to get that status

  1. outliers

61936037 is deleted & merged into 30580937. The email of the deleted contact is different to the one that was kept so the contact in Acoustic has probably been added to the suppression list (which we do so they don't get further emails on the secondary address - although they do seem to have had some around about the time they were merged - probably a timing issue

Looking at the others - a bunch are deleted in Civi so there would be nothing to update

select t.id as id_from_acoustic, c.id as id_in_civi, is_deleted FROM temp_ids t LEFT JOIN civicrm.civicrm_contact c ON t.id= c.id ORDER BY is_deleted;
+------------------+------------+------------+

id_from_acousticid_in_civiis_deleted
52089835NULLNULL
53940107NULLNULL
54488102NULLNULL
8090444NULLNULL
12522325NULLNULL
60845717608457171
61936037619360371
62632871626328711

Which leaves the table below - I dug into a handful & they were all oddballs & not seemingly a systemic issue so I'm not sure it's worth going through all of them

Civi recordAcoustic recordstatus
12884388253D701597032558appears to be correct in Acoustic now
54107090CiviCRM contact no longer has an email & is no-longer syncable
41953210446706796219In the past donor was Acoustic-merged with 38761373 - that changed because their email changed but the combo was still uploaded. I merged them in Civi since I had done the digging
61102307as with the previous one - this was an RML contact who should have been merged in the past with a contact sharing an email, who since changed their email.
42034634should have been merged with https://civicrm.wikimedia.org/civicrm/contact/view?reset=1&cid=35377931 as used to share an email
446392050
585904050
501938110
189994300
619807450
503207380
629967390
32549950
350276180
628259570
336225300
627687250
626671640
234985140
585702740
18400650

@KHaggard see above for comments - I fixed the label on 'new' - the others didn't seem to need anything - I didn't check all the rows from the last table

Thank you @Eileenmcnaughton ! I appreciate you looking into this and I agree there's no systemic issues so far, just oddball cases as you mentioned.
We have a couple people still doing their QA so I will flag if anything else surfaces by the end of the week.

KHaggard claimed this task.

Everyone has wrapped up QA and everything looks great! Thank you :) Resolving this now