Triage and Bookings Reporting (UserStory1789)

What's recorded to report the customer funnel from Job Registered → Customer Triage Form → booking link → Estimator Booked, and the views the Metabase "Customer Triage and Bookings" dashboard is built on.

All times are UTC. Durations are clock hours, including nights and weekends. A job registered on a Saturday has been waiting all weekend, and the dashboard counts it that way.

What's recorded

Added by migration 0035_20261007_TriageFunnelReporting.sql. None of it is backfilled, so these values exist only for activity from 2026-10 onwards.

Column	Set by	Meaning
ethFormTriage.SubmittedByUPN	spEthFormTriageSetSubmitted (FormsPortal Triage.razor, on submit)	Staff member who submitted the form. NULL = the insured.
ethFormTriage.FurthestStep	spEthFormTriageUpsert (every autosave; keeps the larger value)	0-based index of the furthest step reached (0 Welcome … 10 Review & sign)
ethFormSharingCode.FirstOpenedAt	spEthFormSharingCodeSetFirstOpened (Triage.razor load)	First time the customer opened the link. Not stamped when a signed-in staff member opens it.
ethBooking.InviteViewedAtUtc	spEthBookingSetInteractionTimestamp event 5 (Estimator.razor load)	Insured first opened the field/complex booking link. Same as ethVABooking.InviteViewedAtUtc.
ethBooking.FirstBookedBy / FirstBookedSource	spEthBookingSetDate, together with FirstBookedAtUtc	Who made the first booking. Source is FormsPortal.Estimator (insured via the link), FormsPortal.Estimator.Staff (staff on the staff booking route), AdminPortal.AdvancedBooking (staff) or WebAPI.Booking.
ethVABooking.FirstBookedAtUtc / FirstBookedBy	spEthVABookingSetDate	The first Desktop VA booking. FirstBookedBy is the staff UPN; NULL = the insured.

Staff links

Staff who complete a customer form on the customer's behalf (for example, on the phone) use its staff route. It is the same form and record as the customer's link, but it requires an EntraID sign-in.

Customer link	Staff route	Page
/triage/{code}	/triage/staff/{code}	TriageStaff.razor
/booking/estimator/{code}	/booking/estimator/staff/{code}	EstimatorStaff.razor
/booking/va/{code}	/booking/va/staff/{code}	VirtualAssessmentStaff.razor

• AdminPortal's link dialogs (FormCustomerLink.razor for forms, BookingCustomerLink.razor for bookings) copy the customer link, and their Open link opens the staff route.
• When a staff member is signed in, the page shows a banner (StaffLinkBanner.razor) with their name and a Copy customer link button, so the staff link never gets sent to the customer.
• A signed-in staff member (StaffUser.GetAsync) is detected on either route. A staff member already signed in to FormsPortal who opens the customer link is still recorded as staff.
• When staff open a link, it doesn't count as the customer opening it: triage FirstOpenedAt, booking InviteViewedAtUtc and VA SlotPickedAtUtc are not stamped.

Views

Files are in DbMigrations/Scripts/Programmable/Views/. The number prefix sets the run order, because each view builds on the one before it.

vEthFormTriageSubmission: one row per triage form

Every ethFormTriage row, whether submitted or only started, with its link, progress, outcome and main answers (read from the FormData JSON).

