Page MenuHomePhabricator

retrieve lists of contacts who received a particular mailing
Closed, ResolvedPublic8 Story Points


I probably need more clarity about this, I think it might be mostly for data queries but possibly for MG, DS etc to see.

from @CCogdill_WMF : “It would be ideal if we stored data not only on which contacts received a mailing, but also who opened itclicks & donations are on our servers but it would be great if all email engagement data was.“

Store how?
Potentially store as an activity, source_record_id links back to the mailing

Other notes:
Does Silverpop hold our contact id for us to link to


Related Gerrit Patches:
wikimedia/fundraising/crm : masterAdd throttling to Omnrecipients.load function

Event Timeline

Hi @CCogdill_WMF

I've pulled down some data onto staging from the beginning of the year. It's in the civicrm_mailing_provider_data table - eg.

SELECT * FROM civicrm_mailing_provider_data d LEFT JOIN civicrm_mailing m on m.hash = d.mailing_identifier WHERE event_type = 'SENT' LIMIT 5\G

It's probably worth you taking a look in that table & seeing what the data looks like. That table will probably work as a staging point for data. Having done a few queries already I can see that the ones who have 'Reply Abuse' as the event_type have not necessarily been unsubscribed.

Event types are :

SELECT DISTINCT event_type FROM civicrm_mailing_provider_data;



Click Through
Hard Bounce
Opt Out
Reply Abuse
Reply Change Address
Reply Mail Block
Reply Mail Restriction
Reply Other
Soft Bounce


Also, I thought I was puling contact_id in but although some are correct

select email, contact_id FROM civicrm_mailing_provider_data WHERE contact_id <> '' LIMIT 5;

others are missing like ...

I haven't figured out if the missing ones are in silverpop

Eileenmcnaughton set the point value for this task to 8.

I've brought this into the sprint because it is now mostly done. I have got one issue with retrieving the data at the moment - something that worked locally but not on the server due to different sensitivity to casing. That patch is now pending review (& is very small)

Change 368127 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add throttling to Omnrecipients.load function

I just added a patch to allow us to add some throttling to this.

Change 368127 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Add throttling to Omnrecipients.load function

@CCogdill_WMF I think we can close this one out too! Still backfilling data - that will take a while but it's happening

@CCogdill_WMF I am closing this. Let us know if it needs to be reopened.

DStrine closed this task as Resolved.Aug 16 2017, 7:50 PM

Yep, awesome work!! Thanks :)