FYI @Manuel, this is the breakdown of agent_type for yesterday's REST API requests:

And the same for http_status is:

I've written very detailed pseudocode for all the potential table creation scripts. Just need to figure out the insertion step after we get more information from WMF and then the queries should be done :)

• Where to store the query and output data?

Most of our users store their data in HDFS, as part of a Hive table.

• Queries within the analytics repo on GitLab?

Yes that is what we recommend these days. You will have your GitLab repo, and perhaps inside of it a sql folder with all your queries. Then, from Airflow, you can trigger a particular SQL command via SparkSqlOperator. See docs here, but if you have not used Airflow before, we can have a meeting to get you on your way.

• Where would such tables that we want to create live within the data lake?

We can create a new database for you, and you can start adding tables there as needed. Let us know a database name that would make sense for you.

• When to run it
  • In the morning the next day after the period ends? (day, month, quarter)

This is easily set on your Airflow DAGs as there are schedule tags like @daily or @monthly, and yes, they run after the period ends.

• Relation to WMF flows within Airflow
  • I'm assuming that there are .xml/.yaml files that define the flows?
  • This would be run after the requests flow, but what else would proceed it in the flows just for our overall understanding of its position within the general graph?

We do have a file defining most of our datasets in Airflow. But I think all you need to care about is whether the webrequest data is available for a specific time period. We have built tooling making this a one liner. That line gets you an Airflow sensor that knows the details on how to wait for webrequest data. This is probably confusing if you have not done Airflow before, so if that is the case, let's setup an intro call.

• How to test it prior to deployment
  • Specifically is there a test server where new flows are ran against the graph?

We have instructions on how to launch your own development environment on stat machines.

• How will we be able to access it?
  • We'd like to use Grafana, so output to Graphite/Prometheus?
    • Or how easy is it to import this data into the WM Cloud Superset?

From @Ottomata vis Slack:
re grafana/prometheus: this is difficult for aggregate data, because prometheus does not allow you to produce data with historical times.
best viz tool we use is: https://wikitech.wikimedia.org/wiki/Data_Engineering/Systems/Superset

• How will errors be reported/handled if something goes wrong
  • Email notification, or can we set it up such that we get an alert within their flows notifications channel on Slack? Potentially set up a group like wmde-analytics?
  • We'd prefer to be able to mute the full alerts channel and just be notified when something that belongs to us has failed.

You can set an email or email array per Airflow DAG. Regarding Slack, we currently don't have an integration like that, but if you want to work on it, definitely go for it!

• Can we backdate the information for April, May and June of this year so that it's available for a quarterly aggregate?

Yes, you can do a 'backfill' of data. Whenever we are ready to go to production, we would set the start_date of your DAG to whatever date makes sense to you and it will auto backfill.

We have office hours if you want to ask more questions. Here is a google calendar link to next office hours.

Also, we should figure out whether we can set you up on the platform_eng Airflow instance, or if we should launch a new one. CC @lbowmaker.

@xcollazo and @Ottomata: Thank you for your input! An introduction to Airflow for @AndrewTavis_WMDE and me would be fantastic! I'll contact you on Slack to talk more about it.

Thanks for the detailed explanations, @xcollazo! Really is such a wonderful wealth of information :) We have a call with you all to discuss Airflow later today that will also help explain some more points as far as really getting it all set up. Can't want to get into the flow of all this!

Note for analytics on our end: further useful exploration of webrequests based on a conversation with @Manuel this morning would be:

• Breaking down agent_type also by user_agent and share with @Manuel privately
• The example queries for aggregation tables should be segmented based on http_status, specifically into successful and unsuccessful queries (we're not doing to include this in these workflows, but queries to subset based on that should be written)

@Manuel, here's the breakdown of http_status for requests yesterday just to show that I have the segmentation for that setup now. Am just adding WHERE http_status LIKE '2%' to a CASE WHEN for this.

I'll send along a link to a Google Sheet with the breakdown for agent_type and user_agent soon. We can discuss from there if you'd like a larger dataset generated for that :)

