In T193641#4307136, @RazShuty wrote:

Also, the event_timestamp doesn't fit, right?

We want to know these 2 things:

1. How many co editors we had in Q2 2017 (broken by wikipedia)

P7278

2. How many co editors we had in Q2 2018 (broken by wikipedia)

P7282

3. How many co editors we had since 01.01.2017 until Q2 including

P7283

@Ladsgroup what were the queries you used to generate the data in the above comment?

T193641#4304748 has a couple of issues:

• You looking at data from 2017 (not sure if that was intended or not), the example query does indeed have 2017 in it.
  • I now realize that this is probably fine in the queries you ran in the above comment
• The snapshots don't match, one is using 2018-05 and the other 2018-2, these should probably match, Analytics and @JAllemandou would be able to clear up this. I'm also not sure where we can get the ID of the latest snapshot either.

11:54 AM <joal> Joseph Allemandou addshore: By convention, it's YYYY-MM, but there is no automated place to get it except asking hive for partitions (or look in folder availability)
11:54 AM <addshore> ack
11:54 AM so generally can one assume that each month there will be a new one?
11:54 AM <joal> Joseph Allemandou addshore: That's what we do yes
11:54 AM <addshore> thanks!
11:55 AM <joal> Joseph Allemandou But it arrives in thew middle of the month addshore, about the 10th

So 2018-02 is created in march and will contain data for all of feb and possibly some of march

In T193641#4321435, @Addshore wrote:

@Ladsgroup what were the queries you used to generate the data in the above comment?

To get answer for the first question:

WITH
wikipedias AS (
  SELECT
    DISTINCT dbname
  FROM wmf_raw.mediawiki_project_namespace_map
  WHERE snapshot = '2018-05'
    AND hostname LIKE '%wikipedia.org'
),

wikidata_editors AS (
  SELECT
    DISTINCT event_user_text
  FROM wmf.mediawiki_history
  WHERE snapshot = '2018-05'
    AND event_entity = 'revision'
    AND event_type = 'create'
    AND wiki_db = 'wikidatawiki'
    AND NOT event_user_is_anonymous
    AND NOT ARRAY_CONTAINS(event_user_groups, 'bot')
    AND event_timestamp RLIKE '^2017-0[45].*'
)

SELECT
  mwh.wiki_db,
  COUNT(DISTINCT mwh.event_user_text) as wikidata_coeditors
FROM wmf.mediawiki_history mwh
  JOIN wikipedias w ON (mwh.wiki_db = w.dbname)
  JOIN wikidata_editors wde ON (mwh.event_user_text = wde.event_user_text)
WHERE snapshot = '2018-05'
  AND event_entity = 'revision'
  AND event_type = 'create'
  AND NOT mwh.event_user_is_anonymous
  AND NOT ARRAY_CONTAINS(mwh.event_user_groups, 'bot')
  AND mwh.event_timestamp RLIKE '^2017-0[45].*'
GROUP BY
  mwh.wiki_db
ORDER BY wikidata_coeditors DESC
LIMIT 1000;

To get answer to the second question

WITH
wikipedias AS (
  SELECT
    DISTINCT dbname
  FROM wmf_raw.mediawiki_project_namespace_map
  WHERE snapshot = '2018-05'
    AND hostname LIKE '%wikipedia.org'
),

wikidata_editors AS (
  SELECT
    DISTINCT event_user_text
  FROM wmf.mediawiki_history
  WHERE snapshot = '2018-05'
    AND event_entity = 'revision'
    AND event_type = 'create'
    AND wiki_db = 'wikidatawiki'
    AND NOT event_user_is_anonymous
    AND NOT ARRAY_CONTAINS(event_user_groups, 'bot')
    AND event_timestamp RLIKE '^2018-0[45].*'
)

SELECT
  mwh.wiki_db,
  COUNT(DISTINCT mwh.event_user_text) as wikidata_coeditors
FROM wmf.mediawiki_history mwh
  JOIN wikipedias w ON (mwh.wiki_db = w.dbname)
  JOIN wikidata_editors wde ON (mwh.event_user_text = wde.event_user_text)
WHERE snapshot = '2018-05'
  AND event_entity = 'revision'
  AND event_type = 'create'
  AND NOT mwh.event_user_is_anonymous
  AND NOT ARRAY_CONTAINS(mwh.event_user_groups, 'bot')
  AND mwh.event_timestamp RLIKE '^2018-0[45].*'
GROUP BY
  mwh.wiki_db
ORDER BY wikidata_coeditors DESC
LIMIT 1000;

And to answer the third question:

WITH
wikipedias AS (
  SELECT
    DISTINCT dbname
  FROM wmf_raw.mediawiki_project_namespace_map
  WHERE snapshot = '2018-05'
    AND hostname LIKE '%wikipedia.org'
),

wikidata_editors AS (
  SELECT
    DISTINCT event_user_text
  FROM wmf.mediawiki_history
  WHERE snapshot = '2018-05'
    AND event_entity = 'revision'
    AND event_type = 'create'
    AND wiki_db = 'wikidatawiki'
    AND NOT event_user_is_anonymous
    AND NOT ARRAY_CONTAINS(event_user_groups, 'bot')
    AND event_timestamp RLIKE '^201[78].*'
)

SELECT
  mwh.wiki_db,
  COUNT(DISTINCT mwh.event_user_text) as wikidata_coeditors
FROM wmf.mediawiki_history mwh
  JOIN wikipedias w ON (mwh.wiki_db = w.dbname)
  JOIN wikidata_editors wde ON (mwh.event_user_text = wde.event_user_text)
