Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i´m facing a Problem:
Let´s say i have following Data:
EventDate | SignUpDate | UserId | CountSessions |
---|---|---|---|
01.01.2015 | 01.01.2015 | 1 | 1 |
01.01.2015 | 01.01.2015 | 2 | 1 |
02.01.2015 | 01.01.2015 | 1 | 1 |
03.01.2015 | 01.01.2015 | 2 | 1 |
.... | .... | .... |
with a lot more users and entries in a bigger timerange
what i´m trying to achieve is a cohort analysis, to check for every month if a user has still entries for "CountSessions"
So the Result should be like:
TimeRange | Jan-2015 Count(UserId with "CountSessions") | Feb-2015 Count(UserId with "CountSessions") | Mar-2015 Count(UserId with "CountSessions") | Apr-2015 Count(UserId with "CountSessions") |
---|---|---|---|---|
Jan-2015 SignUp Users | 500 | 450 | 300 | 250 |
Feb-2015 SignUp Users | - | 550 | 450 | 380 |
Mar-2015 SignUp Users | - | - | 350 | 300 |
... | ... | ... | ... | ... |
So I want to have a Chart where i can See for a given Timerange how many Users, who signed up in a given Month (e.g. Jan-2015 = 500) and how many Users of these have still Session Entries in the following Months.
So for this example the Result is: 50% of the Users who signed in Jan-2015 are still active in April-2015 (250 User/500 User).
I hope somebody can help me
Cheers
Steve
Plz see attachment.
Hi!
You can use something like this:
1. create new table with UserID and SingUpDate.
2. remove field SignUpDate from your table
3. add pivot table with dimentions MonthName(EventDate), MonthName(SingUpDate) and expression count(distinct UserID)
4. press on expression and write
if( count(distinct UserID)/count({$<SignUpDate=>} distinct UserID)>=0.5, red())
Hi Sergey,
thank you for your fast reply.
I only used the color to highlight my issue.
I tried it and it works finde but now i have another question:
i only want to count Users for a given Month who have at least 1 Session in this Month and the following 2 Months
So, for
Jan: if(user has at least one Session in Jan, Feb, Mar, then count him)
Feb: if(user has at least one Session in Feb,Mar,Apr, then count him)
Thank you
And I only want to count the users for one Row who hat their SignUp in this Month.
e.g. In Row "Jan-2015 SignUp Users": COUNT only the Users for each EventsMonth Column who have the SignUp in Jan
I don't understand your task completely. But...
t1:
load distinct
UserID,
SignUpDate,
1 As MonthCnt
resident Data;
concatenate (t1)
load distinct
UserID,
SignUpDate,
1 As MonthCnt
resident Data
Where MonthStart(AddMonth(EventDate,-1)=MonthStart(SignUpDate);
concatenate (t1)
load distinct
UserID,
SignUpDate,
1 As MonthCnt
resident Data
Where MonthStart(AddMonth(EventDate,-1)=MonthStart(SignUpDate);
t2:
load
UserID,
SignUpDate
where MonthCnt=3;
load
UserID,
SignUpDate,
sum(MonthCnt) as MonthCnt
resident t1
group by
UserID,
SignUpDate;
drop table t1;
HI Sergey,
i try to explain: Let´s say we have two users
user1 has SignUp on 01.01.2015
user2 has SignUp on 01.02.2015
assuming Users have Sessions in all Months
So Result should be like:
TimeRange | EventMonth Jan-2015 | EventMonth Feb-2015 |
---|---|---|
SignUp Jan-2015 | 1 | 1 |
SignUp Feb-2015 | 1 | 1 |
So User1 must not be counted in Row "SignUp Feb-2015" and User2 must not be counted in Row "SignUp Jan-2015".
I hope it makes it a bit clearer
Plz see attachment.
thank you for your Help
Hii Sergey I also want help from you for Cohort Analysis...
PFA link of raw data and output in image..Please help me out with its logic..want output as following image..
Thanks in Advanced.