@JAllemandou @GGoncalves-WMF can you please review this and what do you think about the proposed approach?

I can see user_agent_compliance_hourly is computed from webrequest by joining it with user_agent_compliance_classified_hourly.

How do we currently compute user_agent_compliance_classified_hourly? Is this logic still in a notebook?

As I understand it, the proposal is to create a table for "classified user agents that we saw in dumps logs" (dumps_user_agent_compliance_classified_hourly). This is separate from "classified user agents that we saw in webrequest" (which is presumably user_agent_compliance_classified_hourly).

Do we have a strong reason to keep those tables separate? Wouldn't we want to (for example) use a classification we got from webrequest to calculate compliance for dumps?

How do we currently compute user_agent_compliance_classified_hourly? Is this logic still in a notebook?

@GGoncalves-WMF The code for user_agent_compliance_classified_hourly is in this PySpark repo, which is used as an artifact in the DAG.

Do we have a strong reason to keep those tables separate? Wouldn't we want to (for example) use a classification we got from webrequest to calculate compliance for dumps?

Not really, and good point. There is some overlap, but there some agents in dumps logs that are not present in webrequest logs.

So the way the job repo is setup currently is, it takes a source table argument, currently defaults to webrequest, but can be changed. It extracts unique user-agents, determines compliance, and writes to user_agent_compliance_classified_hourly (as of now classified user agents that we saw in webrequest).

If having them in the same table is fine, I can change the PySpark code to accept multiple sources (can be helpful to expand in future), union and derive unique agents, which can be sent to determine compliance.

Thanks, that makes sense!

I think it makes sense to have one canonical table that contains structured data for the User-Agents we've seen anywhere (dumps logs as well as webrequest). We'd probably still want to keep the original source as a dimension to allow you to do analysis on dumps alone.

I would also like us to consider how this overlaps with bot detection, so that maybe we can consolidate some logic and definitions in one place.

Right now our "spider" classification contains a long regex of User-Agent strings, where a match means the agent is classified as "spider". The regex also ends with a rough "email" pattern, which a comment says meant to implement the UA policy. I think effectively that means that only compliant actors are classified as "spider". That is, the way we classify "spider" seems like a coarse version of the logic you're implementing.

I wonder if we shouldn't extract a single pipeline, and corresponding output table, that is the source of truth for:

• The regex we use for classifying something as a spider
• The logic we use for computing compliance with the UA policy
• Sub-classifications of spider, like the ones you have now in is_wikipedia_app_client (and there will be more to come in the next FY!)
• Other structured information extracted from the UA, like contact information.

Then KAPOW, our bot detection system, would just join with that. How does that sound?

I think it makes sense to have one canonical table that contains structured data for the User-Agents we've seen anywhere (dumps logs as well as webrequest). We'd probably still want to keep the original source as a dimension to allow you to do analysis on dumps alone.

That sounds good.

Yes, in the long term it would be great to consolidate into a single pipelines that can be reused across the board. I also have more use cases for known-clients and other client tracking. Let's chat more :)

I like the idea of having a single table grouping all UA data from various sources.
About the pipelining, I think it could be more a single definition/code, with multiple pipelines depending on how sources line up in terms of frequency for instance.

@JAllemandou and I discussed about the schema changes on Slack.

We agreed on one row per source approach, as opposed to using array<string> column for sources. In cases where we have a User-Agent appearing both in webrequest and dumps (or any other future sources), we will have different rows for each source. This approach will be helpful to accommodate any future use cases calculating of stats per source within the table, without having to do complex nesting.

It's also easier to filter this way, but we have to emphasize in documentation to explicit specify a source to avoid duplicates.

@GGoncalves-WMF how does that sound?

I will do the necessary changes after your confirmation.

I don't have a strong opinion on "one row per source", that's sensible if you two agree to it :) For what it's worth, in KAPOW, we'll probably not care about what is the source of a UA, we'd just join to that table based on (unique) UAs.

