Data Exports: Lending

TABLE OF CONTENTS

• Guidelines
  • What Is a Data Export?
  • What Is Included in This Data Export?
  • How to Use the Exports
  • How Not to Use the Exports
  • Terminology
  • Ingesting Exports Into Your Data Warehouse
• Data Dictionary
• How to Calculate Metrics From the Export
  • Total Numbers / Amount of Applications
  • Conversion
  • Avg. Time-to-Apply
  • Cross-Sell
• Frequently Asked Questions

This article helps users at partner credit unions understand our lending data exports, their available fields, and how to use them to generate metrics.

The Lending export replaced the previous funnel report, which wasn't meeting its intended purpose and couldn't be produced at scale.

Guidelines

What Is a Data Export?

A data export is a daily batch of data, sent in .csv format via SFTP or the File Transfer API. It contains one table in a fixed, predetermined format.

• Frequency: daily
• Channel: SFTP or File Transfer API
• Format: .csv, pipe-delimited, double-quoted

Data included follows a fixed, predetermined format that scales across all Clutch credit unions.

What Is Included in This Data Export?

Two files are included:

• Loan applications for the past 1 day:
  • A list of all loan applications (submitted and dropped) in the previous 1 day
  • File name (for SFTP only): lending-1-day_YYYYMMDDThhmmssZ_part-00000.csv
  • For the File Transfer API, the CSV file will be inside a zip
• Loan applications for the past 30 days:
  • A list of all loan applications (submitted and dropped) in the previous 30 days
  • File name (for SFTP only): lending-30-days_YYYYMMDDThhmmssZ_part-00000.csv
  • For the File Transfer API, the CSV file will be inside a zip

Both files share the same fields (columns), but they differ in use case (when to use each) and total volume of data, since one includes data for the past 30 days.

How to Use the Exports

• Data warehouse ingestion:
  • Ingest the 30-day file into your warehouse while upserting records (updating existing rows and inserting new ones) based on the primary key ID. This way, if your ingestion process skips a day, it will catch up on the next run.
  • Alternatively, append the 1-day file into your warehouse, taking care to check whether the record already exists. If an application started yesterday and finished today, it will be repeated — use the record_last_updated_at field to capture the latest data for that record.
• Analysis: if you're analyzing the data in a spreadsheet, use the 30-day file so you don't need to append anything.

How Not to Use the Exports

Terminology

• An application refers to any user who started the application process, even if they dropped off before submitting it to the loan origination system (LOS).
• If one user made multiple applications in the same session, the export will have multiple rows for that session — one for each application.
• If a user applied multiple times for the same loan, multiple applications will appear (one per row).

Ingesting Exports Into Your Data Warehouse

1. Update: if an application already exists in your data warehouse as a row in a table, update the entire row; if it doesn't, create a new one.
2. Append: always add new rows to the existing table.

Data Dictionary

How to Calculate Metrics From the Export

Total Numbers / Amount of Applications

Sum application_amnt to get total application volume, since this file only shows applications that have already been submitted.

To count total leads (not necessarily submitted), you first need to define what a "lead" means for your purposes, such as:

1. A user who started the application experience
2. Any application session that has been started
3. Any application form that has been started

In this example there are three users: one applied for two different loan types (each in a different session), another has three applications with one repeated (same loan type, different session), and a third dropped off before choosing a loan type.

Depending on your lead definition, you'd get a different count:

Conversion

This metric measures the percentage of applications that ended up submitted to the LOS, out of all applications that were initiated.

To calculate a conversion rate, count applications where application_confirmed_at is not null, then divide by your total number of leads (based on your chosen lead definition). You can break this down by other fields — such as loan type or income range — to associate the metric with something specific.

Example: measuring conversion by loan type, using both a user-based and session-based lead definition:

Avg. Time-to-Apply

This is the average time it takes a user to apply for a loan. Add a column (e.g., time_to_apply) that calculates the difference between experience_started_at and application_submitted_at, in minutes.

The raw difference will be in days, so multiply by 24 and then by 60 to convert to minutes. For example:

Average column D to get your average time to apply. You can split this average by loan type (or any other field) using a formula like AVERAGEIFS.

Cross-Sell

This metric measures how often an additional loan is sold to a user who already submitted an application in the same period. There are three ways to define cross-sell:

• Simple: the most generous definition — total loan applications over total applicants. An applicant who applied for two of the same loan type (e.g., two auto loans) would still count.
• Distinct purpose: excludes applications that share the same application_category, collateral_type, funds_access_structure, and application_mode. For example, two new motorcycle loans would not count as cross-sell.
• Basic: the most conservative definition — only counts applications with an entirely different application_category.

For example, looking at all applications submitted in a single day:

To calculate distinct purpose and basic cross-sell, concatenate the relevant fields per application to build a comparison key, then count unique keys per user. Applying all three definitions to this example:

Frequently Asked Questions