Early 2023 marked the start of my journey in the transportation industry, when I stepped into a Credit Control Administrator role. Only one month into the job, my director entrusted me with leading a high-impact initiative—one that would ultimately reshape our department’s workflow and accelerate my own evolution into a data analyst and dashboard builder. At the time, I didn’t yet realise the scale of the challenge ahead or the long-term value this opportunity would unlock. The issue was urgent: payments linked to failed credit card transactions were being managed through an unstructured, reactive, and largely manual collections process. There was no central tracking system, no audit trail, and no historical record of recovery efforts. With a small team operating under constant cash-driven pressure, the focus was naturally on resolving immediate financial impact—leaving no capacity for building visibility, measuring performance, or designing repeatable processes. The problem wasn’t mismanagement—it was a blind spot created by limited resourcing. As failed credit card payments accumulated, so did debt exposure. Without a system to measure recovery rates, reinstatement status, or ageing risk, financial leakage was becoming a material operational burden. My role quickly became the missing link—and my mission expanded beyond collections alone. It was now about building a scalable, data-driven framework where none existed. --- ### **Understanding the problem** A credit card transaction can fail for several reasons—declined authorisation, insufficient funds, fraud flags, or issuing bank restrictions. While BizOps and Fraud teams had already established rules to suspend accounts after repeated failures, the payment gateway continued to retry daily for up to 30 days before a capture attempt was triggered. Even after a successful capture, manual intervention was still required to reinstate clients and offset recovered amounts against outstanding balances. This resulted in a **dual operational workload**: * chasing payments, and * manually restoring account access— all without a single source of truth to validate whether a customer had already paid through another channel in the interim. --- ### **Source Data** My inputs originated from two core streams: * A Tableau dashboard listing companies with outstanding debt, including Company ID, Company Name, Debt Amount, Capture Attempts, and Failure Reasons. * Real-time payment failure alerts received through email. Despite data availability, there was no structured mechanism for validation, reconciliation, invoice tracking, or long-term performance measurement. --- ### **Establishing the workflow** To create accountability and consistency, I mapped a full recovery cycle: 1. **Data ingestion** – Consolidate all failed transaction records and alerts. 2. **Validation** – Cross-check payments received via alternate channels to prevent duplicate outreach. 3. **Invoicing** – Generate manual invoices based on account structure (shared corporate card vs. individual employee corporate cards). 4. **Payment link distribution** – Send secure payment links to the responsible party. 5. **Settlement & reinstatement** – Reinstate accounts after payment confirmation and offset the recovered balance, provided no other debts remain. 6. **Tracking & monitoring** – Log every stage to measure efficiency, overdue risk, and recovery performance. --- ### **Building the Master Tracking File** Once the process logic was clear, I built the monitoring backbone of the project: **Excel.** The master workbook centralised the full failed-payment lifecycle, including: * Company ID * Company Name * Contact Email * Ride/Order ID * Amount * Capture Attempts * Failure Reason * First Transaction Date To enable richer finance and commercial insights, I later introduced: * **Ride/Order ID → Invoice ID mapping** for traceability * Invoice posting and payment dates for **DSO calculation** * A classifier for **Collected, Uncollected, or Written-Off** invoices * Payment type and refund/write-off flags for financial integrity --- ### **Final Data Structure**

Core Identifiers & Payment Info

* Company ID * Company Name * Email * OrderID * Amount * First Transaction Date * Invoice ID

Invoice Tracking & Risk Metrics

* Invoice Posting Date * Payment Date * Invoice Status * Payment Type * Refund / Write-Off Flags * Capture Attempts * Failure Reason * Refund Type

--- ### **Project Impact & Evolution** With a unified master file in place, I could finally track unsettled orders, validate payments, issue invoices, and execute structured follow-ups—with measurable performance for the first time. After gaining a Tableau licence and database access, I elevated the framework into a **self-service reporting environment** that replaced ad-hoc finance requests. Management and commercial stakeholders, including Sales Account Managers, could now instantly answer key questions such as: * How many customers are currently suspended? * What is our total outstanding exposure? * How much has been recovered, and at what rate? * How is debt ageing, and where is the highest risk? * Which payment channels perform best? This marked my shift from **reactive operator → proactive data storyteller**, using analytics to deliver answers before they were asked for. --- ### **The Tableau Dashboard** The dashboard became the final layer of the project: a structured, business-ready narrative powered by reconciled finance data. It translated raw debt exposure into KPIs, efficiency metrics, risk profiling, and trend visibility—allowing stakeholders to monitor recovery performance, prioritise at-risk accounts, and understand operational friction points through visuals rather than manual spreadsheets or email digging. --- #### **1. Executive Summary** A leadership-focused page combining high-level KPIs with a customer table mapped to Sales ownership and payment method. It includes stacked comparisons of collected vs uncollected values and payment channel performance, enabling instant assessment of exposure, accountability, and recovery efficiency. ![Executive Summary](https://ctuewxihltjdzchfpvrk.supabase.co/storage/v1/object/public/project-images/screenshots/Tableau1.png) --- #### **2. Collection & Efficiency** An invoice-centric view analysing recovery velocity, payment delays, and DSO trends. Stacked bars compare settlement outcomes while channel-efficiency charts identify timing and process bottlenecks—supporting smarter, faster collections strategies. ![Collection and Efficiency](https://ctuewxihltjdzchfpvrk.supabase.co/storage/v1/object/public/project-images/screenshots/Tableau2.png) --- #### **3. Risk & Exposure** A risk-profiling view using ageing bands, failure behaviour, and fraud flags to assess collectability. Ranked and stacked visuals surface concentrated financial exposure, enabling prioritisation of high-risk accounts before write-off conversion. ![Risk and Exposure](https://ctuewxihltjdzchfpvrk.supabase.co/storage/v1/object/public/project-images/screenshots/Tableau3.png) --- #### **4. Volume & Trends** A historical trend view tracking failed payment volume, recovery trajectory, and cumulative exposure patterns. It reveals seasonality and establishes a baseline for forecasting, capacity planning, and trend-driven finance decisions. ![Volume and Trends](https://ctuewxihltjdzchfpvrk.supabase.co/storage/v1/object/public/project-images/screenshots/Tableau4.png) --- ### **Project Outcomes** * **2.9k+ failed payment orders** centralised, reconciled, and tracked * **£170k+ recovered**, achieving **90.7% recovery rate** * First-ever visibility into debt ageing and reinstatement status * Reduced manual workload through payment validation and invoice mapping * Enabled self-service reporting for finance and commercial stakeholders * Shifted the department from reactive → measurable → data-driven --- ### **Personal Growth & Takeaway** This initiative catalysed my transition from Credit Control operations into analytics, dashboard development, and financial storytelling. It strengthened my interest in Finance process optimisation, collections forecasting, and business-driven visualisation—skills now showcased through the Tableau dashboard that anchors my portfolio. ---

Failed Credit Card Collections & Recovery Analytics

Overview