Osmosis 🧪

The Osmosis automated market maker (AMM) affords users the ability to create new and unique liquidity pools that are controlled and voted on by participants. The Osmosis token (OSMO) is used to vote, stake, and provide liquidity throughout its pools. Superfluid staking, a process novel to the Osmosis protocol, allows users to stake assets to secure the network while simultaneously providing assets in a liquidity pool.

🔗 Links

• Source

• Image Source

• Osmosis - Website

• Osmosis - Twitter

• Osmosis - Discord

✍️ Description of Work

In this dashboard we want to check the Daily Active User on Osmosis. For this, we first obtain DAUs on Osmosis (based on the definition of DAUs presented in the Methodology section), then based on DAUs, we examine the following:

• Total DAUs
  • Total Number of DAUs
  • Total Number of DAUs Transactions
  • DAUs Weekly Transactions
  • DAUs Wallet Creation Date
• DAUs Activity Action (Swap, Liquidity Provider Action, Staking, Staking Rewards, Superfluid Staking, IBC Transfer)
  • Total Number of DAUs per Activity Action
  • Total Number of Transactions per Activity Action by DAUs
  • Weekly Number of Transactions per Activity Action by DAUs
• How often do they (DAUs) transfer tokens into Osmosis (IBC Transfer In)?
  • Average and Frequently Distribution
• Top 10 Tokens Received by DAUs from IBC Transfer by Number of Transfer
  • Total and Weekly Distribution
• Top 10 Tokens Sent by DAUs to IBC Transfer by Number of Transfer

🧠 Methodology

To deal with this dashboard, we obtain the data from Flipsidecrypto using the osmosis.core schema and fact_transfers, fact_transactions, fact_staking, fact_staking_rewards, fact_superfluid_staking, fact_swaps and fact_liquidity_provider_actions tables.

First, we need to understand the definition of DAUs. Well, first, we provide a definition of DAUs according to bounty requirements and explain how to obtain DAUs.

DAUs: consider a DAU would be any wallet transacting on Osmosis a ==majority== of ==days every week==.

Well, now to find DAUs, we first need to define some parameters:

• Activity Start Date: The date of the first transaction of each user in Osmosis.

• Duration of Activity: The duration of a user's activity from the date of the first transaction until today

  • These two parameters are obtained according to the following query:

  select 
  	tx_from as "User", min(block_timestamp::date) as "Activity Start Date", datediff('day', "Activity Start Date", current_date) as "Duration of Activity"
  	from osmosis.core.fact_transactions where tx_status = 'SUCCEEDED' group by "User" having "Activity Start Date" < current_date - 7
  

For this reason, in this query, we have used the having "Activity Start Date" < current_date - 7 command because our goal is to find ==DAUs==, and according to the definition, users must have transactions on ==majority== days of each week, and users who have made their first transaction in Osmosis less than a week ago. It is not yet clear whether they are active users or not because less than a week has passed since their activity. So we ignore them

• Number of Active Days: This parameter specifies the number of days each user has had a transaction

  count(distinct block_timestamp::date) as "Number of Active Days" by each users
  

  \

• Activity Status: This parameter specifies whether a user is active or inactive and is calculated according to the following formula:

  if "Number of Active Days" / "Duration of Activity") * 100 >= 85 then "Activity Status" = 1 else = 0
  

Now it's time to determine why we set "Activity Status" to 0 or 1 for a user in this way?:

> Our goal is to find DAUs and by definition it should be active (have transactions) on majority days of each week. Well, we know that each week is 7 days, so each day of the week is approximately 14.28% of the entire week. Now to make sure that each user He had a transaction on majority days of the week we considered the "Number of Active Days" / "Duration of Activity") 100 >= 85 , users who are active on at least 6 days out of 7 days of the week (6 * 14.28 = 85.7) > > Finally, using the parameters and definitions that were provided, we consider the list of users whose Activity Status = 1 as DAU and obtain other sections according to this users list.

• DAUs Activity Action
  • For this section, we consider Swap, Liquidity Provider Action, Staking, Staking Rewards, Superfluid Staking, IBC Transfer activities
  • To obtain this part of the fact_transfers, fact_staking, fact_staking_rewards, fact_superfluid_staking, fact_swaps and fact_liquidity_provider_actions, we count the list of transactions of DAUs for each action
