Bug 1629968 Comment 1 Edit History

Note: The actual edited comment in the bug view page will always show the original commenter’s name and original timestamp.

Original comment by
on 
I've collected the requested numbers in the spreadsheet:
https://docs.google.com/spreadsheets/d/1kJzAS6cLXUSdaPm7a_oAnG-8zSBHQUbMpsil4rLqIJs/edit?usp=sharing

I used the scripts below.

Let me know if you have more data requests.

```python
# New User Registrations

users = User.objects.all() \
    .annotate(period=TruncMonth("date_joined")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in users:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in translations:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# Active Users

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period", "user_id") \
    .annotate(count=Count("user_id")) \
    .values('period', 'count') \
    .order_by("period")

translations_dict = defaultdict(list)
for x in translations:
    translations_dict[x["period"]].append(x["count"])

for y in translations_dict.items():
    date = y[0]
    count = len(y[1])
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))
```
Revision 1 by
on 
I've collected the requested numbers in the spreadsheet:
https://docs.google.com/spreadsheets/d/1kJzAS6cLXUSdaPm7a_oAnG-8zSBHQUbMpsil4rLqIJs/edit?usp=sharing

I used the scripts below.

Let me know if you have more data requests.

```python
# New User Registrations

users = User.objects.all() \
    .annotate(period=TruncMonth("date_joined")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in users:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in translations:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# Active Users

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period", "user_id") \
    .annotate(count=Count("user_id")) \
    .values('period', 'count') \
    .order_by("period")

translations_dict = defaultdict(list)
for x in translations:
    translations_dict[x["period"]].append(x["count"])

for y in translations_dict.items():
    date = y[0]
    count = len(y[1])
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions per Locale

import datetime
from pontoon.base.models import *
from django.db.models.functions import TruncMonth

locales = Locale.objects.available().order_by("code")
data = {}
for year in range(2017, datetime.datetime.now().year + 1):
    for month in range(1, 13):
        if year == datetime.datetime.now().year and month > datetime.datetime.now().month:
            continue
        data["{}/{}".format(month, year)] = {}

for locale in locales:
    translations = Translation.objects.filter(
        date__gte=datetime.datetime(2017,1,1),
        locale=locale,
        user__isnull=False,
    ) \
        .annotate(period=TruncMonth("date")) \
        .values("period") \
        .annotate(count=Count("id")) \
        .order_by("period")
    for x in translations:
        date = x["period"]
        date = "{}/{}".format(date.month, date.year)
        count = x["count"]
        data[date][locale.code] = count

print("," + ",".join(locales.values_list("code", flat=True)))
for date, values in data.items():
    line = date
    for l in locales:
        line = line + "," + str(values.get(l.code, 0))
    print(line)
```
Revision 2 by
on 
I've collected the requested numbers in the spreadsheet:
https://docs.google.com/spreadsheets/d/1kJzAS6cLXUSdaPm7a_oAnG-8zSBHQUbMpsil4rLqIJs/edit?usp=sharing

I used the scripts below.

Let me know if you have more data requests.

```python
import datetime
from pontoon.base.models import *
from django.db.models.functions import TruncMonth

# New User Registrations

users = User.objects.all() \
    .annotate(period=TruncMonth("date_joined")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in users:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in translations:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# Active Users

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period", "user_id") \
    .annotate(count=Count("user_id")) \
    .values('period', 'count') \
    .order_by("period")

translations_dict = defaultdict(list)
for x in translations:
    translations_dict[x["period"]].append(x["count"])

for y in translations_dict.items():
    date = y[0]
    count = len(y[1])
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions per Locale

locales = Locale.objects.available().order_by("code")
data = {}
for year in range(2017, datetime.datetime.now().year + 1):
    for month in range(1, 13):
        if year == datetime.datetime.now().year and month > datetime.datetime.now().month:
            continue
        data["{}/{}".format(month, year)] = {}

for locale in locales:
    translations = Translation.objects.filter(
        date__gte=datetime.datetime(2017,1,1),
        locale=locale,
        user__isnull=False,
    ) \
        .annotate(period=TruncMonth("date")) \
        .values("period") \
        .annotate(count=Count("id")) \
        .order_by("period")
    for x in translations:
        date = x["period"]
        date = "{}/{}".format(date.month, date.year)
        count = x["count"]
        data[date][locale.code] = count

print("," + ",".join(locales.values_list("code", flat=True)))
for date, values in data.items():
    line = date
    for l in locales:
        line = line + "," + str(values.get(l.code, 0))
    print(line)
```
Revision 3 by
on 
I've collected the requested numbers in the spreadsheet:
https://docs.google.com/spreadsheets/d/1kJzAS6cLXUSdaPm7a_oAnG-8zSBHQUbMpsil4rLqIJs/edit?usp=sharing

