Response rate

Tue, 29 May 2012 15:04:47 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:

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

Response rate

Tue, 29 May 2012 18:31:53 GMT

In essence I am trying to build in date logic. If on a given date the marketing campaign burns went in on a poloicy and then on a greater than or equal to date for those same policies a customer made a call to inquire or update their policy, then that's the customers I want to return.

Do you think I need to build an if statement, a where statment, or do a join?

Any help would be greatly appreciated.

Thanks.

topic Response rate in QlikView

Response rate

Response rate