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:
- the click is tracked in the campaign_log table (
campaign_log.activity_type= āremovedā ANDcampaign_log.hits> 0) - 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
SELECT *
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` DESCFYI 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.