Connective Mercury trail report — exports, columns and gotchas

Where to find it in Mercury

Connective Mercury exposes trail data through several reports under Commissions → Reports. Brokers most often pull the Commission Summary (one row per commission event) and the Loan Balance snapshot (one row per loan with current outstanding balance). For comprehensive analysis you want both: the commission report gives you cash flow, the balance snapshot gives you book size.

Export as CSV or XLSX. Mercury's UI also shows YTD figures inline, but the exports are what you'll work with for portfolio decisions.

What's in the file

Connective Mercury is column-typed: separate columns for upfront, trail and clawback amounts. That's more verbose but less confusing than the row-typed AFG approach.

• Loan ID / Account Number — primary key. Keep this; it's how you join across multiple exports over time.
• Client name, broker — readable labels. The broker column is the writing broker for the loan, useful when you have multiple writers under one Connective accreditation.
• Lender, product — the bank holding the loan and the specific product code. Same use as in AFG: concentration analysis and BDM conversations.
• Settlement date — when the loan settled. Drives clawback-period analysis.
• Original loan amount / Loan limit — principal at settlement. Doesn't change as the loan amortises (unlike AFG's mixed-meaning column).
• Current balance — period-end outstanding, often as of the report run date.
• Upfront commission, Trail commission, Clawback — each in its own column. Sum across rows for portfolio totals.
• Period (start/end) — the commission period the row covers. Critical for accurate trend analysis — don't double-count loans that appear in multiple period reports.

Reading it for portfolio decisions

Three views the Connective report supports cleanly:

1. Book growth vs cash flow — pair the Loan Balance snapshot with the Commission Summary. A growing book with flat trail revenue points to lender-mix drift (newer settlements at lower commission rates).
2. Fixed-rate roll-off pipeline — if Mercury includes the fixed-rate expiry column for your loans (depends on lender data quality), surface it as a forward calendar.
3. Clawback exposure by settlement age — bucket loans by months since settlement (0–6, 7–12, 13–24) and sum upfront earned. The 0–6 bucket is the highest-risk clawback zone.

Common gotchas

• Multiple commission rows per loan per period — Connective sometimes splits a single loan's monthly trail into multiple rows (e.g. principal change mid-month). Aggregate by Loan ID + period when summing.
• Date columns as text — Connective's exports often serialise dates as DD/MM/YYYY text strings rather than Excel date serials. Cast to dates carefully — 01/04/2026 is 1 April in Australian convention, not 4 January.
• Balance lag — the Loan Balance snapshot is run at month-end; commission rows may reference an end-of-period balance one or two days different. Tiny variance is normal.
• Multiple brokers per loan — if you've changed loan-writer assignment, historical commission rows still carry the original writer. New rows carry the new one. Track the writer column over time, not just on the latest snapshot.

Working with this in TrailScope

TrailScope detects the Connective column shape automatically. The Loan Balance snapshot can be uploaded alongside the Commission Summary to enrich loan principal data. The clawback schedule and retention scoring then run on the unified dataset. See all features →