<jupyter_start><jupyter_text>Replica-based ("official") metrics Up until February, active editors were calculated using the following procedure. For the initial run (probably some time in 2018), the procedure was run for all previous time. Then, each month, it was re-run to add data for the previous month.First, we built an [editor month dataset](https://meta.wikimedia.org/wiki/Research:Editor_month_dataset) by running [the update_editor_month query](https://github.com/wikimedia-research/Editing-movement-metrics/blob/3f5322cc1302419114ea7f647fdf4592063c6a35/queries/update_editor_month.sql) (or [a rewritten version](https://github.com/wikimedia-research/Editing-movement-metrics/blob/f6db91f3c64ffc05ae6eeda599755af744928803/queries/update_editor_month.sql) in January 2018) on [a specific selection of wikis](https://github.com/neilpquinn/wmfdata/blob/b0548529c4d39fc37f40fb637025e8a9b428a33f/wmfdata/mariadb.pyL94) sequentially.Then, the active editor numbers were calculated using [an SQL query](https://github.com/wikimedia-research/Editing-movement-metrics/blob/f6db91f3c64ffc05ae6eeda599755af744928803/queries/active_editors.sql) on that editor-month table.This gave the following, which is our currently accepted version of reality:<jupyter_code>metrics_url = "https://raw.githubusercontent.com/wikimedia-research/Editing-movement-metrics/75b3251727f8c766e4872f775f57a09632df6500/metrics/metrics.tsv"
metrics_stream = StringIO(requests.get(metrics_url).text)

official_ae = pd.read_csv(
    metrics_stream,
    sep="\t",
    parse_dates=["month"]  
).set_index("month")["active_editors"].to_frame()

official_ae.tail()<jupyter_output><empty_output><jupyter_text>Data Lake-based ("new") metrics For February's metrics, we switched to calculating these based on the `mediawiki_history` dataset in the Data Lake. First, we built an editor-month table using the following SQL: ```sqlinsert into neilpquinn.editor_monthselect    trunc(event_timestamp, "MONTH") as month,    wiki_db,    event_user_id as local_user_id,    max(event_user_text) as user_name, -- Some rows incorrectly have a null `event_user_text`    count(*) as edits,    coalesce(        sum(cast(page_namespace_is_content_historical as int)),        0    ) as content_edits,    NULL as mobile_web_edits,    NULL as mobile_app_edits,    NULL as visual_edits,    NULL as ve_source_edits,    (        max(event_user_is_bot_by_name) or         max(array_contains(event_user_groups, "bot")) or        max(array_contains(event_user_groups_historical, "bot"))    ) as bot,    min(event_user_creation_timestamp) as user_registrationfrom wmf.mediawiki_historywhere    event_timestamp between "{start}" and "{end}" and    event_entity = "revision" and    event_type = "create" and    snapshot = "{mwh_snapshot}"group by    trunc(event_timestamp, "MONTH"),    wiki_db,    event_user_id```<jupyter_code>new_ae = (
    hive.run("""
    select
        month,
        count(*) as active_editors
    from (
        select
            cast(month as date) as month,
            user_name,
            sum(content_edits) as content_edits,
            max(bot) as bot
        from neilpquinn.editor_month
        where
            month < "2019-02-01" and
            local_user_id != 0
        group by month, user_name
    ) global_edits
    where
        content_edits >= 5 and
        (not bot or user_name in ("Paucabot", "Niabot", "Marbot"))
    group by month
    """)
    .assign(month=lambda df: pd.to_datetime(df["month"]))
    .set_index("month")
)<jupyter_output><empty_output><jupyter_text>These differ a LOT from the replica-based metrics.<jupyter_code>(new_ae - official_ae).plot(title="Deviation of 'new' active editors from 'official'");<jupyter_output><empty_output><jupyter_text>Load editor month datasets for comparisons Let's directly compare the official version of the dataset with the new one (tweaked to eliminate a few obvious differences).<jupyter_code>staging_host = !analytics-mysql -d staging --print-target
staging_host = staging_host[0]
jdbc_uri = "jdbc:mysql://" + staging_host + "/staging"
cnf_path = "/etc/mysql/conf.d/research-client.cnf"
sqoop_query = """
select
    convert(wiki using utf8) as wiki,
    cast(month as datetime) as month,
    local_user_id,
    convert(user_name using utf8) as user_name,
    edits,
    content_edits,
    bot_flag,
    user_registration
from editor_month
where $CONDITIONS
"""

