1. Címlap
  2. Blogs
  3. Blogs

Solution: All Recipients Not Showing Up For Sending Mass Email

Közzétéve
2019-09-03 08:54
Written by
simon.parker@v… - member of the CiviCRM community - view blog guidelines

TL:DR of entire project: 30-Year-old Charity migrates to CiviCRM from an archaic bespoke legacy database. Although we were warned that Drupal 7 is the preferable CMS for Civi, we decide on WordPress for staff skill and market longevity reasons. Documenting our journey and any challenges, watchpoints and lessons learnt. Solution uses CiviCRM, WordPress, memberships with portal, donations, GoCardless, Stripe, Mosaico, SparkPost & GiftAid. You can follow our progress at https://medium.com/@simonjohnparker

 

 

Today, I went to send our first email via Mosaico within CiviCRM. Previously we’d be doing a manual member export and import to MailChimp each time we wanted to send a mailing.

When I added our Membership Newsletter, Smart Group, it only showed 199 recipients to send to:

We have 812 members, 650 or so have email addresses:

The same thing was happening for smart groups and groups, so it led me to believe it could be issue with the contacts rather than the groups.

With the help of the community, I tried:

  • Disabling the GDPR plugin to ensure no suppressions were happening from those who hadn’t opted in
  • Checking if any contacts had ‘bounced’
  • Checking if any contacts had ‘Email On Hold’

A special thanks to eileen, mikeymjco and babu on the civicrm.org Mattermost who helped me with the above process of elimination.

Listing a few helpful links that also alluded to the above also:

https://civicrm.stackexchange.com/questions/18199/in-mailing-all-recipients-are-not-showing
https://civicrm.stackexchange.com/questions/31339/discrepancy-between-of-contacts-in-smart-group-vs-of-contacts-in-smart-group/

Unfortunately, these didn’t solve the issue. A bit more research led me to find this:

CiviMail Does Not Send To A Whole Group
 
This person did an incredible amount of research to try and fix this issue and eventually solved it:

 

"I finally gave up and forwarded this our developer who found that indeed, the issue was the is_deceased flag which had become NULL of their own accord!

It could be noted that manually setting all the flags via the GUI does not resolve this issue.

It would be interesting at some point to work out whether there was a specific action on the website that caused these flags to become NULL vice 0."

 

I went into our PHPMyAdmin and looked for the contact is_deceased column and low and behold:

 

The correct values should be: 0 = is not deceased and 1 = deceased

 

I did an advanced search in CiviCRM just to test:

Out of 900 contacts, apparently only 359 were not deceased!
Out of 900 contacts, 4 were deceased!

Clearly, that isn’t adding up.

 

mikeymjco helped me further to run the correct DB queries to correct those NULL fields.

SELECT is_deceased, id FROM civicrm_contact WHERE is_deceased IS NULL;

for me, that returned 537 rows.

Moment of truth. I then ran the update command:

UPDATE civicrm_contact SET is_deceased = 0 WHERE is_deceased IS NULL;

 

I returned to CiviCRM and Mosaico mailing and…

 

It worked!

This was a real odd one. I can only think the way we’ve imported contacts may have caused this but I guess I’ll never know for sure.

Hope this helps others with this issue.

UPDATE: 04/09/2019 — Turns out more people have this issue than I realised. I’ve had someone clarify it is due to a bad import and that there has actually been an extension developed to solve this.

 

Filed under
CiviMail

Comments

Thanks for the write up!

Another related issue, aimed more improving the journey for this type of problem: https://lab.civicrm.org/dev/mail/issues/51

If you hit this there is an anomaly in your database.

 

In 4.7 the following ran

 

-- CRM-17147 People with empty deceased-flag ('is null') get removed from recipient list of a mailing
UPDATE civicrm_contact SET is_deceased = 0 WHERE is_deceased IS NULL;
ALTER TABLE civicrm_contact ALTER COLUMN is_deceased SET DEFAULT 0;

 

New installs should have a civicrm_contact table that looks like

 

