How to Analyze Yield Farming User Retention: Complete Stickiness Metrics Guide

// Calculate Day-N Retention Rate
function calculateRetention(firstDayUsers, returnUsers, days) {
  const retentionRate = (returnUsers / firstDayUsers) * 100;
  console.log(`Day ${days} Retention: ${retentionRate.toFixed(2)}%`);
  return retentionRate;
}

// Example: 1000 users started, 300 returned after 7 days
calculateRetention(1000, 300, 7);
// Output: Day 7 Retention: 30.00%

// Calculate Rolling Retention
function calculateRollingRetention(cohortUsers, activeUsers, windowDays) {
  const rollingRate = (activeUsers / cohortUsers) * 100;
  console.log(`${windowDays}-day Rolling Retention: ${rollingRate.toFixed(2)}%`);
  return rollingRate;
}

// Example: 1000 users in cohort, 450 active within 7 days
calculateRollingRetention(1000, 450, 7);
// Output: 7-day Rolling Retention: 45.00%

import matplotlib.pyplot as plt
import numpy as np

# Sample retention data
days = np.array([1, 3, 7, 14, 30, 60, 90])
retention_rates = np.array([45, 32, 25, 18, 12, 8, 6])

# Create retention curve
plt.figure(figsize=(10, 6))
plt.plot(days, retention_rates, marker='o', linewidth=2, markersize=8)
plt.title('Yield Farming User Retention Curve')
plt.xlabel('Days Since First Interaction')
plt.ylabel('Retention Rate (%)')
plt.grid(True, alpha=0.3)
plt.show()

# Analyze curve shape
if retention_rates[1] > 30:
    print("Good initial retention - users find value quickly")
else:
    print("Poor initial retention - review onboarding process")

// Define trackable user actions
const TRACKED_ACTIONS = {
  DEPOSIT: 'liquidity_deposit',
  WITHDRAW: 'liquidity_withdraw', 
  CLAIM: 'rewards_claim',
  STAKE: 'token_stake',
  SWAP: 'token_swap',
  VOTE: 'governance_vote'
};

// Example event tracking
function trackUserAction(userAddress, action, timestamp, metadata) {
  const event = {
    user_id: userAddress.toLowerCase(),
    action: action,
    timestamp: timestamp,
    session_id: generateSessionId(),
    metadata: metadata
  };
  
  // Send to analytics service
  analytics.track(event);
  console.log(`Tracked: ${action} for user ${userAddress}`);
}

-- User events table
CREATE TABLE user_events (
  id SERIAL PRIMARY KEY,
  user_address VARCHAR(42) NOT NULL,
  action VARCHAR(50) NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  session_id VARCHAR(36),
  block_number BIGINT,
  transaction_hash VARCHAR(66),
  metadata JSONB
);

-- Create indexes for fast queries
CREATE INDEX idx_user_events_address_time 
ON user_events(user_address, timestamp);

CREATE INDEX idx_user_events_action_time 
ON user_events(action, timestamp);

-- Get DAU for specific date
SELECT COUNT(DISTINCT user_address) as daily_active_users
FROM user_events 
WHERE DATE(timestamp) = '2025-07-20'
AND action IN ('liquidity_deposit', 'liquidity_withdraw', 'rewards_claim');

-- Get DAU trend over 30 days
SELECT 
  DATE(timestamp) as date,
  COUNT(DISTINCT user_address) as dau
FROM user_events 
WHERE timestamp >= CURRENT_DATE - INTERVAL '30 days'
AND action IN ('liquidity_deposit', 'liquidity_withdraw', 'rewards_claim')
GROUP BY DATE(timestamp)
ORDER BY date;

// Create weekly cohorts
function createWeeklyCohorts(userEvents) {
  const cohorts = new Map();
  
  userEvents.forEach(event => {
    const weekStart = getWeekStart(event.timestamp);
    const cohortKey = `${weekStart.getFullYear()}-W${getWeekNumber(weekStart)}`;
    
    if (!cohorts.has(cohortKey)) {
      cohorts.set(cohortKey, new Set());
    }
    
    cohorts.get(cohortKey).add(event.user_address);
  });
  
  return cohorts;
}

