Research talk:Anonymous editor acquisition/Signup CTA experiment/Work log/2014-05-28

Wednesday, May 28th

Latest comment: 10 years ago4 comments1 person in discussion

Today I'm bringing together the token work and generating the first funnel bits. First I need to write a query that combines the info I have on token'd users into user classes.

SELECT
    wiki,
    token,
    IF(RIGHT(token, 1) <= "J", "pre-edit", 
      IF(RIGHT(token, 1) <= "d", "post-edit", "control")) AS bucket,
    first_event,
    token_stats.revisions AS tokened_revisions,
    token_stats.account_creations,
    token_stats.creation_impressions,
    token_stats.button_clicks,
    token_stats.cta_impressions,
    token_stats.link_clicks,
    SUM(DISTINCT user_id) AS user_accounts,
    MIN(user_id) AS first_user_id,
    MIN(registration) AS first_user_registration
FROM staging.token_stats 
LEFT JOIN staging.user_token USING (wiki, token)
GROUP BY 1,2;

--Halfak (WMF) (talk) 16:24, 28 May 2014 (UTC)Reply

Looks like I made a mistake with the queries above. I didn't include an old version of the Schema:TrackedPageContentSaveComplete. So I need to include that now and re-run the queries (that took forever yesterday. Ugh.). --Halfak (WMF) (talk) 16:25, 28 May 2014 (UTC)Reply

I have data! OK. Time to pick through it.

> select count(*) from token_info;
+----------+
| count(*) |
+----------+
|  1384785 |
+----------+
1 row in set (0.00 sec)

About 1.4 million tokens appeared in since we started tracking data. Let's get the experiment bounds so that we can draw some cutoffs.

Query