!sqoop import --connect {jdbc_uri} --connection-param-file {cnf_path} --query '{sqoop_query}' \
--split-by local_user_id --target-dir /user/neilpquinn-wmf/editor_month_official_raw \
--hive-import --hive-table neilpquinn.editor_month_official \
--map-column-hive month=timestamp,user_registration=timestamp
hive.run(["""
CREATE TABLE IF NOT EXISTS neilpquinn.editor_month_new (
  `wiki` STRING,
  `month` TIMESTAMP, -- Hive 1.1 does not support the DATE type
  `local_user_id` BIGINT,
  `user_name` STRING,
  `edits` BIGINT,
  `content_edits` BIGINT,
  `bot_flag` BOOLEAN,
  `user_registration` TIMESTAMP
) 
STORED AS PARQUET
""", """
insert into neilpquinn.editor_month_new
select
    wiki_db as wiki,
    trunc(event_timestamp, "MONTH") as month,
    event_user_id as local_user_id,
    max(event_user_text) as user_name, -- Some rows incorrectly have a null `event_user_text`
    count(*) as edits,
    coalesce(
        sum(cast(page_namespace_is_content_historical as int)),
        0
    ) as content_edits,
    (
        max(array_contains(event_user_groups, "bot")) or 
        max(array_contains(event_user_groups_historical, "bot"))
    ) as bot,
    min(event_user_creation_timestamp) as user_registration
from wmf.mediawiki_history
where
    event_timestamp < "2019-02-01" and
    event_entity = "revision" and
    event_type = "create" and
    snapshot = "2019-03"
group by
    trunc(event_timestamp, "MONTH"),
    wiki_db,
    event_user_id
"""])<jupyter_output><empty_output><jupyter_text>Let's make sure these datasets have the same active editors discrepancy.<jupyter_code>ae_query = """
select
    month,
    count(*) as active_editors
from (
    select
        cast(month as date) as month,
        user_name,
        sum(content_edits) as content_edits,
        max(bot_flag) as bot_flag
    from neilpquinn.{table}
    where 
        local_user_id != 0
    group by month, user_name
) global_edits
where
    content_edits >= 5 and
    not bot_flag and 
    user_name not regexp "bot\\b"
group by month
"""

emo_ae_query = ae_query.format(table="editor_month_official")
emn_ae_query = ae_query.format(table="editor_month_new")

emo_ae = hive.run(emo_ae_query).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
emn_ae = hive.run(emn_ae_query).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")<jupyter_output><empty_output><jupyter_text>Yup, it's the same. So we can proceed to compare just these two datasets.<jupyter_code>(emn_ae - emo_ae)["2001":].plot(
    title="Deviation of 'new' active editors from 'official'"
);<jupyter_output><empty_output><jupyter_text>Wiki inclusion It looks there are discrepancies in which wikis are included.<jupyter_code>emo_wikis = hive.run("""
select distinct wiki
from neilpquinn.editor_month_official
""")
emo_wikis = set(emo_wikis["wiki"].unique())


emn_wikis = hive.run("""
select distinct wiki
from neilpquinn.editor_month_new
""")
emn_wikis = set(emn_wikis["wiki"].unique())<jupyter_output><empty_output><jupyter_text>The extra wikis included in `editor_month_official` seem to be a miscellaneous collection that are [mistakenly not included](https://phabricator.wikimedia.org/T220456) in `mediawiki_history`.<jupyter_code>extra_emo_wikis = emo_wikis - emn_wikis
len(extra_emo_wikis)<jupyter_output><empty_output><jupyter_text>The extra wikis included in `editor_month_new` are a mixture of test wikis, infrastructure wikis (`donatewiki`, `loginwiki`), and affiliate wikis that are only there because I forgot to port the logic excluding them from the replicas-based pipeline to the Data Lake-based one.<jupyter_code>extra_emn_wikis = emn_wikis - emo_wikis
len(extra_emn_wikis)<jupyter_output><empty_output><jupyter_text>Let's see what the discrepancy looks like when we exclude these extra wikis.<jupyter_code>ae_same_wikis_sql = """
select
    month,
    count(*) as active_editors
