Monday, 10 December 2018

Collections Connect Rate in DAX

Collections Connect Rate (CCR) is one of the first Key Performance Indicators that needs to be implemented within the Collections departments. It measures the number of outbound calls that succeeded in connecting to a valid phone number over the total number of outbound calls.

Naturally, a healthy Collections process requires a high CCR. Benchmarks show that the average value of that KPI is about 34.9%.

Accounts having a low value of this KPI might be considered as unlikely recovered and thus increase charge-off rates which might increase the financial risks especially regarding the IFRS9 expected loss calculation.

A low value of the CCR indicated inefficiencies within the customer relationship management processes that handled the customers account before getting transferred to Collections. This KPI can rise by improving Data Quality process, organising and auditing Contact data on a regular basis and using multiple company and public databases to enrich contacts records.

The calculation of CCR takes into consideration all outbound calls connected to a valid phone, including right party contacts (borrower or other trusted party) and wrong party contacts. The only condition is that the contact phone number should be in service.  The CCR is calculated as follow :


CCR = 100 * Outbound Calls Connected to a Valid Phone Number / Total Number of Outbound Calls

Suppose CollectionActions[] is the table containing details about the Collection outbound calls such as the AccountId, Date, Time, ScriptId, AgentID, PreviousStatus, NewStatus...

The PreviousStatus and NewStatus can have the following values depending on the result of the collection action: Call not connected, Call Connected, Right party contact, Promise to Pay, Partial Payment, Installment payments, payment in full...

Let's calculate CCR in DAX.

-------------------------------------------------------------------

'CollectionActions'[CallsValidNumber] =
CALCULATE(
DISTINCTCOUNT('CollectionActions'[AccountId]),
'CollectionActions'[NewStatus] IN { "[Call Connected]" }
)
-------------------------------------------------------------------
CRC = 
DIVIDE( 
    IF (
      'CollectionActions'[CallsValidNumber]  = BLANK (),
      0,
      'CollectionActions'[CallsValidNumber]
    ),
     CALCULATE(
      DISTINCTCOUNT('CollectionActions'[AccountId]),
   )

)


No comments:

Post a Comment