UNVERIFIED SOLUTION i

How does Unsubscribe Category work in Portrait Dialogue

Product Feature: Message/Master templates
Operating System: Windows 2008
Database: SQL Server 2008
Configuration: IIS
In Portrait Dialogue (PD), there is an option where we can configure an Unsubscribe URL in the message template and participants can click on that to unsubscribe themselves from receiving that email notification.
To do this, we have to use Link Tracking in the message template.
Below is an example dialog to illustrate this.

User-added image

The Divide operation has two branches.
The right branch can be any operation (Email bounced is our example)
The left operation contains a Category set that will filter the participants who clicked on the unsubscribe URL.

Portrait Dialogue does not provide any table in its database that directly keeps track of unsubscribe URL participants, therefore we have to use a customized query to fetch data from the database.
Below is the SQL Server query.


SELECT serial_no AS mh_customer_id 
FROM [Customer].[dbo].[Member] 
WHERE [email] = (SELECT REPLACE((SELECT TOP 1 REPLACE([ML_CONTROL_PARAMS], char(13)+char(10),'') 
FROM [PD].[dbo].[MESSAGE_LOG] 
INNER JOIN [PD].[dbo].[WEB_TRACK_LOG] 
ON [ML_ID]=[WTL_CM_ID] ),'To address=','')) 


Note: The above query needs to be altered to account for your own database and table names.

The Category set operation will contain those participants who have clicked on the unsubscribe URL.


 
UPDATED:  October 2, 2017