from (
    select
        cast(month as date) as month,
        user_name,
        sum(content_edits) as content_edits,
        max(bot_flag) as bot_flag
    from neilpquinn.{table}
    where 
        local_user_id != 0 and
        wiki not in {excluded_wikis!r}
    group by month, user_name
) global_edits
where
    content_edits >= 5 and
    not bot_flag and 
    user_name not regexp "bot\\b"
group by month
"""

emo_ae_same_wikis_sql = ae_same_wikis_sql.format(
    table="editor_month_official",
    excluded_wikis=tuple(extra_emo_wikis)
)

emn_ae_same_wikis_sql = ae_same_wikis_sql.format(
    table="editor_month_new",
    excluded_wikis=tuple(extra_emn_wikis)
)

emo_ae_same_wikis = hive.run(emo_ae_same_wikis_sql).assign(
    month=lambda df: pd.to_datetime(df["month"])
).set_index("month")

emn_ae_same_wikis = hive.run(emn_ae_same_wikis_sql).assign(
    month=lambda df: pd.to_datetime(df["month"])
).set_index("month")<jupyter_output><empty_output><jupyter_text>Basically no change, which makes sense when you consider that all the wikis involved are tiny.<jupyter_code>(emn_ae_same_wikis - emo_ae_same_wikis)["2001":].plot(
    title="Deviation of 'new' active editors from 'official'"
);<jupyter_output><empty_output><jupyter_text>Unmatched rows If we use the same selection of wikis, `editor_month_new` has about 476,000 more rows than `editor_month_official`.<jupyter_code>row_count_sql = """
select count(*)
from neilpquinn.{table}
where wiki not in {excluded_wikis!r}
"""

emo_row_count_sql = row_count_sql.format(
    table="editor_month_official",
    excluded_wikis=tuple(extra_emo_wikis)
)

emn_row_count_sql = row_count_sql.format(
    table="editor_month_new",
    excluded_wikis=tuple(extra_emn_wikis)
)
hive.run(emn_row_count_sql)
hive.run(emo_row_count_sql)<jupyter_output><empty_output><jupyter_text>Let's find the rows that don't match up.<jupyter_code>unmatched_rows_sql = """
select *
from neilpquinn.editor_month_official emo
full outer join neilpquinn.editor_month_new emn
on
    emo.month = emn.month and
    emo.wiki = emn.wiki and
    emo.local_user_id = emn.local_user_id
where
    coalesce(emo.wiki not in {extra_emo_wikis!r}, true) and
    coalesce(emn.wiki not in {extra_emn_wikis!r}, true) and
    (emo.local_user_id is null or emn.local_user_id is null) 
