The initial analysis of blocked edit attempts rate by wiki shows some mismatch. Post the info here for discussion and investigation.
The edit attempts block rate is defined as:
the number blocked edit attempts / estimated total edit attempts = the number blocked edit attempts / (the number of sampled edit attempts / sample rate)

But the data shows schemas captured more blocked edit attempts than the estimated total edit attempts on anonymous users. Take metawiki for example, on most of days between 2022-10-13 and 2022-11-08, the block rate is more than 100%. Especially, the blocked edit attempts between 2022-10-31 and 2022-11-04 are very high.

It leads us to question:

1. Is blocked edit attempt also captured in event.editattemptstep schema?
2. Are the sample rates below correct for anonymous users? The number blocked edit attempts is extracted from event.mediawiki_editattempt_block schema. The number of sampled edit attempts is extracted from event.editattemptstep schema. Currently, the info about sample rates we have are:

The sample rate of event.mediawiki_editattempt_block schema is 100%.
The sample rate of event.editattemptstep schema on metawiki is 6.25% according to https://meta.wikimedia.org/wiki/Schema_talk:VisualEditorFeatureUse#Sampling .

3. What else could cause the mismatch?

Query to extract data

WITH blocked_attempts AS (
SELECT `database` AS wiki_db, 
CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) AS event_date,
count(1) AS blocked_edit_attempts
FROM event.mediawiki_editattempt_block
WHERE year=2022 
-- event is avaiable since 2022-10-12. 2022-10-13 is the first day with complete data
AND CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) BETWEEN '2022-10-13' AND '2022-11-08'
AND `database`='metawiki'
-- anonymous users
AND performer.user_id == 0
GROUP BY `database` ,
CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))
),

edit_attempts AS (
SELECT wiki AS wiki_db, 
CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) AS event_date,
count(1) AS sampled_edit_attempts
FROM event.editattemptstep
WHERE year=2022 
-- Blocked edit attempts events have complete data since 2022-10-13, 
-- select the same timing window for analysis
AND CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) BETWEEN '2022-10-13' AND '2022-11-08'
-- init when user clicks edit
AND event.action='init' 
-- anonymous users
AND event.user_id == 0
-- The default sample rate is 6.25%. The events at default sample rate are identified by event.is_oversample=false. 
-- Some wikis has non-default sample rate. The additional sampled events are identified by event.is_oversample=true.
AND NOT event.is_oversample 
AND wiki='metawiki'
GROUP BY wiki,
CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))
)
SELECT t1.wiki_db,  t1.event_date, t1.blocked_edit_attempts,  t2.sampled_edit_attempts
FROM blocked_attempts AS t1
INNER JOIN edit_attempts AS t2 ON t1.wiki_db=t2.wiki_db 
AND t1.event_date=t2.event_date
ORDER BY t1.wiki_db,  event_date, t1.blocked_edit_attempts DESC
LIMIT 1000000

Hi @DavidL, @MNeisler, as we discussed, whether the blocked edit attempt is also captured in event.editattemptstep schema is different by interface. Therefore, I am looking into the breakdown by interface. However, I noticed that the string values of interface in mediawiki_editattempt_block schema and editattemptstep schema are different. Can you help to match them?

The string value of interface in two schemas are listed below.

Value of interface field in mediawiki_editattempt_block schema

Value of editor_interface field in editattemptstep schema

Sorry, we've wound up with some inconsistent naming here. Specifically, interface in the editattemptsblocked schema is much closer to integration in the editattemptstep schema.

That said, this is the actual mapping you'll need to distinguish them:

@DLynch, thank you for the info. I added one more column to document our discussion about how init events are stored in schema.

Here are some initial findings from the analysis of blocked editing attempts. The initial analysis focused on answering below questions:

• To what extent do blocks play a role in preventing potentially productive editors from making an edit?
• What countries do blocked edit attempts most frequently occur?
• What are the differences in frequency of blocked edit attempts by wiki, editing interface?

Note
The blocked edit attempts happened mostly on anonymous users. The block rate on anonymous users is high. Want to check with Editing team whether it's expected.
Code location:stat1005: /home/jiawang/share/T322682_blocked_edit_attempts_R_v01.ipynb
Date sheet of all wikis and countries: link

Please see the number of blocked edit attempts and block rate by interface below.

*Data timeframe: 2022-10-21 ~ 2022-11-08

Next steps

• Discuss the initial findings and deep dive.
• Analyze following questions
  • What are the differences in frequency of blocked edit attempts by platform?
  • What types of IP blocks (local/global, short-/long-term) are more frequently encountered?
  • How many distinct users are stopped from editing by a block?

@DLynch, After the adjustment to count for 1) different sampling rates across wikis , 2) wikieditor edit attempts not logged in EditAttemptStep, the block rate is still high on anonymous users as mentioned in T322682#8482611. On some of wikis, it is close to 99%. (link). @MNeisler and I feel the result is suspicious and want to double check with you. Is the sample rate before 2022-11-08 correct for anonymous users (mentioned in T322682#8429742)? What other possible causes? Thanks!

The blocked edit attempts happened mostly on anonymous users. The block rate on anonymous users is high. Want to check with Editing team whether it's expected.
Code location:stat1005: /home/jiawang/share/T322682_blocked_edit_attempts_R_v01.ipynb
Date sheet of all wikis and countries: link

