Also attaching a screenshot of the total number of contacts we had queried (from task T293587).

From the meeting today:
Fr-tech could us a description of how/why acoustic sets a "reply mail block"
Email team with come back with a list of domains to retry.

We should not retry

• anyone who has actually opted out
• anyone who now has a new primary email address which has not been blocked or bounced in any way.

What is a reply mail block?

• Generally because a mailbox provider thinks we’re delivering spam, it’s a type of soft bounce
• (Note from fr-tech) Sometimes silverpop initiates the reply mail block - The mail block is actually coming from the domain

According to https://wikitech.wikimedia.org/wiki/Fundraising/Data_and_Integrated_Processes/Acoustic_Integration, the On Hold status was built from Hard Bounces and Blocks. We want a list of people put on hold as a result of a reply mail block NOT a hard bounce
We want the max date a contact was in a reply mail block event, and the date they were marked on hold in Civi
Example case: Civi contact ID 13349

So @nisrael needs a list of civi record in "On Hold" who got on hold via a reply mail block. Can this we created in searchkit?

@nisrael - this search should get all the ones on hold who have had a reply mail block. We don't have a way to 100% confirm the reason they are on hold but probably in most cases it would be

https://civicrm.wikimedia.org/civicrm/admin/search#/create/Contact?params=%7B%22version%22:4,%22select%22:%5B%22id%22,%22display_name%22,%22GROUP_CONCAT(DISTINCT%20Contact_Email_contact_id_01.email)%20AS%20GROUP_CONCAT_Contact_Email_contact_id_01_email%22%5D,%22orderBy%22:%7B%7D,%22where%22:%5B%5B%22Contact_MailingProviderData_contact_id_01.event_type%22,%22%3D%22,%22REPLY%20MAIL%20BLOCK%22%5D,%5B%22Contact_Email_contact_id_01.on_hold:name%22,%22%3D%22,%22On%20Hold%20Bounce%22%5D%5D,%22groupBy%22:%5B%22id%22%5D,%22join%22:%5B%5B%22MailingProviderData%20AS%20Contact_MailingProviderData_contact_id_01%22,%22INNER%22,%5B%22id%22,%22%3D%22,%22Contact_MailingProviderData_contact_id_01.contact_id%22%5D%5D,%5B%22Email%20AS%20Contact_Email_contact_id_01%22,%22INNER%22,%5B%22id%22,%22%3D%22,%22Contact_Email_contact_id_01.contact_id%22%5D,%5B%22Contact_Email_contact_id_01.is_primary%22,%22%3D%22,true%5D%5D%5D,%22having%22:%5B%5D%7D

Thanks @Eileenmcnaughton! Does this search exclude hard-bounced contacts? According to https://wikitech.wikimedia.org/wiki/Fundraising/Data_and_Integrated_Processes/Acoustic_Integration, the On Hold status was built from Hard Bounces and Blocks. Are Hard Bounced contacts that are marked on hold excluded from this search?

Hi @Eileenmcnaughton, Katie and I are planning to upload the "On Hold" contacts from the above search query during our Maintenance Week (May 16 - 20). Do we need to remove these contacts from being on hold in order to re-upload them to our Acoustic database? If so, would we be able to do that during the Maintenance Week?

I can answer the first question @nisrael !
We'll need Eileen or someone to un-hold the contacts once we're ready. We don't need to re-upload any records, we just need to purge all of the on-hold records out of the MSL and _all_Wikimedia, then the un-holded contacts will import back into Acoustic correctly the following day. The un-holding process triggers Civi to send them over to Acoustic for 7 days.

Hi @Eileenmcnaughton confirming if you or someone on your team would be available during the maintenance week?

@nisrael sure - but I think you might not need us - you can remove the on hold from contact in bulk from search results (which is what we would do too).

However, I just need to clarify - the MSL - the entire calculated MSL is uploaded each night - so there is no timing issue around that - but @KHaggard what are you going to purge from _all_Wikimedia? Is the issue that we are adding the on_hold from that second unsubscribes import not the nightly import so they are more sticky?

Thanks Eileen for clarifying! And a heads up that Katie will be back in office next week.

Hi @Eileenmcnaughton - Yes I have to purge the on-hold list from _all_Wikimedia too because we have them labeled as "opt out" in that database too (along with the MSL database). There is no easy way to switch their label back to "opt in" inside _all_Wikimedia so we have to resort to bulk deletion, and then Civi will re-insert them properly as opt-ins again for 7 days.

