[Self-Hosted] Track activated users

[lucasvaltl]

Is your feature request related to a problem? Please describe

To better understand the utilisation of Gitpod within a specific installation (a.k.ka. activation rate), we want to enhance the current telemetry we send with the amount of activated users. This metric is defined as the number of users that have started a workspace in at least 3 of the last 7 days.

Describe the behaviour you'd like

• Daily telemetry dump includes a total count of actiated users (as defined above). We are NOT interested in specific users, and especially not in personally identifiable info from a user. Just a total number
• This is well documented and clarified to the user: It is shown in the telemetry preview in the admin dashboard (should happen by default but worth checking)
• Need to ensure that there is no impact on performance due to the query

Describe alternatives you've considered

• We could run the following query against the DB and add the result to the telemetry:

SELECT COUNT(DISTINCT(ws.ownerId))
FROM d_b_workspace AS ws
WHERE ws.id IN (
   SELECT wsi.workspaceId
   FROM d_b_workspace_instance AS wsi
   WHERE wsi.creationTime within last 7 days
   GROUP BY wsi.workspaceId
   HAVING COUNT(wsi.id) > 3
)

Additional context

• See internal slack thread
• Expected implementation time: 1 Day

[lucasvaltl]

@Pothulapati I know that you were going to look into this, did you make any progress?

[Pothulapati]

@lucasvaltl Not yet! Haven't spent anytime at all on this.

Unassigning hence, and will assign me back once I start working to prevent confusion.

[Pothulapati]

Started working on this, and hence assigned myself

[Pothulapati]

@lucasvaltl The query that you mentioned seems to calculate users who created atleast 3 workspace instances of a single workspace in the last 7 days i.e they could all be on the same day, and not users who created workspace instances in 3 days of the last 7 days.

I'm trying for the latter now, Feel free to tell me otherwise if that's not what you need.

[jakobhero]

The query that you mentioned seems to calculate users who created atleast 3 workspace instances of a single workspace in the last 7 days i.e they could all be on the same day, and not users who created workspace instances in 3 days of the last 7 days.

i've had a look and agree, this query should do the trick instead:

select
    count(userId) as activatedUsers
from (
    select
        u.userId
    from d_b_workspace_instance i
    join d_b_workspace_instance_user u
    on i.id = u.instanceId
    and i.startedTime between date_sub(current_date,interval 7 day) and current_date()
    group by 1
    having count(distinct date(i.startedTime)) >= 3
)

let me know if there are other details that you would like to discuss about this @Pothulapati , happy to jump on a quick call anytime!🏀