Remove or unsubscribe low engaged contacts?

[Pablo]

Hi!

We have been using EspoCRM Mass Email feature to send our monthly newsletter to contacts.

However, we would like to identify those contacts that haven't opened or clicked any of our campaigns in a while (example: in the last 6 months).

We know EspoCRM logs who opened or clicked each email campaign. Therefore, is there a way to obtain those "low engaged" contacts to delete or unsubscribe them?

Thanks a lot!

[victor]

Hi Pablo,

Tell me, do I understand correctly that you want to get summary data on any of the parameters of the Log? And this data should be taken from several Campaigns, and not one, as in the example on the screenshot?

[Pablo]

Hi victor,

YES! You are right. As you say, the purpose is to get the "data should be taken from several Campaigns, and not one".

Is it possible?
Thanks

[victor]

1. Create a List Report with Entity Type Campaign Log Record, with the following filters (as shown in screenshot 1). You should be careful with the filters so that nothing extra gets into the report. To create reports, you must have the Advanced Pack extension installed.
2. After saving the changes, our List Report will look like in screenshot 2.
3. Create a Workflow with parameters as shown in screenshot 3, in which we select our List Report. You don't need to use all the formulas in your Workflow. Choose the most suitable for you:

• Formula for indicating opted-out in the campaign:

$contactId = workflow\targetEntity\attribute('parentId');
$campaignId = workflow\targetEntity\attribute('campaignId');
ifThen(
    record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Opened', 'parentId=', $contactId) == false || record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Clicked', 'parentId=', $contactId) == false,
    entity\setAttribute('action', 'Opted Out')
    );​

• Formula for marking an email as opted-out:

$contactId = workflow\targetEntity\attribute('parentId');
$campaignId = workflow\targetEntity\attribute('campaignId');
ifThen(
    record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Opened', 'parentId=', $contactId) == false || record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Clicked', 'parentId=', $contactId) == false,
    record\update('Contact', $contactId, 'emailAddressIsOptedOut', true)
    );​​

• Contact removal formula:

$contactId = workflow\targetEntity\attribute('parentId');
$campaignId = workflow\targetEntity\attribute('campaignId');
ifThen(
    record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Opened', 'parentId=', $contactId) == false || record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Clicked', 'parentId=', $contactId) == false,
    record\update('Contact', $contactId, 'deleted', true)
    );​

[Pablo]

Hey victor you are a PRO at this!

Thanks a lot for taking the time step by step. I was able to follow your instructions.

For contacts that haved Clicked inside a Campaign, I would like to change a contact's field called "Engagement" to "Clicked". I believe I can adjust your step 1 and 2. However, for step 3 I think the following formula would be correct?

$contactId = workflow\targetEntity\attribute('parentId');
$campaignId = workflow\targetEntity\attribute('campaignId');
ifThen(
record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Opened', 'parentId=', $contactId) == false || record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Clicked', 'parentId=', $contactId) == false,
record\update('Contact', $contactId, 'engagement', "Clicked"));​​

Is this right?

Thanks again for your help.

[victor]

Is this right?

Thanks again for your help.

So you're right, just fix the "Clicked" in your formula to 'Clicked':

$contactId = workflow\targetEntity\attribute('parentId');
$campaignId = workflow\targetEntity\attribute('campaignId');
ifThen(
    record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Opened', 'parentId=', $contactId) == false || record\exists('CampaignLogRecord', 'campaignId=', $campaignId, 'action=', 'Clicked', 'parentId=', $contactId) == false,
    record\update('Contact', $contactId, 'engagement', 'Clicked')
    );​

Be careful with the attributes and their values, which must be specified correctly (as shown in screenshots 1 and 2).

[Pablo]

Thanks a LOT victor!

I will test it.

Have a great day!