Research Task - new metabase question for EDT to map deposits to taps

[csuyat-dot]

Research Task

As a data analyst in EDT, I want to know: if i receive a deposit, what route (or transit/fare category) comprises that amount. Example, Megan from EDT received a deposit of $24, in that $24 were some $3, $4.5 transactions. However, is the $3 transaction a single paratransit ride, or 3 single regular rides?

After discussing with Megan, she wants a way to disaggregate the deposits to know which fare category it came from, specifically if a ride was paratransit or other.

• Relevant PR (if applicable):

• Question or Goal: overarching goal of this issue

• Data Required:

  • Currently have:
  • Need:
    • what routes number designate a paratransit ride
    • any other categories she needs
    • any other columns that identify ride/fare types in LP.

• Research Required:

• Metrics: what specific calculations are you looking to find?

• Expected Outputs / Findings: what do you hope to have created when this is complete?
  a new metabase question

[csuyat-dot]

Dima was thinking of possibly joining on aggregation id > RRN > micropayment?

[csuyat-dot]

discussed this during the monthly touch point meetings. Data engineers also suggested making a mini-dashboard with multiple cards instead of getting everything into 1 joined table.

[csuyat-dot]

Working on this now. Possible data needed:

• Littlepay tap data

  • fct_payment_rides_v2 table
  • main sales reconciliation table
  • agg id/RRN/elavon purch id, to join on fare type (to know if a tap was for paratransit, regular, discount fare),
  • transaction date,
  • final charge amount

• Elavon deposit date

  • all settlements reconciliation table
  • fct_payments_deposit_transactions table?
  • agg id/RRN/ elavon purch id to join on
  • payment date
  • payment amount

[csuyat-dot]

draft metabase collection here: https://metabase.dds.dot.ca.gov/collection/435-issue-1995-edt-elavon-deposits-to-littlepay-taps

Megan says she:

couldnt make it [the payment data] line up with our bank deposits shown on our bank statments

Im wondering what the elavon bank statements look like, will probably need it to validate against the draft dashbaord

[csuyat-dot]

Sent draft dashboard to EDT, awaiting for reply. Informed them that my draft is really close, but not exact.

[csuyat-dot]

need to contact megan one more time before closing out. @amandaha8

[amandaha8]

Contacted Megan yesterday. Still waiting for her response.