This document aims to help users at partner Credit Unions to better understand our data exports, their available columns and how to use them to generate metrics.

Guidelines

What is a Data Export?

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

• Frequency: daily
• Channel: SFTP and File Transfer API
• Format: .csv

Data included: fixed predetermined format that scales across 100+ credit unions.

What is included in this Data Export?

Two different files:

• 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
• 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

Both will have the same fields (columns), but they will differ in use-cases (when to use each) and total volume of data, since one of them will include data for the past 30 days.

How to use the exports?

• Data warehouse ingestion: append the 1 day file into your warehouse, taking care to check if the record already exists there. In case an application started yesterday but finished today, it will be repeated and we have the column record_last_updated_at to ensure you capture the latest data on that record.
• Analysis: if you are going to analyze the data in a spreadsheet, use the 30 day file so you don't need to append anything.

How NOT to use the exports?

• Avoid duplicates:
  • If you require a funnel metric (e.g. conversion) of over 30 days, you shouldn't append multiple 30 day files, because if you do you will get duplicates of the same applications.

The best way to proceed in this case would be to iterate through multiple files, getting the most recent status of each application.

Terminology:

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

Ingesting Exports into your Data Warehouse (DW):

1. Update: if an application already exists in your DW 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

a) Total numbers / amount of applications

Sum the application_amnt, because this file only shows apps that have already been submitted.

To count the total number of leads (not necessarily submitted), then you have to define what a lead is first, 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

As you can see, we have three users:

• One of them with two applications to different loan types (each application in a different session)
• Another with three applications, but one of them repeated (same loan type, but in a different session)
• A third one who dropped off before choosing a loan type

This means that, depending on the definition of lead, you may have a different count of leads:

1. You have 3 users, therefore 3 leads
2. You have 3 users, two with 2 sessions, one with 1 session, therefore 5 leads
3. You have 3 users, one that didn't start the form and the others started it 5 times, therefore 5 leads

The way you should calculate it is:

b) Conversion

This is a metric to measure the % of applications that ended up submitted to the LOS among all applications that were initiated.

To better calculate conversion rates, we propose counting applications where application_confirmed_at is not null.

After that, divide by the total number of leads (based on your definition of a lead). You can break it down by any other fields to have your conversion metric associated with something else, such as by loan type or by income range. Example:

We want to measure conversion by loan type, using the same example as above, looking at two lead definitions: by user and by session. The first thing is to get a list of all possible loan types, then count the number of leads and the number of apps submitted:

After that, just divide the two numbers:

c) Avg. time-to-apply

This is the time it takes, on average, for the user to apply for a loan.

To calculate it, add a new column to your table, let's call it time_to_apply, which will contain the time to apply for the loan in minutes. The values in this column should be the difference between fields experience_started_at and application_submitted_at.

The results will be in multiples of days, so to convert to hours, multiply by 24, and multiply by 60 to get minutes. See the example below:

Now, take the average out of column D, and you have your average time to apply. You can also split the average times by account type (or any other field) using the formula AVERAGEIFS.

Please notice: we're not using application_confirmed_at because it doesn't represent anything the user has done, only the confirmation from the LOS that the application was received.

d) Cross-sell

This metric measures the ability of selling an additional loan to an user who has submitted an application in the same period.

We have three definitions of cross-sell:

• Simple: the most generous metric, we look at total loan applications over total applicants, so an applicant who applied for two equal loans (e.g. two auto loans) would count.
• Distinct purpose: in this case, we don't count applications with the same application_category, collateral_type, funds_access_structure and application_mode. e.g. Two vehicle loans, both for motorcycles and both are new loans do not count as cross-sell.
• Basic: more pessimistic, we only count applications with entirely different application_category.

Look at the following example:

The first thing to do here is to decide what is the period for cross-sell. Let's say it's 1 day, so we got all applications submitted on March 7th, 2024.

We have to define, per application, the keys to define if there is or there isn't cross-sell, and we do that by concatenating the values to be considered:

Now we're going to count, per user, how many cross sales they had based on each definition:

Now, we count how many applicants with over 1 cross sold applications we got, and divide by total number of applicants:

Now we just get the rates, dividing B, C and D by A:

• Simple: 2 applicants out of 5 = 40%
• Distinct purpose: 2 applicants out of 5 = 40%
• Basic: 1 applicant out of 5 = 20%

FAQ

1. How can I get started with data exports? A: Just ask your Customer Success manager to turn it on for you! We will need to set up a proper SFTP connection, so we might have to engage your IT team. We also require the server to be hosted by you. As an alternative to SFTP, you can download these reports through our File Transfer API.
2. Why should I use Clutch's data exports? A: It's a way for your team to be informed about what's going on without having to ask for ad-hoc reports. It works for both operations and IT, because it allows you to manipulate the .csv file in Microsoft Excel or Google Sheets, and also empowers your IT team to ingest the data into your data warehouse. It's an extract of data you can build reports on top of.
3. What's the cost? A: Clutch does not charge any additional fees. We just require you to have an SFTP server hosted by you for now. Alternatively, you can use our File Transfer API.
4. How long are the exports available for download? A: When using SFTP, it is up to you. Since the server is hosted by you, once we push the .csv files you can decide what's the best approach on how to deal with them. For the File Transfer API, new exports are generated daily at 11:00AM UTC and will remain available until 23:59 UTC each day.
5. What should I do if I didn't receive my daily file? A: Contact your Customer Success manager to inform them of the problem. We'll ensure you receive your data ASAP.
6. I lost access to a file and I need to recover a copy. How can I get it? A: Talk to your Customer Success manager to arrange a recovery.
7. I need a data export for sandbox data, how do I get it? A: Sandbox only has test users and can be misleading when interpreting data. We do not support sandbox data export.
8. How can I request a new data export to answer new business questions? A: Help us understand what exactly you are trying to achieve. Your implementation engineer can guide you if there is a way to use existing exports. If not they will bubble up this feedback to the product team.
9. What to do if I want to ask for additional fields in my data export? A: We currently only support limited fields as we build a scalable data infrastructure for 2024. However, do send us your feedback for future considerations.
10. What to do if I want to report inconsistencies in the data? A: Inform your Customer Success manager, and we will assign an engineer to work on it immediately.