Web3 Analytics For Solana Using Flipside Crypto

https://en.bitcoinsistemi.com/analytics-firm-unveils-the-origins-of-the-latest-solana-sol-rally/

Solana has experienced significant growth in its developer ecosystem. So, the need for web3 analytics tools has become important to measure user and developer activity, track transaction data, and gain insights. Flipside Crypto is a leading web3 analytics platform that provides users with instant access to comprehensive analytics for 26+ blockchain networks.

In this technical tutorial, you will learn more details about Flipside Crypto, and the data available and look at published dashboards targeting the Solana ecosystem. Besides this, you will gain insights into the key metrics used to analyze blockchains and develop dashboards for 3 different use cases using Flipside:

• Monitoring adoption and activity
• Comparing Solana marketplaces
• Tracking user loyalty

Prerequisites

To follow along, make sure you have

• signed up for a free flipside account
• basic SQL skills

Flipside Crypto Overview

To access updated data on the Solana blockchain, log in to your free account to launch the studio. In the left menu, you can access the list of available blockchains and start exploring your data.

The data is organized in tables core, defi, gov, nft, and price. Under these tables, you will see various views. If you hover your mouse on a view’s name, details about it will be displayed. The screenshot below shows views with Solana data under the core table.

The view names have one of the 3 prefixes that signal to the user what type of information they have:

• fact — constitute observations or events, providing a concise overview of on-chain activities.
• dim — provides contextual information within a block or transaction
• ez — presents labeled and tagged perspectives on activities and entities on-chain.

To analyze the blockchain data, you will need to write and run SQL queries to retrieve the insights you need. Flipside makes it easy to do this by selecting + > query on the left menu. A blank query will be opened and listed on the collections panel on the left.

To write the query, you need to know which view you want to query and the fields involved. Open the desired table and select the view. You will be presented with a list of fields present in that view.

After writing your query and running it, you can create a chart using the results by clicking the +Add Chart button at the bottom. Once you have your charts ready, you can go ahead and create a dashboard by clicking + > Dashboard then give it a name. Add your charts, organize and resize them as you’d like. Once you have finished, click publish and your dashboard is live.

Metrics

In this section, you will learn how to write SQL queries to extract insights from Solana’s blockchain data, create charts, and add them to your dashboard. From the collections panel, click + > Add Collection and name it Solana Dashboard. In the collection, click + > Dashboard and name it Dashboard. Let’s begin with the metrics outlined above.

Disclaimer: All analysis and deductions made are based on data collected at the time of publishing this article (On 11th January 2024)

Monitoring Adoption and Activity

Adoption relates to insights about the people using Solana while activity looks at the transactions these people are making. For this tutorial, you look at the following key metrics:

• Total number of transactions made
• Total number of users
• Daily transactions
• Daily users

Create a collection under the Solana Dashboard collection called adoption. Create a query and name it totals. Add the SQL query as shown below.

SELECT 
  COUNT(DISTINCT tx_id) AS txs,
  COUNT(DISTINCT signers[0]) AS usrs,
  txs/COUNT(DISTINCT block_timestamp::date) as daily_avg_tx,
  usrs/COUNT(DISTINCT block_timestamp::date) as daily_avg_usr
FROM solana.core.fact_events
WHERE 
  block_timestamp > CURRENT_TIMESTAMP - INTERVAL '1 Month'
  AND SUCCEEDED

The query above fetches data for the past 1 month from the fact_events view of the core table and aggregates the number of transactions and users. Run the query to get the results that you will visualize. Since the results are discrete data, you will use the single number chart item to showcase them. Click + Add Chart > Single number, set the title to “Daily Average Transactions” and select the column to be txs. Repeat the process to add one more chart for Daily Average Users. The 2 visuals should appear as shown below.

Create another query under the adoption collection and name it trends. Add the following SQL query

