About retention rate

[#1]

Hi all,

I was wondering how to calculate retention rate:

I load the raw data into QV:

OrderId MemberId Amount

1 1 $30

2 1 $35

3 2 $30

4 2 $30

5 3 $11

I want to use an object to show how many people buy 3 times, 2 times, 1 times and so on.

In this sample, the results should be:

2 times: 2

1 times: 1

How can I achieve it and I should use which object to show?

Thanks.

[Miguel_Angel_Baeyens]

Hello Isaac,

Take a look at the code posted here to see if that helps.

Regards.

[#3]

Hello Isaac,

I would use a (bar-) chart with MemberId as Dimension. The expression is similar to:

rank(Count(OrderId))

Default sort-order is ascedning, You can order it descending if you like.

Regards, Roland

[#4]

Hi Miguel,

I answered in parallel. Seems you are (sometimes) a little bit faster.

Nevertheless I tested your link and my browser failed with an object refernce error. Would you validate your address?

Kindly regards, Roland

[Miguel_Angel_Baeyens]

Hello Roland,

Thanks for noting. I've deleted the link and added it again and it seems it's working now. Your solution may be easier, as I'm suggesting to create that field in the script.

Thanks again and Regards.

[#6]

Hi Roland,

Thanks for you answer, but I don't want to show specific MemberId as the dimension, the dimension should be "Times":

charged 1 times, 2 times,3 times, and the corresponding value is 1, 2, 0.

So can I create a custom dimension for Times?

Thanks.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Check something like the following in the script

OriginalData:LOAD * INLINE [OrderId, MemberId, Amount1, 1, $302, 1, $353, 2, $304, 2, $305, 3, $11]; RankedData:LOAD OrderId, MemberId, Amount, IF(MemberId = PREVIOUS(MemberId), RANGESUM(PEEK('Times'), 1), 1) AS TimesRESIDENT OriginalDataORDER BY MemberId; DROP TABLE OriginalData;

Now you have the "Times" field you can use as dimension. Playing a bit with the code will display more accurate results.

Hope that helps

[#8]

Hi,

If you want to do this in chart, here is the solution (see the file attached):

1. Load data

2. Add Calculated dimension :

=

Aggr(Count(OrderId), MemberId)

3. Add Expression:

=Count(distinct MemberId)

But be careful - if there is a lot of data - use script version!

Kind regards,

Miha

[#9]

Oh dear Miha,

Many thanks for your expression, it works well for me.

And also thanks the script of Miguel Angel Baeyens, it also helps me solve another question.

Thanks for your every experts!

Isaac Li