This example uses model files with the input to determine the loyalty levels of customers from a new sequence of purchases. The loyalty levels are low (L), normal (N), and high (H).
Input
The ObservationTable, customer_loyalty_newseq, is a collection of three new test sequences (seq_id 4, 5, 6) for user_id 1. HMMSupervised trained multiple users and obtained the trained model files (pi_loyalty, A_loyalty and B_loyalty).
| id | user_id | seq_id | purchase_date | observation |
|---|---|---|---|---|
| 301 | 1 | 4 | 2014-05-01 | LL |
| 302 | 1 | 4 | 2014-05-02 | LS |
| 303 | 1 | 4 | 2014-05-03 | SS |
| 304 | 1 | 4 | 2014-05-04 | SM |
| 305 | 1 | 4 | 2014-05-05 | LL |
| 306 | 1 | 4 | 2014-05-06 | ML |
| 307 | 1 | 4 | 2014-05-07 | SS |
| 308 | 1 | 4 | 2014-05-08 | MM |
| 309 | 1 | 4 | 2014-05-09 | MS |
| 310 | 1 | 4 | 2014-05-10 | ML |
| 311 | 1 | 5 | 2014-05-01 | ML |
| 312 | 1 | 5 | 2014-05-02 | SM |
| 313 | 1 | 5 | 2014-05-03 | MS |
| 314 | 1 | 5 | 2014-05-04 | MS |
| 315 | 1 | 5 | 2014-05-05 | SS |
| 316 | 1 | 5 | 2014-05-06 | MS |
| 317 | 1 | 5 | 2014-05-07 | ML |
| 318 | 1 | 5 | 2014-05-08 | MM |
| 319 | 1 | 5 | 2014-05-09 | SM |
| 320 | 1 | 5 | 2014-05-10 | SM |
| 321 | 1 | 6 | 2014-05-01 | SM |
| 322 | 1 | 6 | 2014-05-02 | MS |
| 323 | 1 | 6 | 2014-05-03 | SS |
| 324 | 1 | 6 | 2014-05-04 | LM |
| 325 | 1 | 6 | 2014-05-05 | SL |
| 326 | 1 | 6 | 2014-05-06 | SS |
| 327 | 1 | 6 | 2014-05-07 | SS |
| 328 | 1 | 6 | 2014-05-08 | SM |
| 329 | 1 | 6 | 2014-05-09 | LS |
| 330 | 1 | 6 | 2014-05-10 | LS |
SQL Call
SELECT * FROM HMMDecoder(
ON pi_loyalty AS InitialStateProbs PARTITION BY user_id ORDER BY user_id, state
ON A_loyalty AS TransitionProbs PARTITION BY user_id
ON B_loyalty AS EmissionProbs PARTITION BY user_id
ON customer_loyalty_newseq AS ObservationTable PARTITION BY user_id
ORDER BY user_id, seq_id, purchase_date ASC
USING
InitStateModelColumn('user_id')
InitStateColumn('state')
InitStateProbColumn('probability')
TransAttributeColumn('user_id')
TransFromStateColumn('from_state')
TransToStateColumn('to_state')
TransProbColumn('probability')
EmitModelColumn('user_id')
EmitStateColumn('state')
EmitObsColumn('observed')
EmitProbColumn('probability')
ModelColumn('user_id')
SeqColumn('seq_id')
ObsColumn('observation')
Accumulate('purchase_date')
) AS dt ORDER BY 1, 2, 5;
Output
The output table shows the decoded loyalty levels for the new sequence. For seq_id 5, the loyalty level increased towards the end of the sequence (from L to H; for the other sequences (seq_id 4 and seq_id 6), the loyalty level did not change.
user_id seq_id observation state purchase_date ------- ------ ----------- ----- ------------- 1 4 ll l 2014-05-01 1 4 ls l 2014-05-02 1 4 ss l 2014-05-03 1 4 sm l 2014-05-04 1 4 ll l 2014-05-05 1 4 ml l 2014-05-06 1 4 ss l 2014-05-07 1 4 mm l 2014-05-08 1 4 ms l 2014-05-09 1 4 ml l 2014-05-10 1 5 ml l 2014-05-01 1 5 sm l 2014-05-02 1 5 ms l 2014-05-03 1 5 ms l 2014-05-04 1 5 ss l 2014-05-05 1 5 ms l 2014-05-06 1 5 ml l 2014-05-07 1 5 mm l 2014-05-08 1 5 sm h 2014-05-09 1 5 sm h 2014-05-10 1 6 sm l 2014-05-01 1 6 ms l 2014-05-02 1 6 ss l 2014-05-03 1 6 lm l 2014-05-04 1 6 sl l 2014-05-05 1 6 ss l 2014-05-06 1 6 ss l 2014-05-07 1 6 sm l 2014-05-08 1 6 ls l 2014-05-09 1 6 ls l 2014-05-10
Download a zip file of all examples and a SQL script file that creates their input tables.