How To Automate Mortgage Sales Lead-to-Application Workflow Analytics [Live dashboard]

How to make it easier for your mortgage sales team to improve their performance

Mortgage sales function performance is one of the most significant contributors to mortgage lenders’ profitability.

Borrower Lead to Loan Application is one of the mortgage sales function workflows that has the most impact on the sales function performance.

And it’s one of the workflows a mortgage sales team has the most control over.

So, improving lead-to-loan application workflow performance can deliver significant business value for mortgage companies.

But it’s tough for a sales team to improve their workflow performance when they don’t know:

• How to measure performance?
• What’s good performance?
• What’s their performance?
• Why is their performance what it is?
• What will their performance be like if nothing changes?
• What do they need to change to get performance to where you want it to be?

So, in this post, I’ll share how to automate operational analytics to help the mortgage sales team answer these questions and improve lead-to-application workflow performance.

This post results from the same solution design process we use when companies hire us to automate operational analytics.

And the live dashboard below resulted from the implementation of this solution design.

So, if you find this dashboard helpful and would like to have the same, you can use this post as a specification, and most data analytics engineers will be able to implement it.

— — — - — — —

Bonus: Get operational analytics for your team for free. Our team offers 4 spots each quarter for free operational analytics implementation. So, if you’re looking for one, feel free to reach out to us to see if a spot is available.‍

Operational analytics solution design structure

Before we dive into the solution design details, let’s see what a solution design is.

The goal of the solution design is to answer how to solve the problem and provide a blueprint for implementing the solution.

In the case of operational analytics solution design, the problem is that we can’t measure the performance of the workflow because:

• It’s hard to collect the data needed to measure performance (data extraction)
• It’s hard to clean and prepare data to measure performance (data processing)
• It’s hard to calculate the performance (data analysis)
• It’s hard to interpret what the result means (data visualization)

An operational analytics solution answers how to automate operational data extraction, processing, analysis, and visualization to make it easier.

In this post revision, we’ll focus on the last 2 points (analysis and visualization), and in future posts, I’ll share blueprints for data extraction and processing.

So, the solution design below consists of 2 parts.

The first one answers the what:

• What workflow we’re trying to measure
• What performance means for this workflow

The second one answers how to automate it. The “HOW” part is more technical as it’s intended to serve as specifications for data and analytics engineers.

Here, you can find a more in-depth overview of how operational analytics impact mortgage performance and how to use technology to improve it.

Live lead-to-application workflow dashboard

To give you a better idea of the final result, we’ve implemented this solution design.

Here’s the link to open the dashboard in a new tab.‍

1.WHAT

Mortgage borrower lead-to-application workflow

To measure the performance of lead-to-application workflow, we first need to define:

• What it is
• Where it starts and where it ends
• What’s the output of the workflow
• What’s the purpose of the workflow
• Who decides what the next step is in the workflow

Below is the definition of the lead-to-application workflow that we use for this solution design.

Workflow trigger event

The workflow starts when an LO has the means to contact a borrower.

Event: Borrower lead contact details added

Workflow completion event

The workflow ends when LO has 6 TRIDA items to start loan processing.

Event: Loan application submitted

Workflow output

The output of the workflow is Submitted Loan Application (Entity).

Workflow purpose

Generate a loan application that results in a funded loan to profit from the gain on sale or origination.

Event: Loan application funded (Event)

Workflow agent

A workflow agent is the person who decides what to do next and when to complete the workflow.

In our case, the person who makes decisions is the Loan Officer (Entity).

Borrower lead-to-application workflow performance

To measure lead-to-application workflow performance, we first need to define what performance means for each of the performance aspects:

1. Quantity: What is many or few?
2. Volume: What is a lot or a little?
3. Speed: What is fast or slow?
4. Quality: What is good or bad?
5. Efficiency: What is efficient?
6. Effectiveness: What is effective?
7. Experience: What is good or bad?
8. Compliance: What is compliant and not?

Lead-to-application quantity

What is quantity:

Quantity is the total count of work product units produced by the workflow.

In our case, the work product of lead-to-application workflow is the loan application.

So, quantity is the total number of submitted loan applications.

