Recent Order Date of X

Question & Answer
, , ,
1

Hello,

I have a dataset that has customer no, order no, item no and order date. I’m looking to count the number of customers that have re-purchased product/brand/category X (with ‘X’ being a dynamic parameter). As part of the re-purchase rate analysis I want to count customers where their subsequent order is within N time frame (where again I have set up N time frame as a parameter).

To do this I need to pull the previous order date of X for each row of data (therefore I can say "order_Date <= Previous Order Date + N time frame). I have used minOver function which has worked (“minOver({Order Date of X Order}, [{customer_no}], PRE_AGG)”) however it is pulling back the 1st purchase date of X, not the most recent and therefore is capping subsequent orders where it might be 6 months after the first purchase date, but only 1 month after they’d re-purchased X and therefore they would still be in the ‘retention window’.

I have created another calculated field (minOver((runningMax(MIN({Order Date of X Order}), [{order_sequence_no} ASC])), [{order_no}])) which does give the most recent order date of X, but I cannot use it in my ‘no of returned customers’ calculations as I’m getting the error: Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed.

I feel like the solution is that I need some sort of MinOver calculation, but that gives me the most recent order date of X for every row of data.

TIA

2

It seems like you’re working with a dataset that contains customer information, order details, and order dates. You’re interested in analyzing the re-purchase rate of a specific product/brand/category X within a certain time frame.

To count the number of customers who have re-purchased X within the specified time frame, you need to compare the order date of each row with the most recent previous order date of X for that customer. Unfortunately, using nested aggregate functions like the combination of minOver and runningMax is not allowed in your current system, as indicated by the error message you received.

To overcome this limitation, you can consider using a different approach to achieve your desired result. One possible solution is to preprocess your dataset to calculate the most recent order date of X for each customer and store it as a separate field or column. This preprocessing step can be performed outside of your current analysis tool, using a programming language like Python or SQL.

Here’s a high-level outline of the steps you can follow:

  1. Group your dataset by customer number and filter it to include only orders of product/brand/category X.
  2. Within each customer group, sort the orders by order date in descending order.
  3. Iterate over the sorted orders for each customer and find the most recent order of X.
  4. Store the most recent order date of X as a separate field or column in your dataset, associated with each customer.
  5. Once you have the dataset with the additional field for the most recent order date of X, you can perform your analysis without the need for nested aggregate functions.

After preprocessing your dataset, you can use the following calculation to count the number of customers who re-purchased X within the specified time frame:

COUNTIF({order_Date} <= {Previous Order Date of X} + N)

Replace {order_Date} with the order date of each row, and {Previous Order Date of X} with the corresponding most recent order date of X for that customer. The parameter N represents your desired time frame.

By following this approach, you’ll have the necessary information readily available in your dataset, allowing you to perform your analysis without relying on nested aggregate functions.