Notes from the WMF-WMDE meeting on Airflow with @mforns:

• data-engineering/airflow-dags houses all the DAGS for scheduling
  • Data transformation doesn't happen here
  • We'll need our own folder for WMDE Analytics
• Each repo has a config and dags
  • dag config will be written by data engineering
  • artifacts.yaml may not be necessary as of now (for Java jars and other tools)
  • datasets.yaml will be necessary
• *_dag.py files define DAGs
  • DagProperties allows for properties to be overwritten at runtime (also for testing)
  • We'll use this for setting the start date and alert emails, etc
  • SLAs (service level agreements) can be defined for notifications, etc
  • URLs are raw files within GitLab or another repo
    • Consider using the versioned version of queries as if we update the query Airflow will run the DAG
    • Properly testing the workflows should mean that we can deploy from main though
  • Airflow macros are used to define the times for DAG runs (year, month, day, etc)
• SSH tunnel for Airflow UI
  • Development instance of Airflow can be spun up and should be ran per new flow being added
  • Clone the script in a stats machine and call it
  • We're system users and thus need to create a home env /tmp folder, for example
  • DagProperties automates the creation of airflow variables that we can then edit/override for testing
  • Override the variables that determine where the data processes export, etc
  • Write to our separate user instance of the database
    • If we don't have a personal database yet, then we should make one
  • System users are read only for prod data and can only write to our own (or where we have access to)

Note that I've made T338785 for my GitLab access :)

Thx, @AndrewTavis_WMDE!

In T334558#8922872, @AndrewTavis_WMDE wrote:

Am just adding WHERE http_status LIKE '2%' to a CASE WHEN for this.

Broadly speaking 2xx and 3xx would be successful, and 4xx or 5xx would not be successful. https://en.wikipedia.org/wiki/List_of_HTTP_status_codes

I am sure that we can find some Wikimedia-specific example code.

@mforns, we'd discussed creating a directory for WMDE Analytics in the GitLab repo for Airflow DAGs :) @Manuel, should we finalize the name on this and get it created? Product analytics at WMF appears to be in analytics_product, so we could do analytics_wmde or wmde_analytics if we want a bit more "separation" of our work within local copies of the repo?

@AndrewTavis_WMDE Hi! I think you could go with simply wmde. The analytics prefix in product_analytics exists because the team is named like that. In your case, you could use wmde I think.
BTW this is the task to create the WMDE Airflow instance: T340648

We should avoid team names in functional code / namespacing. Team names change often.

However, wmde is more like 'wmf' in this case, and I think not likely to change. +1 to wmde. :)

Thanks both for the quick responses! I'll let @Manuel make the final call, but makes sense on my end :)

Sounds good to me, thx!

The wmde name goes way beyond the scope of our team (or even the software department). But if I understand correctly, we could still organize this further using subfolders (e.g. going by products). If this assumption is right, I would suggest that we start with wmde but place our teams Airflow DAGs only in a subfolder that we will definitely still own in the future (e.g. wmde/wikidata/dags). That way the top level folder structure still remains simple and we are prepared for potential future developments.

^ sounds good!

wikidata could be an okay name too. I like functional groupings. However, that might overlap with some dags that the search platform team does for wikidata in their search instance. ¯\_(ツ)_/¯

As the current structure of the repo is till now always the following:

data-engineering/airflow-dags/
    └─team_folder
        └─ config
            | config_file
            ...
        └─ dags
            └─ sub_project
                | sub_project_dag.py
                ...
            ...
    ...

I'd suggest that we stick with wmde in this case and we can organize ourselves within the wmde/dags directory as we progress :)

Yes, this makes sense to me as well. Let's go with only 'wmde' and we can add a wikidata subdirectory ourselves (see also task about setting up our Airflow instance T340648).