""".format(
    extra_emo_wikis=tuple(extra_emo_wikis),
    extra_emn_wikis=tuple(extra_emn_wikis)
)

unmatched_rows = hive.run([
    "set hive.resultset.use.unique.column.names=true",
    unmatched_rows_sql
]).rename(columns=lambda x: x.replace(".", "_"))<jupyter_output><empty_output><jupyter_text>This gives us about 522,000 unmatched rows, which is about 46,000 more than the overall row count discrepancy. That suggests that we're mostly talking about rows that don't appear at all in one dataset, not about rows that appear in both dataset but didn't match up.Out of these these unmatched rows, 96% are found only in `editor_month_new` and 4% only in `editor_month_official`.<jupyter_code>len(unmatched_rows)
emn_only = unmatched_rows.query("~emn_wiki.isnull()")
len(emn_only)
emo_only = unmatched_rows.query("~emo_wiki.isnull()")
len(emo_only)<jupyter_output><empty_output><jupyter_text>The rows found only in `editor_month_new` correspond to revisions [imported](https://www.mediawiki.org/wiki/Manual:Importing_XML_dumps) from one wiki to another. The number dropped substantially in the past few years, when `editor_month_official` was being periodically built a month after the fact (unlike the `mediawiki_history`, which is rebuilt completely every month). This suggests that these rows are at least partly a case of "history" being changed gradually after the fact.<jupyter_code>emn_only.groupby("emn_month")["emn_wiki"].count().plot();<jupyter_output><empty_output><jupyter_text>The wikis concerned reinforces the idea that these are imported revision. I know dewiki has a policy of importing pages from other wikis before translating them, and it looks like [enwikibooks](https://en.wikibooks.org/wiki/Wikibooks:Requests_for_import) and [simplewiki](https://simple.wikipedia.org/wiki/Wikipedia:Importers) do as well.<jupyter_code>emn_only.groupby("emn_wiki")["emn_wiki"].count().sort_values(ascending=False).head(20)<jupyter_output><empty_output><jupyter_text>Looking at 10 random rows, all ten represent imported revisions.<jupyter_code>emn_only.sample(10, random_state=18300941).iloc[:, 8:].reset_index(drop=True)
# Filter with a datetime object because there's a "0001-11-30" month which causes all sorts of weirdness
year_2001 = pd.to_datetime("2001")
emo_only.groupby("emo_month")["emo_wiki"].count()[year_2001:].plot();
emo_only.groupby("emo_wiki")["emo_wiki"].count().sort_values(ascending=False).head(20)<jupyter_output><empty_output><jupyter_text>Looking at 10 random rows, all ten represent revisions where the user name was [revision deleted](https://www.mediawiki.org/wiki/Manual:RevisionDelete).<jupyter_code>emo_only.sample(10, random_state=18300941).iloc[:, :8].reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Taken together, these had only a minimal impact on the discrepancy.<jupyter_code>ae_matched_sql = """
select
    month,
    count(*) as active_editors
from (
    select
        cast({table_abbrev}.month as date) as month,
        {table_abbrev}.user_name,
        sum({table_abbrev}.content_edits) as content_edits,
        max({table_abbrev}.bot_flag) as bot_flag
    from neilpquinn.{table} {table_abbrev}
    inner join neilpquinn.{other_table} {other_table_abbrev}
    on
        emo.month = emn.month and
        emo.wiki = emn.wiki and
        emo.local_user_id = emn.local_user_id
    where 
        {table_abbrev}.local_user_id != 0
    group by {table_abbrev}.month, {table_abbrev}.user_name
) global_edits
where
    content_edits >= 5 and
    not bot_flag and 
    user_name not regexp "bot\\b"