Please see the number of blocked edit attempts and block rate by interface below.

User type	interface	blocked edit attempts	estimated_edit_attempts	estimated edit attempts include blocked	block rate
Logged-in	discussiontools	2505	147375	147375	1.70%
Logged-in	mobilefrontend	10167	1427847	1427847	0.71%
Logged-in	visualeditor	12205	1437034	1437034	0.85%
Logged-in	wikieditor	28732	6136066	6164798	0.47%
Anonymous	discussiontools	21412	97218	97218	22.02%
Anonymous	mobilefrontend	1703000	13579624	13579624	12.54%
Anonymous	visualeditor	503649	4526907	4526907	11.13%
Anonymous	wikieditor	19261335	31378933	50640268	38.04%

*Data timeframe: 2022-10-21 ~ 2022-11-08

I'd expect things to be heavily skewed towards blocks on loggedout wikieditor, because that's where all the automated crawlers are going to be hitting. e.g. a naïve spider crawling every link on wikipedia is going to hit the edit-link on every single page, and if its IP is blocked as many are.... Similarly, those spam scripts that just go around and try to submit every form they can find with links, etc.

Granted, that's a gut feeling rather than something I can back up with data.

In T322682#8482611, @jwang wrote:

Here are some initial findings from the analysis of blocked editing attempts. The initial analysis focused on answering below questions:

• To what extent do blocks play a role in preventing potentially productive editors from making an edit?
• What countries do blocked edit attempts most frequently occur?
• What are the differences in frequency of blocked edit attempts by wiki, editing interface?

Note
The blocked edit attempts happened mostly on anonymous users. The block rate on anonymous users is high. Want to check with Editing team whether it's expected.
Code location:stat1005: /home/jiawang/share/T322682_blocked_edit_attempts_R_v01.ipynb
Date sheet of all wikis and countries: link

Please see the number of blocked edit attempts and block rate by interface below.

User type	interface	blocked edit attempts	estimated_edit_attempts	estimated edit attempts include blocked	block rate
Logged-in	discussiontools	2505	147375	147375	1.70%
Logged-in	mobilefrontend	10167	1427847	1427847	0.71%
Logged-in	visualeditor	12205	1437034	1437034	0.85%
Logged-in	wikieditor	28732	6136066	6164798	0.47%
Anonymous	discussiontools	21412	97218	97218	22.02%
Anonymous	mobilefrontend	1703000	13579624	13579624	12.54%
Anonymous	visualeditor	503649	4526907	4526907	11.13%
Anonymous	wikieditor	19261335	31378933	50640268	38.04%

*Data timeframe: 2022-10-21 ~ 2022-11-08

@jwang are these numbers for all types of blocks, or for IP blocks specifically?

Next steps

• What types of IP blocks (local/global, short-/long-term) are more frequently encountered?

I am curious if there are any updates on this question.

@kostajh, Please see my answers below

are these numbers for all types of blocks, or for IP blocks specifically?

For all types of blocks

What types of IP blocks (local/global, short-/long-term) are more frequently encountered?

Based on the Aug 2023 monthly data,

• The majority of blocked edit attempts are due to IP and IP range blocks.
• The majority of blocked edit attempts are due to the blocks lasting longer longer than 6 months.

Here is the Aug 2023 monthly summary for blocked edit attempts by block types and block scope across all wikis.

The Aug 2023 monthly summary for blocked edit attempts by length of the block across all wikis.

query

SELECT block_type, block_scope, count(1) AS blocked_edit_attempts
FROM event.mediawiki_editattempt_block
WHERE year=2023 and month=8
GROUP BY block_type, block_scope
ORDER BY block_scope, blocked_edit_attempts DESC
LIMIT 1000

SELECT
CASE 
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=7 THEN '7 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=30 THEN '8-30 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=90 THEN '31-90 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=180 THEN '91-180 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=365 THEN '181-365 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=1095 THEN '1-3 years'
  ELSE '>3 years'
  END AS days_to_expire
, COUNT(1) AS blocked_edit_attempts
FROM event.mediawiki_editattempt_block
WHERE year=2023 and month=8
GROUP BY 
CASE 
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=7 THEN '7 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=30 THEN '8-30 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=90 THEN '31-90 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=180 THEN '91-180 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=365 THEN '181-365 days'
  WHEN DATEDIFF(to_date(block_expiry), to_date('2023-08-31'))<=1095 THEN '1-3 years'
  ELSE '>3 years'
  END
ORDER BY blocked_edit_attempts DESC
LIMIT 1000

In T322682#8550524, @DLynch wrote:

I'd expect things to be heavily skewed towards blocks on loggedout wikieditor, because that's where all the automated crawlers are going to be hitting. e.g. a naïve spider crawling every link on wikipedia is going to hit the edit-link on every single page, and if its IP is blocked as many are.... Similarly, those spam scripts that just go around and try to submit every form they can find with links, etc.

Granted, that's a gut feeling rather than something I can back up with data.

More or less confirmed with the notebook, created for T347463: Add rel=nofollow to edit links.

@jwang would you be able to re-run your edit attempt and account creation analyses, but filtering out edit attempt blocks with browser_family containing bot|Bot|spider|Spider? That would hopefully give us a clearer picture about humans being impacted by blocks, as there is such a high volume of blocked bot edit attempts in the data.