flipside

    user rentention
    permaryuser rentention
    Updated 2025-03-17

    WITH first_tx_month AS (
    -- Find each user's first interaction with XSGD per chain (including both senders & receivers)
    SELECT
    'Ethereum' AS chain,
    user_address,
    DATE_TRUNC('month', MIN(block_timestamp)) AS first_tx_month
    FROM (
    SELECT origin_from_address AS user_address, block_timestamp
    FROM ethereum.core.ez_token_transfers
    WHERE contract_address = LOWER('0x70e8dE73cE538DA2bEEd35d14187F6959a8ecA96')
    AND block_timestamp >= '2024-09-01'
    UNION ALL
    SELECT origin_to_address AS user_address, block_timestamp
    FROM ethereum.core.ez_token_transfers
    WHERE contract_address = LOWER('0x70e8dE73cE538DA2bEEd35d14187F6959a8ecA96')
    AND block_timestamp >= '2024-09-01'
    ) combined
    GROUP BY 1, 2

    UNION ALL

    SELECT
    'Polygon' AS chain,
    user_address,
    DATE_TRUNC('month', MIN(block_timestamp)) AS first_tx_month
    FROM (
    SELECT origin_from_address AS user_address, block_timestamp
    FROM polygon.core.ez_token_transfers
    WHERE contract_address = LOWER('0xDC3326e71D45186F113a2F448984CA0e8D201995')
    AND block_timestamp >= '2024-09-01'
    UNION ALL
    Last run: about 1 month ago
    CHAIN
    FIRST_TX_MONTH
    ACTIVE_MONTH
    RETAINED_USERS
    RETENTION_RATE
    1
    		Avalanche2024-09-01 00:00:00.0002024-09-01 00:00:00.00070100
    2
    		Avalanche2024-09-01 00:00:00.0002024-10-01 00:00:00.0005071.43
    3
    		Avalanche2024-09-01 00:00:00.0002024-11-01 00:00:00.0004462.86
    4
    		Avalanche2024-09-01 00:00:00.0002024-12-01 00:00:00.0005071.43
    5
    		Avalanche2024-09-01 00:00:00.0002025-01-01 00:00:00.0004564.29
    6
    		Avalanche2024-09-01 00:00:00.0002025-02-01 00:00:00.0004564.29
    7
    		Avalanche2024-09-01 00:00:00.0002025-03-01 00:00:00.0004462.86
    8
    		Avalanche2024-10-01 00:00:00.0002024-10-01 00:00:00.000111100
    9
    		Avalanche2024-10-01 00:00:00.0002024-11-01 00:00:00.0007466.67
    10
    		Avalanche2024-10-01 00:00:00.0002024-12-01 00:00:00.0002926.13
    11
    		Avalanche2024-10-01 00:00:00.0002025-01-01 00:00:00.0003027.03
    12
    		Avalanche2024-10-01 00:00:00.0002025-02-01 00:00:00.0003127.93
    13
    		Avalanche2024-10-01 00:00:00.0002025-03-01 00:00:00.0002623.42
    14
    		Avalanche2024-11-01 00:00:00.0002024-11-01 00:00:00.000426100
    15
    		Avalanche2024-11-01 00:00:00.0002024-12-01 00:00:00.0009321.83
    16
    		Avalanche2024-11-01 00:00:00.0002025-01-01 00:00:00.0009021.13
    17
    		Avalanche2024-11-01 00:00:00.0002025-02-01 00:00:00.0007818.31
    18
    		Avalanche2024-11-01 00:00:00.0002025-03-01 00:00:00.0006114.32
    19
    		Avalanche2024-12-01 00:00:00.0002024-12-01 00:00:00.000692100
    20
    		Avalanche2024-12-01 00:00:00.0002025-01-01 00:00:00.00023433.82
    84
    6KB
    6s