Categorizing orders as New, Portfolio and Churn

[jacmanda]

Hi

I am working on a churn analysis that tells me how many new and existing sales we have based on the order table.

The order table consists of Customer_key, Order_key, Order_from, Order_to, and price basically. 

I want to iterate row by row (per order_key) with a calculated column whether the sale is a new or existing sales. The rule we use is that any customer that has an order that runs out ex 15.02.2018 - has to be closed (new order) by the end of March (31.03.2018) to be considered an existing sale. If a customer buys after that period it's considered a new sale. If an order is not renewed by that date, I also want to categorize the order as churn. 

My logic is that I can do a distinct count on the customer_key in order to get an accurate count of how many customers churned, and how many new customers we get per month. I already have a rapport that shows the total number of customers we have per month, and it also shows increase and decrease in customers per month. But what i want to know is the number of new customers and churned customers so when I subtract them the Netto customer increase/decrease matches.

The order_table I am working with.

Thanks in advance to anyone who can help or point me in the right direction 🙂

[v-juanli-msft]

Hi @jacmanda

Does this table represent correctly according to your requirement?

Actually, I don’t understand the following statement clear, could you give me some example to help me understand.

“The rule we use is that any customer that has an order that runs out ex 15.02.2018 - has to be closed (new order) by the end of March (31.03.2018) to be considered an existing sale.”

“If an order is not renewed by that date”

a sale is exsiting, new, or churn, it depends on whether the customer have any other order after this period (15.02.2018- 31.03.2018), right?

“I subtract them the Netto customer increase/decrease matches.” What is Netto customer, is it refer to new customer?

Best Regards

Maggie

[jacmanda]

Hi! Thanks for answering.

@v-juanli-msft 

I was thinking more like this example:

So every order is either New or Existing. And when an order is complete, and the appropriate amount of time has passed (month of churn + 1) without a new order, the order is categorized as churned.