10.23.1 XGBoost Ranking for Customer Lifetime Value
This example demonstrates how to use XGBoost ranking in OML4Py to predict customer lifetime value (LTV) rankings from grouped customer data.
The target variable, LTV_RANK, represents four ranking levels:
1 = LOW, 2 = MEDIUM, 3 = HIGH, and
4 = VERY HIGH. Unlike a standard regression model, this example uses the
rank:pairwise objective to learn the relative ordering of customers
within each GROUP_ID. The model is trained using demographic attributes,
financial behavior, and transaction activity. After training, it generates ranking scores
that can be sorted to identify customers with the highest predicted lifetime value. All data
preparation, model training, and scoring are performed inside Oracle Autonomous
Database.
Example 10-23 Using the oml.xgb Class
This example creates an XGB model and uses some of the methods of the oml.xgb class.
import oml
import pandas as pd
CUSTOMER_INSURANCE_LTV_RANKING = oml.sync(query="""
SELECT
REGION || '_' || CEIL(ROW_NUMBER() OVER (PARTITION BY REGION ORDER BY CUSTOMER_ID) / 10) as GROUP_ID,
CUSTOMER_ID,
REGION, PROFESSION, INCOME, AGE, CUSTOMER_TENURE, CREDIT_BALANCE, GENDER,
MARITAL_STATUS, STATE, HOME_OWNERSHIP, NUM_MORTGAGES, MORTGAGE_AMOUNT,
CAR_OWNERSHIP, CREDIT_CARD_LIMITS, BANK_FUNDS, CHECKING_BALANCE, NUM_DEPENDENTS,
HAS_CHILDREN, NUM_ONLINE_TRANS, BUY_INSURANCE, MONTHLY_CHECKS, NUM_TRANS_KIOSK,
MONEY_MONTLY_OVERDRAWN, TOTAL_AUTOM_PAYMENTS, NUM_TRANS_TELLER, NUM_TRANS_ATM,
CAST(CASE LTV_BIN
WHEN 'LOW' THEN 1
WHEN 'MEDIUM' THEN 2
WHEN 'HIGH' THEN 3
WHEN 'VERY HIGH' THEN 4
END AS INTEGER) AS LTV_RANK
FROM CUSTOMER_INSURANCE_LTV""")
# Display the first 10 rows
CUSTOMER_INSURANCE_LTV_RANKING.head(10)
# Drop model if it exists
try:
oml.drop(model = 'XGB_RANKING_MODEL')
except:
pass
# Prepare training data - drop CUSTOMER_ID and GROUP_ID from features
train_x = CUSTOMER_INSURANCE_LTV_RANKING.drop(['LTV_RANK', 'CUSTOMER_ID'])
train_y = CUSTOMER_INSURANCE_LTV_RANKING['LTV_RANK']
# Build XGBoost ranking model
# XGBoost ranking uses pairwise comparison to learn relative rankings
# Note: All parameters must use 'xgboost_' prefix
setting = {
'xgboost_objective': 'rank:pairwise', # Pairwise ranking objective
'xgboost_max_depth': '6', # Maximum tree depth
'xgboost_eta': '0.1', # Learning rate
'xgboost_gamma': '1.0', # Minimum split loss
'xgboost_num_round': '100', # Number of boosting rounds
'xgboost_min_child_weight': '0.1' # Minimum leaf node weight
}
xgb_ranking_model = oml.xgb('regression', **setting)
# Fit the model with GROUP_ID as case_id for ranking
# The case_id groups data points for pairwise ranking
xgb_ranking_model.fit(
train_x,
train_y,
case_id = 'GROUP_ID',
model_name = 'XGB_RANKING_MODEL')
# Generate predictions - drop only the target column
pred=xgb_ranking_model.predict(
CUSTOMER_INSURANCE_LTV_RANKING.drop('LTV_RANK'),
supplemental_cols = CUSTOMER_INSURANCE_LTV_RANKING[:, ['CUSTOMER_ID', 'REGION', 'LTV_RANK']]
)
# Display first 10 predictions
pred.head(10) # crashes
# Generate predictions for all customers
all_predictions = xgb_ranking_model.predict(
CUSTOMER_INSURANCE_LTV_RANKING,
supplemental_cols=CUSTOMER_INSURANCE_LTV_RANKING[['CUSTOMER_ID', 'REGION', 'LTV_RANK']])
# Sort by predicted score descending and get top 15
top_10 = all_predictions.sort_values(by='PREDICTION', ascending=False).head(10)
top_10Listing for This Example
>>> import oml
... import pandas as pd
>>> CUSTOMER_INSURANCE_LTV_RANKING = oml.sync(query="""
... SELECT
... REGION || '_' || CEIL(ROW_NUMBER() OVER (PARTITION BY REGION ORDER BY CUSTOMER_ID) / 10) as \
GROUP_ID,
... CUSTOMER_ID,
... REGION, PROFESSION, INCOME, AGE, CUSTOMER_TENURE, CREDIT_BALANCE, GENDER,
... MARITAL_STATUS, STATE, HOME_OWNERSHIP, NUM_MORTGAGES, MORTGAGE_AMOUNT,
... CAR_OWNERSHIP, CREDIT_CARD_LIMITS, BANK_FUNDS, CHECKING_BALANCE, NUM_DEPENDENTS,
... HAS_CHILDREN, NUM_ONLINE_TRANS, BUY_INSURANCE, MONTHLY_CHECKS, NUM_TRANS_KIOSK,
... MONEY_MONTLY_OVERDRAWN, TOTAL_AUTOM_PAYMENTS, NUM_TRANS_TELLER, NUM_TRANS_ATM,
... CAST(CASE LTV_BIN
... WHEN 'LOW' THEN 1
... WHEN 'MEDIUM' THEN 2
... WHEN 'HIGH' THEN 3
... WHEN 'VERY HIGH' THEN 4
... END AS INTEGER) AS LTV_RANK
... FROM CUSTOMER_INSURANCE_LTV""")
>>> CUSTOMER_INSURANCE_LTV_RANKING.head(10)
>>> CUSTOMER_INSURANCE_LTV_RANKING.head(10)
GROUP_ID CUSTOMER_ID REGION ... NUM_TRANS_TELLER NUM_TRANS_ATM LTV_RANK
0 Midwest_1 CU10003 Midwest ... 1 2 3
1 Midwest_1 CU10004 Midwest ... 3 2 1
2 Midwest_1 CU10010 Midwest ... 1 0 3
3 Midwest_1 CU10011 Midwest ... 6 4 4
4 Midwest_1 CU10014 Midwest ... 0 1 3
5 Midwest_1 CU10015 Midwest ... 2 1 2
6 Midwest_1 CU10017 Midwest ... 3 2 3
7 Midwest_1 CU10019 Midwest ... 0 2 3
8 Midwest_1 CU10036 Midwest ... 0 0 2
9 Midwest_1 CU10038 Midwest ... 0 0 2
[10 rows x 29 columns]
>>> # Drop model if it exists
... try:
... oml.drop(model = 'XGB_RANKING_MODEL')
... except:
... pass
...
... # Prepare training data - drop CUSTOMER_ID and GROUP_ID from features
... train_x = CUSTOMER_INSURANCE_LTV_RANKING.drop(['LTV_RANK', 'CUSTOMER_ID'])
... train_y = CUSTOMER_INSURANCE_LTV_RANKING['LTV_RANK']
...
... # Build XGBoost ranking model
... # XGBoost ranking uses pairwise comparison to learn relative rankings
... # Note: All parameters must use 'xgboost_' prefix
... setting = {
... 'xgboost_objective': 'rank:pairwise', # Pairwise ranking objective
... 'xgboost_max_depth': '6', # Maximum tree depth
... 'xgboost_eta': '0.1', # Learning rate
... 'xgboost_gamma': '1.0', # Minimum split loss
... 'xgboost_num_round': '100', # Number of boosting rounds
... 'xgboost_min_child_weight': '0.1' # Minimum leaf node weight
... }
...
... xgb_ranking_model = oml.xgb('regression', **setting)
...
... # Fit the model with GROUP_ID as case_id for ranking
... # The case_id groups data points for pairwise ranking
... xgb_ranking_model.fit(
... train_x,
... train_y,
... case_id = 'GROUP_ID',
... model_name = 'XGB_RANKING_MODEL')
...
Model Name: XGB_RANKING_MODEL
Model Owner: OML_USER
Algorithm Name: XGBOOST
Mining Function: REGRESSION
Target: LTV_RANK
Settings:
setting name setting value
0 ALGO_NAME ALGO_XGBOOST
1 ODMS_DETAILS ODMS_ENABLE
2 ODMS_MISSING_VALUE_TREATMENT ODMS_MISSING_VALUE_AUTO
3 ODMS_SAMPLING ODMS_SAMPLING_DISABLE
4 PREP_AUTO ON
5 booster gbtree
6 eta 0.1
7 gamma 1.0
8 max_depth 6
9 min_child_weight 0.1
10 ntree_limit 0
11 num_round 100
12 objective rank:pairwise
Computed Settings:
setting name setting value
0 ODMS_EXPLOSION_MIN_SUPP 13
Global Statistics:
attribute name attribute value
0 NUM_ROWS 13880
1 ndcg@32 0.999912
Attributes:
AGE
BANK_FUNDS
BUY_INSURANCE
CAR_OWNERSHIP
CHECKING_BALANCE
CREDIT_BALANCE
CREDIT_CARD_LIMITS
CUSTOMER_TENURE
GENDER
HAS_CHILDREN
HOME_OWNERSHIP
INCOME
MARITAL_STATUS
MONEY_MONTLY_OVERDRAWN
MONTHLY_CHECKS
MORTGAGE_AMOUNT
NUM_DEPENDENTS
NUM_MORTGAGES
NUM_ONLINE_TRANS
NUM_TRANS_ATM
NUM_TRANS_KIOSK
NUM_TRANS_TELLER
PROFESSION
REGION
STATE
TOTAL_AUTOM_PAYMENTS
Partition: NO
ATTRIBUTE IMPORTANCE:
PNAME ATTRIBUTE_NAME ATTRIBUTE_SUBNAME ATTRIBUTE_VALUE GAIN COVER FREQUENCY
0 None AGE None None 0.139700 0.190058 0.277539
1 None BANK_FUNDS None None 0.000529 0.000563 0.005642
2 None BUY_INSURANCE None Yes 0.000045 0.000010 0.000537
3 None CAR_OWNERSHIP None None 0.000020 0.000056 0.000269
4 None CHECKING_BALANCE None None 0.000316 0.000708 0.003224
.. ... ... ... ... ... ... ...
56 None STATE None AK 0.000021 0.000052 0.000269
57 None STATE None WA 0.000024 0.000103 0.000269
58 None STATE None FL 0.000038 0.000053 0.000537
59 None STATE None MS 0.000087 0.000239 0.001075
60 None TOTAL_AUTOM_PAYMENTS None None 0.000664 0.001391 0.005911
[61 rows x 7 columns]
>>>
>>> xgb_ranking_model.settings
...
{'xgboost_objective': 'rank:pairwise', 'xgboost_max_depth': '6', 'xgboost_eta': '0.1', 'xgboost_gamma': '1.0', 'xgboost_num_round': '100', 'xgboost_min_child_weight': '0.1'}
>>> # Generate predictions - drop only the target column
... pred=xgb_ranking_model.predict(
... CUSTOMER_INSURANCE_LTV_RANKING.drop('LTV_RANK'),
... supplemental_cols = CUSTOMER_INSURANCE_LTV_RANKING[:, ['CUSTOMER_ID', 'REGION', 'LTV_RANK']]
... )
...
... # Display first 10 predictions
... pred.head(10) # crashes
...
CUSTOMER_ID REGION LTV_RANK PREDICTION
0 CU14797 NorthEast 2 -3.386016
1 CU14798 West 2 -3.205726
2 CU14800 NorthEast 4 4.347278
3 CU14801 NorthEast 3 0.759600
4 CU14802 West 3 0.355775
5 CU14803 South 3 1.155726
6 CU14804 NorthEast 2 -2.324930
7 CU14805 NorthEast 3 -0.244180
8 CU9251 Midwest 3 1.380295
9 CU9260 Southwest 4 3.839250
>>> # Generate predictions for all customers
... all_predictions = xgb_ranking_model.predict(
... CUSTOMER_INSURANCE_LTV_RANKING,
... supplemental_cols=CUSTOMER_INSURANCE_LTV_RANKING[['CUSTOMER_ID', 'REGION', 'LTV_RANK']])
...
... # Sort by predicted score descending and get top 15
... top_10 = all_predictions.sort_values(by='PREDICTION', ascending=False).head(10)
...
... top_10
...
CUSTOMER_ID REGION LTV_RANK PREDICTION
0 CU1764 West 4 6.025033
1 CU5689 West 4 6.004729
2 CU6449 Midwest 4 5.918326
3 CU6110 NorthEast 4 5.915053
4 CU5605 Midwest 4 5.851047
5 CU6839 NorthEast 4 5.851047
6 CU6124 Midwest 4 5.826969
7 CU2079 West 4 5.820862
8 CU2823 NorthEast 4 5.814080
9 CU6450 NorthEast 4 5.805640
>>>
>>>
Parent topic: XGBoost