MoM Retention rate is calculated by grouping users into “cohorts” according to the first time they transacted (month, quarter or year) and tracking what percentage of the original cohort remains over time. Understanding retention rates of monthly cohorts is vital to the health of a project as a fast growth rate in new users can mask high churn rates in older, smaller cohorts. It's only when growth slows down that this “leaky bucket” becomes obvious - something we've seen with the steep decline of many projects.

with user_cohorts as (
    SELECT  tx_sender as address
            , min(date_trunc('month', signed_at)) as cohortMonth
    FROM blockchains.all_chains
    WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
        AND chain_name = 'avalanche_mainnet'
        AND signed_at >  '2021-11-12'
    GROUP BY address
),
following_months as (
    SELECT  tx_sender as address
            , date_diff('month', uc.cohortMonth, date_trunc('month', signed_at))  as month_number
    FROM blockchains.all_chains
    LEFT JOIN user_cohorts uc ON address = uc.address
    WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
        AND chain_name = 'avalanche_mainnet'
        AND signed_at >  '2021-11-12'
    GROUP BY address, month_number
),
cohort_size as (
    SELECT  uc.cohortMonth as cohortMonth
            , count(*) as num_users
    FROM user_cohorts uc
    GROUP BY cohortMonth
    ORDER BY cohortMonth
),
retention_table as (
    SELECT  c.cohortMonth as cohortMonth
            , o.month_number as month_number
            , count(*) as num_users
    FROM following_months o
    LEFT JOIN user_cohorts c ON o.address = c.address
    GROUP BY cohortMonth, month_number
)
SELECT  r.cohortMonth
        , s.num_users as new_users
        , r.month_number
        , r.num_users / s.num_users as retention
FROM retention_table r
LEFT JOIN cohort_size s 
	ON r.cohortMonth = s.cohortMonth
WHERE r.month_number != 0
AND [cohortMonth:daterange]
ORDER BY r.cohortMonth, r.month_number

Want to learn how we got there? 👇

The goal of this tutorial is to create a query that groups new users into monthly cohorts and measures how many return over time - a.k.a MoM retention.

For this we need to create 4 CTEs:

• user_cohorts
  • Finds every unique address that interacted with the protocol and the first time they interacted.
• following_months
  • Finds the addresses from the original cohort that transacted in the months following.
• cohort_size
  • Counts the number of addresses in the original cohort
• retention_table
  • Counts the number of addresses from the original cohort in the months following.

User Cohorts

Let’s start with user_cohorts. The following query will be transformed to find every unique address that interacted with the contract and the first time they did so. Finding the first date an address transacted enables you to group it into a cohort month.

SELECT * FROM blockchains.all_chains LIMIT 10

There are many ways to find users in transaction data but in this example you will be looking at unique senders that have interacted with the Crabada: Game contract on Avalanche. To do so you need to query the unique addresses in the tx_sender column where the tx_recipient is the Crabada: Game contract. As you can see in the image below, the Crabada: Game contract is always the recipient for in game transactions (to column).

State the following in the WHERE statement - click here to read more on the unhex() function.

WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')

To then optimise your query, add:

1. AND chain_name = 'avalanche_mainnet' so your query isn’t searching through data on other chains.
2. AND signed_at > ‘2021-11-12’ which is the date Crabada’s gameplay contract got deployed on Avalanche.

SELECT tx_sender
FROM blockchains.all_chains 
WHERE tx_recipient = unhex('82a85407BD612f52577909F4A58bfC6873f14DA8')
AND chain_name = 'avalanche_mainnet'
AND signed_at > ‘2021-11-12’