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.

Dashboard	Creator	Published
NEAR On Chain Data Report: Before vs. After ATL Jan 15, 2023	mercury	Jan 15, 2023
NEAR - 16. Secrets of the NETH-erworld Mar 1, 2023	mercury	Mar 1, 2023
New and Improved NEAR Labels May 12, 2023	mercury	May 12, 2023

mercury