User Retention Query

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- How many recently active users come back to the site after their initial account creation?

 -----

 -- User input:  How many days between creation and last activity counts as "retained"?

DECLARE @RetentionDays int = ##RetentionDays##

 -- User input:  How far back do we want to search (in days)?
 
DECLARE @ReportingPeriodDays int = ##ReportingPeriodDays##
 
 -- Get the count of users who were active N or more days after creation, and were active in the reporting period
 -- (N defined in RetentionDays)
 -- (reporting period defined in ReportingPeriodDays)

DECLARE @Returning_Users float

SELECT @Returning_Users = Count(Id)
FROM Users
WHERE DATEDIFF(day,CreationDate,LastAccessDate) > @RetentionDays AND DATEDIFF(day,LastAccessDate,GetDate()) <= @ReportingPeriodDays;

 -- Get the count of users who were active in the reporting period
 -- (reporting period defined in ReportingPeriodDays)

DECLARE @Total_Users float

SELECT @Total_Users = Count(Id)
From Users
WHERE DATEDIFF(day,LastAccessDate,GetDate()) <= @ReportingPeriodDays;

 -- Compute the proportion of total users who were "retained"

SELECT @Returning_Users / @Total_Users

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results