DataDirect Tables

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_activecount_customer_idsum_customer_principal_cents
TRUE4992291066

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_statuscount
SUSPENDED19031
ACTIVE3185
CLOSED208

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_idaccount_idprincipal_balance_cents
77be883f-7f3f-467905d2c4e9-c200-4567500000000
69091164-b7f0-4ea70bb24aa2-2399-4fa8486050000
13c97c81-df13-48a805d2c4e9-c200-4567486050000
0e5a0371-eea4-48670bb24aa2-2399-4fa8473046000
7c675a5b-c10b-427e05d2c4e9-c200-4567473046000

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_idcount
REVOLVING CARD PROGRAM2589503323
MULTILOAN267000000
NO INTEREST192000000

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](https://docs.canopyservicing.com/reference/getaccountcards) > Responses > 200_

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_idcard_type
2d16cae7-2112-49f5-a8bfVIRTUAL
5b37bef5-c178-4592-ac49VIRTUAL
70c7d6d0-d0e4-4779-98ddVIRTUAL

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