Research: How many operators is Cal-ITP assessing?

[evansiroky]

User Story

As a Cal-ITP program manager or more senior Caltrans executive, I want to know how many transit agencies are being assessed in California so that I can have a baseline for calculating other metrics like the one described in cal-itp/data-infra#984.

Additional Context

The gist of this question lays the foundation for answering a variety of questions that high-level executives asks such as "what percent of transit agencies have GTFS Schedule data?" or "what percent of transit agencies have Fares v2?" or "what percent of transit agencies are GTFS-compliant?"

Research should be performed with various stakeholders to determine how to define and filter the data we have in airtable about organizations, services and potentially other items. Part of this task should include a document detailing how to filter the data in airtable in order to provide this baseline for measurement. If none of the stakeholders can give a clear answer about how to calculate this baseline, a deliverable of this report should propse at least one recommended option for calculating this baseline.

Acceptance Criteria

Given the data Cal-ITP has collected about transit agencies with respect to how they are funded, what kind of service they operate, and any other relevant critieria When applying all relevant criteria about what qualifies as a transit agency for reporting purposes Then a number should be calculated.

The deliverable of this should include:

1. A memo containing the precise, quantifiable and measurable definition of what qualifies as a transit agency for answering the above-mentioned high-level questions
2. A metabase question that simply shows the resulting number of transit agencies when applying the criteria to the data in airtable

Sprint Ready Checklist

• [ ] Acceptance criteria defined
• [ ] Team understands acceptance criteria
• [ ] Team has defined solution / steps to satisfy acceptance criteria
• [ ] Acceptance criteria is verifiable / testable
• [ ] Dependencies identified

Appendix

The document Cal-ITP Transit Provider Categorization + Activities is a detailed document about the various ways that transit agencies could be categorized, but it does not include a recommendation for how to establish a baseline for reporting.

There already exists a filter within airtable that seems to do something with regarding filter assessed operators. Research should be done to determine if this is relevant. Screenshots of this filter is shown below:

Overall airtable filter

Reporting Category

Currently Operating

Service Type

Additional service type filter

[lauriemerrell]

I created a draft attempt to answer this in Metabase: https://dashboards.calitp.org/question/255-testing-answer-issue-985

Note -- I had to assume that the Service Type filter shown was service type of services operated since there is also a service type of services managed.

Also note that I think right now this has to be answered in SQL in Metabase because they don't seem to allow you to filter on OR conditions in the question builder UI (so you can't select multiple options for things like reporting category as shown in the screenshot).

Also also note this is based on the initial data import setup from cal-itp/data-infra#990 -- subject to change as that PR evolves.

[evansiroky]

Status update: @lauriemerrell is working on creating some mapping tables that are derived from the raw airtable data to be able to do standard database joins via metabase.

[tiffanychu90]

Tracking this in Metabase GTFS Features Dashboard