0 Replies Latest reply: May 29, 2012 8:31 AM by pilrim11 RSS

    Getting a response rate

      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