ACH Lead Performance Dashboard

Zoho replacement for the manually maintained Google Sheets "Accident Claims Helpline — Monthly Lead Report" (currently emailed to Nick). Shows lead volume, outcomes, claim-type mix, and conversion for the ACH lead source.

:::warning Design document This is a design/brainstorm doc — the dashboard is not built yet. Outcome-status mappings and the implementation platform need sign-off (see Open Questions). :::

Related: ACH Leads Integration — the intake flow that creates the ACH Lead records this dashboard reports on.

Source Report — Anatomy

The Google Sheet report (July 2026 example) has four sections. Everything below must be replicated.

1. Summary tiles

Tile	Example value	Definition
Total Leads	55	Leads received in the month
Went Ahead	30 (55%)	Leads that converted — % of total shown
On Hold / Pending	6	Awaiting decision
Booked / Not Yet Completed	5	Booking made, job not completed
Cancelled	14	Lead/claim cancelled

2. Daily breakdown table

One row per day that received leads (zero-lead days are omitted in the sheet).

Column	Definition
Date	Day the lead was received
Leads	Count received that day
Went Ahead	Of that day's leads, count that went ahead
On Hold / Pending	Of that day's leads, count on hold
Booked / Not Yet Completed	Of that day's leads, count booked but not completed
Cancelled	Of that day's leads, count cancelled
Repair Only Received	That day's leads with claim type Repair Only
Repair & Rental Received	That day's leads with claim type Repair & Rental
Rental Only	That day's leads with claim type Rental Only
Conversion	Went Ahead ÷ Leads (per day)

Month total row repeats every column across the month.

3. Month total — detailed outcome breakdown

Splits each outcome into its repair and rental components:

Column pair	Definition
Repairs / Rentals Went Ahead	30 / 10
Repairs / Rentals On Hold	6 / 4
Repairs / Rentals Booked — Not Yet Completed	5 / 3
Repairs / Rentals Cancelled	13 / 8

Derivation (verified against the sheet): these are not separately tracked numbers —

Repairs <outcome> = Repair Only leads with that outcome + Repair & Rental leads with that outcome
Rentals <outcome> = Rental Only leads with that outcome + Repair & Rental leads with that outcome

(A Repair & Rental lead counts once in each column.)

4. Claim type breakdown — month to date

Column	Definition
Claim Type	Repair & Rental / Repair Only / Rental Only
Received	Leads of this type in the month
Went Ahead / On Hold / Booked / Cancelled	Outcome counts within the type
Conversion %	Went Ahead ÷ Received

5. Cancelled — breakdown by claim type

Column	Definition
Cancelled	Cancelled count for the claim type
% of Total Cancelled	Share of all cancellations

Key Insight — One Fact Table Drives Everything

Every section above is an aggregation of the same per-lead record:

Attribute	Values
Date received	date
Claim type group	Repair Only / Repair & Rental / Rental Only
Outcome status	Went Ahead / On Hold–Pending / Booked–Not Yet Completed / Cancelled

So the build is: get these three attributes right per ACH Lead, and every tile/table is a simple group-by. No section needs its own data entry.

:::info Why the sheet should be retired The July sheet already contains a manual-tally error: the 06/07 row's outcomes sum to 5 against 4 leads, so the "On Hold" summary tile (6) disagrees with the daily column total (7). A dashboard computed from records can't drift like this. :::

The Numbers We Must Reproduce

Emily's manual email to Nick (July 2026) is the acceptance test. Every criterion below must be verified against the master report until Kratos returns exactly these figures:

#	Figure	Value	Plain-English meaning (from the email)
1	Total leads received	55	Leads received from ACH, month to date
2	Repair Only leads	30	Claim-type mix of the 55
3	Repair & Rental leads	24	"
4	Rental Only leads	1	"
5	Repair jobs gone ahead	30	Repair jobs that physically proceeded
6	Rental jobs gone ahead	10	Rental jobs that physically proceeded
7	Total jobs gone ahead	30	Leads (claims) that went ahead
8	Pending / on hold	6	Awaiting decision
9	Booked, not yet completed	5	"Scheduled for today and future dates, including 15/07" — booking exists, job not done
10	Leads cancelled	14	Whole lead cancelled
11	Repairs / rentals cancelled	13 / 8	Component split of the 14

Criteria Definitions (DRAFT — verify each against the master report)

Base population — figure 1 ✅ CONFIRMED

All figures are computed over this set:

Claim_Form records where
  Lead_Source1 == <Accident Claims Helpline>       // Lead_Source lookup
  && Date_Created within the reporting month

Expected: 55 — must return exactly the reference population below.

Decision	Status
Date basis	✅ `Date_Created` (confirmed)
Cancelled included?	✅ Yes — the 14 cancelled are inside the 55
Duplicates / re-entered claims	TBD — how does Emily avoid double-counting today?

note

Date_Created is a user-entered field (defaults to today) — unlike Added_Time it can be edited or backdated, so a lead can move between months after the fact. Accepted behaviour, but worth knowing when a month's total shifts after it was reported.

Claim type groups — figures 2–4

Group by whether Claim_Type1 includes a repair component, a rental component, or both:

Report group	`Claim_Type1` values
Repair Only	Repair Only · Repair Only IITR/Crash Collab · Towing + Repair
Repair & Rental	Repair & Rental · Towing + Repair + Rental · Towing + Repair + Free Rental (?)
Rental Only	Rental Only · Rental Only IITR/Crash Collab · Free Rental · Free Rental Only · Total Loss Rental Only · Towing/Storage + Rental (?) · Towing/Storage + Free Rental (?) · Total Loss + Towing/Storage + Rental (?)
Other / unmapped	Towing/Storage Only · Storage Only

Expected: 30 / 24 / 1 (and 0 unmapped)

Open: do Free Rental variants count as a rental for Nick's report, and do towing/storage+rental combos belong under Rental Only? If the group counts don't hit 30/24/1, this mapping is the first place to look.

Outcome buckets — figures 5–11

The four outcomes are mutually exclusive and exhaustive (30 + 6 + 5 + 14 = 55 exactly). Evaluate in precedence order — first match wins:

Order	Outcome	Proposed criteria (Claim_Form)	Expected
1	Cancelled ✅ CONFIRMED	`Claims_Management_Form.Status in (Cancel, Reject)` OR `Claim_Form.Is_Claim_Accepted == "Declined"`. Note for developer: this spans two forms — the Status lives on the linked Claims_Management_Form record, not the Claim Form itself. Returned 16 on the live 58 (15 with no job taken on + 1 cancelled after booking)	14 (snapshot)
2	Went Ahead ✅ CONFIRMED	`Job_Taken_On1` (date) is not empty AND not Cancelled. The "AND not Cancelled" is proven necessary: 1 record has a Job Taken On date but was cancelled afterwards — it counts as Cancelled. Live: 33 with the date set − 1 cancelled = 32	30 (snapshot)
3	Booked — Not Yet Completed ✅ CONFIRMED	`Claims_Management_Form.Hire_Status == "Booked"` (and not matched by rules 1–2). `Claim_Form.Booking_Date` holds the scheduled booking date — this is the date behind Emily's "scheduled for today and future dates, including 15/07" and should be shown as a column	5 (snapshot)
4	On Hold / Pending ✅ CONFIRMED	Residual — everything not matched by rules 1–3. Cross-check: these records show `Is_Claim_Accepted` in (Red Flag Job – Pending Validation, Pending). Raw counts on the live 58 were 9 + 4 = 13 — more than the 10 records left after Cancelled/Went Ahead, proving some Pending/Red-Flag records already progressed; only the residual (precedence) definition gives correct totals	6 (snapshot)

Making "On Hold / Pending" the residual guarantees the four buckets always sum to the total — no lead can fall through the cracks.

Component splits — figures 5, 6, 11

Derived from group × outcome (verified against the sheet — no extra tracking needed):

Repairs <outcome> = Repair Only <outcome> + Repair & Rental <outcome>
Rentals <outcome> = Rental Only <outcome> + Repair & Rental <outcome>

Checks: repairs gone ahead 20 + 10 = 30 · rentals 0 + 10 = 10 · repairs cancelled 6 + 7 = 13 · rentals cancelled 1 + 7 = 8 ✓

:::caution Edge case — partial component outcomes This model assumes one outcome per claim. A Repair & Rental claim where the rental is on hire but the repair hasn't started counts as fully "went ahead" on both components (that is how the sheet works today). If Nick needs true per-component status, the Claim Form has no separate repair-outcome vs rental-outcome field — that would be a schema addition, not a criteria tweak. Recommend keeping claim-level outcomes for v1. :::

Verification worksheet

Run these as advanced-search filters on the master report for July 2026, Lead_Source1 = Accident Claims Helpline. Fill in actuals; every row must match before the developer builds.

#	Filter to apply	Expected
V1	Base population only	55
V2	+ claim type in Repair Only group	30
V3	+ claim type in Repair & Rental group	24
V4	+ claim type in Rental Only group	1
V5	+ `Claims_Management_Stage` = Cancel	14
V6	V5 split: Repair Only / R&R / Rental Only	6 / 7 / 1
V7	+ stage in (On Hire, In Repairs, Recovery, Litigation, Hearing, Settled, Remitted)	30
V8	V7 split: Repair Only / R&R / Rental Only	20 / 10 / 0
V9	+ booking in future, not delivered, not cancelled/went-ahead	5
V10	Remainder (55 − V5 − V7 − V9)	6
V11	V10: check each record really is pending/on hold	—

Where a row doesn't match, list the offending records and note which field value broke the rule — that's exactly the information needed to refine the criteria.

Verification log

Date	Check	Result	Reading
14/07/2026	Base population (Lead Source = ACH, `Date_Created` in July)	58	Live drift — Emily's 55 was as of ~13/07. Re-run with `Date_Created` 01/07–13/07; must return exactly the 55 reference file numbers
14/07/2026	`Job_Taken_On1` (date) not empty, on the 58	33	Leading Went Ahead candidate — 33 − 30 = 3, same as the population delta. Re-run on the ≤ 13/07 subset; expect 30
14/07/2026	Claim type grouping (on the 58, in progress)	Towing + Repair = 21 · Towing + Repair + Rental = 11 · Towing/Storage Only = 1	ACH leads arrive mostly as towing-combo types — confirms Emily's groups are roll-ups. The 1 × Towing/Storage Only has no home in her 3 groups: check whether it's one of the 3 post-snapshot leads or inside the reference 55
14/07/2026	Cancelled = Claims_Management `Status` in (Cancel, Reject) OR `Is_Claim_Accepted` = Declined	16 (15 with `Job_Taken_On1` empty + 1 with it set)	✅ Criteria confirmed. The 1 record cancelled after booking proves precedence: Cancelled is evaluated before Went Ahead
14/07/2026	Cancelled (16) by claim type	Towing + Repair = 4 · Towing + Repair + Rental = 8 · Towing/Storage Only = 1 · Repair & Rental = 2 · Rental Only = 1	Grouped: Repair Only group 4, R&R group 10, Rental Only 1, unmapped 1. Emily's sheet had 6 / 7 / 1 — her Repair Only cancels (6) exceed what Kratos can ever show (4), and cancels can't decrease over time, so her manual grouping/tallies don't match Kratos. Consistent with the tally error already found in the sheet (06/07 row)
14/07/2026	Booked — Not Yet Completed = Claims_Management `Hire_Status` = Booked	✅ Criteria confirmed	`Booking_Date` (Claim Form) is the scheduled date to display per record
14/07/2026	Pending — `Is_Claim_Accepted` raw counts on the 58	Red Flag Job – Pending Validation = 9 · Pending = 4 (13 raw)	Only 10 records remain after Cancelled (16) + Went Ahead (32) — so ~3 Pending/Red-Flag records already progressed or cancelled. Confirms Pending must be the residual bucket, with these values as a cross-check only

Reference population — July 2026 ACH leads (55 file numbers)

The base-population criteria (V1) must return exactly this set — not just any 55 records. Any record returned that isn't in this list, or in this list but not returned, pinpoints a criteria problem (wrong date field, wrong lead source value, duplicate, etc.).

55 Kratos file numbers

220271  220288  220290  220293  220292
220279  220282  220287  220304  220298
220307  220295  220323  220314  220331
220326  220313  220317  220319  220321
220332  220351  220346  220352  220344
220366  220361  220353  220370  220367
220358  220362  220381  220377  220390
220388  220389  220373  220413  220410
220416  220407  220401  220399  220405
220414  220415  220427  220418  220444
220446  220438  220452  220442  220441

