New and Improved NEAR Labels

    A plethora of new labels have been added to Flipside's NEAR labels table. Using the labels, dive into which specific projects and project types (defi, nfts, etc) generate the most gas fees, have the most users, and have the highest number of transactions per user. Next, dig a little deeper and see if you can find which project types and which projects have the stickiest(repeat) users!

    Introduction

    Near Blockchain

    NEAR is a next-generation proof-of-stake blockchain protocol that offers scalability and low costs of operation[1]. It was designed as a community-run cloud computing platform and eliminates some of the limitations that have been bogging competing blockchains, such as low transaction speeds, low throughput and poor interoperability[2]. NEAR’s innovative blockchain design uses a fraction of the energy other networks use, and requires no sort of power-draining “crypto mining” to perform transactions¹.

    You can learn more about NEAR on their official website [3] or their documentation page [4].

    Methodology
    Problem Definition
    db_img

    In this dashboard, we aim to investigate the performance of various projects and project categories on Flipside’s NEAR labels table. Using the newly added labels, we will analyze which projects and project types (such as defi and nfts) generate the most gas fees, have the highest number of users, and have the greatest number of transactions per user. Furthermore, we will delve deeper to determine which project types and individual projects have the most loyal (repeat) users.

    The main query used in this report is as follows:

    
    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')
    )
    select 
        count(distinct tx_hash) num_tx_hash,
        count(distinct TX_SIGNER) num_users,
        count(distinct project_name) num_projects,
        count(distinct label_type) num_label_types,
        sum(tx_fee) sum_fees,
        avg(tx_fee) fee_per_tx,
        sum_fees/num_users fee_per_user,
        num_tx_hash/num_users tx_per_user
    from raw_date
    

    The query begins by creating a temporary table called raw_date that selects various columns from the near.core.fact_transactions and near.core.dim_address_labels tables. The two tables are joined on the tx_receiver (as project address or contract address) and address columns, respectively. The query filters the results to only include transactions with a TX_STATUS of 'Success'.

    Next, the query selects several aggregate functions from the raw_date table. These include the count of distinct tx_hash, TX_SIGNER as user wallet, project_name, and label_type as project type values, as well as the sum and average of the tx_fee column. Additionally, the query calculates the fee per user and transactions per user by dividing the sum of fees by the number of users and the number of transactions by the number of users, respectively.

    This query has been used in different ways in this report. For example, to determine the Tops. Or, for example, to display metrics over time, we have added the value of the block_timestamp column to result queries.

    Some Definitions

    Transaction

    A Transaction in blockchain refers to the transfer of data or cryptocurrency between two parties on the blockchain network. Transactions contain essential information such as the sender's address, the recipient's address, the amount being transferred, and any associated fees. In a blockchain network, transactions are verified by nodes or miners according to the network's defined consensus mechanism. Once a transaction is validated, it is recorded onto the blockchain ledger, which is a decentralized and immutable record of all past transactions on the network. Every transaction on the blockchain network is assigned a unique transaction ID or hash, which is used to identify the transaction on the blockchain. The transaction ID, along with other transaction details, is stored in blocks which are linked together forming a chain or "blockchain". Transactions in blockchain networks can involve the transfer of various types of digital assets and data, including cryptocurrencies, decentralised applications, smart contracts, and tokens. As such, transactions form the building blocks upon which blockchain networks operate, providing the means for peer-to-peer value and information transfer without the need for intermediaries.

    Fee (Gas Used)

    The Fee refers to the amount charged for the processing of a transaction or an operation on the network. The fee is paid in the native cryptocurrency of the blockchain network and is used to incentivize miners or validators to process the transaction or operation. The fee is usually determined by the current level of network congestion and the amount of gas required to execute the transaction or operation. In general, the higher the fee paid, the faster the transaction is processed since miners or validators prioritize transactions with higher fees. However, some blockchain networks may also give users the option to set a lower fee and wait for a longer processing time. Fees are an important aspect of the blockchain ecosystem as they ensure the smooth functioning of the network and discourage spamming or malicious use of network resources. By paying a fee, users are essentially purchasing priority access to the network, ensuring the timely and efficient execution of their transactions or operations.

    Active Users

    In Blockchain, an Active User on a blockchain is defined as an individual or entity that has performed at least one transaction within a specified time period (e.g. every day). This can include activities such as sending and receiving cryptocurrency, executing smart contracts, staking tokens, voting on governance proposals, and more. By actively engaging with the blockchain network and its ecosystem, these users help to drive the growth and adoption of the technology.

    Project Type

    In the context of blockchain, a Project Type typically refers to the category or classification of a specific blockchain-based project or application. Blockchain projects can be categorized based on their purpose, functionality, and underlying technology. Some common project types in the blockchain space include cryptocurrencies, decentralized finance (DeFi) applications, non-fungible tokens (NFTs), and supply chain management solutions. A project type can contain several project names.

    Project Name

    In the context of blockchain, a Project Name typically refers to the name of a specific blockchain-based project or application. Blockchain projects can vary widely in their purpose and functionality, ranging from cryptocurrencies like Bitcoin to decentralized applications (dapps) built on blockchain platforms like Ethereum. A project name can contain several smart contracts.

    Contract Address

    A smart Contract Address is a self-executing agreement or contract that is stored on a blockchain. It is a computer program that automatically executes the terms of the contract when certain predetermined conditions are met. Smart contracts are built using code, and are stored on a decentralized blockchain network, meaning they are not controlled by any central authority.
    Tabs and Parameters

    Tabs

    This dashboard has 8 tabs:
    1. Introduction: The current tab, which includes a description of the issue and how to understand, adjust and use this dashboard.
    2. Labels: Due to the fact that this report is based on different types of tags (project name and project type), we have placed a special tab to provide information related to the main data of the near.core.dim_address_labels table.
    3. Overall Status: In this tab, general information related to the definition of the problem and the NEAR network are provided.
    4. Tops: Due to the large number of names and types of projects as well as the number of records over time in the grouping of different columns in some queries, we tried to identify and introduce the best ones based on different defined metrics in this tab.
    5. Over Time: After determining the top ones, in this tab you can study the trend of related metric changes over time. The graphs drawn over time are limited in two time ranges, From_Date through To_Date.
    6. Users' Sticking Rate: If you want to know which projects and which types of project have the most stickiness (repetition) of users in using them, you should refer to this tab.
    7. Specified: In this section, you can study the various metrics related to project name or project type by specifying the exact name of the project or the type of project in the related parameters.
    8. Conclustion: In this tab, we have mentioned a summary of the most important results obtained in this report.

    Parameters

    The use of parameters in this report has caused a lot of this dashboard to be dynamic and out of static mode. With the help of the explanations given about the parameters and their application, you can easily extract the desired results from the mass of data and check them. In this report, we have used 5 parameters:
    1. Interval: Determines time intervals on charts over time. By default it is Daily.
    2. From_Date: The beginning of the studied time period. This parameters is only applied only on the over time charts.
    3. To_Date: End of the studied time period. This parameters is only applied only on the over time charts.
    4. Project_Type: Enter the type of your desired project completely in this parameter. It is not case sensitive.
    5. Project_Name:Enter the name of your desired project completely in this parameter. It is not case sensitive..

    To use the parameters, you need to LOGIN and click on Apply all parameters button after specifying the desired values.

    Please note that specifying long time period may cause your query to run with a very long delay.

    Email: PiroozTrader

    Discord: Mercury#8660

    Twitter: @PiroozTrader

    Please read and like my reports (dashboards) in my Flipsidecrypto account: Mercury