My company is trying to get a response rate for its various marketing campaigns. The system that we have that I am trying to chart the response rates from is a note based (diary) system. We burned a note for every policy that our marketing department sent a mailing to and we want to put that list up against the customers that called in for the change to the policy that we marketed for. We only want to get the customers that were sent the mailing and called us back and made the change.
Here is what I have so far:
Directory;
//Load Data
Temp:
LOAD LTRIM(RTRIM("ACT_ID")) AS Action_ID,
//RIGHT(LEFT("ACT_DATETIME",7),2)&'/'&RIGHT(LEFT("ACT_DATETIME",10),2)&'/'&(LEFT("ACT_DATETIME",4)) AS Date,
FLOOR(DATE(ACT_DATETIME,'MM/DD/YYYY')) AS Date,
TIME(ACT_DATETIME) AS Time,
LTRIM(RTRIM("CUS_ID")) AS Customer_ID,
FLOOR(DATE(ACT_DATETIME,'MM/DD/YYYY')) & LTRIM(RTRIM("CUS_ID")) & LTRIM(RTRIM("ACT_DATA_CHR")) & UPPER(LTRIM(RTRIM("REP_ID"))) AS DateAcctCallerRep,
LTRIM(RTRIM("POL_ID")) AS Policy_ID,
LEFT(RIGHT(RTRIM("POL_ID"),2),1) AS LOB,
UPPER(LTRIM(RTRIM("REP_ID"))) AS REP_ID,
LTRIM(RTRIM("ATY_ID")) AS PRIMARY_CALL_REASON,
LTRIM(RTRIM("ACT_DATA_LONG")) AS SECONDARY_CALL_REASON,
LTRIM(RTRIM("ACT_DATA_CHR")) AS CALLER
//create another table that is unique
FROM
\\swqlikv1\QLIKVIEW\5_EnterpriseQVDs\ContactCenter\Diary_Actions_ENT.qvd (qvd)
Temp1:
Load
Date(Date, 'MM/DD/YYYY') AS Date,
Policy_ID,
REP_ID,
CALLER,
Customer_ID,
PRIMARY_CALL_REASON,
SECONDARY_CALL_REASON
Resident Temp
where Date >04/01/2012;
DROP Table Temp;
left JOIN (Temp1)
LOAD
//Ext,
Rep_Name,
Department,
Team_Leader,
//Begin_Date,
End_Date,
IF(End_Date > Today(),1,0) AS Current_Rep,
(Today () - Begin_Date)/30 AS Tenure,
REP_ID
resident ContactCenterReps;
DROP TABLE ContactCenterReps;
Temp2:
//LEFT JOIN (Temp2)
LOAD
//DateAcctCallerRep,
//TimeSinceLastAcctNote,
//Date(Date) AS DATE,
Date,
If(((CALLER= 'INS')AND (PRIMARY_CALL_REASON= 'INFORMATION') AND (SECONDARY_CALL_REASON='HO PLUS')),1,0) AS Customer_HOPlus_Inquiry, //HO PLUS INQUIRY
If(((CALLER= 'INS')AND (PRIMARY_CALL_REASON= 'INFORMATION') AND (SECONDARY_CALL_REASON='SMARTCHOICE')),1,0) AS Customer_SmartChoice_Inquiry, //SMARTCHOICE INQUIRY
IF(((CALLER='INT')AND (PRIMARY_CALL_REASON='COVERAGE MAILER') AND (SECONDARY_CALL_REASON= 'HO PLUS/SMART')),1,0) AS Customer_Mailers,//MAILERS
IF(((CALLER='INS')AND (PRIMARY_CALL_REASON= 'HOME POL CHANGE') AND (SECONDARY_CALL_REASON= 'HO PLUS')),1,0) AS Customer_HOPlus_Accepts, //(customer response/mailer) As mailer response rate - HO PLUS
IF(((CALLER='INS')AND (PRIMARY_CALL_REASON= 'HOME POL CHANGE') AND (SECONDARY_CALL_REASON= 'SMARTCHOICE')),1,0) AS Customer_SmartChoice_Accepts, //(customer response/mailer) As mailer response rate - SMART CHOICE
IF(((CALLER='INT')AND (PRIMARY_CALL_REASON='EMAIL/FAX') AND (SECONDARY_CALL_REASON='CORR SENT')),1,0) AS Customer_Email, //response
Customer_ID AS Account_Number,
Policy_ID AS Policy_Number,
Rep_Name,
Department,
Current_Rep,
Tenure,
PRIMARY_CALL_REASON,
SECONDARY_CALL_REASON,
CALLER
Resident Temp1
WHERE (Department = 'CS') AND (Date > 04/01/2012) AND (CALLER= 'INS' AND PRIMARY_CALL_REASON= 'INFORMATION' AND SECONDARY_CALL_REASON='HO PLUS') OR (CALLER= 'INS' AND PRIMARY_CALL_REASON='HOME POL CHANGE' AND SECONDARY_CALL_REASON='HO PLUS') OR (CALLER= 'INS' AND PRIMARY_CALL_REASON= 'INFORMATION'AND SECONDARY_CALL_REASON='SMARTCHOICE') OR (CALLER= 'INS' AND PRIMARY_CALL_REASON='HOME POL CHANGE' AND SECONDARY_CALL_REASON='SMARTCHOICE')OR (CALLER='INT' AND PRIMARY_CALL_REASON='COVERAGE MAILER' AND SECONDARY_CALL_REASON= 'HO PLUS/SMART');
drop Table Temp1;
Should I reload the diary qvd and do a new where statement or an if question?
The marketing campaign burn is:
IF(((CALLER='INT')AND (PRIMARY_CALL_REASON='COVERAGE MAILER') AND (SECONDARY_CALL_REASON= 'HO PLUS/SMART')),1,0) AS Customer_Mailers,//MAILERS
The policy changes notes for the the campaign are:
IF(((CALLER='INS')AND (PRIMARY_CALL_REASON= 'HOME POL CHANGE') AND (SECONDARY_CALL_REASON= 'HO PLUS')),1,0) AS Customer_HOPlus_Accepts, //(customer response/mailer) As mailer response rate - HO PLUS
IF(((CALLER='INS')AND (PRIMARY_CALL_REASON= 'HOME POL CHANGE') AND (SECONDARY_CALL_REASON= 'SMARTCHOICE')),1,0) AS Customer_SmartChoice_Accepts, //(customer response/mailer) As mailer response rate - SMART CHOICE
The customer inquiries for the campaing are:
If(((CALLER= 'INS')AND (PRIMARY_CALL_REASON= 'INFORMATION') AND (SECONDARY_CALL_REASON='HO PLUS')),1,0) AS Customer_HOPlus_Inquiry, //HO PLUS INQUIRY
If(((CALLER= 'INS')AND (PRIMARY_CALL_REASON= 'INFORMATION') AND (SECONDARY_CALL_REASON='SMARTCHOICE')),1,0) AS Customer_SmartChoice_Inquiry, //SMARTCHOICE INQUIRY
I appreciate any guidance I can get!.
Thanks,
Tony