Let me try to flesh out the resulting table, then. How do you feel about something like this?

Where:

• category reflects the taxonomy that @Pablo is working on, to be finalized in a couple of weeks (e.g. "AI Assistant", or "Page Preview"). Until then, I guess this can be omitted/stay null.
• agent is the actual agent we've identified from the UA. is_instant_commons_agent, is_foreign_api_agent, is_wikipedia_app_agent can be folded into here, and we'd also flag more of Pablo's identified bots (e.g. chatgpt-bot) when ready.

And we can just call this table user_agent_info or something more generic. I do realize we're expanding the scope here a little, but I'm trying to see if we can have something reusable and extensible beyond WE5.2!

In T429280#12036689, @GGoncalves-WMF wrote:

I don't have a strong opinion on "one row per source", that's sensible if you two agree to it :) For what it's worth, in KAPOW, we'll probably not care about what is the source of a UA, we'd just join to that table based on (unique) UAs.
Let me try to flesh out the resulting table, then. How do you feel about something like this?

user_agent	source	user_agent_map	contact_method	contact_info	ua_policy_compliance_level	category	agent	ts

Cool. In principle, that sounds good to me. I'd love to have something like that and unify the work. Let's chat about this next week.

But in the meantime, trying to do that now, would be expanding the scope significantly, I think :)

We have two threads:

• We have the existing User-Agent compliance outputs and pipelines outputs and pipelines, which solely evaluates whether User-Agents are compliant with our policy or not.
  • The outputs are being used by various downstream pipelines like API analytics, media traffic etc. And now we need that for Dumps, as the source is different from webrequest.
• Ideally, we would like to have a table with consolidated User-Agent info that can be reused across the board. Possibly extending the current setup we have for determining compliance. That will require a broader set of changes, within this pipeline and also in downstream pipelines to adapt, which I fear might not possible in this month as the Dumps metrics should be operationalized in a few days (for objective metrics reporting). This is something we can work on, in early July.

As we plan to build a more comprehensive user_agent_info table, I think we can keep the changes to minimal now - if that works. I'd say, we skip changing the schema (like adding a source; as we can filter on join), and only change the PySpark code to accept an additional table as source.

• category reflects the taxonomy that @Pablo is working on, to be finalized in a couple of weeks (e.g. "AI Assistant", or "Page Preview"). Until then, I guess this can be omitted/stay null.

The taxonomy is heavily based on the classification provided by Cloudflare’s Radar bot directory (list of categories), so building a dictionary to add such a value should be straightforward

P.S. I am planning to share a first draft of the taxonomy for internal review by early next week.

Having spoken to @KCVelaga_WMF this morning, we can break up the work into a couple of chunks to keep the risk to WE5 manageable:

• In this ticket, we implement an initial version of user_agent_info with information extracted from webrequest and Dumps, but without incorporating additional taxonomy information.
• I've just filed T429789 to track the overall vision of including the richer taxonomy (and necessary schema changes), and making information from this table available where it's needed. I'll make that a parent of this task.

I've also asked KC to collaborate with @mforns to make sure this initial pass at the table can be integrated nicely with the rest of KAPOW.

Let me know if I'm missing anything!

Summary of planned changes from Slack discussion

• user_agent_info table
  • I will create a DDL for the new table
  • Check with Marcel for alignment with KAPOW
  • Add a DAG task to populate the new table.
  • The output will be used by Dumps metrics.
• regarding the existing user_agent_compliance_classified_hourly table: for the time being, we will continue populating this table as well, as the data in its current shape is being used by several downstream pipelines (at least 4) that are part of the ongoing objective metrics work.
• After 1 July:
  • I will update the downstream pipelines to use the new table; we will also have a few days of data by then.
  • After all the downstream pipelines are switched, we can stop populating: user_agent_compliance_classified_hourly (purge and drop it entirely after 90 days)
  • Continue planned work on user_agent_info (like taxonomy etc.)