```CREATE TABLE `civicrm_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Contact ID',
  `contact_type` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Type of Contact.',
  `contact_sub_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'May be used to over-ride contact view and edit templates.',
  `do_not_email` tinyint(4) DEFAULT '0',
  `do_not_phone` tinyint(4) DEFAULT '0',
  `do_not_mail` tinyint(4) DEFAULT '0',
  `do_not_sms` tinyint(4) DEFAULT '0',
  `do_not_trade` tinyint(4) DEFAULT '0',
  `is_opt_out` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Has the contact opted out from receiving all bulk email from the organization or site domain?',
  `legal_identifier` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'May be used for SSN, EIN/TIN, Household ID (census) or other applicable unique legal/government ID.',
  `external_identifier` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Unique trusted external ID (generally from a legacy app/datasource). Particularly useful for deduping operations.',
  `sort_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Name used for sorting different contact types',
  `display_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Formatted name representing preferred format for display/print/other output.',
  `nick_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Nickname.',
  `legal_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Legal Name.',
  `image_URL` text COLLATE utf8_unicode_ci COMMENT 'optional URL for preferred image (photo, logo, etc.) to display for this contact.',
  `preferred_communication_method` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'What is the preferred mode of communication.',
  `preferred_language` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Which language is preferred for communication. FK to languages in civicrm_option_value.',
  `preferred_mail_format` varchar(8) COLLATE utf8_unicode_ci DEFAULT 'Both' COMMENT 'What is the preferred mode of sending an email.',
  `hash` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Key for validating requests related to this contact.',
  `api_key` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'API Key for validating requests related to this contact.',
  `source` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'where contact come from, e.g. import, donate module insert...',
  `first_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'First Name.',
  `middle_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Middle Name.',
  `last_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Last Name.',
  `prefix_id` int(10) unsigned DEFAULT NULL COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID',
  `suffix_id` int(10) unsigned DEFAULT NULL COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID',
  `formal_title` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)',
  `communication_style_id` int(10) unsigned DEFAULT NULL COMMENT 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in civicrm_option_value.',
  `email_greeting_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Email Greeting.',
  `email_greeting_custom` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Email Greeting.',
  `email_greeting_display` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Email Greeting.',
  `postal_greeting_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Postal Greeting.',
  `postal_greeting_custom` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Postal greeting.',
  `postal_greeting_display` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Postal greeting.',
  `addressee_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Addressee.',
  `addressee_custom` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Addressee.',
  `addressee_display` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Addressee.',
  `job_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Job Title',
  `gender_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to gender ID',
  `birth_date` date DEFAULT NULL COMMENT 'Date of birth',
  `is_deceased` tinyint(4) DEFAULT '0',
  `deceased_date` date DEFAULT NULL COMMENT 'Date of deceased',
  `household_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Household Name.',
  `primary_contact_id` int(10) unsigned DEFAULT NULL COMMENT 'Optional FK to Primary Contact for this household.',
  `organization_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Organization Name.',
  `sic_code` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Standard Industry Classification Code.',
  `user_unique_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
  `employer_id` int(10) unsigned DEFAULT NULL COMMENT 'OPTIONAL FK to civicrm_contact record.',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the contact was created.',
  `modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the contact (or closely related entity) was created or modified or deleted.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UI_external_identifier` (`external_identifier`),
  KEY `index_contact_type` (`contact_type`),
  KEY `index_contact_sub_type` (`contact_sub_type`),
  KEY `index_sort_name` (`sort_name`),
  KEY `index_preferred_communication_method` (`preferred_communication_method`),
  KEY `index_hash` (`hash`),
  KEY `index_api_key` (`api_key`),
  KEY `index_first_name` (`first_name`),
  KEY `index_last_name` (`last_name`),
  KEY `UI_prefix` (`prefix_id`),
  KEY `UI_suffix` (`suffix_id`),
  KEY `index_communication_style_id` (`communication_style_id`),
  KEY `UI_gender` (`gender_id`),
  KEY `index_is_deceased` (`is_deceased`),
  KEY `index_household_name` (`household_name`),
  KEY `index_organization_name` (`organization_name`),
  KEY `index_is_deleted_sort_name` (`is_deleted`,`sort_name`,`id`),
  KEY `FK_civicrm_contact_primary_contact_id` (`primary_contact_id`),
  KEY `FK_civicrm_contact_employer_id` (`employer_id`),
  CONSTRAINT `FK_civicrm_contact_employer_id` FOREIGN KEY (`employer_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_civicrm_contact_primary_contact_id` FOREIGN KEY (`primary_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=266 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
```
 
So I can't see any reason why this would happen but the db default *should* protect you from getting nulls in this field

There has been a suggestion that importing with a column 'is deceased' but no data in that column would set it to null. My understanding is that this is no longer the case but I have not tested