// Helper function to get week start (Monday)
function getWeekStart(date) {
  const day = date.getDay();
  const diff = date.getDate() - day + (day === 0 ? -6 : 1);
  return new Date(date.setDate(diff));
}

-- Create cohort retention analysis
WITH user_cohorts AS (
  SELECT 
    user_address,
    DATE_TRUNC('week', MIN(timestamp)) as cohort_week,
    MIN(timestamp) as first_seen
  FROM user_events
  GROUP BY user_address
),
user_activities AS (
  SELECT 
    uc.user_address,
    uc.cohort_week,
    DATE_TRUNC('week', ue.timestamp) as activity_week,
    EXTRACT(WEEK FROM ue.timestamp) - EXTRACT(WEEK FROM uc.cohort_week) as week_number
  FROM user_cohorts uc
  JOIN user_events ue ON uc.user_address = ue.user_address
)
SELECT 
  cohort_week,
  week_number,
  COUNT(DISTINCT user_address) as active_users,
  COUNT(DISTINCT user_address) * 100.0 / 
    FIRST_VALUE(COUNT(DISTINCT user_address)) 
    OVER (PARTITION BY cohort_week ORDER BY week_number) as retention_rate
FROM user_activities
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;

import pandas as pd
import seaborn as sns

# Load cohort retention data
cohort_data = pd.read_sql(cohort_query, connection)

# Pivot data for heatmap
cohort_table = cohort_data.pivot_table(
    index='cohort_week',
    columns='week_number', 
    values='retention_rate'
)

# Create cohort heatmap
plt.figure(figsize=(15, 8))
sns.heatmap(
    cohort_table, 
    annot=True, 
    fmt='.1f',
    cmap='RdYlBu_r',
    center=0
)
plt.title('Yield Farming User Retention Cohort Analysis')
plt.xlabel('Week Number')
plt.ylabel('Cohort Week')
plt.show()

// Calculate DAU/MAU stickiness ratio
async function calculateStickiness(date) {
  const startOfMonth = new Date(date.getFullYear(), date.getMonth(), 1);
  const endOfMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  
  // Get DAU for specific date
  const dau = await getUserCount(date, date);
  
  // Get MAU for the month
  const mau = await getUserCount(startOfMonth, endOfMonth);
  
  const stickiness = (dau / mau) * 100;
  
  console.log(`Date: ${date.toISOString().split('T')[0]}`);
  console.log(`DAU: ${dau}`);
  console.log(`MAU: ${mau}`);
  console.log(`Stickiness: ${stickiness.toFixed(2)}%`);
  
  return stickiness;
}

// Helper function to get unique user count
async function getUserCount(startDate, endDate) {
  const query = `
    SELECT COUNT(DISTINCT user_address) as user_count
    FROM user_events 
    WHERE timestamp >= $1 AND timestamp <= $2
    AND action IN ('liquidity_deposit', 'liquidity_withdraw', 'rewards_claim')
  `;
  
  const result = await db.query(query, [startDate, endDate]);
  return result.rows[0].user_count;
}

// Calculate WAU/MAU for deeper engagement insights
async function calculateWeeklyStickiness(date) {
  const weekStart = getWeekStart(date);
  const weekEnd = new Date(weekStart);
  weekEnd.setDate(weekEnd.getDate() + 6);
  
  const monthStart = new Date(date.getFullYear(), date.getMonth(), 1);
  const monthEnd = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  
  const wau = await getUserCount(weekStart, weekEnd);
  const mau = await getUserCount(monthStart, monthEnd);
  
  const weeklyStickiness = (wau / mau) * 100;
  
  return {
    week: `${weekStart.toISOString().split('T')[0]}`,
    wau: wau,
    mau: mau,
    stickiness: weeklyStickiness
  };
}

function analyzeStickiness(dauMauRatio, wauMauRatio) {
  const insights = [];
  
  if (dauMauRatio < 10) {
    insights.push("Low daily engagement - improve daily reward mechanisms");
  }
  
  if (wauMauRatio < 25) {
    insights.push("Users not returning weekly - review reward distribution schedule");
  }
  
  if (dauMauRatio > 20 && wauMauRatio > 60) {
    insights.push("Excellent user engagement - scale current strategies");
  }
  
  return insights;
}

