Quarterly analysis

[madhuqliklondon]

Hi friends,

I am trying get the cohort analysis based on quarters. Got data from 1^st apr 2010, where would like to group 2010 to 2012 data as base and work on top in quarters as below attached spread sheet.

I have fields: Customer NO, FirstOrderDate, OrderNo. , OrderValue and Master Calendar built with Year, MonthYear, Month, Week, Quarter,Day.

Please guide to achieve it..

Thanks in advance

[sunny_talwar]

Would you be able to provide sample data and what you expect to see out of it (in numerical terms). If you are looking for just general ideas, try to check these:

Cohort Analysis

[madhuqliklondon]

Hi sunny,

Please see the attachment in sheet 2 got very simple data and applied on sheet 1 . If this is not enough I will update more data. Just kept it simple to understand.

Thanks Sunny

[sunny_talwar]

I understand the calculation for revenue, how are some of the other numbers getting calculated?

[sunny_talwar]

Look at the revenue part

[madhuqliklondon]

Hi Sunny ,

With revenue part, can we get count of orders as well in new customers column and qvw is not fully accessible could you please re upload . 

Calculations: New customers : No of customers ordered their first order in that quarter . Group those customers and tracking their behavior from 2012 to 2017. 

For active customers: Count of orders  which are making revenue.

For retention:  No of active customers through out the total years ex: if we had 100 customers in Q1 as new customers , in q1 its 100 % if Q2 from those 100 customers only 90 customers ordered so it will be 90% retention , in Q3 only 75 customers ordered it's 75% .

for AOV: Order value for the Quarter group / No of orders.

Hope this explains if not I will do a complete document for full one row in spread sheet.

[madhuqliklondon]

Any thoughts please??

[sunny_talwar]

Here is the count... I still don't fully follow... for example how do you define active customer?

You have given the formulas, but I am not an expert in your line of expertise. You will have to take this slowly with me

[madhuqliklondon]

HI Sunny,

Active customers : If a customer made an order in first quarter it comes under active customer but not in 2nd quarter not an active customer again if made an order in Q3  active customer again. Customers who are buying in that quarter they all are active.

[sunny_talwar]

So essentially every customer who buy in a quarter (based on orderdate) is active?