Pontoon: time to translate/review

time_to_translation.py

from pontoon.base.models import Project, Locale, Entity, Translation
from django.db.models import F
from datetime import datetime, timezone

project_slug = "firefox"
# Top 15 desktop locales based on DAU, excluding en-*
locale_codes = ["de", "fr", "es-ES", "ru", "pl", "pt-BR", "it", "zh-CN", "ja", "es-MX", "nl", "id", "cs", "hu", "es-AR"]
start_date = datetime(2026, 1, 1, tzinfo=timezone.utc)
end_date = datetime(2026, 12, 31, 23, 59, 59, tzinfo=timezone.utc)

project = Project.objects.get(slug=project_slug)
entities = Entity.objects.filter(
    resource__project=project,
    date_created__gte=start_date,
    date_created__lte=end_date,
    obsolete=False,
)
entity_ids = list(entities.values_list("id", flat=True))
total_entities = entities.count()
sample = (
    Entity.objects.filter(resource__project=project)
    .order_by("-date_created")
    .values("id", "date_created")
    .first()
)


def compute_stats(times):
    if not times:
        return "", "", ""
    avg_h = round(sum(times) / len(times), 2)
    min_h = round(min(times), 2)
    max_h = round(max(times), 2)
    return avg_h, min_h, max_h


header = ",".join(
    [
        "locale",
        "entities_created",
        "entities_without_translation",
        "pretranslations_waiting_review",
        "avg_submission_d",
        "min_submission_d",
        "max_submission_d",
        "avg_review_d",
        "min_review_d",
        "max_review_d",
    ]
)
rows = [header]

for locale_code in locale_codes:
    locale = Locale.objects.get(code=locale_code)
    entities_with_translation = (
        Translation.objects.filter(
            entity_id__in=entity_ids, locale=locale, approved=True
        )
        .values("entity_id")
        .distinct()
        .count()
    )
    entities_without = total_entities - entities_with_translation
    pretranslations_waiting = Translation.objects.filter(
        entity_id__in=entity_ids,
        locale=locale,
        pretranslated=True,
        approved=False,
        rejected=False,
    ).count()
    # Case 1: human submitted and self-approved → time from entity creation to submission
    human_submitted = (
        Translation.objects.filter(
            entity_id__in=entity_ids,
            locale=locale,
            approved=True,
            user__isnull=False,
            approved_user__isnull=False,
        )
        .filter(user=F("approved_user"))
        .select_related("entity")
    )
    submit_times = [
        (t.date - t.entity.date_created).total_seconds() / 86400
        for t in human_submitted
        if t.date >= t.entity.date_created
    ]
    avg_sub, min_sub, max_sub = compute_stats(submit_times)
    # Case 1: human suggested or pretranslated + approved by different user → time from suggestion to approval
    peer_reviewed = Translation.objects.filter(
        entity_id__in=entity_ids,
        locale=locale,
        approved=True,
        user__isnull=False,
        approved_user__isnull=False,
        approved_date__isnull=False,
    ).exclude(user=F("approved_user"))
    review_times = [
        (t.approved_date - t.date).total_seconds() / 86400
        for t in peer_reviewed
        if t.approved_date >= t.date
    ]
    avg_rev, min_rev, max_rev = compute_stats(review_times)
    rows.append(
        f"{locale_code},{total_entities},{entities_without},{pretranslations_waiting},{avg_sub},{min_sub},{max_sub},{avg_rev},{min_rev},{max_rev}"
    )

print("\n".join(rows))