Checks that we could do to fix issues with users creating multiple user_agent would be:

• Switch this metric to counting unique ip values
• Count unique user_agent values, but only if they have a unique ip
  • If they have a non-unique ip, then they would only count as one user_agent

@Manuel, something else to explore would be to see if we could figure out a metric that links user_agent and ip. I'm a bit confused why we'd go through this and then still we have tons of unique individuals within Python's requests count. A breakdown:

• User has unique user_agent and ip
  • All's fine - select the user_agent/ip pair
• User has unique user_agent and multiple ip values
  • We're assuming that the ip value was changed by their provider and select one user_agent/ip pair
• User has a unique ip but is using multiple user_agent values
  • We select one of the user_agent/ip pairs assuming that it's unique and that the others are generated repeats

This basically boils down to:

• A selection of user_agent/ip pairs where each value can only occur once within the pairs

For the above we'd get a few extra values based on those users who are generating and getting their ip switched, but then this might be better than dramatically undershooting our total user base? Yes it changes the metric, but the goal is an estimation of usage that the Python requests user_agent is masking.

A selection of user_agent/ip pairs where each value can only occur once within the pairs

I don't think so: It's only the IP value that should only occur once. So a more robust metric than the original would likely not rely on user agents at all (e.g. unique IPs).

But in addition to a new metric, we also want to keep reporting the original metric (so that we do not have to officially change the metric through all levels of the org hierarchy). For this we will need to consider the following:

unique user_agent and unique ip

All is fine.

unique user_agent but multiple ip values

This will primarily be because of (multiple different) users using only default values for their user agent. Also, as you wrote, the same users' IPs changed by their provider. In the logic of the original metric we need to consider this as only one user_agent (likely underestimating what we are actually interested in).

unique ip but multiple user_agent values

If this happens too often (as we saw in the data recently) then this makes the original metric meaningless.. so if we wanted to continue to report something in the logic of the original metric, we would need to filter the additional user_agents out (and consider only one user_agent per IP).

And this boils down to user_agent/ip pairs where each value can only occur once in the whole time period considered (e.g. last month/30d).

Does this help?

I'm still a bit confused. The above comment starts with the following:

A selection of user_agent/ip pairs where each value can only occur once within the pairs

I don't think so: It's only the IP value that should only occur once. So a more robust metric than the original would likely not rely on user agents at all (e.g. unique IPs).

... and ends with this:

And this boils down to user_agent/ip pairs where each value can only occur once in the whole time period considered (e.g. last month/30d).

You're more saying that each individual user_agent or ip can only occur once, and I'm more on any combination cannot include both a user_agent and an ip that aren't unique. To clarify where I think this leaves us though, at the end of this we want user_agents, but we just want them to be unique based on ip values?

unique user_agent but multiple ip values

This will primarily be because of (multiple different) users using only default values for their user agent. Also, as you wrote, the same users' IPs changed by their provider. In the logic of the original metric we need to consider this as only one user_agent (likely underestimating what we are actually interested in).

Specifically I'm thinking about the first clause in your comment above where we have everyone lumped into Python requests. Is the purpose of this metric to define unique users of the API? If so, then dramatically undershooting it when we have a way of correcting it via pairing the values is a bit confusing. The big question for me is what are we trying to say in the story here? If at the end we're reporting unique users and we have the caveat that it's undershooting by thousands, then I think that for now it's ok to provide this information, but we really should consider reworking this metric. user_agent undershoots it by a lot and ip overshoots it by a lot, so neither really answers this story well.

Could be something that I could discuss with WMF though 🙃

In talking a bit with folks at the Data Platform Engineering Office Hours the general sentiment was that WMF is currently hashing user_agent and ip for "unique" users, but that there's work to be done here. There's a fair amount of entropy in this method, but then it does generally provide a better metric for reporting than either user_agent or ip alone. References on this:

