From @nettrom_WMF:

I think the way to go is to wait for wmf_raw.mediawiki_private_cu_changes to get sqooped in, then combine that with event.mediawiki_revision_create and event.mediawiki_user_blocks_change to get all the necessary data on edits and whether a block happened related to that edit. I think Martin’s code for determining proxy IPs is going to be good for this as well.

Moving this to Blocked for now since we're waiting for September data to become available.

Moving this back to Doing as the September snapshots are available. I'm planning to use wmf_raw.mediawiki_revision and wmf_raw.mediawiki_private_actor to identify whether the edit was made by a registered user or not as those are authoritative sources for that information. I'll use wmf_raw.mediawiki_private_cu_changes for browser and OS information.

One thing I was made aware of earlier today was that Global blocks are used to block open proxies. These are created on Meta-wiki and logged there with log_type = "gblblock". From what I can tell, these do not show up in event.mediawiki_user_blocks_change. I therefore plan to use wmf_raw.mediawiki_logging to identify local (to a given wiki) and global blocks and join that with the other data sources.

A first pass of this analysis is done and I'm moving this to Needs Review as I've asked @SNowick_WMF and @jwang to review my code.

In the end, I filtered wmf_raw.mediawiki_private_cu_changes on Safari edits and used an adaptation of Martin Urbanec's code to identify those made through the relay service. This reduces the problem to a very tractable size at which point one can add user information from the revision, archive, actor, and user_groups tables to label them as logged-in, bot by name, and bot by group. It's also then feasible to query the logging table on the respective wikis and metawiki to get potential blocks and identify whether they blocked the given IP as one can limit them in time by the edit timestamp (and I've been using a 6 hour threshold based on the analysis of the usefulness of IP edits).

I reviewed @nettrom_WMF 's process and ran the queries, got the same result as his initial findings. Thanks Morten for doing the research and work to get queries down to a reasonable (~3 hours) run time.

@nettrom_WMF, the code and algorithm look correct to me. I left message about the number of blocks on relay IPs in slack. Since it's not part of the review, we can discuss there.

@nettrom_WMF -- I put notes from our talk today as the "adjusted request" in the task description. Thank you!

We now have estimates for both of the questions in this task.

How widespread is the adoption of Relay among Safari users? How has it been changing over time?

I reused the code we developed to identify iOS 15 pageviews in pageview_actor, and set it up to generate daily aggregates on a per-project basis to a table in the Data Lake (nettrom_apple_relay.daily_usage_per_project). From this I built a couple of charts in Superset, one showing it across all projects, and one allowing for filtering on a per-project basis. We find increased adoption and usage of iOS 15 over time, as we'd expect. The overall percentage of iOS 15 pageviews coming in through the relay looks to be fairly stable around 3%.

How many edits are not happening because they are being blocked simply for coming from Relay IP addresses?

It's difficult to provide good estimates for this because we lack good data. There doesn't seem to be any logging in MediaWiki of blocked edit attempts, nor do we appear to have instrumentation that logs this.

In lieu of better data, I chose to estimate this by combining the iOS 15 pageviews from pageview_actor, using the same method as before, with iOS 15 edits from wmf_raw.mediawiki_private_cu_changes. In both cases we derive a boolean is_relay variable that defines whether the pageview or edit was made with an IP in the relay ranges. Using these datasets to make this estimate requires making a key assumption: the probability of clicking "Edit" and saving the edit once started is the same across both groups of users. This assumption might not hold, we might for instance expect users who are using the relay service to be more (or less) likely to click "Edit". With more time we might be able to get a reasonable estimate of that using web requests.

So, this is our best estimate for now. I used data from Sept 20 (iOS 15 release day) through Sept 30 as that data is readily available. I then find that the estimated mean number of edits per 1 million pageviews from iOS 15 when not using the relay is 241 edits (with a Standard Error of 1.3 edits). When using the relay, the same estimate is 9 edits (again with SE of 1.3 edits).

Over the 11-day period, this mean difference in probability comes out to 1,321 edits not being made in the relay group, or about 120 edits per day. As the number of pageviews increases with the adoption of iOS 15, the number of daily edits not being made also increases.

@MMiller_WMF : Do these estimates satisfy this request? If so, let's resolve this task and follow up on further measurement needs in future tasks.

I've created a GitHub repo for this work, where there's now the notebook for aggregating daily pageviews, and the notebook for estimating edits not being made.