Product Affinity michel bruleyteradata com Extract from various

Product Affinity michel. bruley@teradata. com Extract from various presentations: CRS, BUS 782, Aster Data … January 2013 www. decideo. fr/bruley

Product affinity analysis is one of the basket analysis techniques Assortment analysis & Management No. Of Baskets Av. Basket Metrics Traffic Builder ID Traffic analysis Customer Analysis & Marketing Item Contribution Trx Builder ID Price Point Contribution Av. Dist. # of Items & Depts Trx Builder ID Item contribution “Variety driver” Affinity Analysis Item Deletion Cross Sell Lost Sales Prevention Potential overstock Item deletion Item contribution Price Point Identification Price Elasticity Cherry Picker Price Point Frequency of visits Consumer Penetration Value, av. Purchase Discount behaviour Customer Modelling Promotional Evaluation Purchase Variety Behaviour Customer Modelling Promotion Evaluation (w/Cherry Picker) Co-marketing opportunities by customer Cherry Picking Behaviour Consumer Profitability Product quality (returns) Fraud detection Time of Day In-Store activities In-Store Activities Payment influence Payment Transaction analysis www. decideo. fr/bruley Promotion evaluation & management Vendor & Supply Chain Management Consumer Penetration (with Cust. ID) Traffic Builder ID Traffic Analysis (manpower planning) Item Performance Store Performance evaluation Promotion Evaluation Promo Item Selection Event Strategy Cross sell opportunity Vendor Participation Co Merchandising Opportunities (visual merchandising) Promotion Evaluation Promo item selection Margin Protection Vendor participation Price integrity Fraud detection Local pricing Fraud Detection Cashier productivity Manpower planning In-store activities Payment type Traffic builder Opp. Promotional Pricing Event Strategy (“Early Bird” opportunities) Store Operations

Product Affinity Definition n Identify which products are sold together and use that information to influence targeted marketing efforts, store layouts, and in-store promotions n Product Affinity enables an organization to detect product/service purchase patterns, linkages, and cross-sell opportunities in order to increase revenues. Results from this application will enable the organization to identify, with a high degree of accuracy, those customers most interested in specific products, services and product/service groupings www. decideo. fr/bruley

Affinity Analysis n Affinity Analysis is a modeling technique based upon theory that if you buy a certain group of items, you are more (or less) likely to buy another group of items. n The set of items a customer buys is referred to as an item set, and market basket analysis seeks to find relationships between purchases. n Typically the relationship will be in the form of a rule: Example: – IF {beer, no bar meal} THEN {chips} www. decideo. fr/bruley

Product Affinity and Cross- Selling n For instance, customers are very likely to purchase shampoo and conditioner together, so a retailer would not put both items on promotion at the same time. The promotion of one would likely drive sales of the other n A widely used example of cross selling on the internet with market basket analysis is Amazon. com's use of suggestions of the type: – "Customers who bought book A also bought book B", e. g. www. decideo. fr/bruley

Product Affinity Analysis Process n Historic market basket data and analyzes are used to build more effective marketing programs: – Past customer purchase data is used to identify which products/services are acquired by which customer groups – Predictive analytics is applied to this data to discover profiles of customers most likely to buy the products in each group – These profiles are used to target those customers most likely to respond favorably to specific cross-sell campaign – Pair-wise product associations are also determined to enable the constructed of offers featuring the purchase of these pair products – Customer product dislikes are also identified so that company does not promote unwanted products n Benefits that can be realized from utilizing this solution: – Improve customer knowledge allowing company to better understand what their customers are likely to buy and not buy. – Increase revenue and decrease costs by identifying those customers most likely to respond to cross-sell campaigns www. decideo. fr/bruley

Behavior Prediction This uses past consumer behavior to foresee the future behavior of their customers. This analysis includes several variations. 1. Propensity-to-buy analysis- understanding what a particular customer might buy. 2. Next Sequential Purchase- predicting the customers next buy. 3. Product Affinity Analysis- Understanding which products will be bought with others. 4. Price elasticity modeling and dynamic pricing- determine the best price for a given product. www. decideo. fr/bruley

Product Affinity = Link Analysis n Aims to establish links (associations) between records, or sets of records, in a database n There are three specializations – Associations discovery – Sequential pattern discovery – Similar time sequence discovery www. decideo. fr/bruley

