Get all free trials that were not already paying

[anmurali]

WITH publicDomains AS (
SELECT SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) as domain
FROM accounts a 
WHERE SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) IN ('accountant.com','afis.ch','aol.com','applausecard.expensifail.com','artlover.com','asia.com','att.net','bellsouth.net','bills.expensify.com','btinternet.com','cheerful.com','chromeexpensify.com','comcast.net','consultant.com','contractor.com','cox.net','cpa.com','cryptohistoryprice.com','dr.com','email.com','engineer.com','europe.com','evernote.user','execs.com','expensifail.com','expensify.cash','expensify.sms','gamail.com','gmail.com','gmail.con','googlemail.com','hey.com','hotmail.co.uk','hotmail.com','hotmail.fr','hotmail.it','icloud.com','iname.com','jeeviess.com','jioooijojiiooioijoijijo.com','live.co.uk','live.com','mac.com','mail.com','mail.ru','mailfence.com','me.com','msn.com','musician.org','myself.com','outlook.com','pacbell.net','pm.me','post.com','privaterelay.appleid.com','proton.me','protonmail.ch','protonmail.com','Public Domains','qq.com','rigl.ch','sasktel.net','sbcglobal.net','spacehotline.com','tafmail.com','techie.com','usa.com','verizon.net','vomoto.com','wolfandcranebar.tech','workmail.com','writeme.com','yahoo.ca','yahoo.co.au','yahoo.co.in','yahoo.co.uk','yahoo.com','yahoo.com.br','ymail.com','tulanewcostarica.com', 'tulacostarica.com')
),

freeTrialAccounts AS (
SELECT a.email as email, 
   SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) as domain,
   nvp.value as freeTrialStart, 
   CASE WHEN pd.domain IS NOT NULL THEN 1 ELSE 0 END AS isPublic
FROM accounts a
INNER JOIN namevaluepairs nvp ON a.accountID = nvp.accountID AND nvp.name= 'private_firstDayFreeTrial' AND nvp.created > '2018-01-01'
LEFT JOIN publicDomains pd on SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) = pd.domain
),

freeTrialsWithPurchInfo AS (
    SELECT 
        ft.email,
        ft.domain,
        ft.freeTrialStart,
        ft.isPublic,
        MIN(p.created) AS firstPaymentDate
    FROM 
        freeTrialAccounts ft
    LEFT JOIN purchases p 
         INNER JOIN accounts a ON a.accountID = p.accountID
    ON 
         (ft.isPublic = 1 AND ft.email = a.email) OR 
         (ft.isPublic = 0 AND ft.domain = SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1))
    GROUP BY 
        ft.email, ft.domain
)
SELECT count(DISTINCT(fp.email)) as numFreetrials, strftime('%Y-%m', fp.freeTrialStart) as monthFreeTrialStarted
FROM freeTrialsWithPurchInfo fp
WHERE firstPaymentDate IS NULL OR firstPaymentDate > freeTrialStart;

Issue Owner

Current Issue Owner: @anmurali

[cead22]

WITH publicDomains AS (
    SELECT distinct SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) as publicDomain
    FROM accounts a
    WHERE SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) IN ('accountant.com','afis.ch','aol.com','applausecard.expensifail.com','artlover.com','asia.com','att.net','bellsouth.net','bills.expensify.com','btinternet.com','cheerful.com','chromeexpensify.com','comcast.net','consultant.com','contractor.com','cox.net','cpa.com','cryptohistoryprice.com','dr.com','email.com','engineer.com','europe.com','evernote.user','execs.com','expensifail.com','expensify.cash','expensify.sms','gamail.com','gmail.com','gmail.con','googlemail.com','hey.com','hotmail.co.uk','hotmail.com','hotmail.fr','hotmail.it','icloud.com','iname.com','jeeviess.com','jioooijojiiooioijoijijo.com','live.co.uk','live.com','mac.com','mail.com','mail.ru','mailfence.com','me.com','msn.com','musician.org','myself.com','outlook.com','pacbell.net','pm.me','post.com','privaterelay.appleid.com','proton.me','protonmail.ch','protonmail.com','qq.com','rigl.ch','sasktel.net','sbcglobal.net','spacehotline.com','tafmail.com','techie.com','usa.com','verizon.net','vomoto.com','wolfandcranebar.tech','workmail.com','writeme.com','yahoo.ca','yahoo.co.au','yahoo.co.in','yahoo.co.uk','yahoo.com','yahoo.com.br','ymail.com','tulanewcostarica.com', 'tulacostarica.com')
),
freeTrialAccounts AS (
    SELECT
        a.accountID,
        a.email,
       SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) as domain,
       nvp.value freeTrialStart,
       pd.publicDomain IS NOT NULL isPublic
    FROM accounts a
    INNER JOIN namevaluepairs nvp ON a.accountID = nvp.accountID AND nvp.name= 'private_firstDayFreeTrial' AND nvp.created > '2018-01-01'
    LEFT JOIN publicDomains pd ON domain = pd.publicDomain
),
purchaseEmails AS (
    SELECT p.created, p.purchaseID, a.email
    FROM purchases p INNER JOIN accounts a ON p.accountID = a.accountID
    WHERE JSON_EXTRACT(p.message, '$.billingType') IN ('yearly2018', 'monthly2018', 'invoicing2018', 'failed_2018')
),
firstPurchase as (
    select ft.email, ft.domain, ft.freeTrialStart, isPublic, p.purchaseID firstPurchaseID, min(p.created) firstPurchaseDate
    FROM freeTrialAccounts ft LEFT JOIN purchaseEmails p ON (
        (ft.isPublic AND SUBSTR(SUBSTR(p.email, INSTR(p.email, '@') + 1), INSTR(SUBSTR(p.email, INSTR(p.email, '@') + 1), '@') + 1) = ft.domain)
        OR
        (NOT isPublic AND p.email = ft.email)
    )
    group by 1, 2
)
SELECT count(DISTINCT(fp.email)) as numFreetrials, strftime('%Y-%m', fp.freeTrialStart) as monthFreeTrialStarted
FROM firstPurchase fp
WHERE firstPurchaseDate IS NULL OR firstPurchaseDate > freeTrialStart;

