How to automate mortgage lead-to-application KPI tracking using BI [Live demo]

Business intelligence and operational analytics in mortgage sales

Vova Pylypchatin

Published in

MortgageFlow

8 min readJul 21, 2024

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 do we 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.

Solution design structure + live dashboard

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.

So, the solution design below consists of 2 parts.

The first one answers what we’re trying to build:

What questions we’re looking to answer
What workflow we’re trying to measure
What performance means for this workflow
etc.

The second one answers how to automate it.

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 performance dashboard

We’ve implemented this solution design to give you a better idea of the final result.

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

1. WHAT

Lead-to-application workflow performance dashboard

The problem we’re looking to solve is that measuring more lead-to-application performance is hard.

So what would solve our problem and make it easier?

👉 An automated and self-service dashboard that answers questions about lead-to-application workflow performance.

That’s what we’re looking to get as a result.

What comes below peels layer by layer what an engineer needs to know to build this self-service dashboard.

Lead-to-application workflow performance analytics

The purpose of the lead-to-application performance dashboard is to answer questions about performance.

What are the questions we’re looking to answer?

Lead-to-application perfomance can be defined by 8 measures: Quantity, Volume, Speed, Quality, Effectiveness, Efficiency, Experience and Compliance.

As a mortgage sales team member for each lead-to-application performance measure, I would want to know:

What is my {{performance measure}} during this month?
How does my {{performance measure}} this month compare to my performance last month?
How does my {{performance measure}} this month compare to my performance the same month a year ago?
How does my {{performance measure}} month over month over the last 12 months?
How does my {{performance measure}} change per step of the workflow?

So, in total, we end up with 33 questions that the lead-to-application performance dashboard needs to answer.

Lead-to-application workflow performance charts

Dashboards answer questions through charts.

So, what charts do we need to answer questions about lead-to-application performance?

We can answer all 5 questions for each of the 8 performances with 3 chart types:

Questions 1–3 with a single Trend chart.
Question 4 with the Line chart.
Question 5 with the Funnel or Row chart.

So, to answer all 33 questions, we’ll need a total of 18 charts.

Since 18 charts are too much for a single tab, we’ll spread them across 4 tabs:

Overview
Quantity
Effectiveness
Speed

Note: The charts we selected defined given capabilities and limitations of the Metabase (our BI tool of choice)

Here’s how we need to visualise lead-to-application performance data:

Lead-to-application workflow perfomance metrics

To answer questions, we need to render 18 charts.

Each application performance chart needs data to render itself.

So what’s the data behind the lead-to-application perfomance charts?

To render 18 charts, we’ll need 10 lead-to-application performance metrics:

lead-to-application {{volume, speed, etc}} per month for Trend & Line charts
lead-to-application {{volume, speed, etc}} per stage for Funnel and Row charts

Each of the metrics defined by the:

Dataset to measure: Lead-to-application operations
Segment of the dataset to measure: Parent or child operations
Measurement: Volume, Speed, Quality
Dimension to group result by: Per month, per stage, etc

Here’s what performance data we need to visualize:

Lead-to-application workflow perfomance measures

10 lead-to-application perfomance metrics above rely on the 8 perfomance measures to get the metric’s value.

So what does quantity, volume, speed, effectiveness, efficiency, quality, experience, and compliance mean?

Here’s the math behind each of the 8 lead-to-application perfomance measures:

Quantity is the number of successfully completed lead-to-application operations
Volume is a total sum of successfully completed lead-to-application operations size
Speed is the difference between the lead-to-application operation start date and completion date
Effectiveness is the % of completed lead-to-application operations from started lead-to-application operations
Efficiency is the volume produced per unit of resources spent to produce it
Quality is % of the work product units produced without defects
Experience is the average satisfaction score per operation
Compliance is % of completed operations that meet the lead-to-application workflows rules

Lead-to-application workflow operations

Lead-to-application perfomance metrics are derived from the measurement of different segments of the lead-to-application perfomance operations.

So, what does lead-to-application operation mean?

Lead-to-application operation is a single instance of the lead-to-application workflow execution.

Each operation can be described with:

Trigger event: Event that marks the start of the operation
Completion event: Event that marks the completion of the operation
Milestone events: Events that mark the milestones from the start to completion of the workflow
Work product: An entity that results from the operations
Work product size: Value that represents the size of the work product produced
Work product defect event: An event that represents a defect in the work product
Experience survey: An entity that represents survey responses about experience
Experience survey score: An attribute in the survey response that represents experience score
Resource: Labour, material, time, etc, that’s used to produce a work product.

Here’s our definition of the Lead-to-application workflow:

Type: lead-to-application
Trigger event: Mortgage lead added (Event)
Completion event: Loan application completed (Event)
Effectiveness event: Loan application funded (Event)
Milestone events:
↳ Mortgage lead contacted (Event)
↳ Loan loan product selected (Event)
↳ Initial loan application submitted (Event)
↳ Loan application pre-approved (Event)
Work product unit: Loan application (Entity)
Work product size: Loan application amount (Attribute)
Work product defect event: Loan application rejected (Event)
Experience survey: Loan application survey (Entity)
Experience survey score: Loan application survey score (Attribute)
Resource: Loan officer’s time(Entity)

Lead-to-application workflow events

Lead-to-application workflows are defined by the following events:

Mortgage lead added
Mortgage lead contacted
Loan product selected
Initial loan application submitted
Loan application pre-approved
Loan application completed

So, what does each of them mean?

An event is a time stamp of a specific change in the lead-to-application workflow entities data.

For example, a Borrower lead connected event can be described as the timestamp when:

[When] Borrower Email address is not empty
[Or] Borrower Phone number is not empty

The Loan application submitted event can be described as the timestamp when:

[When] Loan Application Borrower First Name is not empty
[And] Loan Application Borrower Last Name is not empty
[And] Loan Application Borrower Income is not empty
[And] Loan Application Borrower SSN is not empty
[And] Loan Application Property Address is not empty
[And] Loan Application Property Estimated Value is not empty
[And] Loan Application Amount is not empty

Here are events that we need to capture to derive lead-to-application operations:

Lead-to-application workflow entities

8 lead-to-application workflow events derived from the changes in the lead-to-application entities.

What entities do we need to capture these events?

Lead-to-application operation and events relies on the following 4 entities:

Mortgage lead (extends Person)
Loan Officer (extends Person)
Loan Application
Property

‍‍

2. HOW

Lead-to-Application performance dashboard development

So, above, we’ve defined what solution we want to build to measure lead-to-application performance.

The performance dashboard development process doesn’t depend on the workflow.

That means the lead-to-application performance dashboard development process will be the same for the application-to-close performance dashboard.

An engineer will need to write code that automates the 10 steps below:

Connect to the raw data sources (Get API access, service accounts, etc.)
Replicate raw datasets from the data sources
Transform raw datasets into the workflow entities
Capture workflow events based on entities changes
Capture workflow operations based on events
Filter workflow operation segments needed for metrics
Calculate workflow performance measures
Calculate workflow performance metrics
Render charts based on the workflow performance metrics
Render the workflow performance dashboard

In the previous section, you can find all the answers engineers need to automate these 10 tasks.

The implementation process is technology-independent and can be done with your preferred tools and technologies.

Here’s our tech stack of choice for analytics pipelines:

Airbyte for connecting to data sources and extracting raw datasets
Google Cloud Storage for storing raw datasets
Google BigQuery Storage for storing transformed data
Mage for transforming raw datasets into entities, capturing events, etc.
Metabase for analytics, data visualization, and dashboards (steps 6–10)

An in-depth post on perfomance dashboard development is coming soon.

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.