DW structure changes
       

  • added new column to F_Interactions table:
    • Survey


New Report 9.01 PCS Detail: 

  • Dataset Report_PCSDetail, fields:
    • InteractionID
    • StartTime
    • EndTime
    • Origin
    • BPName
    • AgentName
    • HoldTime
    • QueueTime
    • TalkTime
    • CrmFirstName
    • CrmLastName
    • surveyName
    • answers_shortName
    • answers_type
    • answers_text
    • answers_selection
    • answers_file
    • answers_endingReason
    • answers_index
         
  • Dataset Report_PCSDetail, parameters:
    • @DW_DBName
    • @StartTime
    • @EndTime
    • @Offset
    • @Agents
    • @BusinessProcessName
    • @OriginName
    • @Current_User
    • @TimeZone
    • @IncludeCRM
             
  • CSV Export, fields:
    • Origin
    • InteractionID
    • StartTime
    • EndTime
    • BPName
    • surveyName
    • AgentName
    • CrmFirstName
    • CrmLastName
    • QueueTime
    • TalkTime
    • HoldTime
    • Question
    • Answer
             

New Report 9.02 PCS Summary Agent:

  • Dataset Report_PCSSummaryAgent, fields:
    • BPName
    • AgentName
    • surveyName
    • AverageScore
  • Dataset Report_PCSSummaryAgent, parameters:
    • @DW_DBName
    • @StartTime
    • @EndTime
    • @Offset
    • @Agents
    • @BusinessProcessName
    • @Current_User
    • @TimeZone
              
  • CSV Export, fields:
    • OptInBusinessProcess
    • AgentName
    • AverageScore
              

New Report 9.03 PCS Summary BP:

  • Dataset Report_PCSSummaryBP, fields:
    • BPName
    • surveyName
    • AverageScore    
  • Dataset Report_PCSSummaryBP, parameters:
    • @DW_DBName
    • @StartTime
    • @EndTime
    • @Offset
    • @Agents
    • @BusinessProcessName
    • @Current_User
    • @TimeZone
  • CSV Export, fields:
    • OptInBusinessProcess
    • SurveyName
    • AverageScore


