Description
This fact table provides the amounts for records (derived from transactions) with a wide assortment of date types (Cash Basis, Revenue Recognized, Locked, Period, Created, Applied, Bill Invoice and Payment Due) as well as the time when the derived transaction was created. The grain of this fact table is at the transaction level for all date types except revenue recognition, which have been distributed across the session days when the service would be rendered. To make this distinction we refer to each row as a record, which is either an original transaction or a flat rat distribution derived from the original transaction (note that where the amount does not distribute equally across days, the remainder will be added to the final day's transaction). A value of -1 for any date key indicates that this record is not applicable to this date type. For example, if a record has not yet been billed then no Bill Invoice date is available. This fact table, coupled with accompanying dimension tables, can help users to understand questions such as: What financial activity happened and when BY account, GL code (for accounting software such as NetSuite, SAGE, & QuickBooks), transaction type (discount, subsidy, payments, credits, deposits, charges [tuition and non-tuition], etc.)? What are the details of the transaction (e.g., charge name, payment type, credit description, transaction type, etc.)? Who owes money and how old is that debt? What payments failed (When?; The method?; How much?; Was auto pay set?; Were there payments after the failure?)?
Columns
| Column Name | Description |
|---|---|
record_key |
Unique identifier for the transaction record or distributed flat rate record |
business_id |
Unique identifier for the business |
school_id |
Unique identifier for the school |
account_id |
Unique identifier for the account associated with the transaction |
customer_id |
Unique identifier for the customer associated with the transaction |
student_id |
Unique identifier for the student associated with the transaction |
class_id |
Unique identifier for the class associated with the transaction |
fee_id |
Unique identifier for the fee associated with the transaction |
gl_code_key |
Surrogate key for the general ledger code associated with the transaction |
transaction_type_id |
Unique identifier for the transaction type associated with the transaction |
transaction_type_key |
Surrogate key for the combination of the business id and transaction type |
transaction_origin_key |
Surrogate key for the source that created the transaction (Kangarootime system generated entities will be unique per business) |
transaction_group_key |
Surrogate key for the combination of the business id and transaction category (top-level grouping such as 'Charge', 'Credit', 'Subsidy'), subcategory (mid-level grouping such as 'Payment Completed', 'Payment Failed ACH Correction', 'Tuition Charge', 'Tuition Discount') and detail field (low-level information associated with the specific subcategory such as payment method type, fee name) associated with the transaction |
cash_basis_date |
The cash basis transaction date (when the charge was due or payment was received), that can be used for cash basis or accounts receivable (connects to the dim_date table; NULL indicates the entry is not valid for this date type) [Date Assignment: If the transaction is a 'Payment Not Applied' then NULL is assigned, payment scheduled process date is used for 'Payment Applied' payments if it exists for the record (use created at date if it does not), finally, payment due date is used for all other transactions] |
revenue_recognized_date |
The date when the revenue is recognized (when the service was rendered), that can be used for accrual basis or revenue recognition; note that for flat rates the records are distributed across service dates and not the original transaction amount; (connects to the dim_date table; NULL indicates the entry is not valid for this date type) [Date Assignment: If the record is a correction then use the greater of (a) applied date or (b) created date, next, use the date when the session occurred (flat rates are distributed) if it exists for the record, finally, use applied date] |
billing_history_date |
The date when the account billing history changes (connects to the dim_date table; NULL indicates the entry is not valid for this date type) |
bill_invoice_date |
The date that the itemized bill, associated with the transaction, went out (connects to the dim_date table; NULL indicates the entry is not valid for this date type) |
created_date |
The created at date for the transaction (connects to the dim_date table; NULL indicates the entry is not valid for this date type) |
description |
The description of a transaction, generated by Kangarootime or the individual creating the record |
amount |
The amount of the transaction or distributed record (for revenue recognition dates) |
payment_processing_fee_paid_by_business |
The service fee amount paid by the business for the payment processing service (typically regarded as revenue lost by the business); NULL indicates that no service fee was paid by the business on the transaction |
payment_processing_fee_passed_on_to_account |
The service fee amount paid by the account for the payment processing service (in addition to payment for the charges incurred); NULL indicates that no service fee was paid by the account on the transaction |
returned_ach_payment_fee |
The fee amount paid by the business for returned ACH payments. Whether businesses charge their customers a dishonor fee for returned ACH payments or not, Payrix always charges the business for the associated fee |
auto_pay_status |
The status of payments; enumerated: 'Enabled' or 'Not Enabled' for payments and NULL for non-payments |
transaction_id |
Unique identifier for the transaction |
correction_level |
The level of reversal for the transaction; NULL indicates original, uncorrected transaction and if a transaction has been corrected, then 0 is the original/root, 1 is a reversal, 2 is a reversal of the first reversal, etc. |
root_transaction_id |
The transaction id of the root transaction; NULL indicates original, uncorrected transaction |
root_payment_transaction_id |
The transaction id of the payment transaction which the service fee record or ACH payment fee is applied to |
contract_id |
Unique identifier for the contract associated with the transaction |
transaction_identifier |
Unique identifier for the transaction that matches the Transaction ID field in the Kangarootime billing UI |
payment_received_status |
The current status for the business's receiving of the payment; enumerated: 'Disbursed', 'Not Disbursed', or 'Physical' |
bill_id |
Unique identifier for the bill |
application_offer_id |
Unique identifier of the individual offers on an application |
program_id |
Unique identifier for the program the transaction record is associated with |
application_student_key |
Unique identifier for the student the transaction record is associated with |
disbursement_id |
Unique identifier for the disbursement |
promo_code_id |
Unique identifier for the promo code applied |
applied_bill_id |
The bill_id that the payment/credit is intended to be applied to (note that these transaction actually belong in a following itemized bill) |
bill_type |
The type of bill; enumerated: 'Unbilled', 'Itemized Bill', 'Chargeback Bill', or 'Registration Bill' |
student_key |
Unique natural key for students (first name + last name + date of birth + business_id); this is useful for counting unique students |
business_unit_id |
Unique identifier for the business unit associated with the transaction |