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 or 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:

• Account opening applications for the past 1 day:
  • A list of all account opening applications (submitted and dropped) in the previous 1 day
  • File name (for SFTP): account-opening-1-day_YYYYMMDDThhmmssZ_part-00000.csv
• Account opening applications for the past 30 days:
  • A list of all account opening applications (submitted and dropped) in the previous 30 days
  • File name (for SFTP): account-opening-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 opening the account.
• An account is the actual account to be created via an application.
• If one application was made for multiple accounts, the export will have multiple rows for the same application, one for each account.
• If a user applied multiple times for the same account, 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) Conversion

This is a metric to measure the % of applications that ended up with an account created among all applications that were initiated.

To better calculate conversion rates, count applications with application_status = ACCEPTED.

After that, divide by the total number of applications. You can break it down by any other fields to have your conversion metric associated with something else, such as by employee, by funding type or by product. Example:

We want to measure conversion by employee. The first thing is to get a list of all employee names and then count the total number of applications handled by each of them. To do that, apply the formula (in a sheet): =COUNTIFS(column: employee_name, field: the employee you want). 

This should be the result:

After that, just add another column with the count of applications accepted and divide the two numbers:

b) Avg. time-to-open account

This is the time it takes, on average, to open an account.

To calculate it, add a new column to your table, let's call it time_to_open, which will contain the time to open that account in minutes. The values in this column should be the difference between fields application_created_at and account_created_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 fund. You can split the average times by account type (or any other field) using the formula AVERAGEIFS.

c) Funding amount by funding type

This is a way of knowing how much is being deposited into new accounts for funding. You can split by any field. In this example we'll usefunding type.

Grab all values for funding type and insert into a table. Then, just take the averages based on them, using =AVERAGEIFS(column: funding_amount, column: funding_type, field: the funding type you want):

d) Volume over time

The objective here is to create a chart with the volume (in quantity of accounts or in funding amount) of new accounts.

Decide on desired granularity (daily, weekly, monthly, etc.) and assign each row to one value. For example, let's say you want a weekly report. This means you need to assign each account to a week, and for that you can use the WEEKNUM function, that returns the week number of the year (from 1 to 53).

Now, you can simply count the number of accounts or even sum the amount of funding for each week of the year in a new table. For that, you can use the COUNTIFS or SUMIFS functions:

You can also calculate the average funding amount per week by dividing C by B. On top of this table, just create the chart you want: bar chart, column chart, line chart.

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 that you host your own SFTP server if you want to use SFTP to receive these reports. 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.