Hi @Neil_P._Quinn_WMF , this task description says that we want to measure the number of articles translated by (new) editors that aren’t deleted in 30 days. In the "Surviving translations by newcomers" section of your notebook (T199342#5290129), you're using the revision_is_deleted field to identify whether a revision is deleted. I'm wondering if this needs to be fixed to fulfill the request of 30-day survival period. Also, I'm wondering whether the revision_is_deleted field would be updated if a page is deleted in the future.

In T194650#5290883, @chelsyx wrote:

Hi @Neil_P._Quinn_WMF , this task description says that we want to measure the number of articles translated by (new) editors that aren’t deleted in 30 days. In the "Surviving translations by newcomers" section of your notebook (T199342#5290129), you're using the revision_is_deleted field to identify whether a revision is deleted. I'm wondering if this needs to be fixed to fulfill the request of 30-day survival period. Also, I'm wondering whether the revision_is_deleted field would be updated if a page is deleted in the future.

Yeah, you are correct that I didn't do it exactly to spec! I think the difference likely to be small since translation deletion is likely to happen quickly if it happens at all, because the main reason would be simple (too much unmodified machine translation) rather than complex (notability), but it would definitely be more correct to fix that. There is the revision_deleted_by_page_deletion_timestamp field which should make that relatively easy.

Also, revision_is_deleted has now changed meaning as part of the big restructuring of how mediawiki_history handles deleted content: you'll want either page_is_deleted or revision_is_deleted_by_page_deletion. The two are close to the same, except that revision_is_deleted_by_page_deletion covers edge cases where the page was deleted and some revisions were undeleted, which are not likely to come up in this case. And, yes, if a revision is deleted by page deletion after a given snapshot, the field will be updated in future snapshots.

Hope that helps!

Thanks so much @Neil_P._Quinn_WMF ! Please let me know if you see any issues in the query below.
(I had some problems connecting to SWAP, so these are run in separate python scripts. Let me see if I can solve the problem and update the notebook later...)

Results:

Query:

select
    date_format(event_timestamp, "YYYY-MM") as month,
    if(array_contains(revision_tags, "contenttranslation-v2"), 2, 1) as cx_version,
    -- 6 months ≈ 26 weeks = 252 days
    if(
        coalesce(datediff(event_timestamp, ssac.dt) > 252, true),
        "experienced",
        "new"
    ) as user_experience,
    if(revision_is_deleted_by_page_deletion, "deleted", "survived") as status,
    count(*) as n_translations
from wmf.mediawiki_history mh
left join event_sanitized.serversideaccountcreation ssac
on
    ssac.event.username = event_user_text and
    ssac.year >= 0
where
    mh.snapshot = "2019-06" and
    mh.event_timestamp >= "2018-07" and
    event_entity = "revision" and
    event_type = "create" and
    array_contains(revision_tags, "contenttranslation")
group by date_format(event_timestamp, "YYYY-MM"), 
if(array_contains(revision_tags, "contenttranslation-v2"), 2, 1), 
if(
        coalesce(datediff(event_timestamp, ssac.dt) > 252, true),
        "experienced",
        "new"
    ), 
if(revision_is_deleted_by_page_deletion, "deleted", "survived")

In T194650#5305709, @chelsyx wrote:

Thanks so much @Neil_P._Quinn_WMF ! Please let me know if you see any issues in the query below.
(I had some problems connecting to SWAP, so these are run in separate python scripts. Let me see if I can solve the problem and update the notebook later...)

The query looks right!

Looking at the results, it seems odd that 2019-06 sees a big jump in translations by experienced users and a big drop in ones by new users. That makes me wonder if there's some sort of classification issue going on (maybe that's why you asked). But I don't see any issues with the query, and the numbers don't line up (~7000 more translations by experienced users, and ~1000 fewer by new users).