Sample Payment Analysis

ℹ️ Important Information

Due to strict regulations and data sensitivity in the domain I had the opportunity to work in, all data shown in these projects is fictional. Names of banks, partners, and other entities are also fictional and do not reflect real-world entities. My aim is to demonstrate the type of work I carried out during my experience at Mangopay and the expected outcomes.

📜 Preface

As stated, this project aims to present a sample of the type of tasks I carried out during my experience at MANGOPAY. In this simulation, the tool used is Supabase, an open-source platform for interacting with databases. The tables and information have been simplified to make this simulation possible.

🌟 Objective

I designed structured analyses of payment flows to monitor profitability, secure transactions, detect anomalies, and optimize payment journeys.

🔎 Context

Here, we simulate a payment API solution managing “IN” payments for multiple platforms (clients). The acronym PSP stands for Payment Solution Provider, which is licensed and authorized to securely handle payments. We select two PSPs—one for each type of payment method (wallet payments and card payments). Additionally, we include a bank transfer payment option without going through a PSP.

🗃️ Database Structure

The payments table is the central dataset. Each row represents a transaction. A transaction may be a card payment (via psp1), a wallet payment (via psp2), or a validated bank transfer (via bank_transfer).

🔗 Relationships

Source	Target	Relation	Description
payments	psp1	payments.psp1_id → psp1.psp_id	Links a card payment to its PSP1 entry
payments	psp2	payments.psp2_id → psp2.psp_id	Links a wallet payment to PSP2
payments	bank_transfer	payments.bank_transfer_id → bank_transfer.transfer_id	Associates payment with a bank transfer
psp1	psp_details	psp1.psp_details_id → psp_details.psp_details_id	Details of the PSP transaction
psp1	card_details	psp1.card_id → card_details.card_id	Links PSP1 to the card used
psp2	psp_details	psp2.psp_details_id → psp_details.psp_details_id	Wallet PSP details

This structure allows the aggregation and detailed analysis of transactions, card data, PSPs, and associated banking operations.

Use Case 1 – Volume and Revenue by Payment Method

🧪 SQL Query

🔍 Explanation

This query groups transactions by payment type and calculates:

Total number of payments
Total processed amount
Total collected fees
Average fee rate (profitability indicator)

📊 Observed Results

✅ Conclusion

The three payment methods (Web, Card, and Bank Transfer) show very similar transaction volumes and total amounts. The fee rates are almost identical (around 3%), indicating no significant cost advantage between them. This suggests that platform or user preference, rather than cost efficiency, may be the main driver for choosing one payment method over another.

🚀 Business Value

Supports decision-making to reduce payment processing costs
Basis for PSP fee negotiations or UX improvements
Feeds into automated finance dashboards

Use Case 2 – Monthly Payment Volume Tracking

🧪 SQL Query

🔍 Explanation

This query aggregates payment data by month to track the number of executed payments and the corresponding total amount.

📊 Observed Results

✅ Conclusion

Helps monitor financial growth over time, identify seasonal trends, and detect unusual spikes or drops in activity.

🚀 Business Value

Supports financial forecasting and performance monitoring
Useful for comparing actuals with business KPIs
Feeds into monthly reporting dashboards

Use Case 3 – Card Payment Details with PSP Info

🧪 SQL Query

🔍 Explanation

This query provides full visibility into card payments, combining technical and geographic card metadata with PSP tracking info.

📊 Observed Results

✅ Conclusion

Useful for fraud analysis, dispute resolution, or compliance requirements (KYC/AML).

🚀 Business Value

Improves traceability of card transactions
Enhances fraud and compliance monitoring capabilities

Use Case 4 – Validated Bank Transfers + Beneficiaries

🧪 SQL Query

🔍 Explanation

Enables detailed tracking of validated wire transfers and their beneficiaries, including dates and references.

📊 Observed Results

✅ Conclusion

Ideal for audits, reconciliation with bank statements, and preventing double payments.

🚀 Business Value

Improves accuracy in manual or external confirmations
Supports compliance and financial integrity

Use Case 5 – Expired Cards Still in Use

🧪 SQL Query

🔍 Explanation

Detects abnormal behavior by identifying payments executed with expired cards, potentially revealing system loopholes or fraud.

📊 Observed Results

✅ Conclusion

Can be used for alerting and incident investigation workflows.

🚀 Business Value

Improves system security monitoring
Supports detection of abuse or technical faults

Use Case 6 – Failed Payments with Error Codes

🧪 SQL Query

🔍 Explanation

This query captures failed payments by fetching relevant error codes and messages, grouped by platform.

📊 Observed Results

✅ Conclusion

Centralized view of errors facilitates technical debugging and improves system resilience.

🚀 Business Value

Helps automate support and issue tracking
Feeds internal logs and SLA monitoring tools

Use Case 7 – Platform-Level Payment Analysis

🧪 SQL Query

🔍 Explanation

Provides insight into which platforms generate the most volume and fees, helping prioritize business efforts.

📊 Observed Results

✅ Conclusion

Supports client segmentation, bonus logic, or partnership prioritization based on financial value.

🚀 Business Value

Feeds client scoring models
Helps detect churn risk and promote high-value clients

Use Case 8 – Payments by Card Origin Country

🧪 SQL Query

🔍 Explanation

Aggregates payments based on the issuing country of cards, revealing geographical patterns and compliance triggers.

📊 Observed Results

✅ Conclusion

Useful for marketing targeting or regulatory audits (cross-border payments).

🚀 Business Value

Supports geographic segmentation
Improves targeting and fraud prevention by country