Input
The input table, 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). This example uses these model files with the input to determine the loyalty levels of customers from the new sequence of purchases. The loyalty levels are low (L), normal (N), and high (H).
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 InitStateProb PARTITION BY user_id ORDER BY user_id, state ON A_loyalty AS TransProb PARTITION BY user_id ON B_loyalty AS EmissionProb PARTITION BY user_id ON customer_loyalty_newseq AS observation 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 |