Research talk:Anonymous editor acquisition/Signup CTA experiment/Work log/2014-06-13

Friday, June 13th[edit]

Just one more bit that I didn't get to last night before I move on. The proportion of tokened anonymous clients who both clicked edit and completed at least one edit:

Edit completion rates. The proportion of anonymous users who both clicked edit and completed at least one edit during the experimental period is plotted by condition and wiki.

It looks like the difference here is even more pronounced. This seems to solidly support Hyp 4. --Halfak (WMF) (talk) 14:02, 13 June 2014 (UTC)[reply]

OK, so now I need to figure out how many edits were reverted. I want to see if the edits that were lost due to the pre-edit condition were less productive. So I'll be generating a revert rate and an overall productive edit count. This is going to take a while since I'll need to look at so many edits. Let's see how many.

mysql:halfak@db1047.eqiad.wmnet [staging]> select count(*) from token_revision INNER JOIN token_info USING (wiki, token) WHERE (first_user_id IS NULL OR first_user_registration > "20140519180800") AND edit_link_clicks > 0 AND timestamp BETWEEN "20140519180800" AND "20140526180800";
+----------+
| count(*) |
+----------+
|   341956 |
+----------+
1 row in set (3 min 55.52 sec)

That's tractable. Cool! I should be able to do this in a reasonable amount of time. Now to gather the revisions so that I can run them through a revert detector. I think I'll be limiting this to the main namespace too.

So, that's going to require me to move all of the tables I have from the current server to the new storage server where I can join against local databases. --Halfak (WMF) (talk) 16:08, 13 June 2014 (UTC)[reply]

While I'm waiting for that move to finish, I can work on the query that will gather the revisions I need to check.

SELECT
    wiki,
    token,
    rev_id,
    page_id,
    page_namespace
FROM token_revision 
INNER JOIN token_info USING (wiki, token)
INNER JOIN enwiki.revision USING (rev_id)
INNER JOIN enwiki.page ON page_id = rev_page
WHERE 
    wiki = "enwiki" AND
    (first_user_id IS NULL OR first_user_registration > "20140519180800") AND 
    edit_link_clicks > 0 AND 
    timestamp BETWEEN "20140519180800" AND "20140526180800"
UNION
SELECT
    wiki,
    token,
    rev_id,
    page_id,
    page_namespace
FROM token_revision 
INNER JOIN token_info USING (wiki, token)
INNER JOIN dewiki.revision USING (rev_id)
INNER JOIN dewiki.page ON page_id = rev_page
WHERE 
    wiki = "dewiki" AND
    (first_user_id IS NULL OR first_user_registration > "20140519180800") AND 
    edit_link_clicks > 0 AND 
    timestamp BETWEEN "20140519180800" AND "20140526180800"
UNION
SELECT
    wiki,
    token,
    rev_id,
    page_id,
    page_namespace
FROM token_revision 
INNER JOIN token_info USING (wiki, token)
INNER JOIN itwiki.revision USING (rev_id)
INNER JOIN itwiki.page ON page_id = rev_page
WHERE 
    wiki = "itwiki" AND
    (first_user_id IS NULL OR first_user_registration > "20140519180800") AND 
    edit_link_clicks > 0 AND 
    timestamp BETWEEN "20140519180800" AND "20140526180800"
UNION
SELECT
    wiki,
    token,
    rev_id,
    page_id,
    page_namespace
FROM token_revision 
INNER JOIN token_info USING (wiki, token)
INNER JOIN frwiki.revision USING (rev_id)
INNER JOIN frwiki.page ON page_id = rev_page
WHERE 
    wiki = "frwiki" AND
    (first_user_id IS NULL OR first_user_registration > "20140519180800") AND 
    edit_link_clicks > 0 AND 
    timestamp BETWEEN "20140519180800" AND "20140526180800";

That looks terrible, but it's brilliant that I can do it at all. --Halfak (WMF) (talk) 16:08, 13 June 2014 (UTC)[reply]