Link Analysis: Associations Discovery n Finds items that imply the presence of other items in the same event n Affinities between items are represented by association rules – e. g. ‘When customer rents property for more than 2 years and is more than 25 years old, in 40% of cases, customer will buy a property. Association happens in 35% of all customers who rent properties’. www. decideo. fr/bruley

Link Analysis: Sequential Pattern Discovery n Finds patterns between events such that the presence of one set of items is followed by another set of items in a database of events over a period of time. – e. g. Used to understand long-term customer buying behaviour www. decideo. fr/bruley

Link Analysis: Similar Time Sequence Discovery n Finds links between two sets of data that are time- dependent, and is based on the degree of similarity between the patterns that both time series demonstrate – e. g. Within three months of buying property, new home owners will purchase goods such as cookers, freezers, and washing machines www. decideo. fr/bruley

For Analytics: SQL or SQL-Map. Reduce Teradata SQL Aster SQL-Map. Reduce SQL is better for: SQL-Map. Reduce is better for: • Standard transformations across every element in a table • Custom Transformations • Standard aggregations using GROUP BY on tables • sum(), max(), stddev() • Custom Aggregations • Dimensional Joins • Inter-row Analysis, like time-series • Set Filtering • Layered queries • Lookups, data pruning to limit a table to a subset. • Presentation formatting • For example, “get me top K counts only” www. decideo. fr/bruley • e. g. unstructured data, log extraction, conditional manipulation • Nested queries, sub-queries, recursive queries • Analysis that requires reorganization of data into new data structures • Graph analysis, decision trees, etc.

Time Series Analysis discover patterns in rows of sequential data Sales Transactions {user, product, time} Purchase 1 Purchase 2 Purchase 3 Purchase 4 Aster Data SQL/MR Approach • Single-pass of data • Linked list sequential analysis • Gap recognition Traditional SQL Approach • Full Table Scans • Self-Joins for sequencing • Limited operators for ordered data www. decideo. fr/bruley

Identify common product baskets of interest Cross-Channel Transactions 43 M Customers Online Alone! Teradata Aster solution In-Store Transactions Challenge • Identify correlations between purchases made over time With Aster Data • SQL-Map. Reduce for market basket analysis indicates correlations between products Impact • Move beyond “people who bought this also bought” to time-based recommendations www. decideo. fr/bruley user. ID EAN Author Store time 15682817 823201 JK Rowling 100 12: 00 PM 16816193 123101 Shakespeare 105 1: 45 PM 19825996 182191 Rick Riordan 201 3: 00 PM 15528047 823201 Walter Isaacson 100 4: 20 PM item_no type EAN 12334 book 823201 Product 13345 music -- Catalog 21456 periodical -- 82673 toy -- Online Transactions IPAddress EAN Author time 192. 168. 20. 14 823201 John Grisham 12: 00 PM 172. 16. 254. 1 123101 Dostoevsky 1: 45 PM 216. 27. 61. 137 182191 Obama 3: 00 PM 194. 66. 82. 11 823201 Stephen King 4: 20 PM

Basket Affinity: Retail Business Need Overview: n For most retailers, Market basket affinity is a well known tool for cross-promotions and marketing. n However, there is very little affinity known “outside” the basket. n For example, there are many cases where the consumer will return to the store to get the additional item(s) they did not purchase initially. Examples: n Electronics retailer (Best Buy, Radio Shack, Fry’s): – A Blue-Ray player is purchased online on a given date. The same customer visits the store next week to buy HDMI cables and a B-R disc. n Fashion Retailer (Target, Macy’s, J Crew): – A customer purchases a dress and hand bag one week. Returns within a month to buy matching shoes. n With this sequential affinity analysis, the retailer can send very specific and timely email marketing, to drive traffic and increase revenue. www. decideo. fr/bruley

Overview of Cross-Basket Affinity Challenge • • Difficult to do in a relational DB due to the sheer size of the combinatorial permutations of the various purchasing sequences. Requires good customer recognition via a credit card database or a customer loyalty card program. With Teradata Aster • • Use n. Path/Sessionization to identify “super” baskets within a time window. Tighter time window implies higher affinity. Run Basket Generator to identify the most frequent affinity items & subcategories. Impact • Enables more accurate targeting of customer needs; reduce direct marketing spend, increase revenue yield. www. decideo. fr/bruley Cross-Channel Transactions X Customers X Marketing Campaigns Transactional DB Customer Loyalty Trans. ID User. Id Date/Time Item UPC 874143 10001 11/12/24 83321 543422 20001 11/12/28 73910 632735 30002 11/12/24 39503 452834 10001 11/12/30 49019 User. Id Address Phone 10001 10 Main St 555 -3421 20001 24 Elm st 232 -5451 30002 534 Rich 232 -5465 Retail EDW Product/Item Hierachy Item UPC Category Dept 83321 Heels Shoes-Womens 73910 Handbags Accessories 39503 Dresses Apparel. Womens 49019 Perfumes Cosmetics Marketing/Promotions Date Campaign. ID User. Id 11/12/24 3241 10001 11/12/28 2352 20001 11/12/24 3241 30002 11/12/30 2352 10001

