Aave V2 User Retention Dashboard

Screenshots

Problem Statement

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 flowAave V2 Subgraph Data is pulled from subgraph and transformed to SQL tables.Below tables are populated: cohorts cohort_base_values cohort_metricsA 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 AnlaysisUsers 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.

Solution

Discussion with Stake holdersHad 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 basisThis analysis can be used to identify the success of feature adoption rate and churn rates as time progressesFunnel analysis help identify barriers that cause users to leave before reaching a conversion point.Technology StackTypescriptPostgres databaseSequelize Postgres clientmoment for datetime parsingDashboard and code is hosted on AWSMaking funnel query was tricky because complex SQL knowledge is needed. Learnt temporary tables in SQL and used nested temporary tables in the query.