Time to translate (Pontoon)

translation_time_report.py

# ============================================================
# Time-to-translate report for Firefox products.
# Run in Django shell:  make shell  ->  ./manage.py shell   then paste.
# Prints CSV to stdout (copy-paste into a spreadsheet).
# ============================================================
import csv
import io
import sys
from datetime import datetime, timezone as dt_timezone

from django.contrib.auth.models import User
from pontoon.base.models import Project, Entity, Translation
from pontoon.actionlog.models import ActionLog

# ---- Config (customize) ----
START_DATE = datetime(2026, 1, 1, tzinfo=dt_timezone.utc)
PROJECT_NAMES = ["Firefox", "Firefox for Android", "Firefox for iOS"]
BOT_EMAILS = ["pontoon-tm@example.com", "pontoon-gt@example.com"]
# Date the implicit `translation:approved` ActionLog action shipped (PR #4316).
# Since then, a translation submitted directly as approved also records an
# (implicit) approval action, so its approval history survives even if it is
# later rejected. Before this date, a born-approved-then-rejected direct
# translation leaves no approval trace and is unrecoverable from the DB.
# Adjust if production deployment differs from the code-merge date.
IMPLICIT_APPROVAL_SINCE = datetime(2026, 7, 23, tzinfo=dt_timezone.utc)

def main():
    bot_ids = set(
        User.objects.filter(email__in=BOT_EMAILS).values_list("id", flat=True)
    )
    output = io.StringIO()
    w = csv.writer(output)
    w.writerow([
        "Project", "Locale code", "Locale name",
        "Strings added", "Words", "Strings translated",
        "% translated", "Avg time to translate (hours)",
    ])
    # Diagnostic: (entity, locale) pairs whose chosen first-accepted translation
    # is preceded by a rejected translation we can't confirm was ever approved.
    # If any of those were born-approved-then-rejected (unrecoverable from the
    # DB), the true time-to-translate could be shorter than reported. Upper bound.
    ambiguous_by_project = {}
    for project_name in PROJECT_NAMES:
        try:
            project = Project.objects.get(name=project_name)
        except Project.DoesNotExist:
            sys.stderr.write("Project not found: %s\n" % project_name)
            continue
        entities = list(
            Entity.objects.filter(
                resource__project=project,
                obsolete=False,
                date_created__gte=START_DATE,
            ).values("id", "string", "date_created")
        )
        entity_ids = [e["id"] for e in entities]
        created = {e["id"]: e["date_created"] for e in entities}
        total_words = sum(len(e["string"].split()) for e in entities)
        strings_added = len(entity_ids)
        # All translations for these entities, across every locale.
        trans = list(
            Translation.objects.filter(entity_id__in=entity_ids).values(
                "id", "entity_id", "locale_id", "user_id", "date",
                "approved", "approved_date", "rejected",
            )
        )
        by_key = {}
        for t in trans:
            by_key.setdefault((t["entity_id"], t["locale_id"]), []).append(t)
        # Approval history per translation, from ActionLog (approved_date is
        # wiped on rejection, so the row alone can't tell a once-approved
        # translation from a suggestion). Two derived sets:
        #   * ever_approved: any translation ever approved, whether by review or
        #     by an implicit self-approval on direct submission (PR #4316).
        #   * approval_ts: earliest *genuine review* approval per translation.
        #     Implicit approvals are stamped at submission time and are not a
        #     review event, so they're excluded here — the bot branch below
        #     uses this to measure time-to-review, not time-to-submit.
        approval_ts = {}
        ever_approved = set()
        for a in ActionLog.objects.filter(
            action_type=ActionLog.ActionType.TRANSLATION_APPROVED,
            translation__entity_id__in=entity_ids,
        ).values("translation_id", "created_at", "is_implicit_action"):
            tid, ts = a["translation_id"], a["created_at"]
            if tid is None:
                continue
            ever_approved.add(tid)
            if a["is_implicit_action"]:
                continue
            if tid not in approval_ts or ts < approval_ts[tid]:
                approval_ts[tid] = ts
        ever_approved |= {t["id"] for t in trans if t["approved"]}  # safety net
        ambiguous = 0
        for locale in project.locales.all().order_by("code"):
            lid = locale.id
            translated = 0
            times = []  # seconds
            for eid in entity_ids:
                tlist = by_key.get((eid, lid))
                if not tlist:
                    continue
                if not any(t["approved"] for t in tlist):
                    continue  # not currently translated
                translated += 1
                # First translation ever accepted (suggestions excluded).
                candidates = [t for t in tlist if t["id"] in ever_approved]
                if not candidates:
                    continue
                first = min(candidates, key=lambda t: t["date"])
                # Flag if an earlier rejected translation can't be confirmed as
                # a past acceptance -> real first acceptance might predate
                # `first`. Only translations submitted before implicit approval
                # tracking are affected: since then, a directly-approved
                # translation always leaves an approval action, so an earlier
                # rejection missing from `ever_approved` was genuinely never
                # approved rather than a wiped acceptance.
                if any(
                    t["rejected"]
                    and t["id"] not in ever_approved
                    and t["date"] < first["date"]
                    and t["date"] < IMPLICIT_APPROVAL_SINCE
                    for t in tlist
                ):
                    ambiguous += 1
                start = created[eid]
                if first["user_id"] in bot_ids:
                    # pretranslation -> time to review (approval), not submission
                    end = approval_ts.get(first["id"]) or first["approved_date"]
                else:
                    end = first["date"]  # human submission of first accepted
                if end is None:
                    continue
                delta = (end - start).total_seconds()
                if delta < 0:
                    continue  # guard against sync/clock anomalies
                times.append(delta)
            pct = (translated / strings_added * 100) if strings_added else 0
            avg_hours = (sum(times) / len(times) / 3600) if times else None
            w.writerow([
                project_name, locale.code, locale.name,
                strings_added, total_words, translated,
                "%.1f" % pct,
                "%.2f" % avg_hours if avg_hours is not None else "",
            ])
        ambiguous_by_project[project_name] = ambiguous
    # Diagnostic summary to stderr so it stays out of the copy-pasted CSV.
    total_ambiguous = sum(ambiguous_by_project.values())
    sys.stderr.write(
        "\nDiagnostic: %d (entity, locale) pair(s) with an earlier rejected "
        "translation (submitted before %s) that could not be confirmed as a "
        "past acceptance.\nIf any were born-approved-then-rejected, their "
        "time-to-translate may be overestimated (upper bound on affected "
        "pairs). Translations submitted on/after that date are unaffected: "
        "implicit approval actions make their history recoverable.\n"
        % (total_ambiguous, IMPLICIT_APPROVAL_SINCE.date().isoformat())
    )
    for name in PROJECT_NAMES:
        if name in ambiguous_by_project:
            sys.stderr.write("  %s: %d\n" % (name, ambiguous_by_project[name]))
    # Print the full CSV at the end (avoids interleaving with query/log noise).
    print()
    print(output.getvalue())

main()