Calculating Average Time Between First/Last Login

CoryFair

Hi,

 

I have a table of user logins that has user id in one column and the date they logged in in another. I'm trying to calculate how long users typically login before they become inactive. Ideally, I'd be able to map number of months on one access and number of users on the other.

 

What type of operations would I need to achieve this? I know I'd need to return the first and last login for each distinct user, do a time differential (as a whole month) and then count how many users fall under each.

 

Input Table:

 

Output:

RGranada

Hi,

 

If I correctly understood your problem, here's the approach I would take. 

 

It involves creating a MySql Dataflow like this (you can use this directly on the output if you do not have other transformations implied):

 

SELECT i4w_userid,Max(i4w_date) as First_Login_Date,Min(i4w_date) as Last_Login_Date, TIMESTAMPDIFF(MONTH,Min(i4w_date),Max(i4w_date)) AS Month_Diferential, Count(i4w_userid) AS Number_of_Logins FROM your_dataset_name
GROUP BY i4w_userid

 

This will give the necessary fields and more some to achieve your results.

 

Then set up a card like the following:

 

Don't forget to set the calculation of i4w_userid to "Count".

 

Hope this helps. Let me know if you need some more tweaks on this.