• How often do they (DAUs) transfer tokens ==into/from== Osmosis (IBC Transfer Out)?
  • To obtain this section, we use the fact_transfers table to find the list of IBC_TRANSFER_OUT and IBC_TRANSFER_IN transactions for DAUs and determine how many consecutive days each user has had IBC transfers. Then we divide the duration of each user's activity by the number of days ("Duration of Activity"/"Number of IBC Transfer Days") that she/he has performed the IBC Transfer, and it is clear that how often do they transfer tokens into Osmosis?

1 → Analyze the Total DAUs

✅ Observations

• The total number of Osmosis users is 571k, of which only 1999 are DAUs according to the definition provided by DAUs, which means they are about 0.3% of all DAU users.
• The total number of Osmosis transactions is 63.5M, of which 16.66M were performed by DAUs, that is, about 26.58% of the total Osmosis transactions were performed by DAUs.

Although only ==0.3%== of all Osmosis users are ==Daily Active==, they made ==26.58%== of all transactions, and this shows the high activity of these types of users.

• In the months of December and February 2021, we see the most creation of wallets by daily active users
• The most transactions of DAUs are from the months of February to the End of May 2022, and after May 2022, their activity has decreased, but they are active again.

2 → Analyze the DAUs Activity Action

✅ Observations

• It can be seen that among the activities of Swap, Liquidity Provider Action, Staking, Staking Rewards, Superfluid Staking, IBC Transfer, the most activities of DAUs are related to Swap, so that among these activities, more than 50% of transactions are related to Swap.
• After Swap, the most active DAUs are related to IBC Transfer (25.5%), Staking Rewards (9%) and Liquidity Provider Action (7%).
• DAUs have done almost 7 Times more Swap than Liquidity Provider Action
• The number of users who participated in these 5 activities (Swap, Liquidity Provider Action, Staking, Staking Rewards, Superfluid Staking, IBC Transfer) is almost equal. That is, most DA users have participated in all activities
• Swap and IBC Transfer activity has recently increased by DAUs and is on an upward trend

3 → How often do they (DAUs) transfer tokens into Osmosis (IBC Transfer In)?

✅ Observations

• According to the obtained results, it can be seen that DAUs transfer tokens into Osmosis on average every 13 days.
• More than 70% of DAUs usually Transfer Tokens into Osmosis Every 1 to 5 Days.
• Also, about 18% of DAUs Transfer Tokens into Osmosis Every 6 to 20 Days, and only 11% of DAUs Transfer Tokens into Osmosis more than every 20 days.

4→ Top 10 Tokens Received by DAUs from IBC Transfer by Number of Transfer

✅ Observations

• DAUs Received more tokens from Cosmos - ATOM
• After Cosmos - ATOM, the most received token is from Bostrom - BOOT, followed by Juno - JUNO
• It can be seen that receiving tokens from Cosmos-ATOM and Juno - JUNO has been a constant process since the beginning
• It can be seen that since the beginning of September 2022, receiving tokens from Bostrom - BOOT by DAUs has increased significantly.

5 → Top 10 Tokens Sent by DAUs to IBC Transfer by Number of Transfer

✅ Observations

• DAUs sent more tokens to Cosmos - ATOM
• After Cosmos - ATOM, the most sent token is to Bostrom - BOOT, followed by Juno - JUNO

✔️ Conclusion

After obtaining and checking DAUs on Osmosis, we obtained the following results:

> * The total number of Osmosis users is 571k, of which only 1999 are DAUs (0.3%). > * The total number of Osmosis transactions is 63.5M, of which 16.66M were performed by DAUs (26.58%). > * Although only ==0.3%== of all Osmosis users are ==Daily Active==, they made ==26.58%== of all transactions, and this shows the high activity of these types of users. > * The most transactions of DAUs are from the months of February to the End of May 2022. > * The most activities of DAUs are related to Swap, so that among Swap, Liquidity Provider Action, Staking, Staking Rewards, Superfluid Staking, IBC Transfer activities, more than 50% of transactions are related to Swap. > * DAUs have done almost 7 Times more Swap than Liquidity Provider Action. > * Swap and IBC Transfer activity has recently increased by DAUs and is on an upward trend. > * DAUs transfer tokens into Osmosis on Average every 13 days. > * More than 70% of DAUs usually Transfer Tokens into Osmosis Every 1 to 5 Days. > * DAUs Received/Sent more tokens From/To Cosmos - ATOM. After Cosmos - ATOM, the most Received/Sent token is From/To Bostrom - BOOT, followed by Juno - JUNO