After connecting DataDirect to your query tool of choice and knowing which entities you can query, you can begin building your own queries! Below are the entities available along with their related fields.

Table Entity Relationship

Entity relationships refer to the associations or connections between different entities (often represented as tables) within a database, these relationships define how data in one table is related to data in another table.

Primary Key (PK): A unique ID for each record in a table. Ensures uniqueness and integrity. Used for identification and linking with other tables.

Foreign Key (FK): Links data between tables. Refers to a Primary Key in another table. Creates relationships, maintains consistency, and enforces data integrity.

DataDirect Cards/Tables

Customers

Retrieve a list of all customers within your system. To manage large datasets efficiently, consider utilizing pagination to retrieve data in smaller, manageable chunks.

Find the meaning of each event inside Customers > Get a specific customer > Responses > 200

Customers Table Query

To see the full table, run the following query or select specific Line Items to create your own queries.

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;

Customers Table Query Example

Say you ran a Valentine's Day commercial on the 14th of Feb in Chicago, and now you wanted to find out how many unique and most valuable customers (e.g. have a balance above $600) joined since then. This should help you understand the ROI of that commercial and the total amount underwritten.

You would start by filtering Customer records by creation date (e.g. Feb 15th), then a specific location (e.g. Chicago), and a balance above a certain threshold (e.g. $600 or 60,000 cents). For all of the filtered records, we would count unique Customers and add up all of their (principal) balances.

SELECT
    c.is_active,
    COUNT(DISTINCT c.customer_id) AS count_customer_id,
    COALESCE(SUM(c.customer_principal_cents), 0) AS sum_customer_principal_cents
FROM
    data_direct.customers AS c
WHERE
    c.address_city = 'Chicago'
    AND c.verification_status = 'verified'
    AND c.customer_principal_cents >= 60000
    AND c.created_at >= '2023-02-15'::timestamp
GROUP BY
    c.is_active
ORDER BY
    count_customer_id DESC;

Number of unique customers onboarded since Feb 15th in Chicago and the total amount underwritten.

is_active	count_customer_id	sum_customer_principal_cents
TRUE	49	92291066

Accounts

Query specific account details in real-time, including associated data and related entities. This feature provides a comprehensive view of account information.

Find the meaning of each event inside Accounts > Get a specific account > Responses > 200

Accounts Table Query

To see the full Accounts table, run the following query or select specific Line Items to create your own queries.

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;

Account Query Example

Count how many accounts are in each status and arrange them from the most common to the least. This information can guide your decisions. For example, if you see many accounts marked "Suspended," you might want to focus on helping those customers catch up with their payments. It's like having a map to understand your customers better and provide them with the right support.

SELECT account_status, COUNT(*) as count
FROM data_direct.accounts
GROUP BY account_status
ORDER BY count DESC;

Total Account Status

account_status	count
SUSPENDED	19031
ACTIVE	3185
CLOSED	208

Line Items

Query a specific line item to access details such as payment history, accrued interest, and other relevant information associated with that particular item.

Find the meaning of each event inside Line Items > Get line items for a specific account. > Responses > 200

Line Items Table Query

To see the full table, run the following query or select specific Line Items to create your own queries.

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;

Line Item Query Example

This query aims to provide clarity on late fee charges incurred during a specific period, from March 1, 2023, to March 31, 2023. By focusing on "LATE_FEE" line items, it offers insights into the financial impact of late fees, facilitating data-driven decisions regarding fee structures and policies. The query's summarized result, "late_fees_charged," quantifies the cumulative late fee charges, supporting financial planning and enhancing transparency in reporting. In essence, it plays a crucial role in understanding and managing late fee revenue effectively.

SELECT sum(original_amount_cents) as late_fees_charged
from data_direct.line_items li
WHERE li.line_item_type = 'LATE_FEE'
AND effective_at < '2023-04-01'
AND effective_at > '2023-02-28'

Total fees charged in March 2023

late_fees_charged
18001590

Statements

Access a specific statement using its unique Statement ID. To obtain historical statements for an account, you can reference the IDs through the _accounts/{account_id}/statements/list route.

