Jump to content

KristieF

New Members
  • Content count

    4
  • Joined

  • Last visited

Community Reputation

0 Neutral

About KristieF

  • Rank
    Newbie
  1. KristieF

    Retention

    Barand, this worked! Many many thanks!!!
  2. KristieF

    Retention

    This site looks to have what I need, but it uses SQL, and I don't have enough knowledge to turn it into MySQL language. All I know is that it doesn't work when I copy/paste, substituting my tables called user and activity_log https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/
  3. KristieF

    Retention

    Barand, thank you for the reply! This is close, but what I'm looking for is: Of the users created in June, how many of those were active in Month 1 (July)? How many of those users were active in Month 2 (August)? etc. through current date. Of the users created in July, how many were active in their Month 1 (August)? How many of those users were active in their Month 2 (September)? etc. through current date. Etc. through current month of users created. Essentially creating output I can use to populate a waterfall chart: Month Created Month 0 Month 1 Month 2 Month 3 June 2 2 1 1 July 2 1 0 0 August 3 3 2 1
  4. KristieF

    Retention

    Hello - I'm trying to determine rolling user retention. I have a table called 'user' that contains the userId, and the date they created their account. I have a second table, activity_log, that also has the userId, and the dates they had activity. I'm looking for a count of userId's by month/year using the date their account was created. And then how many of those users were active a month from that date, then two months, then 3 months, etc. query 1 would look like: select count DATE_FORMAT(created, '%m %Y') AS DateCreated, userId from user where DATE(created) >= "2015-06-01" group by 1; Query 2 would then count how many of those users, by month and year, subsequently had a date in the activity_log from the user.created date +1, +2, +3, +4, etc. I've seen examples of this for SQL, by week, but I can't get it working in MySQL by month: WITH by_week AS (SELECT user_id, TD_DATE_TRUNC('week', login_time) AS login_week FROM logins GROUP BY 1, 2), with_first_week AS (SELECT user_id, login_week, FIRST_VALUE(login_week) OVER (PARTITION BY user_id ORDER BY login_week) AS first_week FROM by_week), with_week_number AS (SELECT user_id, login_week, first_week, (login_week - first_week) / (24 * 60 * 60 * 7) AS week_number FROM with_first_week) SELECT TD_TIME_FORMAT(first_week, 'yyyy-MM-dd') AS first_week, SUM(CASE WHEN week_number = 0 THEN 1 ELSE 0 END) AS week_0, SUM(CASE WHEN week_number = 1 THEN 1 ELSE 0 END) AS week_1, SUM(CASE WHEN week_number = 2 THEN 1 ELSE 0 END) AS week_2, SUM(CASE WHEN week_number = 3 THEN 1 ELSE 0 END) AS week_3, SUM(CASE WHEN week_number = 4 THEN 1 ELSE 0 END) AS week_4, SUM(CASE WHEN week_number = 5 THEN 1 ELSE 0 END) AS week_5, SUM(CASE WHEN week_number = 6 THEN 1 ELSE 0 END) AS week_6, SUM(CASE WHEN week_number = 7 THEN 1 ELSE 0 END) AS week_7, SUM(CASE WHEN week_number = 8 THEN 1 ELSE 0 END) AS week_8, SUM(CASE WHEN week_number = 9 THEN 1 ELSE 0 END) AS week_9 FROM with_week_number GROUP BY 1 ORDER BY 1
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.