Hi @nisrael ! Let me know when you're ready to start deploying this task.

I'm happy to run the purges in Acoustic, but I'll need the list.

Thanks @KHaggard for the ping. I am looking at the query that was provided and something strange has happened. I used to see around 650k contacts in the query, but now I'm only seeing 37k??

@Eileenmcnaughton do you know what could have caused that? I'm using this query, which you provided earlier in our chat.

https://civicrm.wikimedia.org/civicrm/admin/search#/create/Contact?params=%7B%22version%22:4,%22select%22:%5B%22id%22,%22display_name%22,%22GROUP_CONCAT(DISTINCT%20Contact_Email_contact_id_01.email)%20AS%20GROUP_CONCAT_Contact_Email_contact_id_01_email%22%5D,%22orderBy%22:%7B%7D,%22where%22:%5B%5B%22Contact_MailingProviderData_contact_id_01.event_type%22,%22%3D%22,%22REPLY%20MAIL%20BLOCK%22%5D,%5B%22Contact_Email_contact_id_01.on_hold:name%22,%22%3D%22,%22On%20Hold%20Bounce%22%5D%5D,%22groupBy%22:%5B%22id%22%5D,%22join%22:%5B%5B%22MailingProviderData%20AS%20Contact_MailingProviderData_contact_id_01%22,%22INNER%22,%5B%22id%22,%22%3D%22,%22Contact_MailingProviderData_contact_id_01.contact_id%22%5D%5D,%5B%22Email%20AS%20Contact_Email_contact_id_01%22,%22INNER%22,%5B%22id%22,%22%3D%22,%22Contact_Email_contact_id_01.contact_id%22%5D,%5B%22Contact_Email_contact_id_01.is_primary%22,%22%3D%22,true%5D%5D%5D,%22having%22:%5B%5D%7D

@nisrael is looks like you have the ones who are NOT on hold there

It worked for me when I made sure the on hold box was properly populated

Ah got it, thanks Eileen! I'm planning to download these folks as a CSV from Civi today.

Sorry @Eileenmcnaughton one more question. I'm trying to download the 600k contacts from Civi, but I think the file size is too large that my computer isn't able to download it. Do you by chance have any suggestions on how to get these contacts from Civi into a CSV?

@nisrael what is the reason to download them all? I thought the idea was to unhold them in CiviCRM & then that would flow into Acoustic - I'm trying to trace this back to what the original goal was as my understanding there was an intention to selectively unblock some domains (like the Comcast one) where we had reason to think they were blocked in error.

But you seem to be talking about 600k emails? Re-enabling 600k emails where the vast majority are undeliverable could impact on our domain's reputation / our ability to get all our emails delivered so I'm just trying to pick up the thread of this

@Eileenmcnaughton adding them back into our database as a contact list will ensure that we can track and handle them separately. The goal is to send the en6C folks from this group (about 450k donors) a re-engagement email before their Annual Appeals. Acoustic also filters all undeliverable addresses (addresses that have previously hard bounced) when a CSV file is uploaded as a contact list.

Essentially, we would like to upload these folks into Acoustic, but still be able to handle them as their own group separate from the rest of the contacts in our database.

@Eileenmcnaughton are there perhaps parameters I can add to the search query to reduce the size of the file download size? Such as 1) donors only being English language, 2) only being from US, AU, NZ, GB, IE, CA?

@nisrael could you re-explain why you think such a large group are worth re-trying?

If a large portion of these are blocked for a good reason (i.e. the person has blocked us in their account) resending some of these could hurt our or acoustic's reputation.

Also if the re-activation emails are all sent all at the same time and many of them are still legitimately block this could have a more dramatic, negative effect on our rating with a given email system.

So if there is a good chance we will actually get through to most of these people you still might want to phase this. If a large portion of these are still legitimately blocked or somehow undeliverable, even a phased approach could hurt our reputation.

On the filter question - I've saved the search & added the language filter + filtered out email addresses that don't start with a letter
https://civicrm.wikimedia.org/civicrm/admin/search#/edit/569

Hi @DStrine these donors are reply-mail-blocks, which is different than a user-generated block. The reply-mail-block aren't user-generated and are either caused by the domain or by Acoustic itself. Reply-mail-blocks can be temporary (as well as lifted). I've been discussing how to handle these folks with our consultants at Trilogy for several months and they highly recommend that we attempt to email these folks again. Essentially, they recommend treating a reply-mail-block like a soft bounce where it is recommended to re-attempt emailing the recipient.

