Bounty Question : In this Article, Let’s analyze a typical user journey through Osmosis. Analyze the following:
- The most common ways of entering Osmosis (Axelar (Satellite) or elsewhere)
- The most common asset transferred into Osmosis for first transfer
Stay or leave: What do wallets do when they first come to Osmosis? Join to swap and then transfer that asset out? Join to stake OSMO? Join to swap and then LP? Which assets are most commonly swapped for and transferred out? Which assets are most commonly swapped for and then LP’d? Calculate the above from both a volume and # of user standpoint.
An Overview : Osmosis
Using the Inter-Blockchain Communication (IBC) protocol, Osmosis can be integrated with networks that use the Cosmos Hub ecosystem. Using the Cosmos SDK, Osmosis is a Layer-1 proof-of-stake (PoS) blockchain optimized for sandboxes for automated market makers (AMMs). Built upon the blockchain governance system, developers can build customized AMMs using the blockchain governance system. The most effective method is to use this method. Osmosis is able to deploy sovereign liquidity pools in contrast to other AMMs, such as Uniswap and Curve. There is a similar function to the Osmosis liquidity pools as independent decentralized autonomous organizations (DAOs), where liquidity providers have the ability to vote on the changes to the pool’s AMM formula. As the first AMM protocol on Cosmos to offer trades and incentives based on liquidity availability, Osmosis will provide users with rewards for contributing to the platform’s security through voting and making decisions about the network using OSMO, the native token. The Osmosis software provides trading pairs based on OSMO and ATOM as an additional feature.
Approach
Approach
This dashboard provides an analysis of a typical user journey through Osmosis. According to the Sankey chart below, we will assess what wallets do when they first arrive at Osmosis. We can see that Osmosis wallets are performing their first time activities such as DEX swap, staking, token transfer, liquidity provision, and government voting over time. From that we can see the second activity of each of these first activity wallets .The data is calculated by both from a volume perspective and from a user perspective. Additionally, we can see a separate analysis for all of these actions as follows:
- On a weekly basis, see the number of wallets by their activities and their percentages
- Number of wallets based on first activity and percentage view on a weekly basis
- Number of wallets and percentages based on their second activity on a weekly basis
- The Top Liquidity Pools By Transaction Count report shows which assets are most commonly swapped for and then LPed
- What are the top tokens that are joined for a swap followed by a transfer of that asset?
- Which assets are most frequently swapped for and transferred out?
Here From the Below Sankey Chart, we will assess what wallets do when they first arrive at Osmosis. The Osmosis wallet performs their first time activities such as DEX swaps, staking, token transfers, liquidity provision, and government voting over time. In addition, we can also see here what they did with the second activity.
As an example, from the below chart it can be seen that 374.3K wallets performed their first activity as a DEX swap. Among these wallets, the majority (224.2K) are performing their second activity as a token transfer. The next top activity of these wallets is providing liquidity. A very small number of participants (42.3K) devoted their second activity to staking, and 5.5K took part in governance voting.
We can see that when the users are active in stakes during their first time activity, they are mostly active in Gov. voting during their second time activity. A quarter of the users (91.4K) were doing this kind of activity if we look at the join to swap, then the LP activity.
From the below graph, we can see the number of wallets based on their weekly activities. These wallets offer a variety of activities, including airdrops, DEX swaps, governance voting, liquidity, staking, and token transfers.
The maximum number of wallets observed in March 7, 2022 was 71.26K. Total wallets for March 14, 2022 and January 24, 2022 were lower than normal, while in April 18, 2022 and June 13, 2022, they were higher than normal. We can see an increasing trend in user activities from Jan 2022 forward. During March 2022, they hiked. After March, they suddenly started decreasing. Positive correlation can be seen between Token Transfer and Provide Liquidity wallets.
Here is a graph that shows the weekly percentage view of different users’ activities over time. As a result of DEX Swap Activities, 36.52% contributed, and the two most influential activities (DEX Swap and Token Transfer) contributed 64.96%. User activity in token transfers increased from January 2022 onwards. There is a possibility that it was caused by the volatile conditions on the crypto market. According to the market situation, many users are mostly performing token transfers.
In the graph below, we can see the first activity of wallets on a weekly basis. We can see that DEX Swap and Staking accounted for more than 3 quarters of the total wallets. Airdop has the lowest total wallets with 2.79K, and DEX Swap has the highest with 374.2K. The number of wallets in March 14, 2022 and May 2, 2022 is lower than normal and the number of wallets in April 18, 2022 and February 14, 2022 is higher than normal. As part of all the activities, most of the wallets perform a DEX swap on their first activity.
As can be seen from the graph below, more than 80% of wallets initially perform DEX swaps on a weekly schedule. However, after February 2022, we can see that DEX swaps by wallets have been reduced by 60%. From May 2022 onwards, we can observe an increase in token transfers on a weekly basis.
The token transfer activity increased when we analyze the number of wallets based on their second activity. There were 49.74% of total wallets that were linked to token transfer activities. The lowest total wallets are 5.33K (Airdop), and the highest are 244.68K (Token Transfer). In January 24, 2022 and March 28, 2022, total wallets are lower than normal, and in October 10, 2022 and June 13, 2022, total wallets are higher than normal. As a result of second activity, the DEX Swap Activity trended downward dramatically. Most wallets perform a Token Transfer on their 2nd activity as part of all the activities.
From the graph below, we can clearly see the growing trend in token transfer activity on a weekly basis. Comparing the initial days with the current days, we find that the increase is almost double.
From the below graph, we can see the top liquidity pools by transaction count on a weekly basis. Based on this, we can determine which assets are most commonly swapped for and then LP’d over time. According to the Sankey chart, a quarter of users (91.4K) were joining to swap and then making LPs. Taking into account their transaction counts, we can see what assets are most commonly swapped for and LP’d. The pool ID 2267 performs a maximum number of transactions, as shown in the graph below. The maximum number of Lp transactions was observed on February 28, 2022, which was 95.69K.
On a weekly basis, we can see here the top tokens that are joined to swap and then transferred out from the below graph when we are looking for the join to swap and transfer out. We can see 374.3K wallets performed their first activity as a DEX swap from the Sankey chart above. Among these wallets, the majority (224.2K) are performing token transfers as a second activity. Approximately 49.03% of total Transfers were made with Osmosis Tokens in this case. In terms of total transfers, the lowest total transfer is 5.16K (Persistence), and the highest total transfer is 5.57M (Osmosis). A maximum of 2.54 million transfers were recorded on March 14, 2022.
According to the below graph, if we consider the swap pairs, we can see which Assets are most commonly swapped for and transferred out?. The top two swap pairs (Osmosis->Osmosis and Osmosis->Cosmos) contributed 33.43% of all swaps. Axelar -> Osmosis has the lowest total Swaps (52), while Osmosis -> Osmosis has the highest total Swaps (1.61). The maximum number of swaps was 348.55K on May 9, 2022. The total number of swaps on June 6, 2022 is lower than normal and higher than normal on May 9, 2022 and June 21, 2021. Looking at the most commonly traded for and transferred out tokens, here we can see that Osmosis is the main token in this scenario.
Observations
As a result of the analysis above, when analyzing a typical user journey through Osmosis,
- With the Sankey chart, we can see what wallets do when they first arrive at Osmosis. The Osmosis wallets perform their first activities such as DEX swap, staking, token transfer, liquidity provision, and government voting over time. From that, we can see the second activity for each of these first activities. This chart shows us clearly what they do when they first arrive at Osmosis. According to the Sankey chart , 374.3K wallets performed their first activity as a DEX swap. Among these wallets, the majority (224.2K) are performing token transfers as their second activity. Next on their list of activities is the provision of liquidity. 422.3K participants engaged in staking as their second activity, while 5.5K voted on governance.
- Based on analysis of the wallets that are joining to swap and then transferring the asset out, the majority of these wallets (224.2K) are performing token transfers as a second activity after the DEX swap. Approximately 49.03% of the total transfers were made with Osmosis Tokens.
- In the analysis above, we can see that when users are active in stakes during their first time activity, they are mostly active in Gov. voting during their second time activity.
- In the Sankey chart above, 91.4K users were completing this type of activity when checking the join-to-swap and then the LP activities.
- According to the above analysis, the top two swap pairs (Osmosis->Osmosis and Osmosis->Cosmos) contributed 33.43% of all swaps. We can see from this which assets are most commonly swapped for and transferred out. Osmosis is the most commonly traded for and transferred out token in this scenario, looking at the most frequently traded for and transferred out tokens.
- Sankey chart shows that a quarter of users (91.4K) join to swap and then make LPs. As a result of their transaction counts, it is possible to see what assets are swapped for and LP’ed most commonly. There is a maximum number of transactions performed by pool ID 2267.
- All these analyses help us identify the typical user journey through osmosis. In addition, there are users who are already engaged in the Osmosis ecosystem and who do different activities in their second chance. Additionally, we see that token transfers have increased since January 2022. It may have been caused by the volatile conditions on the crypto market. Many users are currently transferring tokens, in accordance with the market situation.
Reference Query
1.
WITH
wallet_first_txn as (
SELECT
tx_from as wallet
FROM osmosis.core.fact_transactions
QUALIFY row_number() over(partition by tx_from order by block_timestamp) = 1
),
various_first_txns as (
SELECT
SENDER as wallet,
block_timestamp,
'Token Transfer' as activity
FROM osmosis.core.fact_transfers
WHERE TX_SUCCEEDED
QUALIFY row_number() over(partition by wallet order by block_timestamp) = 1
UNION
SELECT
RECEIVER as wallet,
block_timestamp,
'Airdop'
FROM osmosis.core.fact_airdrop
WHERE TX_SUCCEEDED
QUALIFY row_number() over(partition by wallet order by block_timestamp) = 1
UNION
SELECT
voter as wallet,
block_timestamp,
'Gov. Voting'
FROM osmosis.core.fact_governance_votes
WHERE TX_SUCCEEDED
QUALIFY row_number() over(partition by wallet order by block_timestamp) = 1
UNION
SELECT
LIQUIDITY_PROVIDER_ADDRESS as wallet,
block_timestamp,
'Provide Liquidity'
FROM osmosis.core.fact_liquidity_provider_actions
WHERE TX_SUCCEEDED
QUALIFY row_number() over(partition by wallet order by block_timestamp) = 1
UNION
SELECT
DELEGATOR_ADDRESS as wallet,
block_timestamp,
'Staking'
FROM osmosis.core.fact_staking
WHERE TX_SUCCEEDED
QUALIFY row_number() over(partition by wallet order by block_timestamp) = 1
UNION
SELECT
Trader as wallet,
block_timestamp,
'DEX Swap'
FROM osmosis.core.fact_swaps
WHERE TX_SUCCEEDED
QUALIFY row_number() over(partition by wallet order by block_timestamp) = 1
),
the_first_txn as(
SELECT
various_first_txns.wallet,
block_timestamp,
activity,
date_trunc(week, block_timestamp) as date,
row_number() over(partition by various_first_txns.wallet order by block_timestamp) as activity_seq,
case when activity_seq = 1 then activity end as first_activity,
case when activity_seq = 2 then activity end as second_activity
FROM various_first_txns
INNER JOIN wallet_first_txn USING(wallet)
QUALIFY row_number() over(partition by various_first_txns.wallet order by block_timestamp) <= 2
),
first_second_act as(
SELECT
t1.wallet,
t1.block_timestamp as first_activity_ts,
t1.first_activity,
t2.second_activity,
t2.block_timestamp as second_activity_ts
FROM the_first_txn as t1
INNER JOIN the_first_txn as t2 USING(wallet)
),
first_act_count as (
SELECT
date_trunc(week,first_activity_ts) as date,
'First Activity' as activity_seq,
first_activity as activity,
count(distinct wallet) as wallets
FROM first_second_act
WHERE first_activity is not null
GROUP BY date, first_activity
),
second_act_count as (
SELECT
date_trunc(week,second_activity_ts) as date,
'Second Activity' as activity_seq,
second_activity as activity,
count(distinct wallet) as wallets
FROM first_second_act
WHERE second_activity is not null and first_activity is not null
and first_activity != second_activity
GROUP BY date, second_activity
)
SELECT * FROM first_act_count
UNION
SELECT * FROM second_act_count
----------------
2.
SELECT
date_trunc(week, block_timestamp) as date,
t1.label token_name,
count(tx_id) as transfers
FROM osmosis.core.fact_transfers
INNER JOIN osmosis.core.fact_transactions USING(tx_id)
INNER JOIN osmosis.core.dim_tokens t1 ON CURRENCY = t1.ADDRESS
GROUP BY date, t1.label
QUALIFY row_number() over(partition by date order by transfers desc) <= 5
--------------
3.
SELECT
date_trunc(week, block_timestamp) as date,
lpa.pool_id[0] as pool_id,
count(tx_id) as lp_txns
FROM osmosis.core.fact_liquidity_provider_actions lpa
--INNER JOIN osmosis.core.dim_liquidity_pools pools ON pools.pool_id = lpa.pool_id[0]
WHERE action ='lp_tokens_minted'
GROUP BY date, pool_id
QUALIFY row_number() over(partition by date order by lp_txns desc) <= 5
----------------
4.
SELECT
date_trunc(week, block_timestamp) as date,
t1.label || ' -> ' || t2.label as swap_pair,
count(tx_id) as swaps
FROM osmosis.core.fact_swaps
INNER JOIN osmosis.core.dim_tokens t1 ON FROM_CURRENCY = t1.ADDRESS
INNER JOIN osmosis.core.dim_tokens t2 ON TO_CURRENCY = t2.ADDRESS
GROUP BY date, t1.label, t2.label
QUALIFY row_number() over(partition by date order by swaps desc) <= 10