How to measure:

• Count the number of submitted loan applications (6 TRIDA items).

Lead-to-application volume

What is volume:

Volume is the total size of the work product produced by the workflow.

In our case, the work product of lead-to-application workflow is the loan application.

The size of the loan application is defined by its dollar value.

So, volume is the total dollar value of the submitted loan applications.

How to measure:

• Calculate the sum of submitted loan applications’ dollar value (6 TRIDA items).

Lead-to-application speed

What is speed:

Speed is the time it takes to produce a single unit of work product. The less time needed to produce a single unit, the higher the speed.

In the lead-to-application workflow, speed is the time it takes for a Loan Officer (LO) to get from where they can contact the borrower to the point where the borrower submits an application.

How to measure:

• Calculate the difference in days from the date the lead received to the date the borrower submitted a loan application.

Lead-to-application quality

What is quality:

Quality can be defined as the extent to which the product fulfills the purpose for which it was produced. The better the product meets its purpose, the higher the quality.

The purpose of a loan application is to result in a funded loan to make a profit from a gain on sale or origination.

So, the quality of the loan application can be determined by how effectively they result in a closed loan.

How to measure:

• Calculate the percentage of funded loan applications from submitted loan applications

Lead-to-application efficiency

What is efficiency:

Efficiency is how well resources are used to achieve the outcome.

The outcome of the lead-to-application workflow is a submitted loan application.

The main resource of the mortgage sales function is the Loan Officers (LO) time.

So, efficiency can be measured by the volume produced per LO.

How to measure:

• Calculate the sum of submitted loan application dollar value per loan officer

Lead-to-application effectiveness

What is effectiveness:

Effectiveness is how well the workflow achieves the intended outcome.

The outcome of the lead-to-application workflow is a submitted loan application.

So, the effectiveness of the workflow can be defined as the conversion rate from borrower lead to submitted application.

How to measure:

• Percentage of borrowers submitted a loan application from the total count of borrower leads

Lead-to-application experience

What is experience:

Experience can be defined as the satisfaction and perception of customers throughout the process of achieving a specific outcome.

In the case of lead-to-application workflow, the customer is the borrower.

The outcome is a submitted loan application.

It can be measured by asking the borrower to score their experience after loan application submission.

How to measure:

• Calculate the average lead-to-application workflow customer satisfaction score.

Lead-to-application compliance

What is compliance:

Compliance can be defined as the extent to which carried-out operations meet the standards set by regulators, the company, or the market.

An operation is a single instance of moving from borrower lead to submitted application.

Examples of standards for lead-to-application workflow are below:

• When a new lead is received, it should be contacted within 60 minutes.
• When pre-approval is requested, a response should be provided within 24 hours.

How to measure:

• Calculate the percentage of lead-to-application operations that meet standards compared to the total number of operations.

‍

2. HOW

Lead-to-application workflow performance dashboard

To understand “What’s their performance?” LOs need first to ask the question and then interpret the answer.

To automate this process, we’ll create a self-service dashboard with 8 predefined questions (one per performance measure) and an easy-to-understand visualization of the answers.

We’ll use Metabase’s Dashboard feature to achieve that.

Below is a configuration for the dashboard:

• Global chart visualisations:
• Type: Line-chart
• X-axis: Date
• Y-axis: Value
• Global chart metrics:
• Filter:
• Date range: (Default: last 12 months)
• Global chart layout:
• Width: 50%
• Height: auto
• Charts:

1. Metric: mt0-quantity-per-month
2. Metric: mt1-volume-per-month
3. Metric: mt2-speed-per-month
4. Metric: mt3-quality-per-month
5. Metric: mt4-efficiency-per-month
6. Metric: mt5-effectiveness-per-month
7. Metric: mt6-experience-per-month
8. Metric: mt7-compliance-per-month

Lead-to-application workflow performance metrics

Metabase query builder

To provide the answers, the dashboard needs to know how to ask the question.

So, we’ll need to define 8 questions, one for each performance measure broken down by date.

Measures broken down by date are called metrics.

We’ll use Metabase’s Questions feature to define our “metrics”.

Below is a definition of the metrics:

Metric #0

