This query shows you how to get your weekly recurring users (WRU) that have at least 10 pageviews in any given week and log in for 4 consecutive weeks.

Weekly Recurring Users Query (Segment Page Calls)

/* 
  You'll need to update the following parts of the query:
  * [segment_souce_name] - Check the SQL settings in Segment to see the schema name
  * [addt'l filters] - If you have multiple web properties feeding into the same source, or maybe certain parts of the app you don't want included, you could add any other filters here using the various context attributes on the page calls.
    
  * Another issue you might have is if you have multiple Segment sources representing different parts of your product.
    If that's the case, you may need to do some sort of union of the pageview data in each weekly corhort, ex:
    ************
    select
      user_id
      , date_trunc('week', received_at) week
      , count(*) score
    from (
      -- You may want to be explict about the cols you're choosing (vs. 'select * from'), as the schemas might differ slightly.
      select user_id, received_at from [segment_souce_name_A].pages
      union all
      select user_id, received_at from [segment_souce_name_B].pages
     ) a
    ************
*/
-- WRU Query (4 consecutive weeks engagement):
select
  user_id
  , count(distinct week) weeks_used
  , sum(score) score
from (
  -- This base table gives us a row for each week. It provides the number of pageviews and a unqiue date for the week in question.
  -- We can then count the `week` column in the parent query to look for how many weeks engagement we want in the time period.
 select
      user_id
      , date_trunc('week', received_at) week
      , count(*) score
    from
      [segment_souce_name].pages a
    where
      received_at > current_date - interval '29 days'
--       [addt'l filters]
    group by
      1,2
    having
      -- Change this depending on what you want the min threshold of engagement/pageviews to be in a given week. Ten pageviews is actually pretty easy to hit.
      score > 10
  )
  -- You may need to left join any addt'l user data that helps you ID them (e.g., email) if they're not already in your page calls.
  -- A lot of times your own employee's will get included in this data, which you'll prob want to exclude.
group by 1
having
  -- This would require a strict 4 consecutive weeks, but see the query below this for a looser definition.
  -- You may want to loosen it up to account for things like people's vacations, or time away from the product generally.
  weeks_used >=4

-- WRU Query (8/12 weeks engagement):
select
  user_id
  , count(distinct week) weeks_used
  , sum(score) score
from (
 select
      user_id
      , date_trunc('week', received_at) week
      , count(*) score
    from
      [segment_souce_name].pages a
    where
      -- Now we'll look 12 weeks back instead
      received_at > current_date - interval '12 weeks'
--       [addt'l filters]
    group by
      1,2
    having
      score > 10
      -- If you want to raise the bar a bit, consider adding something like at least 2 days engagement in a week as well:
      and count(distinct date_trunc('day', received_at)) >= 2
  )
group by 1
having
  -- We up the requirement to 8 weeks out of the 12
  weeks_used >=8