Data Exports: Account Opening

Modified on Thu, Jul 2 at 4:17 PM

TABLE OF CONTENTS

Guidelines
Data Dictionary
How to Calculate Metrics From the Export
Frequently Asked Questions

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?

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:

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
- For the File Transfer API, the CSV file will be inside a zip
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
- 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 application_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.

Note: We recommend ingesting the 30-day file with an upsert operation as your primary method — it's more resilient to missed runs than appending the 1-day file.

How Not to Use the Exports

Important: Avoid duplicates. If you need a funnel metric (e.g., conversion) over a period longer than 30 days, don't append multiple 30-day files without a proper merge/upsert operation — doing so will create duplicate applications. Instead, iterate through multiple files and keep only the most recent status for each application.

Terminology

An application refers to any user who started the application process, even if they dropped off before opening the account.
An account is the actual account 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, multiple applications will appear (one per row).

Ingesting Exports Into Your Data Warehouse

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

Data Dictionary

Field Name	Description	Example
`application_id`	String: a UUID that represents a unique account opening application.	0030dbe4-ead5-4543-afa1-61d24688b4ef
`applicant_id`	String: a UUID that represents an applicant (not unique — `user_id` is the unique ID per applicant).	0030dbe4-ead5-4543-afa1-61d24688b4ef
`account_nr`	Number: the account number in core, if an account was created. Null if no account was created.	300101234
`applicant_name`	String: applicant's first and last name. Null if the user dropped off before identifying themselves.	Chris Coleman
`applicant_email`	String: applicant's email address. Null if the user dropped off before identifying themselves.	chris.coleman@example.com
`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
`applicant_phone`	String: applicant's phone number. Null if the user dropped off before identifying themselves.	2121234567
`applicant_address`	String: applicant's address. Null if the user dropped off before identifying themselves.	123 Main St.
`applicant_zipCode`	String: applicant's zip code. Null if the user dropped off before identifying themselves.	12345
`applicant_city`	String: applicant's city. Null if the user dropped off before identifying themselves.	Rio de Janeiro
`applicant_state`	String: applicant's state. Null if the user dropped off before identifying themselves.	CA
`applicant_employer`	String: applicant's employer. Null if the user dropped off before identifying themselves.	Amazing Credit Union
`applicant_job_title`	String: applicant's job title. Null if the user dropped off before identifying themselves.	Member Service Representative
`eligibility_criteria`	String: applicant's eligibility criteria for membership. Null if the user dropped off before identifying themselves.	family
`eligibility_detail`	String: details entered for the applicant's eligibility criteria. Null if the user dropped off before identifying themselves.	Spouse - Name of family member
`product_name`	String: the account name the user is applying for. Null if the user dropped off before selecting one.	Amazing Account Plus
`product_type`	String: account type the user is applying for. Null if the user dropped off before selecting one.	savings ; certificate ; checking
`is_new_member`	Boolean: true if the applicant was an existing member when the application started, false if not.	true ; false
`funding_type`	String: the funding method the applicant chose: • CREDIT_CARD: credit card • INTERNAL_ACCOUNT: transfer from an existing account at the credit union • ACH: transfer from an existing account at another institution • CASH: cash • LFAO: funding performed by the credit union via internal transfer • NULL: user dropped off before choosing a funding method	CASH
`funding_amnt`	Number: amount the user chose to fund the account with.	$1000.00
`application_origin`	String: where the application originated: branch portal or consumer portal.	branch ; consumer
`branch_name`	String: the name of the branch where the application is sitting.	The Best Branch
`branch_user_id`	Number: identifier of the employee responsible for the application.	1234
`employee_name`	String: first and last name of the employee responsible for the application.	Nicholas Hinrichsen
`application_status`	String: current status of the application. Possible values: • ACCEPTED: the application was successful and an account has been opened • INITIATED: the application started and is still in progress • FRAUD_VERIFICATION: the fraud management review is in progress • DENIED: the application wasn't allowed to proceed • CANCELLED: the application started but didn't continue • ERROR: the application had an issue • EXTERNAL_FUNDING_CONFIRMATION: waiting for funding confirmation • LOAN_FIRST_DRAFT: a non-member submitted a loan application but an employee hasn't triggered account opening yet	ACCEPTED
`account_status`	String: current status of the account. Possible values: • CREATED: the account has been opened • CANCELLED: the application was cancelled before an account was created • PENDING: the account opening process is still in progress	PENDING
`funding_status`	String: current status of the funding. Possible values: • COMPLETED: the account has been funded • CANCELLED_OR_REFUNDED: the application was cancelled before an account was created • CREATED / INITIATED / PENDING: funding is still in progress • ERROR: a funding issue occurred	COMPLETED
`application_created_at`	Timestamp: date and time when the application started.	2024-03-07 18:08:32.765
`account_created_at`	Timestamp: date and time when the account was created. Deprecated — use `account_booked_to_core_at` instead.	2024-03-07 18:08:32.765
`application_updated_at`	Timestamp: date and time when this record was last updated.	2024-03-07 18:08:32.765
`received_AAN`	Boolean: true if the applicant received an Adverse Action Notice, false if not.	true ; false
`aan_created_at`	Timestamp: date and time when the Adverse Action Notice was sent.	2024-03-07 18:08:32.765
`aan_reason`	String: reason(s) the applicant received an Adverse Action Notice.	Asset ownership history
`utm_source`	String: part of the URL used for tracking (must be present in the Clutch URL).	facebook
`utm_medium`	String: part of the URL used for tracking (must be present in the Clutch URL).	ads
`utm_campaign`	String: part of the URL used for tracking (must be present in the Clutch URL).	auto_refi_june_2024
`utm_content`	String: part of the URL used for tracking (must be present in the Clutch URL).	group_a
`utm_term`	String: part of the URL used for tracking (must be present in the Clutch URL).	loans
`funding_started_at`	Timestamp: date and time when the user chose a funding method (null if they didn't).	2024-03-07 18:08:32.765
`funding_authorized_at`	Timestamp: date and time when the user's funding was authorized (null if it wasn't).	2024-03-07 18:08:32.765
`fraud_check_approved_at`	Timestamp: date and time when the user was approved by the integrated fraud check flow (null if not applicable).	2024-03-07 18:08:32.765
`account_booked_to_core_at`	Timestamp: date and time when the user's account was booked to core (null if not applicable).	2024-03-07 18:08:32.765
`user_id`	String: a UUID that represents a unique applicant.	0030dbe4-ead5-4543-afa1-61d24688b4ef
`session_id`	String: an ID representing a unique user session. A session lasts up to 4 hours, or ends after 15 minutes of inactivity.	123456789
`extra_fee`	Number: the extra fee applied, if any. Can be null.	5.0
`accepted_tcpa_at`	Timestamp: date and time when TCPA was accepted (null if it wasn't).	2024-03-07 18:08:32.765
`fraud_check_entity_token`	String: an identifier for the fraud check token. Can be null.	J-KASoidfjs09SNGFKJ123
`account_id`	String: a UUID that represents a unique account.	0030dbe4-ead5-4543-afa1-61d24688b4ef
`citizenship`	String: applicant's citizenship.	US/American
`handoff_type`	String: how the handoff for in-branch applications happened. Possible values: DIGITAL or NON_DIGITAL.	DIGITAL
`consent_type`	String: how consent for account opening was given. Possible values: DIGITAL_CONSENT (applicant clicked in the experience) or MANUAL_CONSENT (applicant gave consent to the employee assisting them).	DIGITAL_CONSENT
`account_features`	Array: list of features the applicant added to the account.	["OVDT", "DBT"]
`fraud_check_status`	String: most up-to-date fraud check status. Possible values: • APPROVED • DENIED • PENDING_USER_ACTION • REFER • ERROR	APPROVED
`fraud_check_application_id`	String: an identifier for the fraud check application. Can be null.	JA-DwyIHO5cTHu9uqquWDy9
`promo_code`	String: promotional code entered during the application for special offers.	CHRISTMAS10
`had_manual_review`	Boolean: whether a human had to intervene or manually review the application.	true ; false
`had_step_up`	Boolean: whether the user saw a step-up screen during the fraud check flow.	true ; false
`fraud_check_started_at`	Timestamp: date and time when the fraud check was started.	2024-03-07 18:08:32.765
`fraud_check_decision_made_at`	Timestamp: date and time when the fraud check was completed (application approved or declined).	2024-03-07 18:08:32.765
`device_id`	String: unique identifier for the device used to start the application session.	a1b2c3d4-e5f6-7890-abcd-ef1234567890
`authentication_method`	String: the method used to authenticate the session that created the application. Possible values: sso, password.	sso
`referral_code`	String: referral code entered during the application (e.g., member-get-member or employee referral program). Null if no referral code was used.	MGM-REF123
`fraud_check_tags`	String: fraud check tags associated with the application, returned as a semicolon-separated string. Null if no tags were returned.	Main Applicant;Name Verified

How to Calculate Metrics From the Export

Conversion

This metric measures the percentage of applications that ended up with an account created, out of all applications that were initiated.

To calculate a conversion rate, count applications where application_status = ACCEPTED, then divide by the total number of applications. You can break this down by other fields — such as employee, funding type, or product — to associate your conversion metric with something specific.

Example: to measure conversion by employee, first get a list of all employee names and count the total number of applications handled by each, using a formula like =COUNTIFS(employee_name, "the employee you want"):

Employee	Count of Applications
Nicholas Hinrichsen	250
Arthur Araujo	23
Tamanna Kotwani	32
Mauricio Castro	107

Then add a column with the count of accepted applications and divide the two numbers:

Employee	Count of Applications	Count of Accepted Applications	Conversion
Nicholas Hinrichsen	250	55	22%
Arthur Araujo	23	5	22%
Tamanna Kotwani	32	28	88%
Mauricio Castro	107	85	79%

Avg. Time-to-Open Account

This is the average time it takes to open an account. Add a new column (for example, time_to_open) that calculates the difference between application_created_at and account_created_at, in minutes.

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

application_id	application_created_at	account_created_at	time_to_open (minutes)
0030dbe4-ead5-4543-afa1-61d24688b4ef	3/7/2024 18:08:33	3/7/2024 18:16:00	7.45
00b49222-93db-4664-ba1d-1161ae9960af	1/23/2024 20:57:05	1/23/2024 21:01:58	4.88
0130be4e-79b5-42d8-b45c-398987e6f3de	1/12/2024 15:23:05	1/12/2024 15:26:12	3.12
013d362c-2cec-498c-a444-5fc6f14dc766	2/12/2024 22:36:45	2/12/2024 22:36:46	0.01

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

Funding Amount by Funding Type

This shows how much is being deposited into new accounts by funding method. List each funding type, then use a formula like =AVERAGEIFS(funding_amount, funding_type, "the funding type you want"):

Funding Type	Avg. Funding Amount
CREDIT_CARD	$1,842.50
INTERNAL_ACCOUNT	$8,761.42
CASH	$5,295.92
ACH	$2,144.23
LFAO	$0.01

Volume Over Time

To chart account volume (in count or funding amount) over time, decide on your desired granularity (daily, weekly, monthly) and assign each row to a period. For a weekly view, use a formula like WEEKNUM to get the week number of the year:

application_id	account_number	account_created_at	week_of_year
0030dbe4-ead5-4543-afa1-61d24688b4ef	305576904	3/7/2024 18:16:00	10
00b49222-93db-4664-ba1d-1161ae9960af	305371007	1/23/2024 21:01:58	4
0130be4e-79b5-42d8-b45c-398987e6f3de	305342298	1/12/2024 15:26:12	2
013d362c-2cec-498c-a444-5fc6f14dc766	305403371	2/12/2024 22:36:46	7

Then count the number of accounts or sum the funding amount for each period using COUNTIFS or SUMIFS:

Week	Number of Accounts	Total Funding
1	62	$45,200
2	75	$58,000
3	84	$72,300
4	23	$22,087
5	78	$60,684

You can also calculate the average funding amount per week by dividing total funding by number of accounts. Use this table as the basis for a bar, column, or line chart.

Frequently Asked Questions

How can I get started with data exports?

Ask your Clutch Customer Success manager to turn it on for you. We recommend querying files through our File Transfer API — it makes it easy to build automations, set retries, or pull retroactive files. As an alternative, you can use SFTP, which requires setting up a connection (you may need to involve your IT team) and hosting your own server.

Why should I use Clutch's data exports?

Data exports keep your team informed without needing to request ad hoc reports. They work for both operations and IT — you can open the .csv file directly in Excel or Google Sheets, or have your IT team ingest it into your data warehouse. It's an extract you can build reports on top of.

What's the cost?

Clutch does not charge any additional fees. If you use SFTP, you'll need to host your own SFTP server. Alternatively, you can use our File Transfer API with minimal setup on your end.

How long are the exports available for download?

For SFTP, it's up to you — since you host the server, you control how long files are retained once we push them. If you need retroactive files, contact our support team. For the File Transfer API, new exports are generated daily at 11:00 AM UTC and remain available for one week; you can query them at any time during that window.

What should I do if I didn't receive my daily file?

Contact our support team at support@withclutch.com for assistance.

I lost access to a file and need to recover a copy. How can I get it?

If you're using the File Transfer API, you can query it using the referenceDate parameter to retrieve past files — see the File Transfer API documentation for details. If you're using SFTP, contact your Customer Success manager to arrange a recovery.

I need a data export for sandbox data. How do I get it?

Sandbox only contains test users and can be misleading when interpreting data, so we don't support sandbox data exports.

How can I request a new data export to answer new business questions?

Tell us what you're trying to achieve. Your implementation contact can help determine whether an existing export already supports your use case — if not, they'll pass your request along to our product team.

What should I do if I want to request additional fields in my data export?

We don't currently support custom fields per credit union, and we typically add new fields on roughly a 90-day cadence. Contact your Customer Success manager to let them know which fields you need.

What should I do if I want to report inconsistencies in the data?

Let your Customer Success Manager know or submit a ticket to our Support team, and we'll assign an engineer to investigate right away.