I used the scripts below.

Let me know if you have more data requests.

```python
import datetime
from pontoon.base.models import *
from django.db.models.functions import TruncMonth

# New User Registrations

users = User.objects.all() \
    .annotate(period=TruncMonth("date_joined")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in users:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in translations:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Entities created

entities = Entity.objects \
    .annotate(period=TruncMonth("date_created")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in entities:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# Active Users

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period", "user_id") \
    .annotate(count=Count("user_id")) \
    .values('period', 'count') \
    .order_by("period")

translations_dict = defaultdict(list)
for x in translations:
    translations_dict[x["period"]].append(x["count"])

for y in translations_dict.items():
    date = y[0]
    count = len(y[1])
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions per Locale

locales = Locale.objects.available().order_by("code")
data = {}
for year in range(2017, datetime.datetime.now().year + 1):
    for month in range(1, 13):
        if year == datetime.datetime.now().year and month > datetime.datetime.now().month:
            continue
        data["{}/{}".format(month, year)] = {}

for locale in locales:
    translations = Translation.objects.filter(
        date__gte=datetime.datetime(2017,1,1),
        locale=locale,
        user__isnull=False,
    ) \
        .annotate(period=TruncMonth("date")) \
        .values("period") \
        .annotate(count=Count("id")) \
        .order_by("period")
    for x in translations:
        date = x["period"]
        date = "{}/{}".format(date.month, date.year)
        count = x["count"]
        data[date][locale.code] = count

print("," + ",".join(locales.values_list("code", flat=True)))
for date, values in data.items():
    line = date
    for l in locales:
        line = line + "," + str(values.get(l.code, 0))
    print(line)
```
Revision 4 by
on 
I've collected the requested numbers in the spreadsheet:
https://docs.google.com/spreadsheets/d/1kJzAS6cLXUSdaPm7a_oAnG-8zSBHQUbMpsil4rLqIJs/edit?usp=sharing

I used the scripts below.

Let me know if you have more data requests.

```python
import datetime
from pontoon.base.models import *
from django.db.models.functions import TruncMonth

# New User Registrations

users = User.objects.all() \
    .annotate(period=TruncMonth("date_joined")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in users:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in translations:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Entity Creations

entities = Entity.objects \
    .annotate(period=TruncMonth("date_created")) \
    .values("period") \
    .annotate(count=Count("id")) \
    .order_by("period")

for x in entities:
    date = x["period"]
    count = x["count"]
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# Active Users

translations = Translation.objects.filter(user__isnull=False) \
    .annotate(period=TruncMonth("date")) \
    .values("period", "user_id") \
    .annotate(count=Count("user_id")) \
    .values('period', 'count') \
    .order_by("period")

translations_dict = defaultdict(list)
for x in translations:
    translations_dict[x["period"]].append(x["count"])

for y in translations_dict.items():
    date = y[0]
    count = len(y[1])
    print("{month}/{year},{count}".format(month=date.month, year=date.year, count=count))


# New Translation Submissions per Locale

locales = Locale.objects.available().order_by("code")
data = {}
for year in range(2017, datetime.datetime.now().year + 1):
    for month in range(1, 13):
        if year == datetime.datetime.now().year and month > datetime.datetime.now().month:
            continue
        data["{}/{}".format(month, year)] = {}

for locale in locales:
    translations = Translation.objects.filter(
        date__gte=datetime.datetime(2017,1,1),
        locale=locale,
        user__isnull=False,
    ) \
        .annotate(period=TruncMonth("date")) \
        .values("period") \
        .annotate(count=Count("id")) \
        .order_by("period")
    for x in translations:
        date = x["period"]
        date = "{}/{}".format(date.month, date.year)
        count = x["count"]
        data[date][locale.code] = count

print("," + ",".join(locales.values_list("code", flat=True)))
for date, values in data.items():
    line = date
    for l in locales:
        line = line + "," + str(values.get(l.code, 0))
    print(line)
```

Back to Bug 1629968 Comment 1