Data Exports: Funding

Modified on Mon, Mar 2 at 5:35 PM

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 funding transactions for the past 1 day:
- A list of all account funding transactions (concluded) in the previous 1 day
- File name (for SFTP): funding-1-day_YYYYMMDDThhmmssZ_part-00000.csv
Account funding transactions for the past 60 days:
- A list of all account funding transactions (concluded) in the previous 60 days
- File name (for SFTP): funding-60-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 60 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.
Analysis: if you are going to analyze the data in a spreadsheet, use the 60 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 60 days, you shouldn't append multiple 60 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):

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.
Append: always add new rows to the existing table.

Data Dictionary

Field name	Description	Example
applicant_name	String: applicant's first and last name. Null if the user dropped-off before identifying themselves.	Jane Doe
member_nr	Number: the user's member number in core. Null if the user wasn't an existing member and didn't finish the application.	400012345
application_id	String: a uuid that represents an unique account opening application.	0030dbe4-ead5-4543-afa1-61d24688b4ef
account_id	String: a uuid that represents an unique account.	0030dbe4-ead5-4543-afa1-61d24688b4ef
transaction_funding_date	Timestamp: date and time when the account was funded.	2024-03-07 18:08:32.765
account_type	String: account type that the user is applying for. Null if the user dropped-off before selecting one.	savings ; certificate ; checking
funding_type	String: the funding method the applicant has chosen:
• CREDIT_CARD: credit card (Stripe);
• INTERNAL_ACCOUNT: transfer from existing account at the CU;
• ACH: transfer from existing account in another institution (Plaid);
• CASH: cash;
• LFAO: funding performed by the CU via transfer from the GL;
• NULL: user dropped-off before choosing a funding method.	CASH
funding_amnt	Number: amount the user decided to use to fund the account.	$1000.00
provider_name	String: name of funding provider.	stripe ; plaid
payment_transaction_id	String: identifier used by the provider for the transaction.	0030dbe4-ead5-4543-afa1-61d24688b4ef
account_number	String: account number from Core.	2121234567
branch_name	String: the name of the branch where the application is sitting at.	The Best Branch

FAQ

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