User Story: add summary stats for to mart_gtfs.fct_observed_trips

[tiffanychu90]

User stories

Summary

As an analyst, I want to have a schedule vs RT trip table that provide additional summary statistics for the RT trips observed.

• This is follow-up for related work to User Story: daily schedule vs RT number and percent of trips #2236, but can be built off of another table of the right grain

Table Schema

• build out the existing mart_gtfs.gtfs.fct_observed_trips table
• Grain: gtfs_dataset_key-name-trip_id-activity_date
• Metric columns:
  • tu_num_distinct_message_ids (existing)
  • tu_min_extract_ts and tu_max_extract_ts.
    Analyst could easily calculate this difference (rt_trip_elapsed = max_extract_ts-min_extract_ts).
    Use that calculation to get rt_trip_elapsed / num_distinct_message_ids = number of distinct messages per minute. If we're hitting close to 3, that's close to capturing every 20 sec.
  • number_of_distinct_minutes_with_vp as a measure of RT availability for that trip. Analyst would compare against scheduled trip's service_hours --> converted to service_minutes and see how much coverage had RT. This could easily surpass a proportion of 1.0, esp when a trip takes longer than the scheduled time and there are vehicle positions keep on coming in.
  • time_of_day: this could be where the heuristic is applied. trip_id appears in schedule and RT, use schedule to determine time_of_day, otherwise use tu_min_extract_ts

Tester [Stakeholder]

• @tiffanychu90

Notes

WIP @amandaha8 providing exploratory context for @edasmalchi's idea: cal-itp/data-analyses#668

[lauriemerrell]

FYIs -- related issues for fct observed trips:

• Add count of messages to fct_observed_trips #2412
• GTFS RT: Investigate trips with different identifier attributes but same trip ID  #2413

[tiffanychu90]

Closing - progress based on above mentioned issues