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.

👉 Create a dataset

In case you have several order Sources (e.g. eShop & PoS) you need to group all sources into an orders master Dataset.

👉 Add a source to an existing 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.

sql
// Favorites products categories Segmentation SQL Template