Getting a response rate

Tue, 29 May 2012 12:31:40 GMT

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:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
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

topic Getting a response rate in QlikView

Getting a response rate