@bsisolak is checking on this issue with Acoustic so we can decipher the error codes. Thanks!

Ok update from me, we can definitely do some cleanup in Acoustic so that:

1. Existing records with no donation is updated with Status/Segment "Non Donor" 1000
2. Newly created records that submit an RML form with no donation history go through an automation to label them as "Non Donor" on an ongoing basis.

Thanks. I'm meeting with Brian tomorrow to see if we can set a default field value for all non-donors to have "Non Donor" segment/status and "1000" ids. I know we can do that for current records, but checking if we can make it default for every newly created contact.

Hi @Eileenmcnaughton I just unlocked your account, Damilare's account, and Wenjun's account. Let me know if you still can't log in.

Hi all, wanted to check on the status of this. Any chance we can get this into the current or next sprint? :)
cc: @spatton @Ejegg @AKanji-WMF

Also @Eileenmcnaughton Can I just confirm: you would like me to run a query in Acoustic for all non-donors and manually set their status and segment to 1000 for Non-donor? And this will only need to be done once because Civi can do per record updates as needed from there? Just making sure I understood you correctly. Thanks!

In T363959#9827632, @Eileenmcnaughton wrote:

@KHaggard we are also pushing up slightly different words - Non donor vs Non Donors - should we consolidate that? if so how should we capitilize?

1. Non donor
2. Non Donor

Yeah, actually if they are the same, then 1000 for both status and segment makes sense to me. Thank you.

Thanks all. Quick question for you @Eileenmcnaughton - Mariana brought to my attention that Major Donor segment and Non-Donor Status share the same id = 100.
Can I request that we make sure all statuses and segments have no overlap in id? I think it would make things clearer and reduce the need for our teams for our teams to re-clarify "which 100" often.

Also here's a screenshot in case it helps:

Ah I see. I think it would be worth asking Sheetal and Erica to weigh in on it. I don't think Sheetal has a phab handle yet so tagging @ERoden-WMF for now.

There are certainly other ways we can isolate Non-donor RML sign-ups in Acoustic, we've done that in the past via "both_funds_latest_donation_date is blank".

Also noting other bugs with the segmentation framework, this is another one:
T363959

deleting my last comment above since it's a different issue; new ticket here: T365523

@Ejegg Honestly, to me it's not super clear how that would work but I think it's possible to re-key the database with CID instead of email. I think it will be quite a process though that we'll need to work with Brian and Trilogy on. Assuming that we will need to re-key Acoustic before we set up any data flows from Civi. Is that correct, @bsisolak ?

Got it, thanks @Eileenmcnaughton Do you have a recommended timeline for an outage? End of June maybe? Let me know and I can at least add in a note for the rest of the team on when to expect segment instability and slowness. Thanks

Hi @AKanji-WMF - yes we already looked at this together with DR before the ticket was logged and we've confirmed on our side that all our queries contained the ruleset:

country is equal to one of the following: US GB CA

The cases mentioned above are contacts with a pattern of weird home address switching back and forth that changed their home country in Acoustic at the time or before the time of sending. But it wasn't done by an agent. Hope this helps.

Here's a file of CIDs to look at prioritizing dedupe / triaging how we can fix this long term with fr-tech.

Quick update: Did a spot check with some names in Acoustic and it looks good from my side :)

Ok, great. @Eileenmcnaughton the data jobs for today completed and all 4 of them look good to me. The full file Unsubscribes looks like what we'd expect too:

I think we exported the full Unsubscribes list because it wasn't causing any slowdowns to do it and it helps us make sure there isn't errors like that one time in 2018 where we saw the MSL shrinking over time and we only were able to catch and fix it because of the full data send summary reports.

Thanks @Eileenmcnaughton - so, the databaseupdate csv processed fine to me, summary below:

Alright, everything looks fine to me @Eileenmcnaughton .

OK @Eileenmcnaughton we were able to successfully map everything. Just running the files that were in queue now. I'm going to do a quick check that they all complete successfully and then I'll comment again here.

Ok cool. I'm hoping to get other members of the email team to watch me map the files so they know how to do it in the future. Since it's getting late my time, I'm thinking I'll push the mapping to tomorrow at a time where others can watch "live".

Oh ok. It is possible to cancel that? Acoustic is at a place now where if it attempts to run the "old regularly scheduled" file it will try to add back the fields I just deleted, which could take several hours to re-delete. I remember that happened in the past once.

Right now it's telling me there's nothing on FTP. @Eileenmcnaughton Just an FYI.

Awesome, thanks @Eileenmcnaughton. I'm going to grab a smoothie really quick but when I get back I'll map it. Please let me know when it's up. :)

Alright I'm all set now and ready to map whenever you are @Eileenmcnaughton Below is what I named everything and it's field type:

