Client Profitability

This Decision Table calculates a banking client's internal segmentation and profitability ratio by analyzing their demographic data and financial portfolio depth.

Solution components

The logic consists of a single, comprehensive Decision Table named Client Profitability. This table functions as a scoring engine that intakes client age, product holdings, and monetary values to output calculated metrics, segmentation, and profitability.

The Decision Table processes the following logic:

• Inputs:

  • Client Age: Used to determine life-stage segments (e.g., Juniors vs. Adults).

  • Product Counts: Specifically analyzes the number of Accounts and Cards held versus Investments held.

  • Portfolio Amount: The total USD value of the client's holdings.

• Calculated Outputs:

  • Total Products Count: The table dynamically calculates the sum of accounts, cards, and investments using a PLUS_CHAR function.

  • Amount per Product: The table calculates the average value per product held by dividing the Portfolio Amount by the Total Products (rounded to 2 decimal places).

• Segmentation & Profitability Logic:

  • Junior & Student: Clients between ages 12 and 15 are classified as "junior" (Profitability: 0.8), while those between 15 and 18 are classified as "student" (Profitability: 0.9).

  • Mass Market: Clients aged 18-30 with low product engagement (3 or fewer accounts/investments) are classified as "mass market" with a profitability ratio of 1.

  • Affluent: Clients aged 18-30 with higher product engagement (4 or more accounts/investments) but a portfolio under $15,000 are classified as "affluent".

  • Senior Affluent: This segment is triggered by two distinct scenarios:

    • Clients aged 18-30 with a portfolio exceeding $15,000.

    • Clients aged 30-65 with high product engagement (5 or more accounts/investments) and a portfolio under $9,999.

  • Top Affluent: Clients aged 30-65 with high product engagement (5 or more accounts/investments) and a portfolio of $10,000 or more are classified as "top affluent" with the highest profitability ratio of 1.6.