Customer profitability

Situation: The company was facing challenges in understanding the profitability of individual customers. Customer profitability is a crucial metric that reflects the financial value contribution of a customer to the business performance (EBITDA). It helps the company determine if the way they conduct business with that customer is profitable, and if there are any opportunities to optimize or segment customers into different segments for further profitable optimization. However, calculating this metric can be complex due to several reasons such as varying customer behaviors, fluctuating market conditions, and diverse product portfolios.

Task: The COO, Sales and Marketing Director, and Operation Director wanted to segment the customer base based on the profitability of each customer to improve EBITDA. They also aimed to restructure the way sales and other operation teams (i.e., auctions team, admin team, logistics team, etc.) interact with customers.

This task was met with two primary challenges:

1. Profit Distribution: The company’s business model involves transactions with both buyers and sellers, who are all considered customers. Given that a seller in one transaction could be a buyer in another, and vice versa, determining how to divide the gross profit from a transaction between the buyer and seller was critical. This division could significantly impact the end profitability of each customer.
2. Cost Allocation: The allocation of shared operational expenses, such as costs of ICT department, posed a significant challenge (i.e., how can we split the license expenses for each customer?) These costs were complex and difficult to distribute among customers without solid and justifiable assumptions.

Action: The project was led by me, in cooperation with a data engineer, leaders from the accounting team, sales, and other operation teams. We broke down the project into several steps:

1. Structure of the Profit: We mapped all types of accounting accounts into groups that helped determine revenues, COGS, and operational expenses by departments.
2. Data Collection: We collaborated with the data engineer to gather all necessary data related to customer transactions, costs, and revenues.
3. Profitability Calculation: : We developed a model to calculate each customer’s profitability, taking into account all relevant factors. Starting from each transaction ID, we linked the product and value-added services to all accounting accounts and team interactions with customers. For instance, if a transaction had a shipping order in the invoice, then the expenses from the couriers for the shipping cost, and the salary of team member of logistics for the labor cost could be shared by that transaction and eventually the customer. To overcome 2 challenges above, we conducted in-depth interviews with each team lead/manager to understand their operations, which suggested what the cost drivers were in each team. We could then use those cost drivers to allocate the operational expenses of that team to each customer. We also conducted meetings with the Management to determine how to distribute the gross profit between sellers and buyers of transactions, which could provide a fairly universal view of the cash flows that the customers brought to the company in those transactions.
4. Customer Segmentation: Based on the calculated profitability, we segmented the customers into different groups based on the data distribution of customer profitability
5. Strategy Development: We worked with the operation teams to present the insights and suggest tailored efforts to each customer segment.
6. Other applications: We are in the process of developing a solution to populate the customer profitability data on Salesforce and Marketing Cloud for further automations.

Result: The project yielded significant results:

Managing unprofitable customers framework

1. We were able to provide real-time cumulative customer profitability of each customer in the fiscal year, taking into account inflation rates. The management can monitor the Stobachoff curve and analyze the quality of the customer base.
2. We segmented the customer base by the customer profitability and populated those data in the datawarehouse for other analyses.
3. All departments, especially marketing, sales, and other operational teams, began optimizing their efforts to achieve the best EBITDA. They refocused on segments of customers who have high profitability while reviewing why some customers were not profitable. Based on this review, decisions were made on whether to have a special treatment, continue nurturing the relationship, or let them be without any additional efforts from sales force (ref. managing unprofitable customer framework).
4. The company’s EBITDA in 2023 exceeded the target.

Learning Points

Throughout the project, we gained several valuable insights:

1. Navigating Grey Zones: There were many areas where critical decisions had not been made by the management prior the project, such as how to allocate the gross profit of the company between buyers and sellers of transactions. This eventually allowed us to analyze the full potentials and contributions of those customers. Persuading all managers on the assumptions created to allocate shared costs back to customers required significant mutual understanding between analysts and the management. Effective communication, presentation of data with visuals (i.e., the cost structures, the analyzing frameworks), and stakeholder management were key in this process.
2. Data Quality: The quality of data from different sources played a strong role in the accuracy of the model and analysis. Especially, the accounting data was usually very sensitive, aggregated, and often lacked detail or missed important information (e.g., the right cost centers, the transaction IDs / product IDs in the invoices). The logistics data was also a challenge as the location and shipping time were not very consistent due to the quality of the data from shipping companies. Therefore, we promoted a data-driven approach to record the data to the managers so they could be customer-oriented and data-minded in their daily operations.