Contacts visible in mailing list group, not on hold or otherwise dnd, but skipped by mail out

Question

I'm fairly sure I've worked out why this is happening, but want some input to confirm the problem, and hopefully pointers for fixing it.

We totally refreshed our main mailing list in line with GDPR, so we've been mailing about 2000 people with only a handful of bounces and unsubscriptions; now suddenly with our latest mail out, CiviCRM is only finding half of these people. Looking at the group, they're still there, and only a dozen of these are on hold due to bounces. Anyone who unsubscribed isn't counted towards the group total, so that's not the issue. Checking for duplication, it's not that we accidentally created 1000 duplicate contacts, so that's not the issue.

What has happened between this mail out and the previous one is that I've imported additional contact info for a lot of our contacts, and although the emails have been matched, so the contact records have been filled rather than duplicated, the newly imported emails have all been added as 'Home' - and the original email was 'Main', or 'Work', or 'Billing'...

Which is fine, if messy - lots of duplicated emails in records, but all the info is in the correct contact record. The problem - as far as I can work out - is that none of the emails have been marked as the one to be used for bulk mailings, and rather than default to the primary email for those contacts, CiviMail simply skips those contacts completely.

So, assuming I haven't missed something, it seems like the only option I have is to edit the database with something along the lines of 'if email is primary, set is bulkmail to yes' - given that my confidence in editing via sql is more or less nil, I'd be grateful for any pointers in carrying this out.

ETA: I’ve noticed that a couple of contacts which do have the bulk mail selected (both with two different emails to send the mail out to, rather than duplicate emails) also did not get sent the mail out, so it looks like setting the bulk mail email isn’t going to fix this...

(It also appears to happen with a smaller mailing list - nearly 100 contacts in the group, none on hold, but only 49 recipients indicated when you add that group to a new mail out)

ETA #2: Finally managed to pin this down to missing values in the is_deceased field - all the contacts with newly imported data seem to have lost the value for this field, even though I'd set the import mapping to skip that field entirely (as I'd already eliminated removed any deceased contacts from the import). Resetting the field to 0 fixed the problem - everyone is now back on the recipients list...

I will add the fix as an answer :)

Answer 1

As I've added to my question, it turns out the problem wasn't with the emails or the DND settings - the missing contacts all had a NULL value for is_deceased, and apparently CiviCRM interprets this as them being dead (rather than just missing information). When you edit the entire contact record, this is flagged, but because I was just checking the emails and DND settings in place, I hadn't noticed this.

Setting all the null values to 0 fixed the problem - I ran the following SQL query on the civicrm_contact table:

UPDATE civicrm_contact SET is_deceased = 0 WHERE is_deceased IS NULL;

(As per the answer here: https://civicrm.stackexchange.com/a/7396/610 )

Following that, all the relevant contact records reappeared in the mailing list recipient count.

(Double checking the import field mapping, it turns out I didn't tell it to skip that field - CiviCRM exports the field as empty if is_deceased = 0, so I guess importing it did overwrite the existing values)

Answer 2

You have probably checked that already but you have to make sure that the contacts do not have a global opt out which is different from the subscription status of the group.

Have a look at the opt out flag: