% Returning users

[#1]

Hello everyone,

Does anyone know if there's an option to show the % of returning customers from 2 periods.

I have a period that is from today - 28 days ago and a period that's from 29 days ago - 56 days ago.

I want to compare all users that have been online between these times and see if the users that were online 29 days to 56 days ago, also were online between today and 28 days ago.

Thanks in advance,

Ivo

[adamdavi3s]

Hi Ivo,

This is certainly possible, can you give us some sample data so we can provide a relevant solution?

[#3]

Im not allowed to share the data i'm working with, due to secrecy. However, I can make some fake data and send that to you.

[rahulpawarb]

Hello Ivo,

Assuming below data table I have created two different straight tables with UserId & DateOnline as dimensions and different expressions for each table to list users online status during said time frame.

OnlineData:
LOAD * INLINE [
UserId, DateOnline
U0001, 02/01/2017
U0002, 02/02/2017
U0003, 02/03/2017
U0004, 02/04/2017
U0005, 02/05/2017
U0006, 02/06/2017
U0001, 02/11/2017
U0002, 02/12/2017
U0007, 03/03/2017
U0008, 03/04/2017
U0009, 03/05/2017
U0006, 02/16/2017
];

Expression for Online Between Today - 28 Days:

If(Today()-Date#(DateOnline, 'MM/DD/YYYY') <=28, 'Online')

Expression for Online Between 29 - 56 Days:

If(Today()-Date#(DateOnline, 'MM/DD/YYYY') >=29 AND Today()-Date#(DateOnline, 'MM/DD/YYYY') <=56, 'Online')

Please refer the attached sample application.

As an alternative, you can create two different resident load statements for Online Between Today - 28 Days and Online Between 29 - 56 Days; use these data sets in two different table boxes.

Hope this will help.

Regards!

Rahul

[adamdavi3s]

Hi Ivo,

Fake would be fine, its just to get an idea of what your data looks like so we can provide an answer that is relevant!

[#6]

Here is a fake data file I made, its a simple few users that have been online in the past 29 - 56 days and some of them were online in the last 28 days as well. As an extra info point, I added 3 users who weren't online on either of the two periods.

Thanks again for your help!

[adamdavi3s]

Hi Ivo,

I just used rahulpawarb‌'s very helpful example as dummy data.

The formula required in this example is:

sum({<DateOnline={">$(=today()-28)"}, UserId=p({<DateOnline={"<=$(=today()-29) >=$(=today()-56)"}>}UserId)>}1)

The important part of this is the P() as we're basically saying for our 28 day group, then restrict it for users who appear int he 29-56 day group as well.

[adamdavi3s]

Ok so in this case it is super simple, you can just use:

=num(sum(Aggr(if(sum(Date_IsLast28Days) >0 AND sum(Date_IsLast28to56Days)>0,1,0),User_Key)) / count(total User_Key),'0.00%')

That is assuming you want the percentage of all users?

[adamdavi3s]

This would get you what you want I think, so % of users who were onluine >56 and > 28

=num(sum(Aggr(if(sum(Date_IsLast28Days) >0 AND sum(Date_IsLast28to56Days)>0,1,0),User_Key)) / sum(Aggr(if(sum(Date_IsLast28Days) >0 OR sum(Date_IsLast28to56Days)>0,1,0),User_Key)),'0.00%')

[#10 · Accepted solution]

Thanks for all the help. I tried to use the sample data and solution, but was not able to create a text box expression with it resulting in a single percentage shown. However, I tried to use a Set Analysis to compare two table and count the output.

Set Analysis:

=Round(sum({1<Date_IsLast28Days= {1}>*<Date_IsLast28to56Days= {1}>} #Debtors)/ Count({< Date_IsLast28to56Days = {1}>} DISTINCT Debtor)*100)&'%'

The Date_IsLast28to56Days and Date_IsLast28Days refer to a dimension noted as:

,	If(Today() - Date <= 28 And  Today() - Date >= 0, 1, 0) As Date_IsLast28Days

,	If(Today() - Date <= 56 And  Today() - Date >= 0, 1, 0) As Date_IsLast56Days

and in Preceding Load:

,	If(Date_IsLast28Days = 0 and Date_IsLast56Days = 1, 1, 0) As Date_IsLast28to56Days

Thanks for all the help again!