This example finds similar customers and recommended products for them.
Teradata recommends PSALSA for recommending products to similar users when the product line has limited pairwise history or changes frequently. An example of a product line that changes frequently is apparel, which is subject to seasons and trends.
Input
- Vertices: user_product_nodes, which has customer names
- Edges: women_apparel_log, which reflects customer shopping patterns
nodeid | nodename |
---|---|
1 | Sandra |
2 | Susan |
3 | Stacie |
4 | Stephanie |
5 | Sally |
6 | coats |
7 | sweaters |
8 | jackets |
9 | blazers |
10 | pants |
11 | pajamas |
username | product | frequency |
---|---|---|
Sally | blazers | 2 |
Sally | coats | 10 |
Sally | jackets | 8 |
Sally | sweaters | 9 |
Sandra | coats | 10 |
Sandra | jackets | 8 |
Sandra | sweaters | 9 |
Stacie | pajamas | 9 |
Stacie | pants | 9 |
Stephanie | blazers | 5 |
Stephanie | jackets | 4 |
Stephanie | pajamas | 7 |
Stephanie | pants | 6 |
Susan | blazers | 5 |
Susan | jackets | 2 |
Susan | pajamas | 5 |
Susan | pants | 4 |
Susan | sweaters | 4 |
SQL Call
This call outputs a maximum of two similar users (hub) and recommends two products (authority) for each user, using frequency of purchase as a weight factor.
SELECT * FROM PSALSA ( ON user_product_nodes AS Vertices PARTITION BY nodename ON women_apparel_log AS Edges PARTITION BY username USING SourceKey ('username') TargetKey ('product') EdgeWeight ('frequency') MaxHubNum (2) MaxAuthorityNum (2) TeleportProb (0.15) RandomWalkLength (500) ) AS dt ORDER BY username, hub_score DESC, authority_score DESC;
Output
The output shows possible recommendations, based on hub_score and authority_score. For example, the seller can recommend pajamas to Sandra and Susan because they and Sally have similar scores. The hub_score and authority_score values vary with every run.
username hub_username hub_score authority_product authority_score --------- ------------ ------------------- ----------------- ------------------- sally sandra 0.23255813953488372 NULL NULL sally stephanie 0.13565891472868216 NULL NULL sally NULL NULL pajamas 0.13127413127413126 sally NULL NULL pants 0.12355212355212356 sandra sally 0.212 NULL NULL sandra stephanie 0.152 NULL NULL sandra NULL NULL pajamas 0.144 sandra NULL NULL pants 0.136 stacie stephanie 0.1984732824427481 NULL NULL stacie sally 0.17557251908396945 NULL NULL stacie NULL NULL jackets 0.1482889733840304 stacie NULL NULL sweaters 0.13307984790874525 stephanie sally 0.18253968253968253 NULL NULL stephanie susan 0.1746031746031746 NULL NULL stephanie NULL NULL coats 0.13043478260869565 stephanie NULL NULL sweaters 0.12648221343873517 susan stephanie 0.17120622568093385 NULL NULL susan stacie 0.16731517509727625 NULL NULL susan NULL NULL coats 0.09689922480620156
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.