Research Task - SBCAG ad-hoc request - late taps

[csuyat-dot]

Research Task

Received email from SBCAG / Clean Air Express

We’d like each late tap transaction to include the date and time and device ID. In dream world, if it’s possible to show the fare amount, that would be even better. I was not able to see the fare amount of late taps in the portal but maybe you have access.

Just to clarify, I am able to see all 12,500 late taps in the portal, I would just prefer to not download 125 separate .csv files with 100 transactions each.

Finally routed to DDS to investigate. Deliver a dataset of the 12,500 late taps back to the requester.

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

Received sample data from SBCAG, comparing the sample data to what we have in the warehouse. specially looking for charge_status = LATE.

[csuyat-dot]

plan of attack. cant find charge_status column using dbt. need to explore the mart_payment tables to see if any column has LATE as a value.

[csuyat-dot]

Also try exploring external_littlepay tables as well.

[amandaha8]

I used Christian's notebook as a jumping off point. I looked through all of the mart_payments tables and external_littlepay. I didn't find anything of note that corresponds signifies there a row is a "late tap". I only found one row in fct_elavon__transactions with authorization_code == "LATEU3" and one row in fct_payments_deposit_transactions with authorization_code == "LATEU3" .

[mrtopsyt]

I don't think that it's relevant here and I couldn't find it on a first check at the GCP buckets, but I'm curious if this related at all to the status field discoverd as part of cal-itp/data-infra#5168

[csuyat-dot]

Found a littlepay article explaining Late taps.

Whilst the vast majority of transactions are submitted to Littlepay in near real time, there have been instances in the past where transactions have been submitted [late]... in some cases weeks or months after the transaction date... Transactions that have not been processed by Littlepay due to exceeding configured limit will be displayed on the Merchant Portal as a “LATE TAP”

Showed Rebel and they explained that since these taps are not processed by LP (due to being submitted late), the transactions never make it to our warehouse. Setting up meeting with CS account manager for more context into the SBCAG relationship before getting back to requestor.

[csuyat-dot]

Met with Rebel, CS team to discus. Confirmed what late taps are. "Transaction was received by Littlepay > 4 days from the time of tap. Transaction will not be processed.". Was informed that SBCAG no longer receives late taps and has processes in place to identify them earlier. SBCAG wants to investigate the late taps that happened earlier this year and are seeking our help.

Replied to requestor explaining that LP never sent up late tap info but can push for longer term solutions on behalf of other agencies. SBCAG granted view only access to their LP portal, confirm that its not user friendly.

can try to manually compile the data from LP but still missing the device id. discussing options

[amandaha8]

Just emailed Little Pay.
Hi Little Pay,
Our partners over at Santa Barbara County Association of Governments want a report of all the late taps and terminal ID numbers associated with each tap, going back to 2021. We were unable to find these transactions with our understanding of the Littlepay v3 API. Are these transactions reported anywhere in the API, and if so, how do we determine which transactions meet the definition of “late taps”? We (Caltrans) don't have these columns in our data warehouse so we can't create a custom report for them.