_Find the meaning of each event inside Statements > Get a specific statement for a specific account. >

Responses > 200_

Statements Table Query

To see the full table, run the following query or select specific Line Items to create your own queries.

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;

Statement Query Example

In the world of finance, some accounts are more significant than others. This query helps you find the top five accounts with the biggest Principal balances.

Why is this important? Well, it could be that most of your revenue comes from these big accounts, making sure they're taken care of properly.

You can share the results with your Customer Success managers daily/ weekly/ monthly, and they will make sure payments on these accounts are on time, reducing the risk for your lending program.

SELECT
    statement_id,
    account_id,
    principal_balance_cents
FROM data_direct.statements
ORDER BY principal_balance_cents DESC
LIMIT 5

Statements with the Highest Principal Balance

statement_id	account_id	principal_balance_cents
77be883f-7f3f-4679	05d2c4e9-c200-4567	500000000
69091164-b7f0-4ea7	0bb24aa2-2399-4fa8	486050000
13c97c81-df13-48a8	05d2c4e9-c200-4567	486050000
0e5a0371-eea4-4867	0bb24aa2-2399-4fa8	473046000
7c675a5b-c10b-427e	05d2c4e9-c200-4567	473046000

Statements Line Items

This table focuses on individual financial transactions, such as purchases, payments, and fees. It can also provide additional custom or external data related to each transaction.

Statements Line Items Table Query

To see the full table, run the following query or select specific Line Items to create your own queries.

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;

Statement Line Items Query Example

Financial institutions can use this query to gain insights into their outstanding loan balances by product. It helps them understand which loan products have the highest outstanding amounts, facilitating strategic decision-making regarding loan management and allocation of resources.

SELECT
    statement_id,
    product_id,
    principal_cents
FROM
    data_direct.statement_line_items
WHERE
    line_item_status = 'VALID'
    AND line_item_type = 'LOAN';
GROUP BY 1
ORDER BY "sum_principal_cents" DESC

Identifying Outstanding Principal Balances

product_id	count
REVOLVING CARD PROGRAM	2589503323
MULTILOAN	267000000
NO INTEREST	192000000

Cards

This endpoint facilitates the management of cards and card transactions. You can specify spend limits and integrate data from Lithic Card systems, streamlining card-related operations.

Find the meaning of each event inside Issuing > Get all cards > Responses > 200

Cards Table Query

To see the full table, run the following query or select specific Line Items to create your own queries.

SELECT
	card_id,
  external_card_id,
  account_id,
  spend_limit,
  token,
  card_program_token,
  last_four,
  card_type,
  state,
  memo
FROM data_direct.cards;

Card Transactions

Cards Transactions Table Query

To see the full table, run the following query or select specific Line Items to create your own queries.

SELECT
	card_transaction_id,
  external_card_transaction_id,
  card_id,
  line_item_id,
  issuer_processor,
  attributes
FROM data_direct.card_transactions;

Card Query Example

If you're in charge of a credit card company. You want to keep track of cards with no spending limits. This query counts how many cards fall into this category and shows the different types of cards. By organizing the data this way, you can understand which card types are most popular without spending limits.

SELECT
    c.card_id,
    c.card_type
FROM
    data_direct.cards AS c
WHERE
    c.spend_limit IS NULL;

Sort Cards without spending limit based on type

card_id	card_type
2d16cae7-2112-49f5-a8bf	VIRTUAL
5b37bef5-c178-4592-ac49	VIRTUAL
70c7d6d0-d0e4-4779-98dd	VIRTUAL

Amortization Schedule Forecast

Retrieve the amortization schedule for a specific account, especially if it involves instalment loans. For accounts with multiple line item loans, this feature consolidates all schedules for ease of reference. Pagination can be applied to manage extensive amortization schedules effectively.

Find the meaning of each event inside Amortization Schedule> Get the amortization schedule for a specific loan > Responses > 200

Amortization Schedule Forecast Table Query

To see the full table, run the following query or select specific Line Items to create your own queries.

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;

Past Due 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, it's 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.

Past Due Report Table Query

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

To find the report query follow DataDirect Reporting > Past Due Report