airajena opened a new pull request, #44:
URL: https://github.com/apache/fineract-business-intelligence/pull/44
## Summary
This PR introduces the complete **Repayment Behavior Dashboard** and its
underlying dbt transformation layer. It delivers cash collection, obligation,
and behavioral metrics (efficiency rates, payment discipline, cash component
breakdown, and waivers) segmented by branch and loan product over time.
---
## Repayment Behavior Dashboard Overview
### Row 1: KPI Cards
#### Chart 1: Collection Efficiency KPI
- **Type**: Big Number
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Formula**:
```sql
(SUM(actual_collected_amount) / NULLIF(SUM(contractually_due_amount), 0))
* 100
```
- **Interpretation**: Percentage of contractually due amount that was
actually collected over the selected period (principal + interest + fees +
penalties).
- **Format**: `,.2f`
<img width="274" height="160"
alt="collection-efficiency-kpi-2026-07-27T12-51-23 673Z"
src="https://github.com/user-attachments/assets/a9c97480-4b3d-49dc-8648-18dbc641fe8c";
/>
#### Chart 2: Collected Amount KPI
- **Type**: Big Number
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Formula**:
```sql
SUM(actual_collected_amount)
```
- **Interpretation**: Total cash collected (principal + interest + fees +
penalties) over the selected period.
- **Format**: `$,.0f`
<img width="274" height="160" alt="collected-amount-kpi-2026-07-27T12-51-28
123Z"
src="https://github.com/user-attachments/assets/1cf1b82d-1652-4672-8bbb-4b6a59b932c4";
/>
#### Chart 3: Repayment Transactions KPI
- **Type**: Big Number
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Formula**:
```sql
SUM(repayment_transaction_count)
```
- **Interpretation**: Total count of individual payment events (note that
one borrower can have multiple payments).
- **Format**: `,d`
<img width="274" height="160"
alt="repayment-transactions-kpi-2026-07-27T12-51-32 804Z"
src="https://github.com/user-attachments/assets/98aae915-8b2f-4ade-8fd1-52fb8f916ada";
/>
#### Chart 4: Repaying Borrowers KPI
- **Type**: Big Number
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Formula**:
```sql
COUNT(DISTINCT client_hash)
```
- **Interpretation**: Total count of unique client entities who made at
least one payment over the selected period.
- **Format**: `,d`
<img width="274" height="160"
alt="repaying-borrowers-kpi-2026-07-27T12-51-36 855Z"
src="https://github.com/user-attachments/assets/c96e8807-bab6-44f2-b69e-47f9526627cb";
/>
---
### Row 2: Trend Analysis (Historical Stock & Efficiency Flow)
#### Chart 5: Repayment Collection Trend
- **Type**: Line Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **X-Axis**: `reporting_date`
- **Y-Axis**:
* `actual_collected_amount` (Inflow collected)
* `contractually_due_amount` (Obligations due)
- **Interpretation**: Compares cash collections vs contractual due schedules
daily. Inflows exceeding due amounts indicate catch-up payments on past arrears.
- **Format**: `$,.0f`
<img width="565" height="336"
alt="repayment-collection-trend-2026-07-27T12-55-40 906Z"
src="https://github.com/user-attachments/assets/ed0d9953-a824-47d3-893c-9cdf400798ed";
/>
#### Chart 6: Collection Efficiency Trend
- **Type**: Line Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **X-Axis**: `reporting_date`
- **Y-Axis**:
```sql
(SUM(actual_collected_amount) / NULLIF(SUM(contractually_due_amount), 0))
* 100
```
- **Interpretation**: Shows daily collection efficiency rate. Under normal
operational seeding, it defaults to a flat 100% baseline in 2025, and shows
daily activity spikes/drops in 2026.
- **Format**: `,.1f`
<img width="632" height="335"
alt="collection-efficiency-trend-2026-07-27T12-55-50 956Z"
src="https://github.com/user-attachments/assets/3709abe3-e41a-45f4-9482-90c1e7e98364";
/>
---
### Row 3: Component Breakdown & Mix
#### Chart 7: Repayment Component Breakdown
- **Type**: Stacked Bar/Area Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **X-Axis**: `reporting_date`
- **Y-Axis Stack**:
* `principal_collected` (Principal portion)
* `interest_collected` (Interest portion)
* `fee_collected` (Fee portion)
* `penalty_collected` (Penalty portion)
- **Interpretation**: Displays daily collection volume segmented by
financial component.
- **Format**: `$,.0f`
<img width="632" height="335"
alt="repayment-component-breakdown-2026-07-27T12-55-55 875Z"
src="https://github.com/user-attachments/assets/9a8e6f1f-65a2-4ba5-8efa-7741091a9028";
/>
#### Chart 8: Collection Mix
- **Type**: Pie Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Metric**:
* `SUM(principal_collected)`
* `SUM(interest_collected)`
* `SUM(fee_collected)`
* `SUM(penalty_collected)`
- **Interpretation**: Aggregate percentage contribution of principal,
interest, fees, and penalties to total collections.
- **Format**: Percentage of total
<img width="632" height="335" alt="collection-mix-2026-07-27T12-55-59 725Z"
src="https://github.com/user-attachments/assets/fa601051-066c-4588-9f90-426d41b2289e";
/>
---
### Row 4: Branch & Product Breakdown
#### Chart 9: Collected Amount by Branch
- **Type**: Bar Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Group by**: `office_name`
- **Y-Axis**:
* `actual_collected_amount`
* `contractually_due_amount`
- **Interpretation**: Side-by-side comparison of total collections vs due
amounts by branch.
- **Format**: `$,.0f`
<img width="632" height="335"
alt="collected-amount-by-branch-2026-07-27T12-56-04 589Z"
src="https://github.com/user-attachments/assets/4e0b0ec3-0240-44ad-b6b6-4a076527998f";
/>
#### Chart 10: Collected Amount by Product
- **Type**: Bar Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Group by**: `product_name`
- **Y-Axis**:
* `actual_collected_amount`
* `contractually_due_amount`
- **Interpretation**: Side-by-side comparison of total collections vs due
amounts by loan product type.
- **Format**: `$,.0f`
<img width="632" height="335"
alt="collected-amount-by-product-2026-07-27T12-56-08 425Z"
src="https://github.com/user-attachments/assets/2de9d6ba-8ec6-4012-9498-d151f00a5281";
/>
---
### Row 5: Payment Discipline Trends
#### Chart 11: Payment Discipline Trend
- **Type**: Grouped Bar Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **X-Axis**: `reporting_date`
- **Y-Axis**:
* `early_payment_count` (Paid in advance)
* `on_time_payment_count` (Paid on due date)
* `late_payment_count` (Paid past due date)
- **Interpretation**: Monitors the daily volume of installments classified
by timing discipline.
- **Format**: `,d`
<img width="862" height="439" alt="image"
src="https://github.com/user-attachments/assets/945d8f32-a5a3-45a7-848b-644f1d43bcd6";
/>
#### Chart 12: Waiver & Late Payment Trend
- **Type**: Line Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **X-Axis**: `reporting_date`
- **Y-Axis**:
* `waived_amount`
* `paid_late_amount`
* `paid_in_advance_amount`
- **Interpretation**: Tracks the value of late fees, prepayments, and waiver
adjustments over time.
- **Format**: `$,.0f`
<img width="632" height="335"
alt="waiver-late-payment-trend-2026-07-27T12-56-18 307Z"
src="https://github.com/user-attachments/assets/cfdda260-8dfc-4f38-8aba-cdd6c84a54d4";
/>
---
### Row 6: Efficiency Breakdown
#### Chart 13: Collection Efficiency by Branch
- **Type**: Bar Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Group by**: `office_name`
- **Metric**:
```sql
(SUM(actual_collected_amount) / NULLIF(SUM(contractually_due_amount), 0))
* 100
```
- **Interpretation**: Historical collection efficiency rate per office
branch.
- **Format**: `,.1f`
<img width="632" height="335"
alt="collection-efficiency-by-branch-2026-07-27T12-56-23 042Z"
src="https://github.com/user-attachments/assets/5f25d514-a792-4bb9-8229-f5bceecad0c9";
/>
#### Chart 14: Collection Efficiency by Product
- **Type**: Bar Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Group by**: `product_name`
- **Metric**:
```sql
(SUM(actual_collected_amount) / NULLIF(SUM(contractually_due_amount), 0))
* 100
```
- **Interpretation**: Historical collection efficiency rate per loan product
type.
- **Format**: `,.1f`
<img width="632" height="335"
alt="collection-efficiency-by-product-2026-07-27T12-56-28 389Z"
src="https://github.com/user-attachments/assets/ad9a5acc-5b5e-48ea-b531-22c46c882bb3";
/>
---
### Row 7: Audit Grid
#### Chart 15: Repayment Summary Table
- **Type**: Table Chart
- **Dataset**: `repayment_behavior_secure_all_dates`
- **Group by**: `office_name`, `product_name`
- **Metrics**: Multi-column summary of collected, due, rounded efficiency
(using SQL `ROUND`), principal, interest, waived, penalty charged,
restructured, late/on-time counts, and transactions.
- **Interpretation**: Deep auditing interface for branch managers.
- **Format**: Multi-column custom formats.
<img width="1281" height="319"
alt="repayment-summary-table-2026-07-27T12-56-35 626Z"
src="https://github.com/user-attachments/assets/cee03108-ceae-4900-a36f-69aed1722572";
/>
---
## Repayment Behavior Dashboard
<img width="1313" height="2303"
alt="repayment-behavior-dashboard-2026-07-27T12-56-49 037Z"
src="https://github.com/user-attachments/assets/31aefaea-fe34-4ead-aeeb-c600a417e3fa";
/>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
