Getting a List of End Customers Who Opted Out

Overview

As someone who cares about the feedback end customers can give, you may be interested in the ones who do not want to receive surveys anymore, the ones who have opted out. This article explains what you need to do to get a list of these people.

Information

The list of people who have opted out is not available in the ResponseTek portal. Therefore, you need to create a support ticket in order to get that. When you create the ticket, please include if you want a report for SMS or email invitations and the period. The support team will generate the report and attach it to the ticket.

<supportagent>

These are the steps to generate the report:

1. Determine the environment used by the customer.
2. Connect to the database server.
3. Copy the script below to the SQL Server Management Studio:

   Click to see the script

   Copy script

   USE RT
   
   SET NOCOUNT ON;
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET DEADLOCK_PRIORITY LOW
   
   DECLARE @eID INT = 0
   DECLARE @sID INT = 0
   DECLARE @startDate DATE = '2020-10-01'
   DECLARE @endDate DATE = '2020-10-31'
   DECLARE @type VARCHAR(5) = 'EMAIL'      --EMAIL or SMS
   
   IF @type = 'EMAIL'
   BEGIN
     SELECT DISTINCT 
       ld.text AS Enterprise,
       t.text AS Template,
       col.text AS CollectionPoint,
       o.tstamp AS OptOut_Time, 
       ISNULL(c.firstname, '') AS FirstName, 
       ISNULL(c.lastname, '') AS LastName,
       ISNULL(c.phone, '') AS Phone, 
       ISNULL(o.email, '') AS Email, 
       ISNULL(c.accountNumber, '') AS AccountNumber, 
       ISNULL(c.title, '') AS Title,
       @type AS Type
     FROM tblOptout o
     INNER JOIN dbo.tblConsumer c ON o.email = c.email AND c.sID = @sID
     INNER JOIN dbo.tblsolution s ON s.ID = c.sID AND s.eID = @eID
     INNER JOIN dbo.tblenterprise e ON e.ID = s.eID
     INNER JOIN dbo.tbllabeldetail ld ON ld.labelID = e.labelID AND ld.eID = @eID AND ld.langID = 1
     INNER JOIN vcollection col ON col.id=o.collectionid
     INNER JOIN vtemplate t ON t.id=col.templateid
     WHERE 
       o.sID = @sID
       AND o.tstamp BETWEEN @startDate AND @endDate
   END
   ELSE IF @type = 'SMS'
   BEGIN
     SELECT DISTINCT 
       ld.text AS Enterprise,
       t.text AS Template,
       col.text AS CollectionPoint, 
       o.tstamp AS OptOut_Time, 
       ISNULL(c.firstname, '') AS FirstName, 
       ISNULL(c.lastname, '') AS LastName,
       ISNULL(c.phone, '') AS Phone, 
       ISNULL(o.email, '') AS Email, 
       ISNULL(c.accountNumber, '') AS AccountNumber, 
       ISNULL(c.title, '') AS Title,
       @type AS Type
     FROM tblOptout o
     INNER JOIN dbo.tblConsumer c ON o.email = c.Phone AND o.sid=c.sid AND c.sID = @sID
     INNER JOIN dbo.tblsolution s ON s.ID = c.sID AND s.eID = @eID
     INNER JOIN dbo.tblenterprise e ON e.ID = s.eID
     INNER JOIN dbo.tbllabeldetail ld ON ld.labelID = e.labelID AND ld.eID = @eID AND ld.langID = 1
     INNER JOIN vcollection col ON col.id=o.collectionid
     INNER JOIN vtemplate t ON t.id=col.templateid
     WHERE 
       o.sID = @sID 
       AND o.tstamp BETWEEN @startDate AND @endDate
   END
   

4. Set the variables to the appropriate values:
   • Click here to check how to get the values for @eID and @sID.
   • Set the @startDate and @endDate according to the period requested by the customer.
   • Set the @type to SMS or EMAIL to specify the origin of the opt-out request.
5. Run the script.
6. Copy the results to an Excel file.
7. If the customer wants a list for both SMS and Email, change the @type and run the script again. Append the result to the same Excel file.
8. Attach the Excel file to the ticket and send it back to the requester.