I understand the nature of reply mail blocks. I was interested in the what new information you have that makes you interested in retrying them.

You're welcome to just try if you want. Fr-tech is recommending that the retry emails go out in small batches and you monitor the performance carefully. Even if a small portion of these were still blocked, this could negatively affect how acoustic is viewed by these email systems. If your team and trilogy understand the risks/consequences then we'll stop bugging. We thought it was worth mentioning.

We realized that a lot of folks were lifted off of these blocks. For example, about 30k Comcast folks experienced a temporary block that has since been lifted, but since the block event occurred they were marked on hold and never emailed again.

And agreed, we will definitely be careful in how we deploy the sends to these folks. Our thought was an initial "warm up" email, that would reintroduce them to receiving our appeals.

@DStrine the only major blocker is we're trying to upload these contacts back into the Acoustic database this week via a contact list that we upload from the fileserver. But since the contact list is so large (over 500k), I've been unsuccessful in downloading them off of Civi. Any tips for how to get them reincorporated in our Civi database, but having a way to keep them as a separate list so we can organize and track them as their own group?

If you have the file already you can manually import big lists. @KHaggard is well versed in that. Can you two sync up?

Hi @DStrine ! We're synced up. Uploading into Acoustic is no problem but we can't get the CSV downloaded from Civi to kick off the process.

ah ok. @Eileenmcnaughton is off for the week and the rest of the team has stuff to do to support next week's campaigns and other priorities. I will be OOO next week. I'm looping in @XenoRyet as fyi.

@KHaggard @nsirael if we can put them in a group in Civi then I think that is the right starting point to

a) unhold them in civi
b) export to Acoustic

My expectation is that even ifyou can get 600k out you might not be able to export via the UI since the csv is likely to be huge - but if you start by putting all the contacts you want to unhold in a civicrm group & unhold them in Civi (using the action we can follow up around uploading)

I think the main issue is that we haven't run the purges in Acoustic yet because I need the email address list in CSV format in order to run the purges the way I know how to do. The size is too huge to pull down from Civi, so the only thing I can think of is make the search results bite-sized and do one country at a time? Is there a way to parse the data like that in Civi so the download time is reasonable?

You might be able to split it up but that may be more time consuming. We've puled big lists like this before for things like target smart. I just don't think anyone will be available to help until next week.

@KHaggard - so we could always just try doing some more filters on email - I get 30k-ish if I do 'just' begins with 'a' - we don't have to do only one letter at once - but maybe it makes sense to go through the process on a set this size & build up from there?

@Eileenmcnaughton I like that idea! Can I add "or" statements in that so I can do begins with 'a' or 'b' or 'c' to see if I can pull bigger chunks at a time?

@nisrael - this gets around 100k rows & chooses starting with a-c

I'd probably step through the process with just 'a' first to test it out, remembering to remove the 'on_hold' from those contacts when you do, so that they disappear out of the master list

Got it so just to make sure I do it right @Eileenmcnaughton once I've successfully downloaded that list of folks I can unhold them?

@nisrael yep!

@nisrael so - I just tried & it was greyed out & then I selected 'just this page' & it worked & then I went back to all pages & it worked - so there is some glitchiness - possibly because it is selecting in the background

However, what I'm seeing then is that is if I have too many rows there is a different fail - which reflect it trying to pass a really long list of ids to
https://civicrm.wikimedia.org/civicrm/task/unhold-email?reset

Doing just the 30k 'a's works

So we can probably test the process on that & then maybe see if @Dwisehaupt can temporarily extend the server url length a bit if that turns out to be hte only limit in the flow after trying 'a'

Thanks and sounds good! I noticed if I waited a while on the page that it was no longer greyed out (so I deleted my comment).

Hey @nisrael and fr-tech folks, this is done pending @nisrael's review.

Here is a reference query for the output:

SELECT COUNT(DISTINCT e.email) AS on_hold_emails
FROM civicrm.civicrm_email AS e
JOIN civicrm.civicrm_mailing_provider_data AS mpd ON e.contact_id = mpd.contact_id AND e.is_primary = 1
WHERE mpd.event_type = 'Reply Mail Block';
--650983 records returned at initial query

Based on this query, I've exported the 650,100+ records in batches of 50,100 - 13 separate .csv files - to this location: smb://10.149.10.174/Fundraising/Email/on_hold_emails on the file server. I tested opening one of the .csv files and it does have a few seconds of lag time, but it should be workable for you. Let me know if you have issues or I can help further.