• ID: mt0-quantity-per-month
• Description: # total count of loan applications submitted per month
• Dataset: ds-mortgage-sales-operations
• Measure: ms4-completed-mortgage-operations-count
• Dimension: Workflow operation [Finished at] as Month

Metric #1

• ID: mt1-volume-per-month
• Description: $ total value of loan applications submitted per month
• Dataset: ds-mortgage-sales-operations
• Measure: ms1-completed-mortgage-sales-value-sum
• Dimension: Workflow operation [Finished at] as Month

Metric #2

• ID: mt2-speed-per-month
• Description: # avg days from lead contact received to loan application submitted per month
• Dataset: ds-mortgage-sales-operations
• Measure: ms2-completed-mortgage-sales-duration-average
• Dimension: Workflow operation [Started at] as Month

Metric #3

• ID: mt3-quality-per-month
• Description: % applications submitted to funded rate per month
• Dataset: ds-mortgage-sales-operations
• Measure: ms3-funded-mortgage-sales-percentage
• Dimension: Workflow operation [Finished at] as Month

Metric #4

• ID: mt4-efficiency-per-month
• Description: $ value of the applications taken per loan officer per month
• Dataset: ds-mortgage-sales-operations
• Measure: ms6-mortgage-sales-efficiency
• Dimension: Workflow operation [Finished at] as Month

Metric #5

• ID: mt5-effectiveness-per-month
• Description: % borrower lead to application taken rate per month
• Dataset: ds-mortgage-sales-operations
• Measure: ms7-completed-mortgage-sales-percentage
• Dimension: Workflow operation [Started at] as Month

Metric #6

• ID: mt6-experience-per-month
• Description: # avg application submission customer satisfaction score per month
• Dataset: ds-mortgage-sales-operations
• Measure: ms9-mortgage-operations-experience-average
• Dimension: Workflow operation [Finished at] as Month

Metric #7

• ID: mt7-compliance-per-month
• Description: % operations that meet mortgage sales function standards per month
• Dataset: ds-mortgage-sales-operations
• Measure: s11-compliant-mortgage-operations-percentage
• Dimension: Workflow operation [Finished at] as Month

Lead-to-application workflow measures

Metabase metric builder

Metrics consist of the dataset, measure, and dimension.

To create the metrics defined above, we need to define measures.

We’ll use Metabase’s Modeling features to define our measures.

Bellow definitions of the measures:

Measure #1

• ID: ms1-completed-mortgage-sales-value-sum
• Name: Completed Mortgage Sales Ops Volume Sum
• Description: Total sum of submitted Loan Application $ value
• Dataset: ds-mortgage-sales-operations
• Filter: sg-completed-mortgage-sales-operations
• Measure: Sum Workflow operation [Volume]

Measure #2

• ID: ms2-completed-mortgage-sales-duration-average
• Name: Mortgage Sales Duration Average
• Description: Avg of the difference between Mortgage Sales Operation Started & Finished
• Dataset: ds-mortgage-sales-operations
• Filter: sg-completed-mortgage-sales-operations
• Measure: Avg Workflow operation [Duration]

Measure #3

• ID: ms3-funded-mortgage-sales-percentage
• Name: Mortgage Sales Quality
• Description: Percentage of the Loan Applications that resulted into funded loan
• Dataset: ds-mortgage-sales-operations
• Measure:
• Percentage of ms4-funded-mortgage-operations-countfromms5-completed-mortgage-operations-count

Measure #4

• ID: ms4-funded-mortgage-operations-count
• Name: Quality Mortgage Sales
• Description: Number of sales operations that meet quality standard
• Dataset: ds-mortgage-sales-operations
• Filter: sg-funded-mortgage-sales-operations
• Measure: Count Workflow operations

Measure #5

• ID: ms5-completed-mortgage-operations-count
• Name: Completed Mortgage Sales
• Description: Number of completed sales operations
• Dataset: ds-mortgage-sales-operations
• Filter: sg-completed-mortgage-sales-operations
• Measure: Count Workflow operations

Measure #6