// Define user behavior segments
const USER_SEGMENTS = {
  WHALE: 'whale',           // >$100k deposited
  POWER_USER: 'power_user', // >10 transactions/week
  CASUAL: 'casual',         // 1-3 transactions/week
  DORMANT: 'dormant'        // No activity >30 days
};

// Classify users into segments
function classifyUser(userAddress, userEvents, userTVL) {
  const recentEvents = userEvents.filter(
    event => event.timestamp > Date.now() - (30 * 24 * 60 * 60 * 1000)
  );
  
  const weeklyTransactions = recentEvents.length / 4;
  
  if (userTVL > 100000) {
    return USER_SEGMENTS.WHALE;
  } else if (weeklyTransactions > 10) {
    return USER_SEGMENTS.POWER_USER;
  } else if (weeklyTransactions >= 1) {
    return USER_SEGMENTS.CASUAL;
  } else {
    return USER_SEGMENTS.DORMANT;
  }
}

// Calculate retention by segment
async function getRetentionBySegment(cohortDate, retentionDay) {
  const query = `
    WITH user_segments AS (
      SELECT 
        user_address,
        CASE 
          WHEN max_tvl > 100000 THEN 'whale'
          WHEN weekly_tx_count > 10 THEN 'power_user'
          WHEN weekly_tx_count >= 1 THEN 'casual'
          ELSE 'dormant'
        END as segment
      FROM user_behavior_summary
    )
    SELECT 
      us.segment,
      COUNT(DISTINCT cohort.user_address) as cohort_size,
      COUNT(DISTINCT retained.user_address) as retained_users,
      (COUNT(DISTINCT retained.user_address) * 100.0 / 
       COUNT(DISTINCT cohort.user_address)) as retention_rate
    FROM user_segments us
    JOIN cohort_users cohort ON us.user_address = cohort.user_address
    LEFT JOIN retained_users retained ON us.user_address = retained.user_address
    WHERE cohort.cohort_date = $1 AND retained.day = $2
    GROUP BY us.segment
  `;
  
  return await db.query(query, [cohortDate, retentionDay]);
}

import pandas as pd
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split

# Feature engineering for churn prediction
def create_user_features(user_events):
    features = pd.DataFrame()
    
    # Calculate user behavior features
    features['days_since_last_action'] = (
        pd.Timestamp.now() - user_events.groupby('user_address')['timestamp'].max()
    ).dt.days
    
    features['total_transactions'] = user_events.groupby('user_address').size()
    features['unique_actions'] = user_events.groupby('user_address')['action'].nunique()
    features['avg_session_length'] = user_events.groupby('user_address')['session_length'].mean()
    features['total_volume'] = user_events.groupby('user_address')['volume'].sum()
    
    # Engagement metrics
    features['days_active'] = user_events.groupby('user_address')['timestamp'].apply(
        lambda x: (x.max() - x.min()).days + 1
    )
    features['frequency'] = features['total_transactions'] / features['days_active']
    
    return features

# Train churn prediction model
def train_churn_model(features, churn_labels):
    X_train, X_test, y_train, y_test = train_test_split(
        features, churn_labels, test_size=0.2, random_state=42
    )
    
    model = RandomForestClassifier(n_estimators=100, random_state=42)
    model.fit(X_train, y_train)
    
    # Model performance
    accuracy = model.score(X_test, y_test)
    print(f"Churn prediction accuracy: {accuracy:.2%}")
    
    # Feature importance
    feature_importance = pd.DataFrame({
        'feature': features.columns,
        'importance': model.feature_importances_
    }).sort_values('importance', ascending=False)
    
    return model, feature_importance

# Predict churn risk for active users
def predict_churn_risk(model, user_features):
    churn_probability = model.predict_proba(user_features)[:, 1]
    
    risk_segments = pd.cut(
        churn_probability, 
        bins=[0, 0.3, 0.7, 1.0], 
        labels=['Low Risk', 'Medium Risk', 'High Risk']
    )
    
    return pd.DataFrame({
        'user_address': user_features.index,
        'churn_probability': churn_probability,
        'risk_segment': risk_segments
    })

// Define user intent categories
const USER_INTENTS = {
  YIELD_MAXIMIZER: 'yield_maximizer',     // Seeks highest APY
  STABLE_FARMER: 'stable_farmer',         // Prefers stable, lower-risk yields  
  LIQUIDITY_MINER: 'liquidity_miner',     // Focuses on token rewards
  TRADER: 'trader'                        // Active trading with yield farming
};