[cead22]

WITH publicDomains AS (
    SELECT distinct SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) as publicDomain
    FROM accounts a
    WHERE SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) IN ('accountant.com','afis.ch','aol.com','applausecard.expensifail.com','artlover.com','asia.com','att.net','bellsouth.net','bills.expensify.com','btinternet.com','cheerful.com','chromeexpensify.com','comcast.net','consultant.com','contractor.com','cox.net','cpa.com','cryptohistoryprice.com','dr.com','email.com','engineer.com','europe.com','evernote.user','execs.com','expensifail.com','expensify.cash','expensify.sms','gamail.com','gmail.com','gmail.con','googlemail.com','hey.com','hotmail.co.uk','hotmail.com','hotmail.fr','hotmail.it','icloud.com','iname.com','jeeviess.com','jioooijojiiooioijoijijo.com','live.co.uk','live.com','mac.com','mail.com','mail.ru','mailfence.com','me.com','msn.com','musician.org','myself.com','outlook.com','pacbell.net','pm.me','post.com','privaterelay.appleid.com','proton.me','protonmail.ch','protonmail.com','qq.com','rigl.ch','sasktel.net','sbcglobal.net','spacehotline.com','tafmail.com','techie.com','usa.com','verizon.net','vomoto.com','wolfandcranebar.tech','workmail.com','writeme.com','yahoo.ca','yahoo.co.au','yahoo.co.in','yahoo.co.uk','yahoo.com','yahoo.com.br','ymail.com','tulanewcostarica.com', 'tulacostarica.com')
),
freeTrialAccounts AS (
    SELECT
        a.accountID,
        a.email,
       SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) as domain,
       nvp.value freeTrialStart,
       pd.publicDomain IS NOT NULL isPublic
    FROM accounts a
    INNER JOIN namevaluepairs nvp ON a.accountID = nvp.accountID AND nvp.name= 'private_firstDayFreeTrial' AND nvp.created > '2018-01-01'
    LEFT JOIN publicDomains pd ON domain = pd.publicDomain
    WHERE SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) NOT IN ('expensify.com', 'expensifail.com', 'applause.expensifail.com', 'applausecard.expensifail.com', 'expensicorp.com')
    AND a.email NOT LIKE '%+%'
),
purchaseEmails AS (
    SELECT min(p.created) firstPurchaseDate, p.purchaseID, a.email
    FROM purchases p INNER JOIN accounts a ON p.accountID = a.accountID
    WHERE JSON_EXTRACT(p.message, '$.billingType') IN ('yearly2018', 'monthly2018', 'invoicing2018', 'failed_2018')
    GROUP BY 3
),
firstPurchase as (
    select p.firstPurchaseDate, ft.email, ft.domain, ft.freeTrialStart, isPublic, p.purchaseID firstPurchaseID
    FROM freeTrialAccounts ft LEFT JOIN purchaseEmails p ON (
        (ft.isPublic AND SUBSTR(SUBSTR(p.email, INSTR(p.email, '@') + 1), INSTR(SUBSTR(p.email, INSTR(p.email, '@') + 1), '@') + 1) = ft.domain)
        OR
        (NOT isPublic AND p.email = ft.email)
    )
)
SELECT count(DISTINCT(fp.email)) as numFreetrials, strftime('%Y-%m', fp.freeTrialStart) as monthFreeTrialStarted
FROM firstPurchase fp
WHERE firstPurchaseDate IS NULL OR firstPurchaseDate > freeTrialStart;

[anmurali]

@cead22 can you actually just run these separately for me? I will do the necessary computation in a spreadsheet.

Free trials started by users that started on Classic (as anyone on ND is testing and isn't a real lead):

SELECT
    a.accountID,
    a.email,
    a.created as signUpDate,
   SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) as domain,
   n1.value freeTrialStart
FROM accounts a
INNER JOIN namevaluepairs n1 ON a.accountID = n1.accountID AND n1.name= 'private_firstDayFreeTrial' AND n1.created > '2018-01-01'
INNER JOIN nameValuePairs n2 ON n2.accountID = a.accountID AND n2.name = 'private_referer' AND n2.value NOT IN ('ecash', 'GoogleSignIn', 'AppleSignIn', '(share policy)')
WHERE SUBSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), INSTR(SUBSTR(a.email, INSTR(a.email, '@') + 1), '@') + 1) NOT IN ('expensify.com', 'team.expensify.com', 'applausemail.com', 'applause.expensifail.com', 'expensifail.com')
    AND a.email NOT LIKE '%+%';

Purchases by first billing date

SELECT a.email, min(p.created)
FROM purchases p
INNER JOIN accounts a ON a.accountID = p.accountID AND p.created > '2018-01-01' AND JSON_EXTRACT(p.message, '$.billingType') IN ('yearly2018', 'monthly2018', 'invoicing2018', 'failed_2018')
group by 1

[cead22]

I DM'ed results to Anu on slack