data-infra icon indicating copy to clipboard operation
data-infra copied to clipboard

Clarify meaning of the `pending_charge_fare` `charge_type` value

Open lauriemerrell opened this issue 1 year ago • 0 comments

In several of our agencies' Littlepay data, either all or ~half of micropayments have a charge_type value of pending_charge_fare which is not documented in LP's data documentation. As a data user, I want to clarify the interpretation of this status so that I can correctly categorize these transactions.

Acceptance criteria:

  • [ ] Contact Littlepay to request clarification of this status
  • [ ] Write a follow-up ticket for next steps once clarification received (specifically: should this status be cast or replaced with something else?)

Query to identify affected agencies by year:

SELECT 
  EXTRACT(YEAR FROM transaction_time),
  participant_id,
  COUNTIF(charge_type = 'pending_charge_fare') AS pending_ct,
  COUNT(*) AS total_ct,
  ROUND(100*COUNTIF(charge_type = 'pending_charge_fare') / COUNT(*),2) AS pending_pct
FROM `cal-itp-data-infra.staging.stg_littlepay__micropayments`
GROUP BY 1,2
HAVING pending_ct > 0
ORDER BY 1 DESC, 2

lauriemerrell avatar Nov 06 '23 21:11 lauriemerrell