Evaluating newsletter opt-outs

From one SuiteCRM installation we send out many different newsletter campaigns covering many different topics, languages, etc.

When an email recipients clicks on an opt-out link in the email, two things happens:

  1. the click is tracked in the campaign_log table (campaign_log.activity_type = ‘removed’ AND campaign_log.hits > 0)
  2. they are added to (one of) the suppression list(s) of the specific campaign.

After many years, we decided to do an overall evaluation of opt-out clicks over the whole lifetime of our CRM to make sure we picked these opt-out clicks up correctly and they landed in the correct suppression lists that are then used for future campaign emails (of the respective topic).

I couldn’t find a way to get the data I needed from a SuiteCRM report module, so I ended up with the following SQL query.

If anybody tries the same and can improve this SQL statement, please post back here. What I see as next steps:

  • select only relevant fields instead of all fields.
  • show username instead of user ids
  • combine contact and lead data into same column (union)
  • add email addresses from contacts and leads
FROM `campaign_log` AS cl
JOIN `campaigns` AS ca ON cl.`campaign_id` = ca.id
LEFT JOIN `prospect_lists` AS pl ON cl.list_id = pl.id
LEFT JOIN `prospect_lists_cstm` AS plc ON pl.id = plc.id_c 
LEFT JOIN `email_marketing` AS emkt ON cl.marketing_id = emkt.id
LEFT JOIN `contacts` AS c ON cl.`target_id` = c.id
LEFT JOIN `contacts_cstm` as cc ON c.id = cc.id_c 
LEFT JOIN `accounts_contacts` AS acco ON c.id = acco.contact_id
LEFT JOIN `accounts` AS a ON acco.account_id = a.id
LEFT JOIN `accounts_cstm` AS ac ON a.id = ac.id_c
LEFT JOIN `leads` AS l ON cl.`target_id` = l.id
LEFT JOIN `leads_cstm` AS lc ON l.id = lc.id_c
WHERE cl.`activity_type` = 'removed' AND (cl.`target_type` = 'Contacts' OR cl.`target_type` = 'Leads') AND cl.`deleted` = 0 AND cl.`hits` > 0 AND pl.list_type <> 'test'
ORDER BY cl.`activity_date` DESC

FYI campaign_log entries with activitiy_type = removed and hits = 0 are targets that never got the email due to the email address flag “Opted Out”.
The opt-out clicks differentiate themselves by the field “hits” being > 0.

1 Like