Has anybody performed basket analysis - but between first and second order products

[kk_shp_user]

I've scoured the internet but can only find exmaples where basket analysis is done for items within the same basket. I have a requirement where I need to check the conditional probability of First order items vs second order items.

Addtionally, the first order/ second order items could be bought as aprt of an offer, had a discount, etc which all need slicers.

Just wondering if anybody has data modelled this?

[rajendraongole1 · Accepted solution]

Hi @kk_shp_user -

The key is to have a data model that distinguishes between different orders and allows conditional probability calculations. Your model should have the following components:

Orders Table: Contains each order’s unique identifier (OrderID), CustomerID, OrderDate, and attributes like OfferType or DiscountFlag.
OrderItems Table: Stores individual items in each order, with OrderID, ProductID, and quantity or price details.
Products Table: Contains product details like ProductID, ProductName, and any other relevant attributes.

You’ll also need to create a relationship between Orders and OrderItems on OrderID and between OrderItems and Products on ProductID.

Create a calculated column in the Orders table to rank orders by date for each customer. This will help identify the sequence of each order per customer.

OrderRank =
RANKX(
FILTER(Orders, Orders[CustomerID] = EARLIER(Orders[CustomerID])),
Orders[OrderDate],
,
ASC
)

Use this ranking to create flags for first-order and second-order items in the OrderItems table.

OrderType =
SWITCH(
TRUE(),
Orders[OrderRank] = 1, "First Order",
Orders[OrderRank] = 2, "Second Order",
"Later Order"
)

Count of Customers Who Bought Item A in First Order:

eg FirstOrderCount =
CALCULATE(
DISTINCTCOUNT(Orders[CustomerID]),
FILTER(OrderItems, OrderItems[OrderType] = "First Order" && OrderItems[ProductID] = "Product A")
)

Count of Customers Who Bought Item B in Second Order After Item A in First Order eg: 

ConditionalSecondOrderCount =
CALCULATE(
DISTINCTCOUNT(Orders[CustomerID]),
FILTER(
Orders,
Orders[OrderType] = "Second Order" &&
Orders[CustomerID] IN
CALCULATETABLE(
VALUES(Orders[CustomerID]),
FILTER(OrderItems, OrderItems[OrderType] = "First Order" && OrderItems[ProductID] = "Product A")
)
),
OrderItems[ProductID] = "Product B"
)

Conditional Probability Measure:

ConditionalProbability =
DIVIDE(
[ConditionalSecondOrderCount],
[FirstOrderCount],
0
)

I hope this approach allows you to perform sequential basket analysis and calculate conditional probabilities, along with the flexibility to add filters for discount and offer attributes. The calculated columns and measures give you control over determining first-order and second-order relationships, and slicers enable further analysis based on different conditions.