Quarterly Metric: Hearings Show Rate

[alisan16]

This metric is required by OIT (Metric ID: 1812216039) and is critical to understand how the Caseflow Hearings products are helping to improve the appeals hearings process.

Metric definition: (Total scheduled meetings held – postposed hearings) / total scheduled meetings from Caseflow Hearing Schedule

Example SQL for AMA appeals from https://caseflow-looker.va.gov/looks/193

SELECT 
	hearings.disposition  AS "hearings.disposition",
	COUNT(DISTINCT appeals.id ) AS "appeals.count"
FROM public.hearings  AS hearings
LEFT JOIN public.appeals  AS appeals ON hearings.appeal_id = appeals.id 
LEFT JOIN public.hearing_days  AS hearing_days ON hearings.hearing_day_id = hearing_days.id 

WHERE 
	(((hearing_days.scheduled_for ) >= (DATE(DATE '2019-06-01')) AND (hearing_days.scheduled_for ) < (DATE(DATE '2019-06-30'))))
GROUP BY 1
ORDER BY 2 DESC
LIMIT 500

-- sql for creating the total and/or determining pivot columns
SELECT 
	COUNT(DISTINCT appeals.id ) AS "appeals.count"
FROM public.hearings  AS hearings
LEFT JOIN public.appeals  AS appeals ON hearings.appeal_id = appeals.id 
LEFT JOIN public.hearing_days  AS hearing_days ON hearings.hearing_day_id = hearing_days.id 

WHERE 
	(((hearing_days.scheduled_for ) >= (DATE(DATE '2019-06-01')) AND (hearing_days.scheduled_for ) < (DATE(DATE '2019-06-30'))))
LIMIT 1

Once this metric is instrumented, ensure delivery to OIT with #12389

[pkarman]

Run in production for 2019-09-01..2019-12-31

irb(main):278:0>
=> 0.7790697674418605

which I interpret as 77% -- does that seem right @D-L-Ware ?

[D-L-Ware]

In the realm of the possible. Running old VACOLS reports on past fiscal years usually gives me a show rate of mid-60%, defined as hearings held versus the total of held, cancelled, postponed, and no show.

So I think I need to understand how we are defining Show Rate to better evaluated this one.

[alisan16]

This hovered around 75% between 2015 - Q3 2018 based on the Product Impact Statement: https://drive.google.com/file/d/1iw3QTGzy0I5lZx_xeWwvqkmPEvPyxfQc/view

It also states:

The percentage of scheduled hearings that are held, omitting postponed hearings. This
measure is believed to track the Veteran experience of Board hearings, potentially
indicating the effectiveness of VA communication with Veterans about their hearing and
the ease of attending a hearing. The target for this KPI is for the show rate to meet or
exceed the baseline of 75% by January 2020.

So if it's at 77%, wahoo we hit the goal!

[pkarman]

When I fixed the date range to just Q4 (2019-10-01 .. 2019-12-31) it goes up slightly to 78.7% so I think we're good. wahoo!

[pkarman]

@D-L-Ware the definition is here: https://github.com/department-of-veterans-affairs/caseflow/pull/12939/files#diff-ea02ec272fafb6f95e557b7406f28231R9

held / total - postponed

which you gave a +1 to here #12939 (comment)

[D-L-Ware]

Yeah, thinking about if it was reasonable or not the minus postponed was what I was missing. This looks correct to me.