group by month
"""

emo_ae_matched_sql = ae_matched_sql.format(
    table="editor_month_official",
    table_abbrev="emo",
    other_table="editor_month_new",
    other_table_abbrev="emn"
)

emn_ae_matched_sql = ae_matched_sql.format(
    table="editor_month_new",
    table_abbrev="emn",
    other_table="editor_month_official",
    other_table_abbrev="emo"
)

emo_ae_matched = hive.run(emo_ae_matched_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
emn_ae_matched = hive.run(emn_ae_matched_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
(emn_ae_matched - emo_ae_matched)["2001":].plot(
    title="Deviation of 'new' active editors from 'official'"
);<jupyter_output><empty_output><jupyter_text>Content edit counts So now let's look at the rows that exist in both datasets. Since this is a much bigger group, we'll only look at the past two year of data.<jupyter_code>matched_rows = hive.run([
    "set hive.resultset.use.unique.column.names=true",
    """
    select *
    from neilpquinn.editor_month_official emo
    inner join neilpquinn.editor_month_new emn
    on
        emo.month = emn.month and
        emo.wiki = emn.wiki and
        emo.local_user_id = emn.local_user_id
    where
        emo.month >= "2017-02-01" and
        emn.month >= "2017-02-01"
    """
]).rename(columns=lambda x: x.replace(".", "_"))<jupyter_output><empty_output><jupyter_text>12% have different numbers of content edits!<jupyter_code>rows = len(matched_rows)
len(matched_rows.query("emo_content_edits != emn_content_edits")) / rows<jupyter_output><empty_output><jupyter_text>Of those rows with differing numbers of content edits, `editor_month_official` shows more 96% of the time and `editor_month_new` more only 4% of the time.<jupyter_code>differing_content_edit_rows = len(matched_rows.query("emo_content_edits != emn_content_edits"))
len(matched_rows.query("emo_content_edits > emn_content_edits")) / differing_content_edit_rows<jupyter_output><empty_output><jupyter_text>Oof. There seem to be at least four different problems at work:Deleted pages where all the revisions have null `page_namespace_historical`, `page_namespace_is_content` and `page_namespace_is_content_historical`:* nlwiki page 5160672* commonswiki page 71797356* wikidatawiki page 9637937* arwiki page 4970274* commonswiki page 73916373* ruwiki page 7054391Revisions with null `page_namespace_historical` and `page_namespace_is_content_historical`. Most but not all of the revisions to the pages concerned are affected. * https://en.wikipedia.org/w/index.php?diff=67017781 (page 28408157)* https://www.wikidata.org/w/index.php?diff=155712677 (page 21524228)* https://pt.wikipedia.org/w/index.php?diff=1691356 (page 96328)* https://en.wikipedia.org/w/index.php?diff=820879007 (page 56326900)Revisions where the join to the page table seems to have failed entirely, because where almost all revisions have null `page_title`, `page_namespace`, and `page_namespace_is_content` (including historical fields) and `page_creation_timestamp`. Most but not all of the revisions to the pages concerned are affected.* https://en.wikipedia.org/w/index.php?diff=859361756 (page 40012938)* https://pt.wikipedia.org/w/index.php?diff=2692528 (page 3177643)* https://en.wikipedia.org/w/index.php?diff=269025183 (page 29397754)* https://en.wikipedia.org/w/index.php?diff=347411263 (page 30865452)Revisions with null page data (as above) because the referenced pages simply don't exist in the underlying page table. These may be botched deletions.* https://ru.wikipedia.org/w/index.php?diff=78824747* https://da.wikipedia.org/w/index.php?diff=1751290* https://nl.wikipedia.org/w/index.php?diff=1363616* https://en.wikipedia.org/w/index.php?diff=17967486<jupyter_code>matched_rows.query("emo_content_edits > emn_content_edits").sample(10)[
    ["emo_month", "emo_wiki", "emo_local_user_id", "emo_user_name", "emo_content_edits", "emn_content_edits"]
]<jupyter_output><empty_output><jupyter_text>In the less common case where `editor_month_new` shows more edits, the reasons seem to be:* content namespaces not included in the list for `editor_month_official`: the Page namespace (104) and Index namespace (106) on enwikisource, the Author namespace (102) and Page namespace (104) on dewikisource, the Page namespace on guwikisource (122), the List namespace of ltwiki (104)* edits to pages later moved out of content namespaces (e.g. from the main namespace to the user namespace)* a history merge that moved edits in the Draft namespace to the main namespace (e.g. with the page https://en.wikipedia.org/w/index.php?title=Wyatt_Omsberg&action=history)<jupyter_code>matched_rows.query("emn_content_edits > emo_content_edits").sample(10)[
    ["emo_month", "emo_wiki", "emo_local_user_id", "emo_user_name", "emo_content_edits", "emn_content_edits"]
]<jupyter_output><empty_output><jupyter_text>This, finally, makes an big impact on the discrepancy: at its largest, it goes from about -19 000 to about -1 200, and in the past year, it goes from about -4 000 to about +50.<jupyter_code>ae_all_ns_sql = """
select
    month,
    count(*) as active_editors
from (
    select
        cast({table_abbrev}.month as date) as month,
        {table_abbrev}.user_name,
        sum({table_abbrev}.edits) as edits,
        max({table_abbrev}.bot_flag) as bot_flag
    from neilpquinn.{table} {table_abbrev}
    inner join neilpquinn.{other_table} {other_table_abbrev}
    on
        emo.month = emn.month and
        emo.wiki = emn.wiki and
        emo.local_user_id = emn.local_user_id
    where 
        {table_abbrev}.local_user_id != 0
    group by {table_abbrev}.month, {table_abbrev}.user_name
) global_edits
where
    edits >= 5 and
    not bot_flag and 
    user_name not regexp "bot\\b"