Implementation Options

	A — Creator Page + Deluge function (recommended)	B — Zoho Analytics	C — Plain Creator report
Pattern	Same as the existing Management Dashboard (`get_panel_details`-style function feeding an HTML page)	Sync Creator data to Analytics, build pivots/charts	Grouped Creator report
Fit to this layout	Full control — tiles, daily matrix, breakdown tables exactly as the sheet	Good pivots, less control over the exact layout	Cannot produce the matrix/tile layout
Cost / licence	Included in Creator	Separate Zoho Analytics licence	Included
Effort	Medium (one Deluge aggregation function + HTML)	Medium + data-sync setup	Low
Verdict	✅ Consistent with existing dashboards	Only if Analytics is already licensed and wanted for other reports	❌ Insufficient

Classic Report View (Zubin's preference — numbers only)

Mockup: /mockups/ach-monthly-lead-report.html — a faithful replica of the original Google Sheets report (summary header, daily table, detailed outcome breakdown, claim-type breakdown, cancelled breakdown) with the same navy/Excel styling, zero JavaScript, Creator-snippet compatible. Every table is computed from the same confirmed criteria, and a "Behind every number" appendix at the bottom states the calculation rule for each column — so the familiar format now carries auditable definitions.

Implementation is identical to the dashboard: the Deluge function's fetch/classify/aggregate steps (see the build spec) stay unchanged — only the HTML emission differs, and this format is simpler to emit. The same tables can also be sent as the scheduled monthly email to Nick. The custom dashboard UI below remains documented as the alternative/optional richer view.

Dashboard UI (Design Approved Pending Review)

Interactive mockup: /mockups/ach-lead-dashboard.html — built with the July 2026 snapshot data, works in light and dark mode, hover any bar for the breakdown. This is the layout the Creator page should reproduce.

Layout (top to bottom)

Row	Component	Beyond-Excel value
1	Filters: Month picker · Lead Source picker (default: Accident Claims Helpline)	Lead-source-generic — the same dashboard works for every lead source later
2	5 stat tiles: Total Leads (with daily sparkline) · Went Ahead + conversion % · Booked (with next scheduled date) · Pending (with oldest-waiting age) · Cancelled (+% of leads)	The sheet's header tiles, plus live "next date" / "oldest age" context
3	Leads received by day — stacked column chart, one column per day, segments = current outcome; hover shows full breakdown	The sheet's daily table as a picture; outcome mix per day is visible at a glance
4a	Outcome by claim type — horizontal stacked bars (Repair Only / R&R / Rental Only) with total + conversion % per row; shows "unmapped types: N"	Surfaces unmapped claim types instead of silently dropping them
4b	Repair vs rental components — 2×4 matrix (Repairs/Rentals × outcomes)	The sheet's "detailed outcome breakdown", derived automatically
5a	Upcoming bookings — the Booked records listed with file number, claim type, received + scheduled dates, sorted by scheduled date	Actionable: Emily's "including 15/07" becomes a work list; file numbers link to the claim record
5b	Pending — needs a decision — the Pending records with reason (`Is_Claim_Accepted` value) and days waiting; > 10 days highlighted	Actionable: aging leads can't hide inside a count of "6"
6	Full daily data table — collapsible, Excel-parity (all 10 sheet columns + month-total footer)	Nothing is lost vs the sheet
7	Footer: one-line statement of the counting criteria	The definitions travel with the numbers — no more "how was this counted?"

Visual rules (from the mockup)

Outcome colors are fixed everywhere: blue = Went Ahead · aqua/green = Booked · amber = Pending · red = Cancelled (palette validated for colour-blind separation in both themes)
Counts on chart segments only where they fit; every number is also in the hover tooltip and the data table
File numbers are links → open the claim record / a filtered report (drill-down)

Can Zoho Creator render this? Yes — without JavaScript

Creator HTML snippets strip <script> tags, so the design was reduced to HTML + CSS only and verified:

Proof: /mockups/ach-lead-dashboard-nojs.html — pixel-identical to the design mockup, rendered with JavaScript disabled. This file's markup is exactly what the Deluge function outputs.

Verified in Creator (14/07/2026): the no-JS markup was pasted into a Creator page (Lead_Source_Performance, development environment) and renders correctly — tiles, sparkline, stacked columns and cards all intact. Two adjustments for the real build:

body { background } has no effect inside a snippet (Creator discards the body tag) — move the page background onto .wrap, or drop it and let the cards sit on Creator's white page
Columns can slightly overshoot the gridlines because the 2px segment gaps add to the stack height — have Deluge subtract (segments − 1) × 2px from the computed heights so a full stack aligns exactly with its gridline

Mockup feature	Creator implementation
Stacked column / bar charts	Plain `<div>`s — Deluge computes each segment's `height`/`flex-grow` inline style (1 lead = 20px)
Hover tooltips	CSS-only — a hidden `div` inside each column/row, shown with `:hover` / `:focus-within`. No JS
Sparkline	Inline `<svg>` with points computed by Deluge (SVG is markup, not script)
Collapsible data table	Native `<details>`/`<summary>` — no JS
Month / Lead Source filters	Reload-based — in-page filtering isn't possible without JS, so each dropdown is a CSS-only `<details>` menu whose options are plain links back to the same page with different query parameters (`?month=07-2026&source=ACH`). Selecting one reloads the page; Deluge reads the parameters and re-renders everything
File-number links	Standard `<a href>` to the claim's record/report URL with criteria params

Full developer hand-off: Lead Performance: Deluge Build Spec — the complete function, HTML mapping table, drill-down link options, and acceptance checklist.

How to build it (developer steps)

Deluge function (pattern: get_panel_details on the Management Dashboard): fetch the base population, compute per-lead fact rows (date, claim type group, outcome via the precedence rules above), aggregate, and return the full HTML string — loop over days/types/records appending the markup from the no-JS mockup, with the <style> block included once at the top.
Creator page: the page accepts month and source as URL query parameters (defaulting to current month + Accident Claims Helpline when absent) and passes them into the function. The filter dropdowns are part of the generated HTML: Deluge builds the option list (e.g. last 12 months; lead sources from the Lead_Source master), renders each option as a link to the same page with the new parameter values, and marks the current selection. Changing a filter = page reload with new parameters — there is no in-page filtering without JS, and a full re-render is acceptable here. All panels derive from the single dataset, so tiles, charts, lists and the table can never disagree.
Copy the CSS and markup patterns verbatim from ach-lead-dashboard-nojs.html — it is self-contained, no external libraries or fonts.

Widget route (in progress): a Creator Widget version lives in the repo at kratos-widgets/lead-source-performance-dashboard/ (ZET project — validated, packed to dist/lead-source-performance-dashboard.zip, upload via Creator → Settings → Widgets). Widget v1 is interface only: it renders from a mock payload in app/js/data.js with no Widget SDK data calls; that file is the single data contract, so v2 swaps it for SDK fetches or a published Custom API (returning the same JSON shape, computed with the confirmed criteria) without touching the renderer. See the widget's README for the dev/pack workflow.

Optional: scheduled email to Nick

The current artefact is an emailed report ("Regards, …"). A monthly Deluge schedule can render the same tables as HTML and email them — dashboard for on-demand, email for the existing cadence. Decide whether the email is still needed once the dashboard exists.

Source Report — Anatomy​

1. Summary tiles​

2. Daily breakdown table​

3. Month total — detailed outcome breakdown​

4. Claim type breakdown — month to date​

5. Cancelled — breakdown by claim type​

Key Insight — One Fact Table Drives Everything​

The Numbers We Must Reproduce​

Criteria Definitions (DRAFT — verify each against the master report)​

Base population — figure 1 ✅ CONFIRMED​

Claim type groups — figures 2–4​

Outcome buckets — figures 5–11​

Component splits — figures 5, 6, 11​

Verification worksheet​

Verification log​

Reference population — July 2026 ACH leads (55 file numbers)​

Implementation Options​

Classic Report View (Zubin's preference — numbers only)​

Dashboard UI (Design Approved Pending Review)​

Layout (top to bottom)​

Visual rules (from the mockup)​

Can Zoho Creator render this? Yes — without JavaScript​

How to build it (developer steps)​

Optional: scheduled email to Nick​

Open Questions​