> select left(timestamp, 10) as hour, count(*) from SignupExpCTAImpression_8101716 group by 1;
+------------+----------+
| hour       | count(*) |
+------------+----------+
| 2014041618 |        1 |
| 2014041720 |        1 |
| 2014041721 |        1 |
| 2014041722 |        1 |
| 2014041816 |        9 |
| 2014042120 |        6 |
| 2014042121 |        7 |
| 2014042505 |       36 |
| 2014042516 |        4 |
| 2014050204 |        4 |
| 2014050205 |        1 |
| 2014050206 |       10 |
| 2014050207 |       10 |
| 2014050323 |        5 |
| 2014050401 |       24 |
| 2014050502 |        8 |
| 2014050503 |        9 |
| 2014050504 |        7 |
| 2014051400 |        1 |
| 2014051402 |        3 |
| 2014051417 |        1 |
| 2014051520 |        2 |
| 2014051603 |        1 |
| 2014051604 |        4 |
| 2014051918 |      908 |
| 2014051919 |     1154 |
| 2014051920 |     1639 |
| 2014051921 |     1596 |
| 2014051922 |     1433 |
| 2014051923 |     1201 |
| 2014052000 |     1197 |
| 2014052001 |     1268 |
| 2014052002 |     1241 |
| 2014052003 |     1139 |
| 2014052004 |     1194 |
| 2014052005 |     1409 |
| 2014052006 |     1724 |
| 2014052007 |     2136 |
| 2014052008 |     2473 |
| 2014052009 |     2510 |
| 2014052010 |     2511 |
| 2014052011 |     2584 |
| 2014052012 |     2928 |
| 2014052013 |     3433 |
| 2014052014 |     3603 |
| 2014052015 |     3500 |
| 2014052016 |     3366 |
| 2014052017 |     3424 |
| 2014052018 |     3386 |
| 2014052019 |     3035 |
| 2014052020 |     2790 |
| 2014052021 |     2414 |
| 2014052022 |     1951 |
| 2014052023 |     1707 |
| 2014052100 |     1642 |
| 2014052101 |     1524 |
| 2014052102 |     1554 |
| 2014052103 |     1510 |
| 2014052104 |     1481 |
| 2014052105 |     1581 |
| 2014052106 |     1882 |
| 2014052107 |     2132 |
| 2014052108 |     2508 |
| 2014052109 |     2642 |
| 2014052110 |     2610 |
| 2014052111 |     2563 |
| 2014052112 |     3063 |
| 2014052113 |     3529 |
| 2014052114 |     3483 |
| 2014052115 |     3314 |
| 2014052116 |     3219 |
| 2014052117 |     3283 |
| 2014052118 |     3187 |
| 2014052119 |     2975 |
| 2014052120 |     2675 |
| 2014052121 |     2271 |
| 2014052122 |     1782 |
| 2014052123 |     1691 |
| 2014052200 |     1527 |
| 2014052201 |     1540 |
| 2014052202 |     1572 |
| 2014052203 |     1353 |
| 2014052204 |     1420 |
| 2014052205 |     1503 |
| 2014052206 |     1920 |
| 2014052207 |     2207 |
| 2014052208 |     2583 |
| 2014052209 |     2639 |
| 2014052210 |     2503 |
| 2014052211 |     2550 |
| 2014052212 |     2933 |
| 2014052213 |     3298 |
| 2014052214 |     3352 |
| 2014052215 |     3437 |
| 2014052216 |     3233 |
| 2014052217 |     3070 |
| 2014052218 |     2876 |
| 2014052219 |     2594 |
| 2014052220 |     2512 |
| 2014052221 |     2143 |
| 2014052222 |     1703 |
| 2014052223 |     1541 |
| 2014052300 |     1534 |
| 2014052301 |     1384 |
| 2014052302 |     1340 |
| 2014052303 |     1162 |
| 2014052304 |     1150 |
| 2014052305 |     1329 |
| 2014052306 |     1658 |
| 2014052307 |     1894 |
| 2014052308 |     2026 |
| 2014052309 |     2174 |
| 2014052310 |     2149 |
| 2014052311 |     2154 |
| 2014052312 |     2435 |
| 2014052313 |     2859 |
| 2014052314 |     2796 |
| 2014052315 |     2775 |
| 2014052316 |     2686 |
| 2014052317 |     2463 |
| 2014052318 |     2368 |
| 2014052319 |     2369 |
| 2014052320 |     2292 |
| 2014052321 |     1918 |
| 2014052322 |     1503 |
| 2014052323 |     1325 |
| 2014052400 |     1122 |
| 2014052401 |     1122 |
| 2014052402 |     1060 |
| 2014052403 |     1110 |
| 2014052404 |     1178 |
| 2014052405 |     1230 |
| 2014052406 |     1293 |
| 2014052407 |     1527 |
| 2014052408 |     1747 |
| 2014052409 |     1819 |
| 2014052410 |     1751 |
| 2014052411 |     1869 |
| 2014052412 |     2049 |
| 2014052413 |     2227 |
| 2014052414 |     2292 |
| 2014052415 |     2366 |
| 2014052416 |     2201 |
| 2014052417 |     2117 |
| 2014052418 |     2008 |
| 2014052419 |     2026 |
| 2014052420 |     1888 |
| 2014052421 |     1869 |
| 2014052422 |     1542 |
| 2014052423 |     1397 |
| 2014052500 |     1288 |
| 2014052501 |     1266 |
| 2014052502 |     1170 |
| 2014052503 |     1086 |
| 2014052504 |     1184 |
| 2014052505 |     1179 |
| 2014052506 |     1321 |
| 2014052507 |     1480 |
| 2014052508 |     1745 |
| 2014052509 |     1871 |
| 2014052510 |     1905 |
| 2014052511 |     1970 |
| 2014052512 |     2125 |
| 2014052513 |     2246 |
| 2014052514 |     2424 |
| 2014052515 |     2679 |
| 2014052516 |     2506 |
| 2014052517 |     2600 |
| 2014052518 |     2446 |
| 2014052519 |     2273 |
| 2014052520 |     2266 |
| 2014052521 |     1913 |
| 2014052522 |     1578 |
| 2014052523 |     1581 |
| 2014052600 |     1429 |
| 2014052601 |     1419 |
| 2014052602 |     1353 |
| 2014052603 |     1267 |
| 2014052604 |     1314 |
| 2014052605 |     1334 |
| 2014052606 |     1705 |
| 2014052607 |     1996 |
| 2014052608 |     2317 |
| 2014052609 |     2419 |
| 2014052610 |     2463 |
| 2014052611 |     2478 |
| 2014052612 |     2526 |
| 2014052613 |     2489 |
| 2014052614 |     2688 |
| 2014052615 |     2968 |
| 2014052616 |     3017 |
| 2014052617 |     2929 |
| 2014052618 |     2787 |
| 2014052619 |     2654 |
| 2014052620 |     2463 |
| 2014052621 |     2127 |
| 2014052622 |     1851 |
| 2014052623 |     1662 |
| 2014052700 |     1609 |
| 2014052701 |     1521 |
| 2014052702 |     1551 |
| 2014052703 |     1320 |
| 2014052704 |     1482 |
| 2014052705 |     1497 |
| 2014052706 |     1909 |
| 2014052707 |     2085 |
| 2014052708 |     2230 |
| 2014052709 |     2620 |
| 2014052710 |     2559 |
| 2014052711 |     2486 |
| 2014052712 |     2720 |
| 2014052713 |     2794 |
| 2014052714 |     2810 |
| 2014052715 |     3158 |
| 2014052716 |     3060 |
| 2014052717 |     3077 |
| 2014052718 |     2898 |
| 2014052719 |     2942 |
| 2014052720 |     2899 |
| 2014052721 |     2520 |
| 2014052722 |     1979 |
| 2014052723 |     1077 |
| 2014052800 |      847 |
| 2014052801 |      764 |
| 2014052802 |      626 |
| 2014052803 |      518 |
| 2014052804 |      490 |
| 2014052805 |      467 |
| 2014052806 |      391 |
| 2014052807 |      366 |
| 2014052808 |      254 |
| 2014052809 |      194 |
| 2014052810 |      136 |
| 2014052811 |      139 |
| 2014052812 |      173 |
| 2014052813 |      169 |
| 2014052814 |      199 |
| 2014052815 |      198 |
| 2014052816 |      165 |
| 2014052817 |      160 |
| 2014052818 |      148 |
| 2014052819 |       58 |
+------------+----------+
242 rows in set (0.93 sec)

It looks like the experiment started in the 18th hour of 2014-05-19 and began to ramp down in the 23rd hour of 2014-05-27.

> select left(timestamp, 11) as ten_minutes, count(*) from SignupExpCTAImpression_8101716 where timestamp LIKE "2014051918%" group by 1;
+-------------+----------+
| ten_minutes | count(*) |
+-------------+----------+
| 20140519180 |       30 |
| 20140519181 |      171 |
| 20140519182 |      179 |
| 20140519183 |      198 |
| 20140519184 |      155 |
| 20140519185 |      175 |
+-------------+----------+
6 rows in set (0.00 sec)

Looks like we're looking for the first 10 minutes of the hour.

> select left(timestamp, 12) as minutes, count(*) from SignupExpCTAImpression_8101716 where timestamp LIKE "20140519180%" group by 1;
+--------------+----------+
| minutes      | count(*) |
+--------------+----------+
| 201405191805 |        1 |
| 201405191806 |        1 |
| 201405191807 |        6 |
| 201405191808 |        9 |
| 201405191809 |       13 |
+--------------+----------+
5 rows in set (0.00 sec)

I think that "201405191807" is the closest we're going to get. It's just aim for the next minute to be sure and say the experiment started at 20140519180800 or 2014-05-19 18:08:00 UTC.

So, for the sake of consistency and dealing with the periodic nature of the work week, I need to limit this to 7 days, so, my assumed end date will be 20140526180800 or 2014-05-16 18:00:00 UTC.

Let me make this bold.

Assumed start and end timestamps

start: 20140519180800
end: 20140526180800

--Halfak (WMF) (talk) 22:42, 28 May 2014 (UTC)Reply

First results

OK, here's the money. These represent counts of tokens based on user classes.

pure anon -- A token that was never associated with a registered account.
old user -- A token that is associated with an account that was registered before tracking began
tracked user -- A token that is associated with an account that was registered after tracking began, but before the experiment
experimental user -- A token that is associated with an account that was registered during the experiment

