Get active users

[jdauphant]

Analysis

• [ ] Defines an active user : a user is connected once a month
• [x] Find where the datas are stored to find active user : user_daily_visits has been retained
  • [ ] Check monthly_active_users
  • [x] Check user_daily_visits

Implementation

• [x] Export db-synapse to S3 storage (csv format)
  • [x] Extract data from user_daily_visits (or monthly_active_users) table into user_daily_visits_view: an aggregated view without sensitive data
  • [x] Extract data from user_daily_visits_view view into csv file user_daily_visits-<YYYY-MM-dd>.csv
  • [x] Send user_daily_visits-<YYYY-MM-dd>.csv to S3 storage
• [x] Import csv file from S3 to metabase db (new table)
  • [x] Create a new table user_daily_visits in metabase-db(scalingo) : schema has to be defined according to user_daily_visits-<YYYY-MM-dd>.csv
  • [x] Build a script to import csv file from S3 into user_daily_visits table
  • [x] Schedule the script via a cron : run at 5am every day
• [ ] Re-format data in metabase (build the metric): To be define
  • [ ] Build a query/graph to get the active users by day for a given a month -- this is suggestion
  • [ ] Build a query to get the active users for the past month/past 30 days -- this is suggestion

[jdauphant]

Montly active users

[jdauphant]

https://github.com/matrix-org/synapse/blob/25f43faa70f7cc58493b636c2702ae63395779dc/synapse/storage/schema/main/delta/51/monthly_active_users.sql

[jdauphant]

https://github.com/matrix-org/synapse/blob/25f43faa70f7cc58493b636c2702ae63395779dc/synapse/storage/schema/main/delta/49/add_user_daily_visits.sql