WHERE snapshot = '2018-05'
  AND event_entity = 'revision'
  AND event_type = 'create'
  AND NOT mwh.event_user_is_anonymous
  AND NOT ARRAY_CONTAINS(mwh.event_user_groups, 'bot')
  AND mwh.event_timestamp RLIKE '^201[78].*'
GROUP BY
  mwh.wiki_db
ORDER BY wikidata_coeditors DESC
LIMIT 1000;

T193641#4304748 has a couple of issues:

• You looking at data from 2017 (not sure if that was intended or not), the example query does indeed have 2017 in it.

That was for comparison.

• • I now realize that this is probably fine in the queries you ran in the above comment
• The snapshots don't match, one is using 2018-05 and the other 2018-2, these should probably match, Analytics and @JAllemandou would be able to clear up this. I'm also not sure where we can get the ID of the latest snapshot either.

I fixed it in the next run but at the end it doesn't matter much, there hasn't been lots of wikipedias created in Q2 of 2018 and they are really small to contribute much to the whole stats.

Change 443069 had a related patch set uploaded (by Jonas Kress (WMDE); owner: Jonas Kress (WMDE)):
[analytics/refinery/source@master] Track number of editors from other Wikimedia projects who also edit on Wikidata over time

https://gerrit.wikimedia.org/r/443069

Change 443370 had a related patch set uploaded (by Jonas Kress (WMDE); owner: Jonas Kress (WMDE)):
[operations/puppet@production] Add monthly storage schema for graphite

https://gerrit.wikimedia.org/r/443370

Change 443409 had a related patch set uploaded (by Jonas Kress (WMDE); owner: Jonas Kress (WMDE)):
[analytics/refinery@master] [WIP] Introduce oozie job to schedule generating metrics for Wikidata co-editors

https://gerrit.wikimedia.org/r/443409

Change 443069 merged by jenkins-bot:
[analytics/refinery/source@master] Track number of editors from Wikipedia who also edit on Wikidata over time

https://gerrit.wikimedia.org/r/443069

Change 443370 abandoned by Jonas Kress (WMDE):
Add monthly storage schema for graphite

https://gerrit.wikimedia.org/r/443370

Change 443409 merged by Joal:
[analytics/refinery@master] Introduce oozie job to schedule generating metrics for Wikidata co-editors

https://gerrit.wikimedia.org/r/443409

We should be able to backfill the data for this since wikidata came to be.
Is this something we want to do @Lydia_Pintscher ?

That'd be awesome!

Though thinking about it it might get a bit weird for the time before we had unified logins for all wikis?

We said we also want this for the other projects. So far it's only Wikipedia. Is there anything preventing us from doing the other ones as well?

Should work as well.
Do you have a list of projects you are also interested in?
What kind of charts would you like to have?

It looks like the dashboard only shows data until June currently.
Is the reason we are missing more recent months because the data just isn't there yet or is there something wrong with the job?

Mh, it seems like the job is not running @JAllemandou ?

1:25 PM <addshore> joal: any idea about https://phabricator.wikimedia.org/T193641#4614082 ?
1:30 PM <joal> addshore: job seems successfully running - something else must be going on
1:43 PM <joal> addshore: just triple checked the query on Spark - Looks correct - The issue is somewhere else (maybe oozie config)
1:43 PM <joal> addshore: just triple checked the query on Spark - Looks correct - The issue is somewhere else (maybe oozie config)

Jobs have been successful for the past months. However rerunning the jobs manually made the data-points appear. This is very bizarre.
Let's keep this open and monitor next month.

Thanks for raising the issue. This is very bizarre.
The job for october was showing successful in our side. I reran it, and data showed up :(
I have the feeling this is not the first time this happens, something must be wrong somewhere.
I'am also going to run backfilling info.

Info backfilled since beggining of time: https://grafana.wikimedia.org/dashboard/db/wikidata-co-editors?orgId=1&from=now-8y&to=now
Will keep an eye on next month run.

I don't remember when this is set to run @JAllemandou , but I see no data for Nov yet?

Same exact problem as last month: job has run, but no data is present :(
More investigations needed, probably early next year.

@JAllemandou: as at WMDE we are in the need for the December data (Dec, 1st in particular), could you maybe please do the magic and re-run the job and backfill the data again? Thanks in advance.

Hi @WMDE-leszek - core data has not been computed et (usually done around the 9th of the following month).
I'll be sure to have an eye on data showing up for month 12 and rerun the job if needed.

Very much appreciated @JAllemandou!

Change 482664 had a related patch set uploaded (by Joal; owner: Joal):
[analytics/refinery@master] Update wikidata-coeditor job data dependency

https://gerrit.wikimedia.org/r/482664

Change 482664 merged by Mforns:
[analytics/refinery@master] Update wikidata-coeditor job data dependency

https://gerrit.wikimedia.org/r/482664

Change 482692 had a related patch set uploaded (by Joal; owner: Joal):
[analytics/refinery@master] Correct wikidata-coeditors data dependencies (2nd)

https://gerrit.wikimedia.org/r/482692

Change 482692 merged by Mforns:
[analytics/refinery@master] Correct wikidata-coeditors data dependencies (2nd)

https://gerrit.wikimedia.org/r/482692

Bug found and corrected (patches above).
Data is available now and the rerun problem should be solved.

I guess we can verify this after the next run!

I confirm the fix :) Closing this task.

Much appreciated @JAllemandou !