Frequency Analysis

[Kyle123]

This is more maths/google juice that I'm after than a total solution, but my maths is rubbish.

I'm trying to do some churn analysis on a group of customers who make ad-hoc purchases, since they're ad-hoc it's difficult to say whether a particular customer has actually churned/gone to a competitor or just hasn't got round to making a purchase yet.

Purchase intervals will vary from annually to 100s per month.

What I'm trying to wrap my puny brain ultimately is creating a model to predict churn based on complaints, past purchase history, purchase value trends etc, but I've fallen at the first hurdle.

I simply want to work out how to rank a set of data based on descending frequency, weighted by recent date; so take purchases for example:
\1
Company C should rank lowest as there are purchases every month, Company K should rank highest as the drop is the largest.

Note: I'm not actually interested in ranking the actual values in excel per se, more learning how to identify in a much larger dataset - I'm going to have to ultimately convert the solution to SQL

I've been reading up on linear regression, which I think is the way to go, but I'm open to suggestions and new to this.

[Glenn Kennedy]

I'd be tempted to look at CUSUM and Shewart analysis to try to identify identify changes/trends

[Richard Buttrey]

Hi Kyle,

Does the attached which use Standard Deviations as a measure of variability give you any ideas?

[Kyle123]

Both these are useful, CUSUM looks good and relatively simple to implement, I just ignore any above the upper limit for these purposes.

Hmm, Richard, I'm not so sure, take company B for example, it ranks highly due to the large stdDev as there are no earlier purchases - in this case this is an upward trend rather than downward so should rank at the bottom. For this purpose, I'm not interested in increasing customer purchases, rather identifying those who purchase less.