SELECT
  DATE_TRUNC('day', block_timestamp) as date,
  COUNT(DISTINCT (tx_id)) AS txs,
  COUNT(DISTINCT (signers[0])) AS usrs,
  SUM(txs) over (ORDER BY date) as cum_txs,
  SUM(usrs) over (ORDER BY date) as cum_usrs
FROM solana.core.fact_events
WHERE 
  block_timestamp > CURRENT_TIMESTAMP - INTERVAL'3 month'
  AND SUCCEEDED
GROUP BY 1
HAVING COUNT(DISTINCT (tx_id)) > 0
ORDER BY 1 DESC

The query above fetches data for the past year from the fact_events view of the core table and counts the number of unique transactions and users for each day. Furthermore, it also calculates the running totals for the transactions and users over the past 3 months. The results are continuous data, therefore you can create a line chart to visualize it. Click + > Line Chart, give it a title, and set the X-axis to date and the Y-axis to

From the charts above, you can see a steady rise in the transaction and user count in Solana for the [ast 3 months

Comparing Solana Marketplaces

Marketplaces in Solana allow sellers to mint NFTs for potential buyers to buy. In this section, you will compare the various marketplaces built on Solana and gain insights into their performance. The key metrics you will use to compare are:

• Total number of sales
• Total volume of sales
• Weekly sales count
• Weekly sales volumes

Create a collection under Solana Dashboard collection called compare and create a query and name it totals. Add the SQL query as shown below.

SELECT 
  marketplace,
  COUNT(DISTINCT(tx_id)) AS sales_count
FROM solana.nft.fact_nft_sales 
WHERE
  block_timestamp > CURRENT_TIMESTAMP - INTERVAL'1 Year'
  AND succeeded
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

The query above fetches data for the past year from the fact_nft_sales view of Solana’s nft table and computes the total number of sales made for each marketplace. It sorts the results in descending order and picks only the top 10 marketplaces.

This being categorical data, you can visualize it using a pie chart. Click + Add Chart > Donut chart, and give it the title “Ratio of Sales in Top 10 NFT Marketplaces”. Under the data section, set the slice to marketplace and angle to sales count. Make sure you add this chart to your dashboard for later use. The chart looks something like this, indicating that Magic Eden is the dominant NFT marketplace followed by Tensorswap

Create another query under the compare collection and name it trends. Add the SQL query as shown below

SELECT 
  date_trunc('week', block_timestamp) as date,
  marketplace,
  COUNT(DISTINCT(tx_id)) AS sales_count,
  COUNT(DISTINCT(purchaser)) as total_buyers,
  SUM(sales_amount) as sales_volume
FROM solana.nft.fact_nft_sales 
WHERE
  block_timestamp > CURRENT_TIMESTAMP - INTERVAL'1 Year'
  AND succeeded
GROUP BY 1,2
ORDER BY 1 ASC

The query above fetches data for the past year from the fact_nft_sales view of Solana’s nft table and calculates the total # of sales, buyers, and sales volumes for each marketplace per week. The results are ordered from the oldest to the latest. This being continuous data, you can visualize it by creating

• 2 separate line graphs to show the total number of sales and buyers over time per marketplace. Select + Add Chart > Line chart, give the chart a title, and set the X-axis as Date, the Y-axis as sales count, and total buyers. Make sure you group the data by marketplace. This should output 2 charts like so.

[insert chart]

• 1 bar graph to show the total volume of sales over time per marketplace. Select + Add Chart > Bar chart, give the chart a title, and set the X-axis to date and, the Y-axis to sales volume. Make sure you group by the marketplace. The chart should look as follows

The charts above show that at the beginning of January 2023, the number of sales and buyers declined and increased from November 2023. During this bullish period, tensor swap experienced a higher sales count and buyers than Magic Eden. This can be confirmed by the sales volume chart, but, you’ll notice some spikes in sales volumes on Feb and Apr 2023 from Tensorswap.

Tracking user loyalty

The main question this section will try to answer is where are users transacting on the Solana blockchain? The following metrics will be used to track user loyalty:

• Top 10 NFTs
• Top 10 protocols
• Top 10 Solana tokens

Create a collection under Solana Dashboard collection called loyalty and create a query and name it nfts. Add the SQL query as shown below.

SELECT
  meta.nft_name AS nft,
  COUNT(DISTINCT(sales.tx_id)) AS sales_count
FROM 
  solana.nft.fact_nft_sales sales
  JOIN Solana.nft.dim_nft_metadata meta ON sales.mint = meta.mint
WHERE
  sales.block_timestamp > CURRENT_TIMESTAMP - INTERVAL'1 Year'
  AND meta.nft_name is not null
  AND nft != ''
  AND nft != 'test'
  AND sales.succeeded
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

The query above aggregates the sales count per NFT project for the past year. This data is pulled from.fact_nft_sales of Solana’s nft table and the mint address is decoded to its NFT name using the dim_nft_metadata view. Create a bar chart and set the X-axis to nft and the Y-axis to sales count. Make sure you order the sales count in descending order

This visual demonstrates lamp NFTs had the most sales, followed by Pepe and FREE.

Create another query under the loyalty collection and name it protocols. Add the SQL query as shown below

SELECT
  lbl.label AS protocol,
  COUNT(DISTINCT(tx.signers[0])) AS users
FROM
  solana.core.fact_transactions tx 
  JOIN solana.core.dim_labels lbl ON tx.instructions[0]:programId = lbl.address
WHERE
  tx.block_timestamp > CURRENT_TIMESTAMP - INTERVAL'1 Year'
  AND lbl.label_subtype != 'token_contract' 
  AND lbl.label != 'solana'
  AND tx.succeeded
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

The query above aggregates the total number of users per Solana Protocol for the past year. This data is pulled from fact_transactions and dim_labels view of Solana’s core table. The program ID in the transaction’s instruction is used to resolve records to retrieve the protocol’s name. Create a bar chart and set the X-axis to protocol and the Y-axis to users. Make sure you order the number of users in descending order

The charts above show that Metaplex, Magic Eden, and Jupiter were the leading Solana protocols in the past year

Create another query under the loyalty collection and name it tokens. Add the SQL query as shown below

SELECT
  l.label AS token,
  COUNT(DISTINCT(t.tx_id)) AS transfer_count
FROM
  solana.core.fact_transfers t 
  LEFT JOIN solana.core.dim_labels l ON t.mint = l.address
WHERE
  t.block_timestamp > CURRENT_TIMESTAMP - INTERVAL'1 Year
  AND token != 'wrapped sol'
  AND token IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

The query above aggregates the total number of transactions per token on Solana for the past year. This data is pulled from fact_transactions and dim_labels view of Solana’s core table. The mint address in the transaction data is decoded to the token’s name using the dim_nft_metadata view. Create a bar chart and set the X-axis to token and the Y-axis to transfer count. Make sure you order the transfer count in descending order

The chart above indicates that users on Solana prefer to transact with USDC tokens.

Publish

Your charts are now ready to be added to your dashboard. Open the dashboard created earlier and create 3 tabs to organize your charts. Name them user adoption, marketplace comparison, and user loyalty. Add all the charts created in the previous section in the appropriate tab and resize/re-organize to your liking. Once finished, click publish to share your work with others. Here’s a link to the published Solana Analytics dashboard

Conclusion

Congratulations on analyzing Solana and creating your dashboard. You have learned how Flipside Crypto offers powerful tools to start analyzing blockchain data and visualize the results. In this tutorial, you’ve learned how to create descriptive analytics for Solana by using SQL queries and visualizing the results using charts. For the scope of this tutorial, you have touched on key metrics like monitoring adoption, comparing marketplaces, and user loyalty. Feel free to explore different metrics and extract more insights as Flipside Crypto offers a variety of data.