x DataDirect How-to Guide (Discontinued)
From getting access to running your first SQL query
Access your credentials
Your DataDirect credentials can be retrieved via the following API call:
GET /organization/data_direct
The response will be formatted as follows:
{
"host": "***********.amazonaws.com",
"password": "**********",
"port": 5432,
"username": `
}
Connect to your preferred database client
From your preferred database querying tool, start a new PostgreSQL connection.
We recommend Postico 2 as a good free database tool if you don't have a preferred tool already.
You should be prompted with a form to enter in the credential information you received in the call you made above.
Query Your Data
Now that you have connected DataDirect to your query tool of choice and are aware of which entities you can query, you’re ready to begin building your own queries! Below are the entities available along with their related fields.
Table entity relationships
Checking data freshness
DataDirect data is typically refreshed every hour. To identify the time of the last refresh and the state it represents, you can check for the most recent related record in the refresh_history
:
select
table_name,
last_updated_at
from data_direct.refresh_history;
Available Entities and Fields
As mentioned on the DataDirect page, the data structure used closely mimics the data structure surfaced from the API.
Hence, you can find a definition for each column by finding an attribute with the same name.
Customers
Customers
To see the full table, run the following query.
select
customer_id,
external_customer_id,
address_line_one,
address_line_two,
address_city,
address_state,
address_zip,
address_country_code,
name_prefix,
name_first,
name_middle,
name_last,
name_suffix,
passport_number,
passport_country,
created_at,
updated_at,
customer_type,
external_customer_parent_id,
customer_parent_id,
is_borrower_portal_user,
is_active,
verification_status,
title,
phone_number,
ssn,
international_customer_id,
email,
date_of_birth,
customer_num_active_accounts,
customer_total_balance_cents,
customer_principal_cents,
customer_interest_balance_cents,
customer_am_interest_balance_cents,
customer_deferred_interest_balance_cents,
customer_am_deferred_interest_balance_cents,
customer_fees_balance_cents,
business_details,
bloom_metadata
from
data_direct.customers;
For more information and descriptions of each field, please see the Customer section of our API documentation.
Accounts
Accounts
To see the full table, run the following query.
select
external_account_id,
organization_id,
customer_id,
account_status,
account_status_subtype,
is_active_scra,
external_fields,
effective_at,
created_at,
updated_at,
product_id,
product_name,
product_color,
product_short_description,
product_long_description,
product_type,
close_of_business_time,
product_time_zone,
effective_close_of_business,
first_cycle_interval,
credit_limit_cents,
max_approved_credit_limit_cents,
min_pay_type,
initial_principal_cents,
interest_grace_method,
product_late_fee_cents,
late_fee_cents,
late_fee_cap_percent,
late_fee_grace,
payment_reversal_fee_cents,
payment_reversal_fee_cap_percent,
product_payment_reversal_fee_cents,
origination_fee_cents,
origination_fee_percent,
is_origination_fee_lesser_value,
is_origination_fee_amortized,
year_fee_cents,
month_fee_cents,
promo_purchase_window_inclusive_start,
promo_purchase_window_exclusive_end,
promo_purchase_window_len,
promo_inclusive_start,
promo_exclusive_end,
promo_impl_interest_rate_percent,
promo_len,
product_promo_len,
promo_default_product_interest_rate,
promo_default_product_len,
is_promo_interest_deferred,
product_promo_interest_rate,
loan_end_date,
post_promo_len,
post_promo_inclusive_start,
post_promo_exclusive_end,
post_promo_impl_interest_rate_percent,
post_promo_default_product_interest_rate,
post_promo_default_product_len,
product_post_promo_len,
product_post_promo_interest_rate,
interest_accrual_interval,
loan_discount_cents,
loan_discount_at,
recurring_fee_interval,
recurring_fee_amount_cents,
initial_delay_offset,
is_fee_interval_floored,
recurring_fee_label,
principal_balance_cents,
amortization_deferred_interest_balance_cents,
amortization_interest_balance_cents,
available_credit_cents,
deferred_interest_balance_cents,
fees_balance_cents,
interest_balance_cents,
open_to_buy_cents,
total_balance_cents,
total_paid_to_date_cents,
total_interest_paid_to_date_cents,
total_payment_supertype_to_date_cents,
total_original_amount_cents,
total_principal_original_amount_cents,
interest_rate_percent,
total_payoff_cents,
min_pay_due_at,
min_pay_cents,
unpaid_min_pay_cents,
current_min_pay_cents,
min_pay_fees_cents,
statement_min_pay_cents,
statement_min_pay_due_at,
statement_min_pay_charges_principal_cents,
min_pay_loans_principal_cents,
statement_min_pay_interest_cents,
statement_min_pay_deferred_interest_cents,
statement_min_pay_amortization_interest_cents,
statement_min_pay_amortization_deferred_interest_cents,
statement_min_pay_fees_cents,
statement_unpaid_min_pay_cents,
statement_current_min_pay_cents,
statement_cycle_payments_cents,
statement_previous_min_pay_cents,
default_payment_processor_method,
autopay_enabled,
ach_payment_processor_name,
ach_token,
ach_last_four,
debit_card_payment_processor_name,
debit_card_token,
debit_last_four,
credit_card_payment_processor_name,
checkout_dotcom_token_source_id,
checkout_dotcom_token_card_token,
checkout_dotcom_token_last_four,
checkout_dotcom_token_expires_on,
canopy_nacha_bank_routing_number,
canopy_nacha_bank_account_number,
canopy_nacha_bank_account_type,
payouts_config,
issuer_processor_details,
disbursement_source_payout_entity_external_id,
payout_entities,
payment_processor_config,
partner_entity,
attributes,
plaid_access_token_valid,
plaid_account_id_valid,
plaid_check_balance_enabled,
bloom_metadata
from
data_direct.accounts;
For more information and descriptions of each field, please see the Accounts section of our API documentation.
Line_items
Line_items
To see the full table, run the following query.
select
line_item_id,
external_line_item_id,
product_id,
account_id,
effective_at,
created_at,
updated_at,
valid_at,
line_item_status,
line_item_type,
description,
allocation,
original_amount_cents,
balance_cents,
principal_cents,
interest_balance_cents,
am_interest_balance_cents,
am_fees_balance_cents,
deferred_interest_balance_cents,
am_deferred_interest_balance_cents,
total_interest_paid_to_date_cents,
merchant_id,
merchant_name,
merchant_mcc_code,
merchant_phone_number,
external_fields,
line_item_relationships
from
data_direct.line_items;
For more information and descriptions of each field, please see the Line Items section of our API documentation.
Am_forecast_public
Am_forecast_public
To see the full table, run the following query
SELECT line_item_id,
cycle_exclusive_end,
min_pay_due_at,
am_min_pay_due_cents,
am_cycle_payment_cents,
am_interest_cents,
am_deferred_cents,
am_principal_cents,
am_fees_cents,
am_start_principal_balance_cents,
am_end_principal_balance_cents,
am_start_total_balance_cents,
am_end_total_balance_cents,
paid_on_time
FROM data_direct.am_forecast_public;
For more information and descriptions of each field, please see the Amortization Schedule section of our API documentation.
Statements
Statements
To see the full table, run the following query
select
statement_id,
external_statement_id,
effective_at,
account_id,
account_promo_purchase_window_inclusive_start,
account_promo_purchase_window_inclusive_end,
account_promo_inclusive_start,
account_promo_inclusive_end,
account_status,
account_status_subtype,
account_payout_entities,
credit_limit_cents,
total_charges_cents,
available_credit_cents,
open_to_buy_cents,
min_pay_cents,
min_pay_due_at,
min_pay_charges_principal_cents,
min_pay_loans_principal_cents,
min_pay_revolving_principal_cents,
min_pay_interest_cents,
min_pay_deferred_cents,
min_pay_am_deferred_interest_cents,
min_pay_am_interest_cents,
min_pay_fees_cents,
min_pay_am_fees_cents,
min_pay_past_due_interest_cents,
min_pay_past_due_deferred_cents,
previous_min_pay_cents,
unpaid_min_pay_cents,
current_min_pay_cents,
min_pay_floor_excess_cents,
cycle_inclusive_start,
cycle_exclusive_end,
cycle_length_days,
cycle_charges_cents,
cycle_loans_cents,
cycle_charge_returns_cents,
cycle_refunds_cents,
cycle_payments_cents,
cycle_payment_reversals_cents,
cycle_debit_adjustments_cents,
cycle_credit_adjustments_cents,
cycle_total_credits_cents,
cycle_interest_cents,
cycle_deferred_interest_cents,
cycle_am_deferred_interest_cents,
cycle_am_interest_cents,
cycle_total_interest_cents,
cycle_late_fees_cents,
cycle_fees_total_cents,
cycle_payment_reversals_fees_cents,
cycle_waived_deferred_interest_cents,
charges_principal_cents,
loans_principal_cents,
principal_balance_cents,
interest_balance_cents,
deferred_interest_balance_cents,
am_deferred_interest_balance_cents,
am_interest_balance_cents,
fees_balance_cents,
total_balance_cents,
previous_total_balance_cents,
expected_remaining_payment_amount_cents,
total_payoff_cents,
inception_to_date_payments_total_cents,
inception_to_date_purchases_total_cents,
inception_to_date_credits_total_cents,
inception_to_date_credits_and_payments_total_cents,
inception_to_date_interest_total_cents,
inception_to_date_fees_total_cents,
inception_to_date_interest_prior_total,
year_to_date_interest_total_cents,
year_to_date_fees_total_cents
from
data_direct.statements;
For more information and descriptions of each field, please see the Statements section of our API documentation.
Statement_line_items
Statement_line_items
To see the full table, run the following query
select
line_item_id,
external_line_item_id,
statement_id,
product_id,
effective_at,
created_at,
valid_at,
line_item_status,
line_item_type,
description,
allocation,
original_amount_cents,
balance_cents,
principal_cents,
interest_balance_cents,
am_interest_balance_cents,
am_fees_balance_cents,
deferred_interest_balance_cents,
am_deferred_interest_balance_cents,
total_interest_paid_to_date_cents,
merchant_id,
merchant_name,
merchant_mcc_code,
merchant_phone_number,
external_fields
from
data_direct.statement_line_items;
For more information and descriptions of each field, please see the Statement Line Items section of our API documentation.
Cards
Cards
To see the full table, run the following query
SELECT
card_id,
external_card_id,
account_id,
spend_limit,
token,
card_program_token,
last_four,
card_type,
state,
memo
FROM data_direct.cards;
For more information and descriptions of each field, please see the Cards section of our API documentation.
Card_transactions
Card_transactions
To see the full table, run the following query
SELECT
card_transaction_id,
external_card_transaction_id,
card_id,
line_item_id,
issuer_processor,
attributes
FROM data_direct.card_transactions;
Pre-built reports
Pre-built reports within DataDirect are reports common to lending operations that we have pre-written the SQL and surfaced in table form for ease of use.
It’s important to note that because these are strictly pre-written and run SQL queries, they are distinctly different than the other entities within DataDirect.
Past_due_report
(a.k.a. delinquency_report
)
Past_due_report
(a.k.a. delinquency_report
)This report strictly queries your data to find the oldest cycle due date that has not yet been paid in full. It uses this information along with the current date and time of execution to calculate the number of days past due.
Given this information, its important to note that the Past_due_report
(previously known as delinquency_report
) does not take into consideration account sub-statuses (such as Delinquent
), late fee grace periods, or any other operationally defined workflows that you may have in place.
Naming Deprecation
In an effort to reduce the possible confusion cased by the conflation of these terms, we will soon be deprecating the
delinquency_report
name in favor of more aptly naming it thepast_due_report
.
SELECT
organization_id,
external_account_id,
delinquency_bucket,
days_past_due,
cure_payment_cents,
stabilization_payment_cents,
minimum_payment_due_date timestamptz,
delinquent_as_of_date timestamptz
FROM data_direct.delinquency_report;
Field Definitions
organization_id
: Unique ID value associated with your organization.account id
: Unique ID value of the related account.days_past_due
: number of days since the oldest statement in which the minimum due balance was not paid in full.delinquency_bucket
: derived from days past due.
Possible values: ‘Current’ (for not delinquent), 0-29, 30-59, 60-89, 90-119, 120-149, 150-179, 180cure_payment_cents
: amount needed to completely exit delinquency.stabilization_payment_cents
: amount needed to maintain current delinquency level; this is the oldest minimum payment minus the payments made since then.minimum_payment_due_date
: The most recent statement’s minimum payment due date.
delinquent_as_of_date: The oldest missed payment due date for the account.
NACHA_entries
NACHA_entries
This table is relevant for customers who use Canopy NACHA payment processor payment processor
SELECT
nacha_entry_id,
external_nacha_entry_id,
subject_account_id,
external_subject_account_id,
external_subject_line_item_id,
nacha_file_reference_code,
receiving_identification_number,
created_at
FROM data_direct.nacha_entries;
For more information and descriptions of each field, please see the NACHA section of our API documentation.
Updated about 1 year ago