• ID: ms6-mortgage-sales-efficiency
• Name: Mortgage Sales Efficiency
• Description: # value of the applications taken per loan officer
• Dataset: ds-mortgage-sales-operations
• Measure: ms1-mortgage-sales-volume / Count distinct Workflow operations [Loan Officer]

Measure #7

• ID: ms7-completed-mortgage-sales-percentage
• Name: Completed ****Mortgage Sales Percentage
• Description: % borrower lead to application taken rate
• Dataset: ds-mortgage-sales-operations
• Measure:
• Percentage of ms4-completed-mortgage-operations-countfromms8-mortgage-operations-count

Measure #8

• ID: ms8-mortgage-operations-count
• Dataset: ds-mortgage-sales-operations
• Name: Mortgage Sales Operations Count
• Description: Total number of mortgage sales operations
• Measure: Count Workflow operations

Measure #9

• ID: ms9-mortgage-operations-experience-average
• Name: Mortgage Sales Operations Experience Average
• Description: Total number of mortgage sales opeations
• Dataset: ds-mortgage-sales-operations
• Filter: sg-completed-mortgage-sales-operations
• Measure: Average Workflow operation [Experience]

Measure #10

• ID: ms10-compliant-mortgage-operations-count
• Name: Compliant Mortgage Sales Operations Count
• Description: Count of compliant mortgage sales operations
• Dataset: ds-mortgage-sales-operations
• Filter: sg-compliant-mortgage-sales-operations
• Measure: Count of Workflow operations

Measure #11

• ID: ms11-compliant-mortgage-operations-percentage
• Name: Compliant Mortgage Sales Operations Percentage
• Description: Total number of mortgage sales operations
• Dataset: ds-mortgage-sales-operations
• Filter: sg-completed-mortgage-sales-operations
• Measure:
• Percentage of ms10-compliant-mortgage-operations-countfrom ms8-mortgage-operations-count

Lead-to-application workflow segments

Metabase segment builder

The measures defined above rely on measuring a specific sub-segment of the dataset.

To define these segments, we’ll use Metabase’s Segments feature.

Below is a definition of the segments:

Segment #1

• ID: sg-completed-mortgage-sales-operations
• Dataset: ds-mortgage-sales-operations
• Name: Completed Mortgage Sales Operations
• Description: Mortgage Sales Operations that resulted in a submitted Loan Application
• Filters:
• Mortgage Sales Operation [Status] is [Completed]

Segment #2

• ID: sg-funded-mortgage-sales-operations
• Dataset: ds-mortgage-sales-operations
• Name: Quality Mortgage Sales Operations
• Description: Mortgage Sales Operations that resulted into a submitted Loan Application that was funded
• Filters:
• Mortgage Sales Operation [Is quality] is [true]

Segment #3

• ID: sg-compliant-mortgage-sales-operations
• Dataset: ds-mortgage-sales-operations
• Name: Compliant Mortgage Sales Operations
• Description: Mortgage Sales Operations that meet compliance standards
• Filters:
• Mortgage Sales Operation [Compliant] is [true]

Lead-to-application operations data model

Metabase model definition view

To measure the metrics defined above, we need the data.

Below is a definition of the data model used to measure the metrics above.

This is a derived table from the lead-to-application events.

To define the model, we used Metabase’s Model feature.

Below is the definition of the model:

Dataset #1

• ID: ds-mortgage-sales-operations
• Name: Mortgage sales operations
• Attributes:
• ID
• Operation type
• Start at (Borrower connected date)
• Finished at (Application submitted date)
• Duration
• Volume (Loan Application Amount): number
• Is quality: boolean (Loan Application Funded)
• Experience: number
• Status: completed | in progress
• Compliant: boolean
• Borrower.ID
• Loan Application.ID
• Loan Officer.ID

What’s next?

I hope this post gave you insight into how you can automate performance measurement of the lead-to-application workflow.

Also, if you’d like to stay on top of the latest mortgage technology and see how it can be applied to mortgage operations, consider signing up for our mortgage technology newsletter.

— — — - — — —

Bonus: Get operational analytics for your team for free. Our team offers 4 spots each quarter for free operational analytics implementation. So, if you’re looking for one, feel free to reach out to us to see if a spot is available.