// Identify user intent from behavior
function identifyUserIntent(userEvents) {
  const actions = userEvents.map(e => e.action);
  const swapRatio = actions.filter(a => a === 'token_swap').length / actions.length;
  const avgStakeDuration = calculateAvgStakeDuration(userEvents);
  const poolTypes = getUniquePoolTypes(userEvents);
  
  if (swapRatio > 0.3) {
    return USER_INTENTS.TRADER;
  } else if (avgStakeDuration > 90) {
    return USER_INTENTS.STABLE_FARMER;
  } else if (poolTypes.includes('new_token_launch')) {
    return USER_INTENTS.LIQUIDITY_MINER;
  } else {
    return USER_INTENTS.YIELD_MAXIMIZER;
  }
}

// Avoid these vanity metrics
const VANITY_METRICS = [
  'total_signups',
  'total_transactions', 
  'total_page_views',
  'social_media_followers'
];

// Focus on engagement metrics instead
const ENGAGEMENT_METRICS = [
  'daily_active_users',
  'user_retention_rate',
  'stickiness_ratio',
  'session_duration',
  'feature_adoption_rate'
];

// Calculate meaningful engagement score
function calculateEngagementScore(userEvents) {
  const metrics = {
    frequency: calculateUsageFrequency(userEvents),
    depth: calculateFeatureUsage(userEvents),
    duration: calculateSessionDuration(userEvents),
    recency: calculateDaysSinceLastUse(userEvents)
  };
  
  // Weighted engagement score (0-100)
  const engagementScore = (
    metrics.frequency * 0.3 +
    metrics.depth * 0.3 +
    metrics.duration * 0.2 +
    (100 - metrics.recency) * 0.2
  );
  
  return Math.max(0, Math.min(100, engagementScore));
}

-- Track extended retention periods
SELECT 
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_address END) as month_1,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_address END) as month_3,
  COUNT(DISTINCT CASE WHEN period_number = 6 THEN user_address END) as month_6,
  COUNT(DISTINCT CASE WHEN period_number = 12 THEN user_address END) as month_12
FROM cohort_retention_extended 
GROUP BY cohort_month
ORDER BY cohort_month;

-- Calculate long-term value correlation
SELECT 
  retention_month,
  AVG(total_volume) as avg_volume,
  AVG(total_fees_paid) as avg_fees,
  COUNT(*) as user_count
FROM long_term_retention_analysis
GROUP BY retention_month
ORDER BY retention_month;

// Retention dashboard configuration
const DASHBOARD_CONFIG = {
  metrics: [
    {
      name: 'DAU/MAU Stickiness',
      calculation: 'dau_mau_ratio',
      target: 15,
      format: 'percentage'
    },
    {
      name: 'Day 7 Retention',
      calculation: 'day_7_retention',
      target: 15,
      format: 'percentage'
    },
    {
      name: 'Cohort LTV',
      calculation: 'cohort_lifetime_value',
      target: 1000,
      format: 'currency'
    }
  ],
  segments: ['whale', 'power_user', 'casual'],
  timeframes: ['7d', '30d', '90d']
};

// Generate dashboard data
async function generateRetentionDashboard(startDate, endDate) {
  const dashboard = {
    overview: {},
    segments: {},
    trends: {},
    alerts: []
  };
  
  // Calculate overview metrics
  for (const metric of DASHBOARD_CONFIG.metrics) {
    const value = await calculateMetric(metric.calculation, startDate, endDate);
    dashboard.overview[metric.name] = {
      value: value,
      target: metric.target,
      status: value >= metric.target ? 'healthy' : 'needs_attention'
    };
    
    // Add alerts for metrics below target
    if (value < metric.target) {
      dashboard.alerts.push({
        type: 'warning',
        metric: metric.name,
        message: `${metric.name} (${value}) below target (${metric.target})`
      });
    }
  }
  
  // Calculate segment-specific metrics
  for (const segment of DASHBOARD_CONFIG.segments) {
    dashboard.segments[segment] = await getSegmentMetrics(segment, startDate, endDate);
  }
  
  return dashboard;
}