LOGIC CHANGES:

  • ETL db changes:
    •  changes procedures:
      • optimization:
        • dbo.Upload_F_Attempts
        • dbo.Upload_F_Callbacks
                       
      • new field load:
        •  dbo.Upload_F_Interactions(add Survey load)
  • Users db procedures changes:
    • dbo.Prepare_For_Deploy
    • dbo.Prepare_For_Export
    • dbo.spRpt_GetInteractionsDataset
  • Users db new procedures:
    • DW.spRpt_CampaignTimeFrameSpecificStatistics.sql
    • DW.spRpt_CampaignTimeFrameSpecificStatistics_SubDs1.sql
    • DW.spRpt_CampaignInspection.sql
    • DW.spRpt_CampaignInspection_SubDs1.sql
    • DW.spRpt_BusinessProcessAgentPerf.sql
    • DW.spRpt_PCSSummaryAgent.sql
    • DW.spRpt_PCSSummaryBP.sql
    • DW.spRpt_PCSDetail.sql
  • Users db delete procedures:
    • dbo.spRpt_CampaignInspection_SubDs1.sql
    • dbo.spRpt_CampaignInspection.sql
    • dbo.spRpt_CampaignTimeFrameSpecificStatistics_SubDs1.sql
    • dbo.spRpt_CampaignTimeFrameSpecificStatistics.sql
    • dbo.spRpt_BusinessProcessAgentPerf.sql
          

    1.06 Staffing:

    • procedures changed:
      • dbo.spRpt_APCStaffing
    • Total Online:
      • Previous:
        • Count(distinct F_AgentLife.AgentID)
          where (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
          or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
          or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
      • Current:
        • Count(distinct F_AgentLife.AgentID)
          where (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
          or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
          or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
          and 
          (
          @BusinessProcessName = '- ALL -' 
           OR
          (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
          )
                         
    • Total Available:
      • Previous:
        • Count(distinct F_AgentLife.AgentID)
          where D_AgentStatus.AgentStatusName in ('Busy','Dialing','WaitingForOffline','WaitingForBreak',
          'WaitingForBackofficeAdministrative',
          'WaitingForHandlingOutgoingInteractions','Ready','Offered')
           and (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
           or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
           or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
               
      •  Current:
        • Count(distinct F_AgentLife.AgentID)
          where D_AgentStatus.AgentStatusName in ('Busy','Dialing','WaitingForOffline','WaitingForBreak',
          'WaitingForBackofficeAdministrative',
          'WaitingForHandlingOutgoingInteractions','Ready','Offered')
           and (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
           or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
           or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
           and 
           (
           @BusinessProcessName = '- ALL -' 
           OR
           (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
           )
                     

2.04 Agent Interaction Summary:

    • procedures changed:
      • DW.spRpt_AgentInteractionSummaryReport    
             
    • Ready Idle:
      • Previous:
        • F_AgentLife.EndTime - F_AgentLife.StartTime where AgentState in ('Ready', 'Offered')
               
      • Current:
        • F_AgentBpTime.Ready + AgentBpTime.Offered
    • Break:
      • Previous:
        • F_AgentLife.EndTime - F_AgentLife.StartTimewhere AgentState in ('Break')
               
      • Current:
        • F_AgentBpTime.Break
    • Busy:    
      • Previous:
        • F_AgentLife.EndTime - F_AgentLife.StartTime
          F_AgentLife.EndTime - F_AgentLife.StartTime
          where AgentState in ('Busy', 'Dialing', 'WaitingFor%')
          or (AgentState in ('Backoffice - Administrative') and F_AgentLife.InteractionId is not null)
          or (AgentState in ('Handling - Outgoing Interactions') and F_AgentLife.InteractionId is not null)
               
      • Current:
        • F_AgentBpTime.Busy + F_AgentBpTime.Dialing + F_AgentBpTime.WaitingFor + F_AgentBpTime.BackOfficeBusy +  F_AgentBpTime.HandlingOutgoingIntBusy
    • Staffed:
      • Previous:
        • max(F_AgentLife.EndTime) - min(F_AgentLife.StartTime) within a shift
               
      • Current:
        • F_AgentBpTime.LoginTime
               
    • No Answer:
      • Previous:
        • F_AgentLife.EndTime - F_AgentLife.StartTime
          where AgentState in ('No Answer')
      • Current:
        • F_AgentBpTime.NoAnswer
    • BackOffice Idle:
      • Previous:
        • F_AgentLife.EndTime - F_AgentLife.StartTime
          where AgentState in ('Backoffice - Administrative')
          and F_AgentLife.InteractionId is null
               
      • Current:
        • F_AgentBpTime.BackOfficeIdle
           
    • Handle Outgoing Idle:
      • Previous:
        • F_AgentLife.EndTime - F_AgentLife.StartTimewhere AgentState in (if there was a state 'Handling - Outgoing Interactions')
          and F_AgentLife.InteractionId is null
                 
      • Current:
        • F_AgentBpTime.HandlingOutgoingIntIdle
                         

5.05 Queue Interval Summary:

  • procedures changed:
    • DW.spRpt_APCSummary
  • Agent Staffed:
    • Previous:
      • Count(distinct F_AgentLife.AgentID)
        F_AgentLife.Status <> 'Offline'
        F_AgentLife.AgentLifeTypeID = 1
    • Current:
      • Count(distinct F_AgentBpTime.AgentID)
  • Agent Available:
    • Previous:
      • Count(distinct F_AgentLife.AgentID)
        F_AgentLife.Status in ('Ready', 'Busy')
        F_AgentLife.AgentLifeTypeID = 1
    • Current:
      • Count(distinct F_AgentBpTime.AgentID)
        F_AgentBpTime.Ready + F_AgentBpTime.Busy > 0

   7.01 Customer Experience:

    • procedures changed:
      • dbo.spRpt_CustomerExperience
      • dbo.spRpt_CustomerExperience_Total
    • Staffed Agents:
      • Previous:
        • Count(distinct F_AgentLife.AgentID)
          F_AgentLife.Status <> 'Offline' 
          and F_AgentLife.AgentLifeTypeID = 1
      • Current:
        • Count(distinct F_AgentBpTime.AgentID)
          @BusinessProcessName = '- ALL -' 
           OR
           (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
                          

7.03 Contact Center Performance:

  • procedures changed:
    • dbo.spRpt_ContactCenterPerformance
    • dbo.spRpt_ContactCenterPerformance_Total
  • Staffed Agents:
    • Previous:
      • Count(distinct F_AgentLife.AgentID)
        F_AgentLife.Status <> 'Offline' 
        and F_AgentLife.AgentLifeTypeID = 1
    • Current:
      • Count(distinct F_AgentBpTime.AgentID)
        @BusinessProcessName = '- ALL -' 
         OR
        (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
                        
           

7.05 Contact Center Performance By Demands:

  • procedures changed:
    • DW.spRpt_ContactCenterPerformanceByDemand
  • Staffed Agents:
    • Previous:
      • Count(distinct F_AgentLife.AgentID)
        F_AgentLife.Status <> 'Offline' 
        and F_AgentLife.AgentLifeTypeID = 1
             
    • Current:
      • Count(distinct F_AgentBpTime.AgentID)
        @BusinessProcessName = '- ALL -' 
        OR
        (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
  • No labels