Cross-Basket Affinity Example User. Id 10001 Prepares multi-structured data • Aster Map. Reduce Platform Address Phone 10 Main St 555 -3421 20001 Trans. ID Date/Time 24 Elm st User. Id 232 -5451 Item UPC 30002 534 Rich 874143 10001 232 -5465 11/12/24 83321 543422 20001 11/12/28 73910 632735 30002 11/12/24 39503 452834 10001 11/12/30 49019 Stitches rows together by customer in a timeordered view Scans all records to produce complete set of sequences • • No need to define patterns in advance Fully parallelized for scalable performance using Map. Reduce where not feasible with SQL/SAS Step 1: n. Path/ Sessionization to identify “super” baskets. Trans. ID User. Id Date/Time Item UPC Super. Sessi on Seq. Num 874143 10001 11/12/24 83321 101 1 452834 10001 11/12/30 49019 101 2 Summarize sequential affinity output for business exploration • Rank order the most popular sequential purchase paths. www. decideo. fr/bruley Step 2: run Basket Generator to identify frequent affinity items. Product Upc. A Product. U pc. B Support Confiden ce Time Window Sequentia l. Order 83321 49019 0. 10 0. 30 14 days true 73910 83321 0. 11 0. 25 7 days false

Identifies the Cross-Basket Affinity Products n The frequent sequence of purchased items identifies products B & C which are likely to be sold when a customer buys a certain product A. – Leverage this Cross-Affinity analysis to run more targeted marketing campaigns; increase affinity purchases – Personalized email offers yields higher customer retention and loyalty, and reduces churn. n Aster SQL-MR functions n. Path/Sessionization and Basket Generators are key algorithmic differentiators; this process cannot be done in a scalable manner in a relational DB and/or SAS www. decideo. fr/bruley

Affinity Use Case 1/3 n Analyzing item price movements and its impact on: – Basket size over a long duration (6 -10 yrs) will provide key insights into halo impact and affinity contribution for items – Basket composition over a long duration (610 yrs) will provide key insights into price bands for items n Analyzing Affinity of items over a long duration (6 -10 yrs) will provide key insights into running better promotions, planogram and price planning of around affinity items www. decideo. fr/bruley

Affinity Use Case 2/3 Affinity Analysis • Analyzing Affinity of items over a long duration (6 -10 yrs) will provide key insights into running better promotions, planogram and price planning using items affinity • Time Frame: 8 Years, 1 Banner - Data Set: Transaction Data, Product hierarchy Consumer Migration • • Analyzing declines in consumer segments over large timeframes. Time Frame: 3 Years - Data Set: Transaction Data, Segment Data, Competitor Data, Pricing Data Pricing Affinity • • Analyzing item price movement and its impact on basket size and affinity of items over a long duration (6 years) Data Set: Transaction Data, Price data - Time Frame: 6 Years Competitor Impact • Data Set: Transaction/Consumer/Competitor/Pricing Data, Unit_Inf - Time Frame: 8 Years Social Media • • Integrating consumer online data (Social Media - Facebook) with existing transaction data and understand impact on consumer loyalty. Data Set: Should be collected by vendor www. decideo. fr/bruley

Affinity Use Case 3/3 n Data – ~ 8 years of transaction data (2004 up to Sep-2011) – 15 Billion baskets (or transactions) – 225 Stores – 367 K Unique UPCs – 12 Categories: Alcohol, Cereal, Frozen – Ice Cream, Laundry Detergent, Cheese (Shredded/Sliced/Chunk/Other), Paper Towels, Pizza & Shelf Stable Juice n Solution – Aster SQL-Map. Reduce: Collaborative Filter – Query Runtime: 48 minutes (4 Workers using Columnar) www. decideo. fr/bruley