Numbers for October and November have been added to the data report.

In T342559#9423616, @AndrewTavis_WMDE wrote:

Numbers for October and November have been added to the data report.

@AndrewTavis_WMDE: Thx! Reporting is soon due, so could you pls also add December?

@Manuel: the numbers for December were added in last night :)

January 2024 is done! :)

Sheet has been updated with the numbers for January. Note that we have less unique user agents from a local maximum last month, but the number of IPs continues to grow. Seems like adoption is picking up, but then we're not necessarily going to pick this up with user agents as many of them would be coming from Python based libraries that share user agents.

@AndrewTavis_WMDE Reporting time is coming: In the first week of April, please ensure that we have both February and March data available. Ideally, test the results from T341330 by also running the original notebooks for comparison. Thx!

I've added the numbers for February to the sheet based on the first DAG run and also just went through the query job one final time to check. The queries that are being ran by the job are directly from the original queries with only a few minor changes:

For counting the filtered user agents we're doing the following:

count(
    DISTINCT CASE
        WHEN user_agent
        NOT LIKE 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/% (KHTML, like Gecko) Chrome/% Safari/%'
        THEN user_agent
    END
) AS total_filtered_user_agents,

... instead of:

SELECT
    count(DISTINCT user_agent) AS total_filtered_user_agents

...

WHERE
    AND user_agent NOT LIKE 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/% (KHTML, like Gecko) Chrome/% Safari/%'

Within the WHERE clause we are further adding webrequest_source = 'text' as discussed, which was suggested by WMF data engineering and meaning that we are not losing any any information, but rather that we are querying from a subset of information that included our original results.

I'll update the numbers for March once the next DAG run is finished at the start of next week!

Sheet has been updated for March via a query of wmde.wd_rest_api_metrics_monthly that's generated by Airflow. Slightly lower user agents than last month, but IPs doubled 📈

Looking good, thx!

Sheet updated with the numbers for April. Higher number of user agents, but lower IPs (but then IPs still much higher than Feb).

Table has been updated with the new data from the most recent DAG run. Lots more user agents - almost a 3x increase. Noting this for now as maybe grounds for further investigation later, but IPs are also increasing (just not by as much).

Note that we need to do some work on the wmde.wd_rest_api_metrics_monthly table at this point as directed in T365699: Published datasets data release request for Wikidata REST API metrics. Specifically, as of now we have all the the outputs of this table being bigint values. As this type of data is classified under users, we need to assure that data points less than 25 are recorded as "<25". All columns will thus need to be converted over to being strings. Goal on this would be of course to not have any data loss in this process. The query has already been updated locally, and will be changed with the next deploy to add the published datasets as a deployment target (both the DAG and the jobs need to be updated at this point).

As the DAG has already been ran for this month, so I'm going to update the job queries now. Another thing to consider is whether in this update we can also backdate the table with the information from months before the DAG was functional.

Hi @AndrewTavis_WMDE, could you please ping me here once the new results are available in the data report?

Hi @Manuel: I just ran the query and it's not yet been updated. Likely that wmf.webrequest isn't updated yet and thus the sensor hasn't fired to trigger the DAG. Will check again tomorrow.

Hi @AndrewTavis_WMDE, any news?

Hey @Manuel. Still not up, which might be because of changes to the queries given the new export to published datasets step. I'm deploying the updated DAG now as tests passed, so we'll see if all finishes.

Okay cool!

@Manuel, report has been updated. The queries ran fine, but for some reason the data export didn't work even though it did for testing. Checking with WMF about this. Will get to the rest of the tasks for this month today/tomorrow and the other DAG will be deployed once the issues with the current ones are figured out.

Okay, thank you for investigating!

Just queried the results and added them to the data report, @Ifrahkhanyaree_WMDE :) T349285 is currently not a priority for setting up an Airflow data pipeline to produce the REST API user agents, but I could run the queries to generate them for you if you'd like me to. Let me know, or I should also be able to finished T349285 by the end of the month if that would be fine. We were thinking to just wait till the pipeline is running, and your new data access rights will also allow you to query that table directly 😊

Data report has been updated with the numbers for October and I just checked and the user agents table has also been updated with those from October :)

Data report has been updated with the numbers for November. Note that we had a dramatic increase in both user agents and IPs. @Ifrahkhanyaree_WMDE, please let me know if I should export the user agents for the last month. They're again available in wmde.wd_rest_api_user_agents_monthly :)

Since I now have access to JN, I run these numbers by myself (maybe it's also one less thing for you to do! cc: @karapayneWMDE and @Lydia_Pintscher) so I did the see the surprising increase in user agents. I'll look into those and see if I find something interesting. But thank you!!

If I don't need to put them in the sheet anymore then that's fine by me, @Ifrahkhanyaree_WMDE :) We should still be running the metrics pipeline so we keep the aggregates and can then dashboard them in the future when we have public dashboards available.

Should I pause the monthly user agents DAG as you can generate them yourself, or would you prefer wmde.wd_rest_api_user_agents_monthly to be populated on the first of the month in case you need them, @Ifrahkhanyaree_WMDE? Happy to have it keep running so you don't need to aggregate it and can just check them directly, but if it's not needed then we should pause it.

Yeah, I can add them to the Wikidata reporting sheet myself (I would think it's needed until the collab agreement ends next summer)

But, makes 100% sense to keep the pipeline running so we keep the aggregates for later dashboarding.

Ok, so a summary of the above:

• We keep the wmde.wd_rest_api_metrics_monthly DAG/pipeline running and @Ifrahkhanyaree_WMDE will take over checking that table and adding the metrics to the data report
• We keep the wmde.wd_rest_api_user_agents_monthly DAG/pipeline running in case @Ifrahkhanyaree_WMDE needs to check the user agents, in which case they'll be pre collected with their total requests
  • Can you confirm this, @Ifrahkhanyaree_WMDE?

If the above stands, I'd open the idea of closing this task :)

confirmed! sounds good, thank you!

Task moved to In Review for the decision to close :) @karapayneWMDE and I will discuss this soon. Thanks, @Ifrahkhanyaree_WMDE! 😊