group by month
"""

emo_ae_all_ns_sql = ae_all_ns_sql.format(
    table="editor_month_official",
    table_abbrev="emo",
    other_table="editor_month_new",
    other_table_abbrev="emn"
)

emn_ae_all_ns_sql = ae_all_ns_sql.format(
    table="editor_month_new",
    table_abbrev="emn",
    other_table="editor_month_official",
    other_table_abbrev="emo"
)

emo_ae_all_ns = hive.run(emo_ae_all_ns_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
emn_ae_all_ns = hive.run(emn_ae_all_ns_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
(emn_ae_all_ns - emo_ae_all_ns)["2001":].plot(
    title="Deviation of 'new' active editors from 'official'"
);<jupyter_output><empty_output><jupyter_text>Overall edit counts Only about 0.1% of rows differ in their overall edit count.<jupyter_code>differing_edits = matched_rows.query("emo_edits != emn_edits")
len(differing_edits) / rows<jupyter_output><empty_output><jupyter_text>About 63% of these rows show more edits in `editor_month_new`.<jupyter_code>len(differing_edits.query("emn_edits > emo_edits")) / len(differing_edits)<jupyter_output><empty_output><jupyter_text>The extra edits in `editor_month_new` all seem to be due to imported revisions: for example, where a user was credited with imported revisions *in addition* to making regular revisions during that month, or had some revisions imported before the row in `editor_month_official` was generated, and others imported between that time and now.<jupyter_code>differing_edits.query(
    "emn_edits > emo_edits"
).groupby("emo_wiki")["emo_wiki"].count().sort_values(ascending=False).head(20)
differing_edits.query(
    "(emn_edits > emo_edits) & (emo_wiki == 'commonswiki')"
).sample(10)[
    ["emo_month", "emo_wiki", "emo_local_user_id", "emo_user_name", "emo_edits", "emn_edits"]
]<jupyter_output><empty_output><jupyter_text>The extra edits in `editor_month_official` all seem to be due to revision deletion.<jupyter_code>differing_edits.query(
    "emo_edits > emn_edits"
).groupby("emo_wiki")["emo_wiki"].count().sort_values(ascending=False).head(20)
differing_edits.query(
    "(emo_edits > emn_edits) & (emo_local_user_id != 0)"
).sample(10)[
    ["emo_month", "emo_wiki", "emo_local_user_id", "emo_user_name", "emo_edits", "emn_edits"]
]<jupyter_output><empty_output><jupyter_text>This doesn't make a significant impact on the discrepancy; overall, while revision importing and deletion add an unfortunate instability to our metrics, the impact is not big enough for serious concern.<jupyter_code>ae_equal_edits_sql = """
select
    month,
    count(*) as active_editors
from (
    select
        cast({table_abbrev}.month as date) as month,
        {table_abbrev}.user_name,
        greatest(sum({table_abbrev}.edits), sum({other_table_abbrev}.edits)) as edits,
        max({table_abbrev}.bot_flag) as bot_flag
    from neilpquinn.{table} {table_abbrev}
    inner join neilpquinn.{other_table} {other_table_abbrev}
    on
        emo.month = emn.month and
        emo.wiki = emn.wiki and
        emo.local_user_id = emn.local_user_id
    where 
        {table_abbrev}.local_user_id != 0
    group by {table_abbrev}.month, {table_abbrev}.user_name
) global_edits
where
    edits >= 5 and
    not bot_flag and 
    user_name not regexp "bot\\b"
group by month
"""

emo_ae_equal_edits_sql = ae_equal_edits_sql.format(
    table="editor_month_official",
    table_abbrev="emo",
    other_table="editor_month_new",
    other_table_abbrev="emn"
)

emn_ae_equal_edits_sql = ae_equal_edits_sql.format(
    table="editor_month_new",
    table_abbrev="emn",
    other_table="editor_month_official",
    other_table_abbrev="emo"
)

emo_ae_equal_edits = hive.run(emo_ae_equal_edits_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
emn_ae_equal_edits = hive.run(emn_ae_equal_edits_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
(emn_ae_equal_edits - emo_ae_equal_edits)["2001":].plot(
    title="Deviation of 'new' active editors from 'official'"
);<jupyter_output><empty_output><jupyter_text>User names Let's look at differing user names, filtering out the rows representing anonymous editors because `editor_month_official` gives the user name as an empty string, where `editor_month_new` gives it as null.<jupyter_code>differing_names = matched_rows.query("emn_user_name != emo_user_name & emo_local_user_id !=0")

pct_str(len(differing_names) / len(matched_rows))<jupyter_output><empty_output><jupyter_text>These are all cases where the user was renamed after the `editor_month_official` row was generated.<jupyter_code>differing_names.sample(10)[
    ["emo_wiki", "emo_month", "emo_user_name", "emn_user_name"]
]<jupyter_output><empty_output><jupyter_text>Rerunning the active editor numbers, grouping in both cases by the user name in `editor_month_new`, it has a significant effect on the discrepancy (although not as significant as the content edits issues).<jupyter_code>ae_new_names_sql = """
select
    month,
    count(*) as active_editors
