E-Commerce Website Page Symbols
This example uses clickstream data from an e-commerce web site. The following table lists and describes the symbols of the web site pages.
Symbol | Meaning | Description |
---|---|---|
H | Home | Home page |
A | Account | User account pages |
C | Category | Page with list of products |
P | Product | Product information pages |
I | Information | Shipping, order status, etc. |
S | ShoppingCart | Pre-order pages |
O | Order | Confirmation/purchase page |
E | Enter/Exit | Noncommercial vendor pages |
Input
The input table, clickstream1, identifies each customer by a userid. The path column lists the symbols for the pages that the customer clicked, ordered from first to last. For symbol meanings, see the preceding table.
userid | usertype | path | cnt |
---|---|---|---|
1 | browser | H,E | 2 |
2 | browser | H,C,P,E | 5 |
3 | browser | H,C,H,P | 7 |
4 | browser | A,H,C | 3 |
5 | browser | A,H,P | 2 |
6 | buyer | H,A,C,P,I,S,O,E | 4 |
7 | buyer | A,P,S,O, | 6 |
8 | buyer | P,C,P,S,O,E | 2 |
9 | buyer | H,A,P,I,S,O | 2 |
10 | buyer | C,S,O,E | 1 |
SQL Call
SELECT * FROM PathGenerator( ON clickstream1 USING SeqColumn('path') Delimiter(',') ) AS dt ORDER BY userid, usertype, cnt ASC;
Output
userid usertype path cnt prefix sequence ------ -------- --------------- --- ----------------- ----------------- 1 browser h,e 2 ^,h,e ^,h,e 1 browser h,e 2 ^,h ^,h,e 2 browser h,c,p,e 5 ^,h,c ^,h,c,p,e 2 browser h,c,p,e 5 ^,h ^,h,c,p,e 2 browser h,c,p,e 5 ^,h,c,p ^,h,c,p,e 2 browser h,c,p,e 5 ^,h,c,p,e ^,h,c,p,e 3 browser h,c,h,p 7 ^,h,c,h ^,h,c,h,p 3 browser h,c,h,p 7 ^,h,c ^,h,c,h,p 3 browser h,c,h,p 7 ^,h,c,h,p ^,h,c,h,p 3 browser h,c,h,p 7 ^,h ^,h,c,h,p 4 browser a,h,c 3 ^,a,h,c ^,a,h,c 4 browser a,h,c 3 ^,a,h ^,a,h,c 4 browser a,h,c 3 ^,a ^,a,h,c 5 browser a,h,p 2 ^,a,h,p ^,a,h,p 5 browser a,h,p 2 ^,a,h ^,a,h,p 5 browser a,h,p 2 ^,a ^,a,h,p 6 buyer h,a,c,p,i,s,o,e 4 ^,h,a,c,p,i,s ^,h,a,c,p,i,s,o,e 6 buyer h,a,c,p,i,s,o,e 4 ^,h,a,c,p,i ^,h,a,c,p,i,s,o,e 6 buyer h,a,c,p,i,s,o,e 4 ^,h,a,c ^,h,a,c,p,i,s,o,e 6 buyer h,a,c,p,i,s,o,e 4 ^,h,a,c,p,i,s,o ^,h,a,c,p,i,s,o,e 6 buyer h,a,c,p,i,s,o,e 4 ^,h ^,h,a,c,p,i,s,o,e 6 buyer h,a,c,p,i,s,o,e 4 ^,h,a,c,p ^,h,a,c,p,i,s,o,e 6 buyer h,a,c,p,i,s,o,e 4 ^,h,a,c,p,i,s,o,e ^,h,a,c,p,i,s,o,e 6 buyer h,a,c,p,i,s,o,e 4 ^,h,a ^,h,a,c,p,i,s,o,e 7 buyer a,p,s,o, 6 ^,a,p,s ^,a,p,s,o, 7 buyer a,p,s,o, 6 ^,a,p ^,a,p,s,o, 7 buyer a,p,s,o, 6 ^,a ^,a,p,s,o, 7 buyer a,p,s,o, 6 ^,a,p,s,o ^,a,p,s,o, 8 buyer p,c,p,s,o,e 2 ^,p,c,p,s,o,e ^,p,c,p,s,o,e 8 buyer p,c,p,s,o,e 2 ^,p,c,p,s,o ^,p,c,p,s,o,e 8 buyer p,c,p,s,o,e 2 ^,p,c,p,s ^,p,c,p,s,o,e 8 buyer p,c,p,s,o,e 2 ^,p,c,p ^,p,c,p,s,o,e 8 buyer p,c,p,s,o,e 2 ^,p,c ^,p,c,p,s,o,e 8 buyer p,c,p,s,o,e 2 ^,p ^,p,c,p,s,o,e 9 buyer h,a,p,i,s,o 2 ^,h,a,p,i,s,o ^,h,a,p,i,s,o 9 buyer h,a,p,i,s,o 2 ^,h,a,p,i,s ^,h,a,p,i,s,o 9 buyer h,a,p,i,s,o 2 ^,h,a,p,i ^,h,a,p,i,s,o 9 buyer h,a,p,i,s,o 2 ^,h,a,p ^,h,a,p,i,s,o 9 buyer h,a,p,i,s,o 2 ^,h,a ^,h,a,p,i,s,o 9 buyer h,a,p,i,s,o 2 ^,h ^,h,a,p,i,s,o 10 buyer c,s,o,e 1 ^,c ^,c,s,o,e 10 buyer c,s,o,e 1 ^,c,s,o,e ^,c,s,o,e 10 buyer c,s,o,e 1 ^,c,s,o ^,c,s,o,e 10 buyer c,s,o,e 1 ^,c,s ^,c,s,o,e
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.