Aave V2 User Retention Dashboard

Implement User retention dashboard for Aave V2 protocol with below features:

• Cohort analysis of new users on week by week basis.
• Track users with repeat transactions.
• Funnel analysis of repeat users to track user conversions.

User retention system flow

• Aave V2 Subgraph Data is pulled from subgraph and transformed to SQL tables.
• Below tables are populated: cohorts cohort_base_values cohort_metrics
• A cohort is specified by week start and week end data.
• Cohort start date is taken when Aave V2 protocol was launched.
• User enters into a cohort based on his first transaction date. User can only be part of 1 cohort at a time.
• In a cohort, user is counted only once, even he makes multiple transactions.
• User retention is calculated based on his transaction activity week by week basis.

Funnel Anlaysis

• Users are put in different categories like User making 2+ transactions, Users making 16+ transactions.
• Users made 32+ and 64+ transactions have been categorized to mini whales and whales.
• Query is run on user_transactions table to group users in different categories. Temporary table concept is used in SQL while constructing the query.
• Funnel chart is prepared using different category and the counts.

Discussion with Stake holders

• Had detailed discussion with Aave team Stani, David others regarding their analytics needs.

• Learnt that for Aave team, user retention analytics is must need. In only short frame of 2 month, there are already more 100k+ transactions.

• Using the retention cohort analysis, we will be track how Aave users are performing on week by week basis

• This analysis can be used to identify the success of feature adoption rate and churn rates as time progresses

• Funnel analysis help identify barriers that cause users to leave before reaching a conversion point.

Technology Stack

• Typescript

• Postgres database

• Sequelize Postgres client

• moment for datetime parsing

• Dashboard and code is hosted on AWS

Making funnel query was tricky because complex SQL knowledge is needed. Learnt temporary tables in SQL and used nested temporary tables in the query.