from (
    select
        cast({table_abbrev}.month as date) as month,
        emn.user_name,
        greatest(sum({table_abbrev}.edits), sum({other_table_abbrev}.edits)) as edits,
        max({table_abbrev}.bot_flag) as bot_flag
    from neilpquinn.{table} {table_abbrev}
    inner join neilpquinn.{other_table} {other_table_abbrev}
    on
        emo.month = emn.month and
        emo.wiki = emn.wiki and
        emo.local_user_id = emn.local_user_id
    where 
        {table_abbrev}.local_user_id != 0
    group by {table_abbrev}.month, emn.user_name
) global_edits
where
    edits >= 5 and
    not bot_flag and 
    user_name not regexp "bot\\b"
group by month
"""

emo_ae_new_names_sql = ae_new_names_sql.format(
    table="editor_month_official",
    table_abbrev="emo",
    other_table="editor_month_new",
    other_table_abbrev="emn"
)

emn_ae_new_names_sql = ae_new_names_sql.format(
    table="editor_month_new",
    table_abbrev="emn",
    other_table="editor_month_official",
    other_table_abbrev="emo"
)

emo_ae_new_names = hive.run(emo_ae_new_names_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
emn_ae_new_names = hive.run(emn_ae_new_names_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
(emn_ae_new_names - emo_ae_new_names)["2001":].plot(
    title="Deviation of 'new' active editors from 'official'"
);<jupyter_output><empty_output><jupyter_text>Only the `editor_month_official` active editors number would have changed, since we were already grouping `editor_month_new` by its own `user_name` column. But it looks like very little of that change happened in the past two years (the range covered by the above `differing_names` dataset.<jupyter_code>(emo_ae_equal_edits - emo_ae_new_names)["2001":].plot()<jupyter_output><empty_output><jupyter_text>So let's get an older sample of differing-name rows and see what's going on.<jupyter_code>older_matched_rows = hive.run([
    "set hive.resultset.use.unique.column.names=true",
    """
    select *
    from neilpquinn.editor_month_official emo
    inner join neilpquinn.editor_month_new emn
    on
        emo.month = emn.month and
        emo.wiki = emn.wiki and
        emo.local_user_id = emn.local_user_id
    where
        emo.month >= "2005-01-01" and
        emo.month < "2007-01-01"
    """
]).rename(columns=lambda x: x.replace(".", "_"))<jupyter_output><empty_output><jupyter_text>It looks like all of these are cases where `editor_month_new` has a null username, almost certainly cases of [T218463](https://phabricator.wikimedia.org/T218463).<jupyter_code>older_matched_rows.query("emo_user_name != emn_user_name")[
    ["emo_wiki", "emo_month", "emo_user_name", "emn_user_name"]
].sample(20)<jupyter_output><empty_output><jupyter_text>Bot flag A very small number of rows differ in their categorization as bots.<jupyter_code>differing_bot_flags = matched_rows.query("emo_bot_flag != emn_bot_flag")
len(differing_bot_flags) / len(matched_rows)<jupyter_output><empty_output><jupyter_text>This is because the `editor_month_official` considers a user a bot if they were *ever* in the "bot" group, whereas `editor_month_new` considers them a bot in a given month if they were in the group during the month or at the time `mediawiki_history` was generated.`editor_month_new` seems to have a better approach, producing the correct result in 17 out of the 20 cases below, mainly because many human users add themselves to the bot group temporarily (sometimes for just 30 minutes or less) for testing or to make a spate of edits without cluttering up the recent changes feed. Moreover, the 3 remaining cases, where real bots were not flagged as such in `editor_month_new`, would have been caught by the user name filter anyway.<jupyter_code>bot_flag_columns = [
    "emo_wiki",
    "emo_month",
    "emo_user_name",
    "emo_bot_flag",
    "emn_user_name",
    "emn_bot_flag"
]

differing_bot_flags.sample(20)[bot_flag_columns]
ae_same_bots_sql = """
select
    month,
    count(*) as active_editors
