Overview
The main reason for adopting ResponseTek is to get feedback from the end customers. However, some of these end customers do not answer the surveys, even when a reminder is sent to them. In order to try to reach out to them and get their feedback, some customers ask ResponseTek to provide a list of those end customers who have not responded so they can call them.
Information
In case you need a permanent report that is always available for you, you need to contact your account manager and file a change request, asking for it. If you need it only once, the support team can help you. For that, create a ticket and provide the following information:
- The headers of the report (e.g. invitation number, name, phone, interaction date)
- The date range (e.g. October 1st, 2020 to October 31st, 2020)
- If you are interested in a specific survey or solution or if the report should contain all of them
The support team will perform this data extract and send it to you.
<supportagent>
In case the customer specifies that they need a permanent report, redirect them to the account manager. They will probably ask the Engineering team to create the report and make it available for this specific customer.
When the request is for a single data extract, you can start using the query below and customize it depending on the headers asked.
This is the query you can use as a starting point:
USE RT
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DEADLOCK_PRIORITY LOW
DECLARE @StartDate DATETIME = '2020-10-06'
DECLARE @EndDate DATETIME = '2020-10-07'
DECLARE @eid INT = NULL
DECLARE @sid INT = NULL
DECLARE @templeteId INT = NULL
DECLARE @collectionId INT = NULL
DECLARE @invitationType INT = NULL --10600=Email, 10601=SMS
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp;
SELECT
I.id,
I.sid,
I.collectionid,
I.ctstamp,
C.text [CollectionName],
T.text [TemplateName],
R.firstname,
R.lastname,
R.email,
R.phone
INTO #Temp
FROM tblinvitation I
INNER JOIN vCollection C ON I.CollectionID = C.ID
INNER JOIN vTemplate T ON T.ID = C.TemplateID
join tblConsumer R on i.consumerID = r.ID
WHERE
T.Sid = @sid AND
ctstamp >= @StartDate
AND ctstamp <= @EndDate
AND Statusid = 10
AND (@collectionId IS NULL OR C.ID = @collectionId)
AND (@templeteId IS NULL OR t.ID = @templeteId)
AND (@invitationType IS NULL OR I.typeid = @invitationType)
;
SELECT
Text1 [FileName]
,tStamp1 AS ProcessStart
,tStamp2 AS ProcessEnd
,C.TEXT AS Collections
,TL.TEXT AS SolutionName
,I.id [Inv_Num]
,S.id [Session_num]
,i.firstname,
i.lastname,
i.email,
i.phone
FROM tblfileimportlog IL
INNER JOIN #Temp I ON I.ctstamp BETWEEN IL.tstamp1 AND IL.tstamp2
LEFT JOIN tblSession S ON I.id = S.invitationid
INNER JOIN vCollection C ON I.CollectionID = C.id
INNER JOIN vTemplate TL ON TL.ID = C.TemplateID
WHERE
eid = @eid AND
IL.tstamp1 BETWEEN @StartDate AND @EndDate
ORDER BY
Text1
,tl.TEXT
,c.TEXT DESC;
DROP TABLE #Temp;
After getting the final result, save it to an Excel file and create a password-protected zip file since the report contains sensitive data.
</supportagent>
Priyanka Bhotika
Comments