Static appeals statistics

[alisan16]

As a Caseflow project lead, I need to be able to understand basic metrics about the appeals process to identify bottlenecks and inform future product strategy.

Acceptance criteria

• Static table of where appeals (AMA and legacy) are in the process at the current point in time

Example of chart:

Out of scope

Dynamic visualization of the data is out of scope and will be covered in a separate ticket

Background/context

As a team, Caseflow hasn't holistically looked at the appeals pipeline for years. In order to inform future work, we need to establish accurate baselines.

Technical notes

The easiest way to accomplish this will be to pull a set list of numbers for appeals and their current status from the past ~year manually and using Google sheets or similar in order to create the visualization.

[pkarman]

FYI the etl database has Appeals status summary readily available for visualizing. Look at ETL::Appeal.status

[yoomlam]

@alisan16
Appeals can be group into categories by using any of the following characterizations:

• AMA vs Legacy
• docket_type
• Virtual vs. Onsite vs. ...
• hearing location or other location/region
• who worked on the case and in what role/capacity (e.g., appeals grouped by judge)
• final appeal decision (granted, denied, partial, ...) or other decisions along the appeal process
• claimant type (veteran, VSO, ...)

Let me know if you want more.

[yoomlam]

In thinking about appeal categorizations, I also came up with some other appeal stats:

1. Appeal counts grouped by stage in the appeal process (e.g., at regional office, intake, establishment, hearing, judge decision, quality review, dispatch, and completed)
2. How long an appeal stays in each stage
3. Time for appeals to get to each stage
4. A count of new appeals per day or week
5. Count of case distribution for new appeals vs returning legacy appeals
6. Any of these stats over time
7. Difference between actual appeal completion and target decision date, grouped by appeal category
8. Number of tasks created grouped by chosen appeal categories
9. Number of tasks cancelled grouped by task type and chosen appeal categories
10. Time between opening and closing various task types

[yoomlam]

AMA Appeals data collected on Feb 3rd.

This table shows AMA appeal status counts grouped by docket type:

Status	direct_review	evidence_submission	hearing	Grand Total
1. Not distributed	10262	4681	18281	33224
2. Distributed to judge	214	70	63	347
3. Assigned to attorney	507	134	81	722
4. Assigned to colocated	225	68	64	357
6. Decision ready for signature	364	79	34	477
7. Decision signed	47	11	7	65
8. Decision dispatched	3971	784	201	4956
CANCELLED	427	93	169	689
MISC	17	5		22
ON HOLD	108	37	41	186
UNKNOWN	158	50	26	234
Grand Total	16300	6012	18967	41279

SQL query used to collect data: SELECT status, docket_type, count(*) FROM appeals GROUP BY status,docket_type ORDER BY docket_type,status

[yoomlam]

Legacy Appeals stats compiled on Feb 3rd:

Count	Status (as reported by VACOLS columns bfmpro, table brieff)
67,679	"ACT" => "Active", # Case currently at BVA
71,151	"ADV" => "Advance", # NOD Filed. Case currently at RO
880,073	"HIS" => "Complete", # BVA action is complete
80,143	"REM" => "Remand", # Case has been Remanded to RO or AMC
325	"MOT" => "Motion", # appellant has filed a motion for reconsideration
78	"CAV" => "CAVC" # Case has been remanded from CAVC to BVA
1,099,449	Total Legacy Appeals worked in Caseflow

Note that there are over 3.5 million legacy appeals in VACOLS. Legacy appeals are only recorded in Caseflow's DB when they are worked by Caseflow.

Code used to get VACOLS ids in Caseflow's DB and query VACOLS directly (for speed):

    results=[]
    initial=0
    cursor=initial*1000 # limited to 1000 vacols ids in the WHERE clause
    ActiveRecord::Base.logger.level = 1  # reduce verbosity
    vcon=VACOLS::Record.connection

    # loop until the status of all 1 million+ legacy appeals are retrieved
    for i in initial..2000 do
      vids=LegacyAppeal.limit(1000).offset(cursor).order(:id).map(&:vacols_id)
      vres=vcon.execute("SELECT bfmpro, count(*) FROM brieff WHERE bfkey IN (#{vids.map{|id| "'#{id}'"}.join(', ')}) GROUP BY bfmpro ORDER BY bfmpro ")
      r={}
      results.append(r.values) while r=vres.fetch_hash
      cursor += 1000
      if vids.count < 1000
        puts "Less than 1000: #{vids.count}. Completed!"
        break
      end
      sleep 2 # don't overwhelm VACOLS
      puts cursor   # print progress; should be around 1 million once complete
    end

    stats={}
    results.each{|p| stats[p.first]=stats.fetch(p.first, 0)+p.second};

[yoomlam]

AC 2 removed. Alisa will take the data back to the project leads.