Customer Retention Calculation

[Anonymous]

Hi 

I have a table where  I have custmer transactions,

TRX DateTransaction YearShowEvent Name UniqueEvent NameShow YearFirst Year SaleOriginating Master IDOriginating Master Name Total Amount 

TX Date	TX Year	Project	Department	Project Year	Customer ID	Customer Name
9/06/2010	2010	DB10	DB	2010	CIE003	CIE
14/02/2014	2014	AOG14	AOG	2014	ENG007	Energy
3/05/2011	2011	FF11	FF	2011	MIN033	Ministry of Commerce
20/01/2015	2015	AOG15	AOG	2015	134565	Gmbh
20/12/2010	2010	AOG11	AOG	2011	IES003	IES
19/09/2011	2011	AOG12	AOG	2012	DEP027	Department of Economic Development
4/12/2009	2009	FF10	FF	2010	EMB012	Embas

same customer can have transaction with different departments and  projects. Whats the best way to calculate customer retention (by  department)

thank you

[artemus]

Do a group by on departement and customer ID as the key fields. In the aggregation, choose Max on TX Date . This will give you a table of when a customer did their most recent transaction. You can then add a column with a calcuation determing if the transaction is recent enough to count as being retained or not e.g.

if Date.IsInCurrentMonth([TX Date]) or Date.IsInPreviousNMonths([TX Date], 3) then 1 else 0

. After that you can do another group by on just departmenet, this time summing your new column up, and another aggregate to count rows. This will give you a customers retained and total customers. Finally you can divide. the two columns to find the percentage.

Note this will include all customers even if they have had no transactions in a very long time. If you want to exclude these. Do a filter on the TX Date to remove all old entries (as your first step)