Skip to content

Instantly share code, notes, and snippets.

@LeoMcA

LeoMcA/report.md Secret

Last active Mar 26, 2021
Embed
What would you like to do?
  • 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
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
)
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment