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

Tuesday, May 27th

Today, I need to get some initial metrics in place for users across experimental conditions. First things first, I need to get some ballpark figures on token'd users. I'd like to split device tokens up by a few classes:

1. Previously registered editors (set of tokens) -- Account registered before experiment. Includes all tokens associated with user_id'd actions.
2. Newly registered editors (set of tokens) -- Account registered during the experiment. Includes all tokens associated with user_id'd actions.
3. Previously anonymous editors (single token) -- Token'd anon edit appears before experimental deployment.
4. New anonymous editors (single token) -- Token'd anon edit does not appear until after experimental deployment.

Precedence starts at 1. Can't be a new anonymous editor unless you aren't any of the other groups. --Halfak (WMF) (talk) 15:59, 27 May 2014 (UTC)

---

SELECT
    token_event.wiki,
    token_event.user_id,
    ssac.timestamp AS registration,
    token_event.token AS token,
    SUM(event = "account creation") AS account_creations,
    SUM(event = "revision") AS revisions,
    COUNT(*) AS total_events
FROM (
        SELECT
            wiki AS wiki,
            event_userId AS user_id,
            event_token AS token,
            "account creation" AS event
        FROM log.SignupExpAccountCreationComplete_8539421
        WHERE wiki IN ("enwiki", "dewiki", "frwiki", "itwiki")
    UNION ALL
        SELECT
            "enwiki" AS wiki,
            IFNULL(rev_user, ar_user) AS user_id,
            event_token AS token,
            "revision" AS event
        FROM log.TrackedPageContentSaveComplete_8535426
        LEFT JOIN enwiki.revision ON rev_id = event_revId
        LEFT JOIN enwiki.archive ON ar_rev_id = event_revId
        WHERE
            wiki = "enwiki" AND
            rev_user IS NOT NULL OR ar_user IS NOT NULL
    UNION ALL
        SELECT
            "dewiki" AS wiki,
            IFNULL(rev_user, ar_user) AS user_id,
            event_token AS token,
            "revision" AS event
        FROM log.TrackedPageContentSaveComplete_8535426
        LEFT JOIN dewiki.revision ON rev_id = event_revId
        LEFT JOIN dewiki.archive ON ar_rev_id = event_revId
        WHERE
            wiki = "dewiki" AND
            rev_user IS NOT NULL OR ar_user IS NOT NULL
    UNION ALL
        SELECT
            "frwiki" AS wiki,
            IFNULL(rev_user, ar_user) AS user_id,
            event_token AS token,
            "revision" AS event
        FROM log.TrackedPageContentSaveComplete_8535426
        LEFT JOIN frwiki.revision ON rev_id = event_revId
        LEFT JOIN frwiki.archive ON ar_rev_id = event_revId
        WHERE
            wiki = "frwiki" AND
            rev_user IS NOT NULL OR ar_user IS NOT NULL
    UNION ALL
        SELECT
            "itwiki" AS wiki,
            IFNULL(rev_user, ar_user) AS user_id,
            event_token AS token,
            "revision" AS event
        FROM log.TrackedPageContentSaveComplete_8535426
        LEFT JOIN itwiki.revision ON rev_id = event_revId
        LEFT JOIN itwiki.archive ON ar_rev_id = event_revId
        WHERE
            wiki = "itwiki" AND
            rev_user IS NOT NULL OR ar_user IS NOT NULL
) AS token_event
INNER JOIN ServerSideAccountCreation_5487345 ssac ON
    ssac.wiki = token_event.wiki AND
    ssac.event_userId = token_event.user_id
GROUP BY 1,2,3;

There we have it. Time to let it run and see what we get. --Halfak (WMF) (talk) 18:41, 27 May 2014 (UTC)

---

Getting this loaded into the staging database is taking longer than expected. So, in the meantime, I'm going to start writing some queries to gather label tokens. Next up is token stats.

SELECT
    wiki,
    token,
    MIN(timestamp) AS first_event,
    SUM(event = "revision") AS revisions,
    SUM(event = "creation complete") AS account_creations,
    SUM(event = "creation impression") AS creation_impressions,
    SUM(event = "button click") AS button_clicks,
    SUM(event = "CTA impression") AS cta_impressions,
    SUM(event = "link click") AS link_clicks
FROM (
        SELECT
            wiki,
            event_token AS token,
            timestamp,
            "revision" AS event
        FROM
            log.TrackedPageContentSaveComplete_8535426
        WHERE wiki IN ("enwiki", "dewiki", "itwiki", "frwiki")
        AND event_token IS NOT NULL
    UNION ALL
        SELECT
            wiki,
            event_token AS token,
            timestamp,
            "creation complete" AS event
        FROM
            log.SignupExpAccountCreationComplete_8539421
        WHERE wiki IN ("enwiki", "dewiki", "itwiki", "frwiki")
        AND event_token IS NOT NULL
    UNION ALL
        SELECT
            wiki,
            event_token AS token,
            timestamp,
            "creation impression" AS event
        FROM
            SignupExpAccountCreationImpression_8539445
        WHERE wiki IN ("enwiki", "dewiki", "itwiki", "frwiki")
        AND event_token IS NOT NULL
    UNION ALL
        SELECT
            wiki,
            event_token AS token,
            timestamp,
            "button click" AS event
        FROM
            log.SignupExpCTAButtonClick_8102619
        WHERE wiki IN ("enwiki", "dewiki", "itwiki", "frwiki")
        AND event_token IS NOT NULL
    UNION ALL
        SELECT
            wiki,
            event_token AS token,
            timestamp,
            "CTA impression" AS event
        FROM
            log.SignupExpCTAImpression_8101716
        WHERE wiki IN ("enwiki", "dewiki", "itwiki", "frwiki")
        AND event_token IS NOT NULL
    UNION ALL
        SELECT
            wiki,
            event_token AS token,
            timestamp,
            "link click" AS event
        FROM
            log.SignupExpPageLinkClick_8101692
        WHERE wiki IN ("enwiki", "dewiki", "itwiki", "frwiki")
        AND event_token IS NOT NULL
) AS token_events
GROUP BY 1,2

--Halfak (WMF) (talk) 15:02, 28 May 2014 (UTC)