How to create churn data for customers by transaction in month?

Analytics

How to create churn data for customers by transaction in month?

So I have created a table that has the following columns from a transaction table with all customer purchase records: 1. Month-Year, 2.Customer ID, 3. Number of Transactions in that month.



I'm trying to create a table that has the output of 1. Month-Year, 2. Number of churned customers in that month defined as customers who have not had a transaction in the last 12 month. (So a customer would churn in Feb of 2015 if they only made one purchase in January of 2014. And if that person then has a transaction in March of 2015 but none until May 2016 then they've churned again in April of 2016).



I would appreciate any suggestions here- I know teradata is really bad at recursive function so I was thinking that if there's no solution I would just manually do each month with select range and then insert into one aggregate table to be updated each month.



Thank you!

2 REPLIES
Senior Apprentice

Re: How to create churn data for customers by transaction in month?

Answered on StackOverflow: How to by pass case not when exists in teradata?

Btw, recursive is not bad in Teradata if it's done the right way :)

Enthusiast

Re: How to create churn data for customers by transaction in month?

Hi Dieter 

I want to Disscuss with you about Structure query in aster , how to create statement query in the aster , 

whether query in aster with the teradata is different or not ? 

by the way , have you Whatsapp ? if you have i want to disscuss via Whatsapp :) 

thanks for advice