Data Exports: HAL

Modified on Thu, Jul 9 at 5:06 PM

TABLE OF CONTENTS

Export Details
What's Included in This Export
Terminology
Ingesting Exports Into Your Data Warehouse
Data Dictionary
How to Calculate Metrics from the Export
- a) Join with Lending Data Export
- b) Join with LOS Data
Frequently Asked Questions

The HAL Report measures and analyzes interactions between applicants and HAL at the application level. Its primary purpose is to provide visibility into how HAL is being used, how actively applicants engage with it, and how those interactions relate to specific loan applications.

Export Details

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

Note: The file uses a .csv extension, but fields are separated by pipes (|), not commas. Make sure your ingestion process is configured to parse it accordingly.

The data included follows a fixed, predetermined format that scales across 100+ credit unions.

What's Included in This Export

Each file contains HAL interactions from the past 1 day.

File name (SFTP only): hal-1-day_YYYYMMDDThhmmssZ_part-00000.csv

Terminology

An application refers to any user who started the application process, even if they dropped off before submitting it to the LOS.

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 exist, create a new row.
Append: Always add new rows to the existing table.

Data Dictionary

Field Name	Description	Example
hal_id	String: an ID that represents a unique conversation with an applicant.	0030dbe4-ead5-4543-afa1-61d24688b4ef
application_id	String: an ID that represents a unique application sent to the LOS. Matches the one in the lending export.	0030dbe4-ead5-4543-afa1-61d24688b4ef
application_nr	Number: loan number in the LOS.	1234
user_id	String: a UUID that represents a unique user (main applicant). Matches the one in the lending export.	0030dbe4-ead5-4543-afa1-61d24688b4ef
applicant_name	String: the main applicant's full name.	John Doe
user_phone	Number: the main applicant's phone number.	1234567891
conversation_started_at	Timestamp: date and time when the conversation started.	2024-03-07 18:08:32.765
hal_message_count	Number: number of messages sent by HAL to the applicant.	4
user_message_count	Number: number of messages sent by the applicant to HAL.	1
record_last_updated_at	Timestamp: date and time when this record was last updated.	2024-03-07 18:08:32.765

How to Calculate Metrics from the Export

a) Join with Lending Data Export

Use the application_id field to connect this data to the lending data export.

b) Join with LOS Data

Use the application_nr field to connect this data to your LOS reports.

Frequently Asked Questions

How can I get started with data exports?

Ask your Implementation team or Customer Success Manager to turn it on for you. We recommend querying files through the File Transfer API, which makes it easy to build automations, set retries, or pull retroactive files. Alternatively, you can use SFTP, which requires setting up a connection (and may 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 teams: operations can open the .csv file in Excel or Google Sheets, while IT can ingest the data directly into your data warehouse to build reports on top of it.

What's the cost?

Clutch does not charge any additional fees. If you choose SFTP, you'll need to host your own SFTP server. If you use the File Transfer API, only minor configuration is required on your side.

How long are the exports available for download?

With SFTP, retention is up to you, since you host the server. If you need retroactive files, submit a ticket with our support team. With the File Transfer API, new exports are generated daily and remain available for one week, so you can query them whenever you need to.

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 re-query the file using its reference date. If you're using SFTP, contact your Implementation team or Customer Success Manager to arrange a recovery.

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

Sandbox environments only contain test users and can be misleading for data interpretation, so we do not support sandbox data exports.

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

Let us know what you're trying to achieve. Your implementation engineer can guide you on whether an existing export already answers your question, or share your request with our product team.

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

Custom fields per credit union aren't currently supported, and new fields are typically added on a quarterly basis. Contact your Implementation team or 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 Implementation team or Customer Success Manager know, or contact our support team at support@withclutch.com, and we'll assign an engineer to investigate right away.