Research Task - SLO RTA / City Payment Dashboard features

[csuyat-dot]

Research Task

As a SLO RTA/City payments user, I want to be able to filter the metabase payment dashboards to switch between SLO RTA and City data. This way I can view RTA and City data individually.

This task is 2 parts:

1. Add a filter to the Contactless metric dashboard to switch between SLO RTA and SLO City. Use the Rebel provided mapping from device id -> bus id -> agency.
2. Create a new visual showing total sales/volumn as stacked column & % split between RTA and City

Initial request came from Rebel:

on the separation between SLO RTA and SLO City (Littlepay combined them under one participant ID SLORTA to allow discounts when transferring from one agency to another).

So I was planning to add a column with mapping to one of the questions, but actually looking at the data and spending more time thinking, I thought it would be better to add directly to the individual 'Fct Payments Rides v2' table, so we can use it in any question.
And I think this is better done by you or Anna or Charlie :)

Can you please add a new column that contains the values from the attached file, based on the device ID column?
And then, in the Contactless metrics dashboard, can you please add the filter by this column (so agencies can filter vy their agency only).
And also, can you please create a new question & visual that shows total sales/volume as a stacked column, with a % split between RTA and City?
no changes in other dashboards - we don't; have device ID in Elavon's data, and it is not of necessity in the reconciliation, though we will have that column for analysis if needed.

Sorry for this rather late ask - I should have been upfront with it, but I thought it would be easier (classic mistake 😞 ).
And let me know, I am happy to add this filter and question with a visual!

• Relevant PR (if applicable):

• Question or Goal: overarching goal of this issue

• Data Required:

  • Currently have:
  • Need:

• 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?

[csuyat-dot]

I was able to create a working dashboard filter and make a custom list for agency - device.

1. edit dashboard
2. add text filter
3. select "dropdown list", edit
4. select "custom list"
5. copy/paste the device ID + agency string combination to the text box.
6. map the filter to the fct_payments_rides_v2.device_id col for each dashboard card

Note, some dashboard cards do not use the fct_payment_rides_v2 table. so the filter does not work with all tables. Possibly can try joining fct_payment_rides_v2 to these other cards to get device ID, but would take additional work.

[csuyat-dot]

informed Rebel of short-term workaround (custom metabase filter).

additional considerations are needed to add data to fct_payment_rides_v2

• who will maintain the mapping between device id -> bus id -> agency name
• what if someone transfer from one agency to another
• do other agencies care about fund splits between sercies
• are there other agencies with this set up
• will adding this mapping only exist for SLO RTA/City, what about the remaining agencies
• will adding this new data cause any issues for automation?

[amandaha8]

@csuyat-dot when Dima says "And also, can you please create a new question & visual that shows total sales/volume as a stacked column, with a % split between RTA and City?"

I'm guessing I will use Fct Payments V2 and use Count for volume. For "total sales" I will use Transaction Amount.

[csuyat-dot]

i like this plan. Lets start with using transaction amount. May need to change to charge amount or nominal amount depending what Rebel says.

[amandaha8]

@csuyat-dot I ended up using nominal_amt in Fct Payments V2 because transaction_amount is from some other table that doesn't have device_id. I can't share the link since I don't have admin access but it's here in the SLORTA collections. I also used SQL to write this out instead. IDK if that's ok. Can you review this? These aren't on the dashboard yet.

[csuyat-dot]

Completed Dashboard demonstartion. SLO City replied to email asking why the dashbaord filtering (the custom list) isnt working for all tables. As explained above, the filter only works for certain tables with rides_v2 device id.

but may now have motivation to look into more robust solutions (upload CSV to metabase, airtable, get something in the warehouse)

[mrtopsyt]

@mrtopsyt to upload table to Metabase as part of an interim solution. Interested in cal-itp/data-infra#5294 as part of a longer term solution

[csuyat-dot]

In the contactless metrics questions that group by route use route short name. It looks like the SLO City data does not have values for "route short name", but does have values in the route ID column

so maybe we can try swapping route short name to route id in the SLO contactless dashboard?

[amandaha8]

Swapping route_id for route_short_name worked. Now going to try upload a CSV to Metabase based on this thread

[amandaha8]

Done uploading CSV, now will test out how to join the CSV to fct_payments_rides_v2 and other tables with device_id.