from (
    select
        cast({table_abbrev}.month as date) as month,
        emn.user_name,
        greatest(sum({table_abbrev}.edits), sum({other_table_abbrev}.edits)) as edits,
        max(emn.bot_flag) as bot_flag
    from neilpquinn.{table} {table_abbrev}
    inner join neilpquinn.{other_table} {other_table_abbrev}
    on
        emo.month = emn.month and
        emo.wiki = emn.wiki and
        emo.local_user_id = emn.local_user_id
    where 
        {table_abbrev}.local_user_id != 0
    group by {table_abbrev}.month, emn.user_name
) global_edits
where
    edits >= 5 and
    not bot_flag and 
    user_name not regexp "bot\\b"
group by month
"""

emo_ae_same_bots_sql = ae_same_bots_sql.format(
    table="editor_month_official",
    table_abbrev="emo",
    other_table="editor_month_new",
    other_table_abbrev="emn"
)

emn_ae_same_bots_sql = ae_same_bots_sql.format(
    table="editor_month_new",
    table_abbrev="emn",
    other_table="editor_month_official",
    other_table_abbrev="emo"
)

emo_ae_same_bots = hive.run(emo_ae_same_bots_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")
emn_ae_same_bots = hive.run(emn_ae_same_bots_sql).assign(month=lambda df: pd.to_datetime(df["month"])).set_index("month")<jupyter_output><empty_output><jupyter_text>And with that, we seem to have taken care of the entire discrepancy! It's not terribly suprising, considering that I have now essentially picked a winner in every case where the two datasets differ, but it's still deeply refreshing after so much investigation 😁<jupyter_code>(emn_ae_same_bots - emo_ae_same_bots)["2001":].plot(
    title="Deviation of 'new' active editors from 'official'"
);<jupyter_output><empty_output><jupyter_text>Mitigation Based on the findings above, I've regenerated `neilpquinn.editor_month`, working around as many of the issues as possible with [this SQL](https://github.com/wikimedia-research/Editing-movement-metrics/blob/1178702104d1cafd1c003759aa09d9dbd8728d0c/queries/update_editor_month.sql).<jupyter_code>mitigated_ae = (
    hive.run("""
    select
        month,
        count(*) as active_editors
    from (
        select
            cast(month as date) as month,
            user_name,
            sum(content_edits) as content_edits,
            max(bot_by_group) as bot -- `bot_by_group` is misnamed and includes the user name regex
        from neilpquinn.editor_month
        where
            month < "2019-02-01" and
            user_id != 0
        group by month, user_name
    ) global_edits
    where
        content_edits >= 5 and
        (not bot or user_name in ("Paucabot", "Niabot", "Marbot"))
    group by month
    """)
    .assign(month=lambda df: pd.to_datetime(df["month"]))
    .set_index("month")
)<jupyter_output><empty_output><jupyter_text>This seems to have worked really well!<jupyter_code>(mitigated_ae - official_ae).plot(title="Deviation of 'mitigated' active editors from 'official'");
ae_comparison = pd.concat([
    new_ae.rename(columns=lambda x: "new (mediawiki_history)"),
    mitigated_ae.rename(columns=lambda x: "mitigated new (mediawiki_history)"),
    official_ae.rename(columns=lambda x: "old (replicas)")
], axis=1)

plt = ae_comparison.plot()
plt.set_title("Comparison of active editor calculations");<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10601001669449082, 'fraction_numerical': 0.020064304705373154, 'mean_word_length': 5.0688555347091935, 'pattern_counts': {'":': 8, '<': 169, '<?xml version=': 0, '>': 185, 'https://': 26, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '41', 'dir': 'jupyter-structured-clean-dedup', 'id': '391016', 'license': 'Apache-2.0', 'n_tokens_mistral': 11848, 'n_tokens_neox': 10938, 'n_words': 2538, 'path': 'analysis.ipynb', 'repo_name': 'wikimedia-research/2019-02-active-editors-discrepancy', 'size': '32346'}