Actually, let me clarify, you can send over the mapping file whenever you're ready, I just won't be able to map until closer to my end of day since reorganizing the fields is taking Acoustic a long time to process.

Hi, so I've paused the data jobs in Acoustic now and I'm changing the existing fields to the new names and purging the old values. I'll update when finished and when I'm ready for the mapping file. @Eileenmcnaughton

Ah ok got it. I just did a poll with the email team on which one makes more sense to them and they all unanimously voted for:

@Eileenmcnaughton Understood. Yeah, let's pin this for a later time. For now, I think it would be fine to leave Civi as-is but just rename the export file / job with whatever fits with Acoustic (as we have been doing).

Ok. For now, I think I want to stick with how I wrote the fiscal year totals above.

Got it, thanks. It might work regardless of how you export the whole numbers, but I just wanted to let you know that's how they present in Acoustic in case that matters. Thanks @Eileenmcnaughton .

I have a quick question. I was going to set numeric for the field type for the id fields. Acoustic formats numeric fields like this (e.g. 32.00) with two spaces after the decimal point. Does this work with you @Eileenmcnaughton or is there a reason we should format those numbers as a text field to retain the whole number as they present in Civi?

Oh yeah thanks, that was what I was wondering. I like "id" better too. Ok so they will be:

Yes those @Eileenmcnaughton . What do you think makes sense as the export name for those? Something like:

Thanks @Eileenmcnaughton - I think we would actually like to do both, so 4 new custom fields actually. What do you currently have those numeric values labeled like in Civi?

Hi @AKanji-WMF - Circling back over to this. I had a conversation with @EWilfong_WMF and I think we are good to move forward on this. I don't believe this would overlap with the coding structures work Nora is working on, because that project is about identifying the type of fundraising campaign and not specifically about sourcing the banner a person signed up for RML before donating via RML email.

Thanks @AKanji-WMF for your patience. Danny is back in office today and I spoke with him in a 1:1. He wants to chat with Brian about timeline if we approached this in the more streamlined way vs. our usual add-and-export-over way and I think that conversation is happening on Thursday. We should have a clearer way forward after that, I'll touch base here again next week on the decision point. Thanks all!

Ok. I'll chat with Danny next week about this, but most likely I don't think we will be re-architecturing anytime soon. I'll circle back next week.

Thanks @Ejegg - that's an interesting link, would that replace the need for adding more custom fields? Ideally, it would be awesome if we didn't have to add more fields to the nightly export, but if that's the only way / best way, we can just add them to the existing file DatabaseUpdate next time we're going to remap.

Do they match the IDs in the main database, _all_Wikimedia ?

@Ejegg Hi, I just deactivated that program in Acoustic.

This looks to be resolved as we had successful import jobs today. Thanks everyone! Resolving this task.

Thanks @Cstone :) Figured it was related to upgrade work as usual with the maintenance weeks but I appreciate you for confirming and @LMedley-WMF for logging this just in case! <3

Hi @AKanji-WMF - Apologies I just saw this. Well, we're back in code-freeze time + email-team-code-freeze time, so this can't move forward right now and has to wait until later.
Besides, it looks like we're also waiting for @NNichols to weigh in.
And I'm not sure at this point if the coding structures project includes this and I may need to reevaluate where this task lands in the fabric of the other data optimizations that surfaced this year. I'm going to make a note for myself to check in with Eric about this soon.

I'll also add that the reason why this is the first time we're seeing this is that we usually don't rely much on suppression lists because we handle everything with queries, however, I always thought of suppression lists as a possibility to use if we have rush jobs and need to suppress a group of people quickly. I didn't realize that if we deployed an email and the suppression was used that Civi would pick it up and translate it as unsubscribes. Definitely don't want going forward, as we may need the flexibility to suppress people for certain sends and not other sends.

Thanks @Eileenmcnaughton . Yes, just confirming that any fields currently in the export file that are being removed / changed / added should (for now) remain in the export file with value=0 until we can find a time after Big English to roll out the changes in Acoustic (I think just swapping the old CY totals with the FY totals). Reasoning is because changes in the export file, no matter how minuscule, tends to make the job fail until Acoustic matches it exactly.

Thanks @Ejegg . I'll leave it up to @krobinson and her team to determine the urgency around updating this now vs. 24 days.

All four data jobs for Oct 3rd have completed successfully. We will proceed with scheduling emails again.

Awesome ticket, @ppenloglou Thanks for logging this.
I'm running the jobs that Jack sent over now.

Oh ok that works. Thanks @Eileenmcnaughton