> SELECT
    ->     wiki,
    ->     bucket,
    ->     IF(first_user_id IS NULL,
    ->         "pure anon",
    ->         IF(first_user_registration IS NULL OR first_user_registration <= 20140502000000,
    ->         "old user",
    ->         IF(first_user_registration <= 20140519180800,
    ->         "tracked user",
    ->         "experiment user"))) AS editor_class,
    ->     COUNT(*) AS tokens
    -> FROM staging.token_info
    -> GROUP BY 1,2,3;
+--------+-----------+-----------------+--------+
| wiki   | bucket    | editor_class    | tokens |
+--------+-----------+-----------------+--------+
| dewiki | control   | experiment user |    723 |
| dewiki | control   | old user        |   4577 |
| dewiki | control   | pure anon       |  60441 |
| dewiki | control   | tracked user    |    630 |
| dewiki | post-edit | experiment user |    711 |
| dewiki | post-edit | old user        |   4342 |
| dewiki | post-edit | pure anon       |  54728 |
| dewiki | post-edit | tracked user    |    566 |
| dewiki | pre-edit  | experiment user |    998 |
| dewiki | pre-edit  | old user        |   4415 |
| dewiki | pre-edit  | pure anon       |  55410 |
| dewiki | pre-edit  | tracked user    |    589 |
| enwiki | control   | experiment user |  11633 |
| enwiki | control   | old user        |  22739 |
| enwiki | control   | pure anon       | 296263 |
| enwiki | control   | tracked user    |   6924 |
| enwiki | post-edit | experiment user |  11047 |
| enwiki | post-edit | old user        |  20737 |
| enwiki | post-edit | pure anon       | 270362 |
| enwiki | post-edit | tracked user    |   6179 |
| enwiki | pre-edit  | experiment user |  14520 |
| enwiki | pre-edit  | old user        |  21651 |
| enwiki | pre-edit  | pure anon       | 275562 |
| enwiki | pre-edit  | tracked user    |   6386 |
| frwiki | control   | experiment user |   1041 |
| frwiki | control   | old user        |   3293 |
| frwiki | control   | pure anon       |  46145 |
| frwiki | control   | tracked user    |    747 |
| frwiki | post-edit | experiment user |   1143 |
| frwiki | post-edit | old user        |   3052 |
| frwiki | post-edit | pure anon       |  41450 |
| frwiki | post-edit | tracked user    |    718 |
| frwiki | pre-edit  | experiment user |   1491 |
| frwiki | pre-edit  | old user        |   3144 |
| frwiki | pre-edit  | pure anon       |  42603 |
| frwiki | pre-edit  | tracked user    |    696 |
| itwiki | control   | experiment user |    382 |
| itwiki | control   | old user        |   1604 |
| itwiki | control   | pure anon       |  27973 |
| itwiki | control   | tracked user    |    309 |
| itwiki | post-edit | experiment user |    470 |
| itwiki | post-edit | old user        |   1546 |
| itwiki | post-edit | pure anon       |  25205 |
| itwiki | post-edit | tracked user    |    252 |
| itwiki | pre-edit  | experiment user |    688 |
| itwiki | pre-edit  | old user        |   1616 |
| itwiki | pre-edit  | pure anon       |  26844 |
| itwiki | pre-edit  | tracked user    |    240 |
+--------+-----------+-----------------+--------+
48 rows in set, 65535 warnings (3.71 sec)

Here, we can already see some differences between the experimental and control conditions become apparent. Let's take itwiki for example.

condition	pure anon	old user	tracked user	experiment user
control	27973	1604	309	382
pre-edit	26844	1616	240	688
post-edit	25205	1546	252	470

Here, we see that about 33% more users registered during the experiment in the pre-edit condition than the control. About 25% more user registered during the experiment in the post-edit condition than the control.

OK. That's about it for today. I'll be limiting the pure-anons and experimental users to the experimental time bounds tomorrow to do a more accurate comparison. --Halfak (WMF) (talk) 23:50, 28 May 2014 (UTC)Reply

Add topic