This example uses HMMDecoder to find the propensity of customer churn, given the actions or transactions of a bank customer.
Input
- InitialStateProbs: churn_initial
- TransitionProbs: churn_state_transition
- EmissionProbs: churn_emission
- ObservationTable: churn_data
In the first three tables, T indicates that the customer is likely to churn and F indicates that the customer is unlikely to churn. In the fourth table, the column action has transactions, the column id has customer identifiers, and the column path_id shows transaction order.
InitialStateProbs: churn_initial| model |
tag |
probability |
|---|
| 1 |
F |
0.909 |
| 1 |
T |
0.091 |
TransitionProbs: 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 |
EmissionProbs: 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 |
ObservationTable: 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 InitialStateProbs PARTITION BY model ORDER BY model, tag
ON churn_state_transition AS TransitionProbs PARTITION BY model
ON churn_emission AS EmissionProbs PARTITION BY model
ON churn_data AS ObservationTable PARTITION BY model
ORDER BY model,id, path_id ASC
USING
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;
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
Download a zip file of all examples and a SQL script file that creates their input tables.