Just a quick little reminder, please hold off on deleting the few CY fields that are still in the export to Acoustic until we replace them with FY... and then we could blow them away after. :)
I'm pretty sure deleting them will cause the import to fail until we remap Acoustic (even with deletions) and I want to avoid remapping anything until after Big English.

Given how this is running again, assuming I can resolve this ticket?

Just ran the Acoustic jobs and everything is coming through normally. Thanks @Ejegg !

Hi @Ejegg - quick clarification, our emails targeted AF_has_active_recurring_donation = No. So the 'pending' donors have "No" as their value currently.

Hi @Ejegg - we use the custom fields in Acoustic. Specifically:
AF_has_active_recurring_donation
AF_recurring_latest_donation_date

Actual date of when clicktracking was turned on: Dec 20 2022

Hi @AnnWF - I think you meant to ping me in your comment, correct?

Yes @AKanji-WMF - this ticket was resolved last year. We have the cert and have had click tracking on for a handful of months.

@Eileenmcnaughton I might need more insight on how we're all aligning for the function of these statuses and segments, but my assumption with the rollout of these new columns is that we would want the most current value possible for our email sends. If a donor was Lapsed June 30 but changed to Active July 1, we would want to act on them as an Active donor for the new FY.

Got it, Thanks @Cstone - we will continue to monitor this week and work with you where needed!

We ended up unscheduling the email sends set to deploy today in an abundance of caution until we were able to run the jobs manually.

Thanks for logging this ticket @LMedley-WMF
I logged into Acoustic and tried out running the jobs manually and the civi export files were found. This is the second time recently we've had a timing issue so I'm going to tag @AKanji-WMF

Ok I was able to manually run the jobs and the files were there. This was just a timing issue - probably something caused the Civi export to take a little longer and it missed the window of time to get to the FTP.
I'm going to resolve the ticket for now, but let's keep an eye on it in case the same issue repeats over the weekend and into Monday.

Thank you @LMedley-WMF !
Looks like the files weren't there for Acoustic to pick up. I'm going to test out manually running the jobs now to see if the files are there now. Will update again shortly.

Thanks for summarizing @Eileenmcnaughton - That list of fields looks right to me.

Just for clarification, Joseph Mando sent me a Delve list to suppress from our email sends. We don't have any room in the calendar for the next few months to import any new fields into Acoustic, so please hold on kicking off any nightly import changes that would affect Acoustic. Thanks!

Ohh ok thanks for clarifying @Eileenmcnaughton I have Civi access and I'm looking at those pages now.
I wasn't expecting two fields for these definitions, but it does makes sense to separate them like that.
I'm surprised to see Deep lapsed, Ultra Lapsed, and NonDonors underneath "Segments" - those seem like just Status fields to me - just my two cents though.

Thanks @Eileenmcnaughton - Okay so just to recap so I understand: you're saying that
donor_status can be a numeric value (i.e. 100, 200, 1000)
donor_segment can be a text string label (i.e. "Major Donor")

Oh ok, can you give a quick value example of the difference between donor_segment and donor_status? Are they both text fields?

Hi @Eileenmcnaughton The removals make sense to me. We haven't been using the calendar year USD total fields at all, but perhaps if we switch it to fiscal year USD totals that would be more useful. Is that possible to do?

Hi @AKanji-WMF - Hmm I'm not sure I haven't seen this task before. I don't manage the Civi TY emails anymore so I'll loop in @NNgu-WMF
Hey Natalie, are you able to send yourself the monthly convert test emails in Civi?

Hi @AKanji-WMF - I don't think this segment ever made it into Superset. However, this is so old now that it it's not worth retroactively working on it, plus I think the data for this send has already expired from Acoustic anyway. My opinion is that we close this ticket out.

Thanks @AKanji-WMF ! I feel like I wrote a lot in my last comment so the over-communicator in me wants to just plainly restate for clarity:

• We only need to stop exporting the MatchingGifts-* CSV to resolve this ticket. No other work is needed at this time. :)

Hi all, just following up. @NNichols and I chat over slack and here is more context from Nora:

Ok, thanks @Eileenmcnaughton . I just read the documentation and confirming on my side that those last two rows in the chart don't exist in Acoustic. We never received a request to create the columns: minimum_gift_matched_usd, match_policy_last_updated.

Thanks to the work of @Cstone and @jgleeson and @AnnWF we're back in business! Copying the job data summary for the Unsubscribes-* List to celebrate because we now have a reduction of bad data (it was 520 and now it's 3, yay!) The rest of the jobs for DatabaseUpdate-*, Optout-*, and the new ChecksumEmails-* have processed successfully. Thanks team!

Thanks @Dwisehaupt ! We have emails scheduled to go out on Monday so I'm hoping we can resolve this today. We didn't receive any files today for June 2nd - will those be coming through in the next couple hours? Thanks