Generate organization usage stats from LMS

[marcospri]

Current situation

The report system is not prepared to deal with arbitrary contract dates and modifying that would be a fundamental architecture change.

As a stop gap measure we allow now to generate usage reports for an org between two any arbitrary dates. It's up to the user of the admin pages now to use the adecute dates here and copy over the information to HubSpot.

Proposal for next version

• Fetch contract dates (deals) in LMS and store them in the DB.

An organization might have multiple deals, current and past, we'll store all of them in the DB fetching them from HS every day/week.

• Generate usage reports for every organization as a background task in LMS.

Based on the contract dates generate monthly reports for ongoing deals.

This usage reports do take into account sub-organizations. Generating the report for all organizations guarantees we have the data for both the parent orgs and their children.

Generating the reports is an expensive operation so we should rate limit the process. If this is a monthly process it shouldn't be a problem that, worst case scenario, all the report takes a couple of days to be generated.

• Store the results of the reports in the DB

Store both the count and the actual report (ie a list of rows) in the DB. Having the counts will allow to create graphs comparing deals/dates/periods trivially in metabase. Storing the full report allow to fetch them repeatedly without the cost of generating it.

• Write the unique count of users back to HS

Once the reports are generated write the result (just the count of unique users) in HS.

[dwhly]

PRD:
https://docs.google.com/document/d/1jjqALVaqkLsN4Qfz0MhW7HOKV_TswU0VYk66gwVavVo/edit#heading=h.6ivhz3247mk9

[marcospri]

Related: hypothesis/product-backlog#1536

[marcospri]

There's now reports generated with the new process this can be accessed from this report:

• https://report.hypothes.is/dashboard/29-organization-usage

Little demo:

simplescreenrecorder-2024-06-18_15.05.22.mp4

What left for this to be closed:

• Complete the backfill of a year's worth of data. In progress.
• Include the unique_teachers metric for all reports. In progress.
• Create a Canadian version of the dashboard.
• Schedule monthly task to generate the report (now the backfill process runs too often)

Known issues:

• We won't generate any reports for deals that start before 2023. We currently don't generate any reports for these even if the end date is post-2023. We can't include the full lenght of the contract.

• We don't generate reports for companies that don't have deals in hubspot. For example the top level CUNY system doesn't have one us.lms.org.ipOw3D7bQheQOqc1AMJCZg.

• We don't generate reports for disabled companies (but we could backfill it if would be useful.

What I have not done in this stream of work:

• Calculate "active users" alongside billable users.