Use it for	How
How triage was completed	IsSubmitted = 1, group by CompletedBy (Insured / Staff)
Customer Triage Form outcome	Group by BookingOutcomeLabel
Drop-off by step	IsSubmitted = 0 AND SiteRejected = 0, group by FurthestStepName
Property type / damage / emergency breakdowns	PropertyType, DamageType, EmergencyAssistance
AI damage type suggestions (AB#1971)	DamageTypeAiSuggestion IS NOT NULL = offered; group by DamageTypeBeforeAi → DamageTypeAiSuggestion and DamageTypeAiResponse (Accepted / Declined). DamageTypeAiConfidence is the AI's confidence; DamageTypeAiAdjusted = 1 when the final DamageType is still the AI's. Recorded from 2026-10.
AI fence only suggestions (AB#2041)	FenceOnlyAiSuggestion IS NOT NULL = offered (Yes / No); compare with FenceOnlyBeforeAi (the customer's own answer) and FenceOnlyAiResponse (Accepted / Declined). FenceOnlyAiConfidence is the AI's confidence.

vEthFormTriageFunnel: one row per Prime job

The dashboard's main source. It builds on vEthJobsRegistered (client, case manager, IsJPrefixedJob) and rolls vEthFormTriageSubmission up to the job. It isn't filtered, so filter IsJPrefixedJob = 1 and RegisteredAtUtc in Metabase.

Stage	Columns	Source
Job Registered	RegisteredAtUtc, RegisteredDateAEST	primeJobs.CreatedAt
Triage link sent	TriageLinkSentAtUtc, IsTriageLinkSent	first ethFormSharingCode (FormType Triage)
Link opened	TriageLinkOpenedAtUtc, IsTriageLinkOpened	FirstOpenedAt
Form started	TriageStartedAtUtc, IsTriageStarted, FurthestStep	ethFormTriage.CreatedAt
Triage completed	TriageCompletedHow, TriageCompletedAtUtc, IsTriageCompleted, IsTriageFormCompleted	first submitted customer form, otherwise a completed Prime "Triage" site form
Booking link sent	BookingLinkSentAtUtc, BookingLinkType, IsBookingLinkSent	first ethVABooking, or ethBooking not created by AdminPortal advanced booking
Booked	BookedBy (Insured / Staff / Unknown), BookedByInsuredAtUtc, IsBookedByInsured	VA FirstBookedBy (SlotPickedAtUtc before 2026-10), field FirstBookedSource
Estimator Booked	EstimatorBookedAtUtc, IsEstimatorBooked	first Prime status starting "Estimator Booked" (primeStatusHistories_Job)

Filters: InstructingClient, CaseManager, BookingLinkType, TriageCompletedHow, PropertyType.

Dashboard cards:

• Funnel: SUM the Is* flags.
• Triage form completion rate: SUM(IsTriageFormCompleted) / SUM(IsTriageLinkSent).
• Booking link → self-booked: SUM(IsBookedByInsured) / SUM(IsBookingLinkSent).
• Booking link outcome by link type: group by BookingLinkType and BookedBy, where NULL = not yet booked.
• Median time between stages: median of HoursRegisteredToLinkSent, HoursLinkSentToTriageCompleted, HoursTriageToBookingLinkSent and HoursBookingLinkToEstimatorBooked.
• Trajectory and weekly KPI by link type: group by month or week of RegisteredAtUtc.
• Conversion by insurer: group by InstructingClient.

BookedBy = 'Staff' also covers jobs booked directly in Prime: Estimator Booked, with the booking link never used. Unknown is a field link booked before FirstBookedSource was recorded.

vEthFormTriageStalled: the stalled-jobs table

Rows from vEthFormTriageFunnel where the job is open, J-prefixed and not yet Estimator Booked, limited to jobs registered since the first triage link was created. Sort by HoursInStage descending.

Column	Meaning
StuckAt	Current stage: No triage link sent, Triage form not completed, Triage link opened / form started, Customer says property details are wrong, Awaiting case manager review, Multi unit - bookings to arrange, Booking link not used
HoursInStage, HoursSinceRegistered	Clock hours to now
Rag	Amber ≥ 24 h, Red ≥ 48 h in stage
PrimeUrl	Job link

Dashboard

Metabase → Forms → Customer Triage and Bookings (dashboard 159), with three tabs: Overview, Funnel detail and Stalled jobs. Its questions are saved inside the dashboard. The funnel card is native SQL with field-filter tags; every other card is a query-builder question on the views above. The "Estimator Booked KPI (Field + Complex)" milestone cards aren't built yet because the KPI isn't defined.

Known limits

• Before 2026-10, every customer-form submission reads as completed by the insured, including forms the claims team completed by phone. Links opened, furthest step and booked-by are blank for that period too.
• Staff are only recorded if they use a staff route, or are already signed in to FormsPortal.
• The Prime "Triage" site form records no completion time, so its approvedAt is used as the completion time.
• Prime statuses are matched by name prefix (Initial contact made%, Insured Triage Complete%, Estimator Appointment Required%, Estimator Booked%). Renaming a status in Prime breaks that stage.