Create an organization activity report in a single region

[jon-betts]

We should create a report which shows various details about organizations and allows you to sort by a number of different conditions. This will prove many of the core data competencies we will need.

This report can start life as a table with the following columns:

• Organization public id
• Name
• Date / semester
• Active teachers
• Active user
• Total annotations

We should be able to group this table by:

• Semester
• Bonus points! - If we can do it by more than one thing in a configurable way, that's cool

We should be able to filter by:

• Organization public id

Other interactivity:

• Bonus points! - If you can "click-through" to more details, that's nice, but not required for this project
• With this in mind we may want to investigate what Metabase models can and can't do for us

Definition of "active"

In the project document active is defined as:

ACTIVE IS SEEN AS LOGGING IN

However in different discussions there has been a desire to have historical data which would rule this definition out. The existing definition of active is:

• A user is active, if they are in a group which has at least one annotation in the given period

One posibility for sliding from one situation to the other is to attempt to count a user as active if:

• They have logged in in the period in question
• or they are in a group with one annotation in the period

Questions we can answer with this report

If we have this report, we will have proven the capability to answer the following questions in region:

• (1) Active teachers per semester - (apply filter to single organization id, set time period to semester)
• (2) Active users per semester - (apply filter to single organization id, set time period to semester)
• (3) Active institutions per semester - (set time period to semester, sort by active users desc)
• (4) Top annotating institutions - (set time period to semester, sort by annotations desc)

Once this data is aggregated to a higher level we should be able to answer (3) and (4) in a global context, but that is for:

• https://github.com/hypothesis/lms/issues/4517

Implementation notes

• As this requires annotation counts we will almost certainly need roll up tables of annotation counts by group by time-period
• Our events table isn't very fast either and could do with a roll up - This might be a good place to start as it's simpler

Tasks

• [ ] Decide on naming scheme and general Metabase configuration
• [ ] Create the correct Metabase instance to host the regional report for the US
• [ ] Create required aggregates and views
• [ ] Create a method to keep the aggregates and views up to date
• [ ] Create the report in this Metabase