1.0 - 8.00 - HMMDecoder Example 4: Bank Customer Churn - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

This example uses HMMDecoder to find the propensity of customer churn, given the actions or transactions of a bank customer.

Input

In the following three tables, T indicates that the customer is likely to churn and F indicates that the customer is unlikely to churn.

InitStateProb: churn_initial
model tag probability
1 F 0.909
1 T 0.091
TransProb: churn_state_transition
model from_tag to_tag probability
1 F F 1
1 F T 0
1 T F 0
1 T T 1
EmissionProb: churn_emission
model state observed probability
1 F ACCOUNT_BOOKED_OFFLINE 0.005
1 F ACCOUNT_BOOKED_ONLINE 0.028
1 F ADD_DIRECT_DEPOSIT 0.001
1 F BALANCE_TRANSFER 0
1 F BROWSE 0.545
1 F CALL_COMPLAINT 0
1 F CLICK 0.003
1 F COMPARE 0.113
1 F COMPLETE_APPLICATION 0.06
1 F ENROLL_AUTO_SAVINGS 0.001
1 F FEE_REVERSAL 0
1 F LINK_EXTERNAL_ACCOUNT 0
1 F LOAN_CALC 0.024
1 F MORTGAGE_CALC 0.011
1 F OLB 0.039
1 F REFERRAL 0.003
1 F STARTS_APPLICATION 0.167
1 T ACCOUNT_BOOKED_OFFLINE 0.027
1 T ACCOUNT_BOOKED_ONLINE 0.074
1 T ADD_DIRECT_DEPOSIT 0
1 T BALANCE_TRANSFER 0.238
1 T BROWSE 0.016
1 T CALL_COMPLAINT 0.233
1 T CLICK 0
1 T COMPARE 0.033
1 T COMPLETE_APPLICATION 0.028
1 T ENROLL_AUTO_SAVINGS 0
1 T FEE_REVERSAL 0.221
1 T LINK_EXTERNAL_ACCOUNT 0.106
1 T LOAN_CALC 0.006
1 T MORTGAGE_CALC 0.002
1 T OLB 0.002
1 T REFERRAL 0.001
1 T STARTS_APPLICATION 0.013

In the observation table, the column action has transactions, the column id has customer identifiers, and the column path_id shows transaction order.

observation: churn_data
model action path_id path_max id product
1 CALL_COMPLAINT 1 4 1 BROKERAGE
1 CALL_COMPLAINT 2 4 1 BROKERAGE
1 FEE_REVERSAL 3 4 1 BROKERAGE
1 BALANCE_TRANSFER 4 4 1 BROKERAGE
1 ACCOUNT_BOOKED_ONLINE 1 4 2 CREDITCARD
1 FEE_REVERSAL 2 4 2 CREDITCARD
1 LINK_EXTERNAL_ACCOUNT 3 4 2 CREDITCARD
1 BALANCE_TRANSFER 4 4 2 CREDITCARD
1 STARTS_APPLICATION 1 5 3 CD
1 MORTGAGE_CALC 2 5 3 CD
1 COMPARE 3 5 3 CD
1 BROWSE 4 5 3 CD
1 COMPLETE_APPLICATION 5 5 3 CD

SQL Call

SELECT * FROM HMMDecoder (
  ON churn_initial AS InitStateProb PARTITION BY model
  ON churn_state_transition as TransProb PARTITION BY model
  ON churn_emission AS EmissionProb PARTITION BY model
  ON churn_data AS observation PARTITION BY model
  ORDER BY model,id, path_id ASC
  InitStateModelColumn ('model')
  InitStateColumn ('tag')
  InitStateProbColumn ('probability')
  TransAttributeColumn ('model')
  TransFromStateColumn ('from_tag')
  TransToStateColumn ('to_tag')
  TransProbColumn ('probability')
  EmitModelColumn ('model')
  EmitStateColumn ('state')
  EmitObsColumn ('observed')
  EmitProbColumn ('probability')
  ModelColumn ('model')
  SeqColumn ('id')
  ObsColumn ('action')
  Accumulate ('path_id')
) ORDER BY 1, 2, 5, 3;

Output

model id action tag path_id
1 1 CALL_COMPLAINT T 1
1 1 CALL_COMPLAINT T 2
1 1 FEE_REVERSAL T 3
1 1 BALANCE_TRANSFER T 4
1 2 ACCOUNT_BOOKED_ONLINE T 1
1 2 FEE_REVERSAL T 2
1 2 LINK_EXTERNAL_ACCOUNT T 3
1 2 BALANCE_TRANSFER T 4
1 3 STARTS_APPLICATION F 1
1 3 MORTGAGE_CALC F 2
1 3 COMPARE F 3
1 3 BROWSE F 4
1 3 COMPLETE_APPLICATION F 5