Payments Monitoring and Reporting for Transportation Client

Background and Challenge Story

Our client, a transportation company, wanted to strengthen their payments related monitoring capabilities. Previously, the client’s fraud investigation department relied on third-party data and created some reports manually in Excel. Consequently, the reporting process was slow, and the information being leveraged for decision-making was out-of-date. With the objective of getting timely insight into various aspects of payments, the client wanted to generate in-house dashboards and reports that would allow them to take a more proactive approach to mitigate the risk of fraud, enhance internal processes, and maximize profitability.

Zelusit’s Solution

The client sought Zelusit’s support to build four different dashboards, each aligned to a particular type of payments-related activity. The project had a tight timeline of 3 months and Zelusit worked hand-in-hand with the client’s team in an agile fashion, with frequent discussions and reviews to ensure the project delivered upon the client’s expectations.

Our experts used Power BI to enable visualization of different payments related data. Prior to building the dashboards, Zelusit’s experts gathered requirements, identified data sources and undertook data profiling and analysis for the in-scope sources. We also identified Key Risk Indicators (KRIs) and Key Performance Indicators (KPIs) for each scenario.

Once the requirements gathering stage was complete, the Zelusit team created the following 4 dashboards:

• Chargebacks Dashboard

A chargeback is a reversal of a credit or debit card transaction, typically when a customer disputes the transaction with the card issuer, which results in the card issuer charging it back to the payment processor, who in turn charges it back to the merchant (in this case, our client). The client wanted a delineated view into chargebacks by channel, card type, volume, and value to observe chargeback trends and peaks over time.

The data sources for this dashboard included data from the payment processor, financial reporting data for chargebacks for different card types (AMEX, MasterCard, Visa, Interac), data on retained balance, historical patterns, and performance targets. 8 queries were built for the dashboard, with drill-down capabilities to allow the business user to slice and dice the information based on different parameters.

Pre-calculated metrics were created for total chargebacks, chargeback value as a percentage of gross sales, percentage of chargeback cases won, net recovery, net chargeback expense, among others.

• Refunds Dashboard

A refund is when a customer surrenders their electronic payment card and is given money equivalent to the balance on the card (less processing fee). The client wanted greater visibility on the value and volume of refunds to ensure that refunds were only being issued to registered cardholders.

The client’s payment card data source provided data on the transaction history of cards, fund loading channels, and card registration statuses. Zelusit’s experts created pre-calculated measures for total number of refunds, number of refunds by amount (>$200, between $100-$200, etc.), average refund amounts, last load amounts, and comparison of present and previous year refunds.

The dashboard allowed users to drill down and keep track of methods of payment, refunds by channels and whether refunds were made by the method of payment of original loads.

• Concessions Dashboard

Our client implements concessions, or preferential rates for seniors, students, children, and some adults, on behalf of its transit agency partners. They wanted to monitor overall use of concessions as well as the potential exploitation of these concessions for fare evasion.

Zelusit’s team used indicators of fare evasion and the concession data sources available in the client’s ecosystem and created a dashboard with 20 queries that would allow them to analyze data on concession use, settings, and frequency of concession changes. Business rules were established for each metric to filter out invalid or irrelevant data.

The dashboard provided insights on time-of-day usage by concession, concession settings by location, concession date settings and frequency of change (e.g., birth date being changed more than once for a card), concession use by routes, and total number of fare inspections.

• General Account Activity Dashboard

The general account activity dashboard was built to provide insight on aspects like card dormancy, balance transfers, flagged payment cards, large payment loads, and activities that could be indicative of potential fraud. The client wanted to monitor the dormancy of payment cards and Zelusit used data from the client’s payment card database to create a dashboard that reflected dormant cards broken down by balance and concession classes.

Impact

• Timely insights for decision-making
• Automated fraud report
• User-friendly interactive dashboard
• Full visibility into patterns and trends

The dashboards Zelusit built enabled the client to get better insights and closely monitor payments trends and patterns. The automated reporting process eliminated the reliance on external vendors, making the process more efficient and providing a near-time picture of the situation, which in turn allowed the client to proactively make improvements in their processes to reduce incidence of fraud and increase profitability.

Our reporting solutions streamlined the process of gathering information from various data sources to minimize the manual work performed by business users.

The Power BI dashboards enabled visualization of information in a user-friendly format, which allowed the client’s executive team to understand potential issues and make better, data-based decisions. The exercise also helped the client identify and resolve certain gaps in their internal processes, and highlighted areas that warranted ongoing attention. Moreover, by keeping track of the different payments trends over a long period of time, the client would also be able to ascertain if their preventive and corrective measures were working and delivering the expected returns.

Looking for a BI and visualization solution? Schedule a meeting with Zelusit’s experts to learn more about how we can help you build customized reports for your business.