topic Re: Cohort analysis in QlikView

Quarterly analysis

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Cohort analysis

sunny_talwar — Tue, 15 Aug 2017 14:11:49 GMT

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

Re: Cohort analysis

madhuqliklondon — Tue, 15 Aug 2017 15:37:19 GMT

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

Re: Cohort analysis

sunny_talwar — Tue, 15 Aug 2017 16:13:18 GMT

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

Re: Cohort analysis

sunny_talwar — Tue, 15 Aug 2017 16:19:40 GMT

Look at the revenue part

Re: Cohort analysis

madhuqliklondon — Tue, 15 Aug 2017 20:36:49 GMT

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.

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 08:51:43 GMT

Any thoughts please??

Re: Cohort analysis

sunny_talwar — Wed, 16 Aug 2017 11:26:43 GMT

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

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 12:15:12 GMT

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.

Re: Cohort analysis

sunny_talwar — Wed, 16 Aug 2017 12:27:24 GMT

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

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 12:37:52 GMT

Yes Sunny.

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 13:02:18 GMT

Hi Sunny , I have used that dimension for new customer count ,I have 2 issues , in the fig 1 : I have correct number but want to track customers orders though out till now. for that I am selecting years 2014 to 2017 , see inthe fig 2 , it is accumulating all years new customers and showing repeats in all years .

Re: Cohort analysis

sunny_talwar — Wed, 16 Aug 2017 13:23:33 GMT

Don't think I understand any of what you are saying. May be someone else can offer help here.... Apologize for the inconvenience I have caused thus far

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 13:38:55 GMT

May be i am not giving you enough explanation

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 13:59:05 GMT

Please check this qvw,

In pivot table, Q1-2012 , new customer count should be 2 only, their order values should be Q1 £80 and Q2 its £50. hope this make sense.

Re: Cohort analysis

vishsaggi — Wed, 16 Aug 2017 15:52:35 GMT

How did you know Q2 should be 50? I see 100 for Q2? Can you explain a little please?

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 18:10:14 GMT

Hi Vish,

For customerid 3 and 4 in q2 they have purchased for 50, with respective order numbers A8 and A9.

Thank you

Re: Cohort analysis

vishsaggi — Wed, 16 Aug 2017 18:43:29 GMT

I think some of the data is missing in your qvw file. I cant see Ordernumber A8 and A9 for Q2 for customerID's 3 and 4. Can you look into that please...?

Re: Cohort analysis

madhuqliklondon — Wed, 16 Aug 2017 20:09:36 GMT

Hi Vish,

Please see the atatchment .Is it not there in the qvw?? I think the confusion is in the first order date and order date. I want group customers on first order date and calculate their sales on order dates. As 3 and 4 customers first order dates are in Q1 so , want them to be in Q1 group. Now those customers buy something in Q2 that value should come under Q2. Please see the pic. Thank you.

Re: Cohort analysis

vishsaggi — Wed, 16 Aug 2017 20:34:33 GMT

Are you looking for this?

File attached.