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

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.

Below, you can see an embedded version of the dashboard.

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:

1. What is my {{performance measure}} during this month?
2. How does my {{performance measure}} this month compare to my performance last month?
3. How does my {{performance measure}} this month compare to my performance the same month a year ago?
4. How does my {{performance measure}} month over month over the last 12 months?
5. 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:

1. Questions 1-3 with a single Trend chart.
2. Question 4 with the Line chart.
3. 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:

1. Quantity is the number of successfully completed lead-to-application operations
2. Volume is a total sum of successfully completed lead-to-application operations size
3. Speed is the difference between the lead-to-application operation start date and completion date
4. Effectiveness is the % of completed lead-to-application operations from started lead-to-application operations
5. Efficiency is the volume produced per unit of resources spent to produce it
6. Quality is % of the work product units produced without defects
7. Experience is the average satisfaction score per operation
8. 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:

1. Mortgage lead added
2. Mortgage lead contacted
3. Loan product selected
4. Initial loan application submitted
5. Loan application pre-approved
6. 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:

1. Connect to the raw data sources (Get API access, service accounts, etc.)
2. Replicate raw datasets from the data sources
3. Transform raw datasets into the workflow entities
4. Capture workflow events based on entities changes
5. Capture workflow operations based on events
6. Filter workflow operation segments needed for metrics
7. Calculate workflow performance measures
8. Calculate workflow performance metrics
9. Render charts based on the workflow performance metrics
10. 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.

———-———

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.

‍