https://github.com/mozilla/sumo-project/issues/811

report.md

• Total users/contributors (all time):
  • users: 536
  • contributors (user with a response or moderation): 222
• Total replies made (Since July 28, 2020): 21497
• Total moderation (Since July 28, 2020): 23508
• Total replies approved and published (Since July 28, 2020): 14484
• Total review replied (approved and published) per month from July 2020-Feb 2020 (breakdown per locale):

Month	Language	Count
2020-07	en	187
2020-07	fr	2
2020-07	id	2
2020-07	es	2
2020-08	en	6709
2020-08	fr	1225
2020-08	id	36
2020-08	es	13
2020-08	it	5
2020-08	zh-Hans	1
2020-08	cs	1
2020-08	de	1
2020-08	pt	1
2020-08	ar	1
2020-09	en	3273
2020-09	fr	899
2020-09	it	1
2020-10	fr	233
2020-10	en	120
2020-11	fr	248
2020-11	en	158
2020-12	fr	151
2020-12	en	142
2021-01	en	675
2021-01	fr	138
2021-02	en	130
2021-02	fr	73

report.py

from datetime import datetime, timezone, timedelta
from mresponse.responses.models import Response
from mresponse.reviews.models import Review
from mresponse.moderations.models import Moderation
from django.contrib.auth import get_user_model
from django.db.models import Count, Q, F
from django.db.models.functions import TruncMonth

User = get_user_model()

start = datetime(2020, 7, 28, 0, tzinfo=timezone.utc)

reviews = Review.objects.filter(created_on__gte=start)
users = User.objects.annotate(
    responses_count_all_time=Count("responses", distinct=True),
    moderations_count_all_time=Count("moderations", distinct=True),
)

# reviews replied to since 2020-07-28: 14484
reviews.filter(responses__submitted_to_play_store=True).order_by().distinct("pk").count()

# contribtors who have replied since 2020-07-28: 99
Response.objects.filter(submitted_to_play_store=True, review__created_on__gte=start).distinct("author_id").count()

# total users all time: 536
User.objects.all().count()

# total contributors all time (user with a response or moderation): 222
users.filter(Q(responses_count_all_time__gte=1) | Q(moderations_count_all_time__gte=1)).count()

# total replies since 2020-07-28: 21497
Response.objects.filter(review__created_on__gte=start).count()

# total moderations since 2020-07-28: 23508
Moderation.objects.filter(response__review__created_on__gte=start).values("response_id", "moderator_id").distinct().count()

# total replies approved and published since 2020-07-28: 14484
Response.objects.filter(submitted_to_play_store=True, review__created_on__gte=start).distinct("review").count()

# total reviews replied to per month by locale:
reviews_per_month_per_locale = (
    Review.objects
    .filter(
        created_on__gte=datetime(2020, 7, 1),
        created_on__lt=datetime(2021, 3, 1),
        responses__submitted_to_play_store=True,
    )
    .order_by()
    .annotate(month=TruncMonth("created_on"))
    .values("month", "review_language")
    .annotate(count=Count("pk", distinct=True))
    .values_list("month", "review_language", "count")
    .order_by("month", "-count")
)
print(
    "| Month | Language | Count |\n| - | - | - |\n| " + " |\n| ".join(
        map(
            lambda x: " | ".join(str(y) for y in (x[0].strftime("%Y-%m"),) + x[1:]),
            reviews_per_month_per_locale
        )
    ) + " |"
)

# top contributors:
top_contributors = (
    User.objects
    .annotate(
        responses_count=Count("responses", distinct=True),
        published_responses_count=Count("responses", filter=Q(responses__submitted_to_play_store=True), distinct=True),
        moderations_count=Count("moderations", filter=Q(~Q(moderations__response__author_id=F("pk"))), distinct=True),
        total=F("responses_count") + F("moderations_count"),
    )
    .values_list(
        "email", "responses_count", "published_responses_count", "moderations_count", "total"
    )
    .order_by("-total")
    [:25]
)
print(
    "email,responses,published responses,moderations,total (responses + moderations)\n" + "\n".join(
        map(
            lambda x: ",".join(str(y) for y in x),
            top_contributors
        )
    )
)