Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
kk_shp_user
Helper I
Helper I

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

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?

1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

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.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

1 REPLY 1
rajendraongole1
Super User
Super User

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.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.