1

I have a group of 18495 contacts but when I send them a civimail, it shows only 9560 recipients. I get that it's going to be less due to opt outs, etc., but how can I find out more specifically why?

It seems like others have asked this question as well:

https://lab.civicrm.org/dev/mail/-/issues/51

2

Here's a piece of sql that answers the question to some degree:

select count(distinct(gc.contact_id)), gc.status, e.on_hold, c.do_not_email, c.is_opt_out, c.is_deceased from civicrm_group_contact gc inner join civicrm_email e on gc.contact_id = e.contact_id inner join civicrm_contact c on gc.contact_id = c.id where group_id = the_group_id_you_care_about and e.is_primary = 1 group by status, on_hold, do_not_email, is_opt_out, is_deceased;

And here's what I got from it:

+--------------------------------+---------+---------+--------------+------------+-------------+
| count(distinct(gc.contact_id)) | status  | on_hold | do_not_email | is_opt_out | is_deceased |
+--------------------------------+---------+---------+--------------+------------+-------------+
|                          10374 | Added   |       0 |            0 |          0 |           0 |
|                           1085 | Added   |       0 |            0 |          1 |           0 |
|                              9 | Added   |       0 |            0 |          1 |           1 |
|                              2 | Added   |       0 |            1 |          0 |           0 |
|                              2 | Added   |       0 |            1 |          1 |           0 |
|                           3792 | Added   |       1 |            0 |          0 |           0 |
|                              1 | Added   |       1 |            0 |          1 |           0 |
|                              3 | Added   |       1 |            0 |          1 |           1 |
|                              1 | Added   |       2 |            0 |          0 |           0 |
|                              1 | Pending |       0 |            0 |          0 |           0 |
|                              2 | Pending |       1 |            0 |          0 |           0 |
|                           2546 | Removed |       0 |            0 |          0 |           0 |
|                            313 | Removed |       0 |            0 |          1 |           0 |
|                              2 | Removed |       0 |            1 |          0 |           0 |
|                              2 | Removed |       0 |            1 |          1 |           0 |
|                             67 | Removed |       1 |            0 |          0 |           0 |
|                              1 | Removed |       1 |            1 |          1 |           0 |
+--------------------------------+---------+---------+--------------+------------+-------------+

The pending and removed could be ignored, but it's also nice to see how many people removed themselves from your list.

Note this doesn't give a complete answer, there's still about 800 to go to get down to 9560 receipients, but in this case, it was due to 800 duplicate email addresses. I'd probably need to use a separate sql to show that, but patches welcomed.

Also seems like a civireport to do this might be nice.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.