Favorites product categories

📊 Business Context

What is it about?
  • The objective is to know for each customer which product categories are the most likely to generate a new order.
  • For each customer, we compute the 3 favorites products categories according to the number of distinct purchases.
  • In the case of a tie, we use the last purchase date: the product category with the most recent purchase is ranked highest.
  • As output, we suggest 3 "Favorites products categories" variables n°1 to 3 which can take for modality all the values of the chosen source field.
What are the main use cases?
  • Email personalization > Push recommended product categories in your automated emails (post purchase, reactivation, ..) or even as a conditional section in your newsletter.
  • Web personalization > Push recommended product categories in different steps of the web user journey, in a dynamic block of some pages, or a popup.
  • Any channel > Almost customer journey touchpoints could be used to upsell / cross-sell your existing customers (outbound phone calls, postal mail promotion, ..)
What business impact can I expect?
  • Impact on: Upsell, Average Order Value, Number of orders per customer
  • Level of impact: ⭐️⭐️️⭐️️⭐️️

↔️ Inputs & Outputs

Data input
  • A dataset containing your Customers data, with columns for:
    • user_id: unique identifier of the customer.
  • A dataset containing your Orders (items) data, with columns for:
    • user_id: unique identifier of the customer.
    • order_date: date at which the purchase was made.
    • product_id: unique identifier of the product purchased.
  • A dataset containing your Products data, with columns for:
    • product_id: unique identifier of each product.
    • category_id: unique identifier of each product.
User Settings
  • Split recommendations output in several columns: YES or NO
    • NO = all recommended categories are shared as a list of IDs in a single column.
    • YES = we create one column by recommended category like cross_sell_category_1 each column containing a single ID.
Processing hypothesis
Expected output
Depending on your settings, you can get all Ids in one column with values separated by “,” or a column for each id. You can also get new columns for names in addition to Ids.
Your new columns will look like :
  • cross_sell_categories (or cross_sell_cat_1 + … + cross_sell_cat_N )

🖥️ Implementation in Octolis

1. Prepare your input data
Make sure that you have built an Dataset containing all your orders.
In case you have several order Sources (e.g. eShop & PoS) you need to group all sources into an orders master Dataset.
2. Apply the SQL Template “Favorites products categories”
  • Create a new Dataset using SQL expert mode.
  • Copy / paste the following SQL template.
  • After pasting the template, make sure to modify all variables with the names of your input columns.
  • Add your Favorites products categories Dataset as a source of your Contacts Dataset, and map the needed fields to import them into your Contacts Dataset.
// Favorites products categories Segmentation SQL Template

Powered by Notaku