Backfill Community Health Score data

[flodolo]

We should have all the data in monthly spreadsheets, we should be able to convert it and backfill the DB.

[flodolo]

I put a couple of scripts here: https://gist.github.com/flodolo/504acd57d0cd6b99fab4e37df560cbd8

One extracts the data from spreadsheets (you can download the full folder, it will convert them to .xlsx) and store it as JSON.

The other is to be run in Django Shell, copying and pasting the JSON generated in the first step. Since the sheets have 1 decimal, the score is recalculated.

[flodolo]

I have the full script (slightly tweaked by Jamie) with data for the last 12 months. Spot-checked a couple of locales, and it looks good 👍🏼
https://gist.github.com/flodolo/c2d86dd415ef78de163a750502896adc

I tested it locally, but we need to update DEV before testing it there (because of #4277).

[flodolo]

Content looks good on DEV.

Script to recompute CHS scores

from pontoon.insights.chs import compute_chs
from pontoon.insights.models import LocaleHealthSnapshot

# Raw metric fields stored on the snapshot, fed into compute_chs().
METRIC_FIELDS = [
    "completion",
    "key_projects_enabled",
    "active_managers",
    "active_translators",
    "active_contributors",
    "all_contributors",
    "new_signups",
]
# Derived fields returned by compute_chs() that we overwrite.
SCORE_FIELDS = [
    "completion_score",
    "key_projects_enabled_score",
    "active_managers_score",
    "active_translators_score",
    "active_contributors_score",
    "all_contributors_score",
    "new_signups_score",
    "chs",
]
snapshots = list(LocaleHealthSnapshot.objects.all())
changed = 0

for snapshot in snapshots:
    args = {field: getattr(snapshot, field) for field in METRIC_FIELDS}
    # completion is a Decimal from the DB; compute_chs multiplies it by float
    # point constants, so cast to float to avoid Decimal * float TypeError.
    args["completion"] = float(args["completion"])
    # Capture the previous chs (Decimal from the DB) before overwriting it.
    old_chs = snapshot.chs
    for field, value in compute_chs(args).items():
        setattr(snapshot, field, value)
    # snapshot.chs is now a float; round to compare against the stored Decimal.
    if round(float(old_chs), 2) != round(snapshot.chs, 2):
        changed += 1
        print(
            f"{snapshot.locale.code} @ {snapshot.created_at}: "
            f"{old_chs} -> {snapshot.chs}"
        )

LocaleHealthSnapshot.objects.bulk_update(snapshots, SCORE_FIELDS, batch_size=1000)
print(
    f"Recalculated scores for {len(snapshots)} snapshot(s); "
    f"{changed} had a different chs."
)

[flodolo]

Data is back-filled also on PROD.

Waiting for #4277 to be deployed on prod to confirm the scores look OK.