Users' Stickiness (Retention or Repetition) Rate

Generally, the rate of user stickiness or repetition to a project can be measured based on the following criteria: - **# of Transactionst**: This metric represents the total number of transactions made by a user in a project. Each individual tx made by the user is counted towards the transactions count. This metric indicates how many times a user has made a txs in a specific project. - **Active Days**: This metric indicates the number of days in which a user has been active in a project. Each day that the user makes a tx or engages in any activity within the project is counted towards the active days. This metric shows how many days a user has been actively involved in a project and consistently engaged in activities. - **Unique Users**: This metric represents the number of unique users who have made at least one tx or engaged in activity within a project. Each user who has been active at least once in a project is counted towards the unique users count. This metric indicates how many different users have been present in a project. Using these three metrics, the level of user stickiness or repetition to different projects can be evaluated. The method we have used in this report to calculate the rate of users sticking to projects is based on the percentage of users who return to a project (type or name) after their first interaction. A higher stickiness rate indicates that a larger ratio of users find value in the product or service and continue to use it. To calculate stickiness rate, you need data on user interactions with the product or service over time. This data can come from various sources we mentioned such as # of txs , # of user activity and fee paid to execute a tx. The first step in calculating stickiness rate is to identify the first interaction for each user. This can be done by grouping the data by user and selecting the earliest interaction for each user. Next, you need to identify users who have had more than one interaction with the project. This can be done by filtering the data for interactions that occurred after the first interaction for each user and grouping by user. Finally, you can calculate the stickiness rate by dividing the number of users who have had more than one interaction by the total number of users and multiplying by 100 to get a percentage. According to these explanations and in order to calculate the users' stickiness rate to all types of projects, we wrote the following query:


with raw_date as (
select block_timestamp,
    tx_hash,
    tx_receiver,
    TX_SIGNER,
    TRANSACTION_FEE/pow(10,24) tx_fee,
    address,
    address_name,
    project_name,
    label_type,
    label_subtype
from near.core.fact_transactions t 
    inner join near.core.dim_address_labels l on t.tx_receiver=l.address
where TX_STATUS in ('Success')
),
cte_first_tx AS (
    SELECT tx_signer, label_type, MIN(block_timestamp) as first_tx_date
    FROM raw_date
    GROUP BY tx_signer, label_type
),
cte_repeat_tx AS (
    SELECT r.tx_signer, r.label_type
    FROM raw_date r
    JOIN cte_first_tx f ON r.tx_signer = f.tx_signer AND r.label_type = f.label_type
    WHERE r.block_timestamp > f.first_tx_date
    GROUP BY r.tx_signer, r.label_type
),
cte_Sticking_rate AS (
    SELECT cte_first_tx.label_type,
           COUNT(DISTINCT cte_repeat_tx.tx_signer) * 100.00 / COUNT(DISTINCT cte_first_tx.tx_signer) as Sticking_rate
    FROM cte_first_tx
    LEFT JOIN cte_repeat_tx ON cte_first_tx.tx_signer = cte_repeat_tx.tx_signer AND cte_first_tx.label_type = cte_repeat_tx.label_type
    GROUP BY cte_first_tx.label_type
)
SELECT upper(label_type) projct_type, Sticking_rate
FROM cte_Sticking_rate
ORDER BY Sticking_rate DESC

This query calculates the user stickiness rate for different project types based on transaction data. The stickiness rate is calculated as the percentage of users who have made more than one transaction with a project type. The query first creates a common table expression (CTE) named `raw_date` that selects relevant columns from the `near.core.fact_transactions` and `near.core.dim_address_labels` tables and filters for successful transactions. Next, the query creates a CTE named `cte_first_tx` that groups the data by `tx_signer` and `label_type` and selects the minimum `block_timestamp` for each group, representing the date of the first transaction for each user and project type. The query then creates a CTE named `cte_repeat_tx` that joins the `raw_date` and `cte_first_tx` CTEs on `tx_signer` and `label_type`, filters for transactions that occurred after the first transaction date, and groups by `tx_signer` and `label_type`. This CTE represents users who have made more than one transaction with a project type. Finally, the query creates a CTE named `cte_Sticking_rate` that calculates the stickiness rate for each project type by dividing the count of distinct users in the `cte_repeat_tx` CTE by the count of distinct users in the `cte_first_tx` CTE and multiplying by 100 to get a percentage. The final SELECT statement selects the project type and stickiness rate from this CTE and orders the results by stickiness rate in descending order. All the graphs drawn (with a slight difference) are drawn based on this query.

mercury

Terra Console for accuracy

Mars Protocol Lockdrop Scavenger Hunt

Provide Liquidity in Thorswap.finance Pools

Let it Burn LUNA to UPGRAE!!

Voting Power Within the Arbitrum DAO

THOR-versary!

Arbitrum Scavenger Hunt

What Are Users Swapping gALGO3 For?

Rand Gallery NFT Sales

Nebula Scavenger Hunt

Osmosis - 9. Swap fees

Juno Scavenger Hunt

Algorand Price Correlation

Paraswap Airdrop Assessment

OP Token Price vs Swapper Activity

Explore the Behavior of the Top 5 LDO Holders.

Sushi Bentobox Borrowing Scavenger Hunt

Top LDO Holders

More Mints

Solana NFT Project Feature

Swapping goBTC and goETH on DEXs

Terra, Programmable Money for The Internet

Sushiswap DEX Loyal Users

Reactor Scavenger Hunt

Folks Finance: Minting gALGO3 For Governance

UST-LUNA Highway

Algofi Recursive Borrowing

Gard.Money Algorand’s First Algorithmic Stablecoin

DAI on the Market (May 13)

Optimistic Bears (EXTREMELY REPORT)

New and Improved NEAR Labels

Daily Active Users on Flow Blockchain

AB2 Gallery Wallets

Optimism Scavenger Hunt (June 1)

Collateral Impact (May 13)

Wormhole Scavenger Hunt: From Terra to Solana

Bridge Users (Hop Protocol)

Getting Started With Marinade Finance Bounties (Redux)