• https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Traffic/Pageviews/Bots_Research
  • Directly makes the hash in the query (three years is old for this to be a direct example)
• https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Edits/Geoeditors
  • Has an already hashed user_agent and ip column value in user_fingerprint_or_name

The usage of (quote unquote) "unique" above comes from the fact that this is an imperfect process that is only getting more imperfect as the internet changes. Chrome for instance is apparently making changes to their user agents whereby there are more merges of users with the above methods, but they don't appear to have immediate plans to switch to anything else for reporting in the future. That user_agent appears to be getting only more problematic for the most popular browser would further point to a bit of a rethink on this. Continuing with the planned reporting is of course very reasonable though! 😊

Is the purpose of this metric to define unique users of the API?

The purpose of the metric is to approximate unique developers that are using the new API, yes.

If at the end we're reporting unique users and we have the caveat that it's undershooting by thousands, then I think that for now it's ok to provide this information, but we really should consider reworking this metric. user_agent undershoots it by a lot and ip overshoots it by a lot, so neither really answers this story well.

This exactly why wanted us to discuss potential alternatives to the original metric. I am not certain by how much IP overshoots (maybe by a lot, but maybe it's not that bad). We can also have more than one metric. There are also other aspects that are relevant, e.g. how much use there is per user.

Could be something that I could discuss with WMF though 🙃

Sure, they might have best practices about robustly measuring the unique developers that are using their public APIs, this would help us for sure.

In talking a bit with folks at the Data Platform Engineering Office Hours the general sentiment was that WMF is currently hashing user_agent and ip for "unique" users, but that there's work to be done here.

A hash of User Agent and IP would make no sense in our case, unfortunately. This would basically just result in a max overshoot.

So it seems that the best we have so far is unique IPs. It is not ideal, as it will undershoot e.g. scripts that run on cloud infrastructure, and overshoots e.g. scripts that run in the browser of end users or with dynamically assigned IPs, but it's a solid candidate to accompany the original unique user_agent metric.

For the original metric we need to filter out all user agents that follow the problematic pattern (e.g. by counting unique IPs instead of user agents whenever we see the problematic pattern).

For the initial report where we just want to count certain user_agent values as one via their ip:

• Match it with regular expressions within the SQL as the initial try
• Check Python packages that help with user_agent values
  • python-user-agents is for identifying devices through their user_agent data
  • user_agent generates random user_agent data (potentially what those users are using)
  • uap-python is for parsing user_agent data
• The date selection function I wrote needs to be changed to be explicit exclusion at the end of the time period
• More documentation is needed for the function for date WHERE clause

@Manuel, the task has now been updated with all the metrics. Took a bit for all the queries to run :) Let me know if you have further thoughts on the final subtask:

• Let's discuss what we can do to make the metric more robust and reliable (e.g. exclude browser user agents)

@AndrewTavis_WMDE: Thx for the update! We last aligned on completely removing all user agents that fit the malicious pattern. Are 126 for June and 122 for May the result of this? I am asking because the tasks description says "filtering out ips with user_agent values that match bot generated versions".

Hey @Manuel 👋 126 and 122 are a result of what it is that we aligned on, yes :) I just didn't update the description properly. Will do so now 😊

It's now the following:

filtering out user_agent values that match bot generated versions

Let me know if you'd like me to post the query here as well :)

Thank you!

About T341330:

• We last aligned on creating running 30 days only for unique IPs and not for user agents (as they are not robust enough for continuous monitoring).
• That means that we will need the unique user agents only for the 2023 reporting (per month, filtered plus potentially manual data cleaning).

Could you modify the subtask accordingly?

Sure, I'll head over to T341330 and edit the task so it's aligned with what we discussed :)

Are there still open questions that need my input or are we done and I only need to sign off (and close) the task?

• Let's discuss what we can do to make the metric more robust and reliable (e.g. exclude browser user agents)

The above was still do be done, but I'd say it's finished and this is good to close :)