✨ Dune novice project combat

—— Beginners can also use Dune to do project data analysis

@DuneAnalytics Dune's official use case analysis mentions three types of usage scenarios:

▪ Industry analysis (NFT, DEX, Lending) ▪ Project analysis (Tornado, OpenSea) ▪ Ecosystem Analysis (Gas Price, DeFi Adoption)

Today's 🧵 with @aztecnetwork For example, let's see how to do project data analysis

1/ What data can Dune track for the project?

Dune currently supports 5 chains, and you can get all its on-chain transaction record data.

After a certain amount of filtering, analysis, and summarization, you can track the following data of the project:

▪ Number of users ▪ Trading volume, lock-up volume (TVL) ▪ Data growth trend ▪ Extremum, mean, data distribution

For specific projects, we filter valuable data summary presentation (data is automatically updated over time)

2/ How to get started with Dune

Getting started with Dune is easier than you might think, especially with only the basic skills

▪ SQL query statement basics ▪ Use and interpretation of Etherscan website ▪ DeFi basics ▪ Basic concepts of smart contracts

3/ High-level perspective: look at project data like a VC investor

Aztec closed a $17 million Series A round led by Paradigm in December

When we research projects, we must also judge the quality and popularity of projects from the perspective of VC

▪ How many users of the project ▪ How much money the user has deposited into the smart contract ▪ What is the average deposit amount per person ▪ User usage frequency

The data tells us:

Roll or not? How to roll? How scientifically rational volumes?

4/ The first step of project analysis: locate the contract address

When using Dune for project data analysis, the first step is to accurately locate the smart contract address of the project. In this way, the relevant data can be filtered out based on this/this batch of addresses.

The easiest and most accurate way is to interact with the project’s official website in person, and then find the corresponding contract address from the interactive address.

Make further confirmation in Etherscan, such as the name of the open source contract, etc.

5/ Review interaction records

Select a successful interaction record arbitrarily, the following data can be extracted and used:

▪ Timestamp ▪ from: send the transaction party address ▪ to: contract address ▪ value: the amount of ETH transferred ▪ inputdata: attached data (function name, parameters, values) ▪ Gas related data ▪ Event log information

6/ Project data extraction test and verification

Now let's try to extract Aztec's data (zk. money) at Dune

Use " where to='\x' " to limit the extraction of data that interacts with the Aztec contract, and "limit=10" to limit the number of results to speed up data processing

Find the first interactive record with the transfer amount, accurately locate the etherscan through the "hash" information, check the transfer amount and find that the two are exactly the same ✅

7/ Count the total number of contract interaction addresses

👉 ' count(distinct "from") as users ' statement, which means that the distinct method is used to remove duplicates (unique interactive addresses) for the "from" field, and then the count method is used to calculate the number

The trailing line 'and success' means to filter only records with successful transactions

✅ Dune told us that the number of unique addresses that have interacted with zk money contracts so far is:

65,930

8/ Count the total amount of ETH deposits

The value field records the ETH transfer amount in a transaction

👉 'sum(value / 1e18) as total' statement, first divide the field value by '1e18' and convert it to ordinary ETH units, then use the sum method to accumulate, and use as to give an alias of total

With just this statement, the total amount of ETH deposited into the contract by all users in history can be counted

✅ Settlement result: 32,341 ETH

8/ Count the total amount of ETH deposits

The value field records the ETH transfer amount in a transaction

👉 'sum(value / 1e18) as total' statement, first divide the field value by '1e18' and convert it to ordinary ETH units, then use the sum method to accumulate, and use as to give an alias of total

With just this statement, the total amount of ETH deposited into the contract by all users in history can be counted

✅ Settlement result: 32,341 ETH

9/ Count the average and maximum ETH deposits and visualize the data

With the total deposit amount of ETH and the total number of people, it is a matter of course to calculate the average deposit amount and the maximum deposit amount.

👉 avg, max two methods just come in handy

✅ per capita deposit 0.39 ETH

In addition, multiple numerical results can be extracted and calculated at one time, and the corresponding data can be visualized separately. In the figure, 4 Counters are created to store them one by one

10/ Statistics by time period

In addition to summary values, extreme values, mean values, more important trends:

As time changes, the number of users, deposit amount and other values change, and the trend graph is aggregated to judge the user popularity and development potential of the project.

👉 "group by day" means to group the data by day, and then use the sum aggregation function to count the daily total

We select the daily number of users and the total amount of ETH deposits to make a graph

11/ Total Cumulative Deposit Curve

The Cumulative Sum curve is a curve formed by adding the current data to the previous data and continuously accumulating according to the time period

You can intuitively see the growth trend of the data: linear growth, exponential growth, whether the growth trend is slowing down, etc.

12/ Analysis of User Deposit Amount Range

Analyze user quality by dividing the user's deposit amount into intervals

First use the "WITH aztec AS" subquery to count the deposit amount of each user, and then use the "CASE WHEN" control flow statement to divide it into fixed intervals

▪ 71% of users deposit between 0.01~0.1 ETH ▪ 21% at 0.1~1 ETH ▪ 8% other

✅ When you deposit more than 0.1 ETH already more than 71% users

13/ User deposit frequency analysis

The traditional retail industry uses RFM models to analyze customer behavior and divide high-quality customer groups. Among them, F refers to "consumption frequency", and M refers to "consumption amount"

Use " COUNT(*) " to count the number of deposits per address, and " GROUP BY 'from' " to group the data

▪ Only once: 81.8% ▪ 2 times: 14.8% ▪ 3 or more times: 3.4%

✅ Use more than 2 times to surpass 82% of users

14/ Identify big and heavy users

▪ Large Project Accounts: Users with the most deposits ▪ Heavy users: users who use the most frequently

👇 ▪ SELECT 3 fields: address, total deposit amount, frequency of use ▪ ORDER BY sort by total, frequency, DESC in reverse order ▪ HAVING for GROUP BY qualification ▪ LIMIT 100 Show TOP

✅ The total deposit of large accounts is 480ETH ✅ Heavy users use 85 times

✨ Dune novice project combat

—— Beginners can also use Dune to do project data analysis

@DuneAnalytics Dune's official use case analysis mentions three types of usage scenarios:

▪ Industry analysis (NFT, DEX, Lending) ▪ Project analysis (Tornado, OpenSea) ▪ Ecosystem Analysis (Gas Price, DeFi Adoption)

Today's 🧵 with @aztecnetwork For example, let's see how to do project data analysis

1/ What data can Dune track for the project?

Dune currently supports 5 chains, and you can get all its on-chain transaction record data.

After a certain amount of filtering, analysis, and summarization, you can track the following data of the project:

▪ Number of users ▪ Trading volume, lock-up volume (TVL) ▪ Data growth trend ▪ Extremum, mean, data distribution

For specific projects, we filter valuable data summary presentation (data is automatically updated over time)

2/ How to get started with Dune

Getting started with Dune is easier than you might think, especially with only the basic skills

▪ SQL query statement basics ▪ Use and interpretation of Etherscan website ▪ DeFi basics ▪ Basic concepts of smart contracts

3/ High-level perspective: look at project data like a VC investor

Aztec closed a $17 million Series A round led by Paradigm in December

When we research projects, we must also judge the quality and popularity of projects from the perspective of VC

▪ How many users of the project ▪ How much money the user has deposited into the smart contract ▪ What is the average deposit amount per person ▪ User usage frequency

The data tells us:

Roll or not? How to roll? How scientifically rational volumes?

4/ The first step of project analysis: locate the contract address

When using Dune for project data analysis, the first step is to accurately locate the smart contract address of the project. In this way, the relevant data can be filtered out based on this/this batch of addresses.

The easiest and most accurate way is to interact with the project’s official website in person, and then find the corresponding contract address from the interactive address.

Make further confirmation in Etherscan, such as the name of the open source contract, etc.

5/ Review interaction records

Select a successful interaction record arbitrarily, the following data can be extracted and used:

▪ Timestamp ▪ from: send the transaction party address ▪ to: contract address ▪ value: the amount of ETH transferred ▪ inputdata: attached data (function name, parameters, values) ▪ Gas related data ▪ Event log information

6/ Project data extraction test and verification

Now let's try to extract Aztec's data (zk. money) at Dune

Use " where to='\x' " to limit the extraction of data that interacts with the Aztec contract, and "limit=10" to limit the number of results to speed up data processing

Find the first interactive record with the transfer amount, accurately locate the etherscan through the "hash" information, check the transfer amount and find that the two are exactly the same ✅

7/ Count the total number of contract interaction addresses

👉 ' count(distinct "from") as users ' statement, which means that the distinct method is used to remove duplicates (unique interactive addresses) for the "from" field, and then the count method is used to calculate the number

The trailing line 'and success' means to filter only records with successful transactions

✅ Dune told us that the number of unique addresses that have interacted with zk money contracts so far is:

65,930

8/ Count the total amount of ETH deposits

The value field records the ETH transfer amount in a transaction

👉 'sum(value / 1e18) as total' statement, first divide the field value by '1e18' and convert it to ordinary ETH units, then use the sum method to accumulate, and use as to give an alias of total

With just this statement, the total amount of ETH deposited into the contract by all users in history can be counted

✅ Settlement result: 32,341 ETH

8/ Count the total amount of ETH deposits

The value field records the ETH transfer amount in a transaction

👉 'sum(value / 1e18) as total' statement, first divide the field value by '1e18' and convert it to ordinary ETH units, then use the sum method to accumulate, and use as to give an alias of total

With just this statement, the total amount of ETH deposited into the contract by all users in history can be counted

✅ Settlement result: 32,341 ETH

9/ Count the average and maximum ETH deposits and visualize the data

With the total deposit amount of ETH and the total number of people, it is a matter of course to calculate the average deposit amount and the maximum deposit amount.

👉 avg, max two methods just come in handy

✅ per capita deposit 0.39 ETH

In addition, multiple numerical results can be extracted and calculated at one time, and the corresponding data can be visualized separately. In the figure, 4 Counters are created to store them one by one

10/ Statistics by time period

In addition to summary values, extreme values, mean values, more important trends:

As time changes, the number of users, deposit amount and other values change, and the trend graph is aggregated to judge the user popularity and development potential of the project.

👉 "group by day" means to group the data by day, and then use the sum aggregation function to count the daily total

We select the daily number of users and the total amount of ETH deposits to make a graph

11/ Total Cumulative Deposit Curve

The Cumulative Sum curve is a curve formed by adding the current data to the previous data and continuously accumulating according to the time period

You can intuitively see the growth trend of the data: linear growth, exponential growth, whether the growth trend is slowing down, etc.

12/ Analysis of User Deposit Amount Range

Analyze user quality by dividing the user's deposit amount into intervals

First use the "WITH aztec AS" subquery to count the deposit amount of each user, and then use the "CASE WHEN" control flow statement to divide it into fixed intervals

▪ 71% of users deposit between 0.01~0.1 ETH ▪ 21% at 0.1~1 ETH ▪ 8% other

✅ When you deposit more than 0.1 ETH already more than 71% users

13/ User deposit frequency analysis

The traditional retail industry uses RFM models to analyze customer behavior and divide high-quality customer groups. Among them, F refers to "consumption frequency", and M refers to "consumption amount"

Use " COUNT(*) " to count the number of deposits per address, and " GROUP BY 'from' " to group the data

▪ Only once: 81.8% ▪ 2 times: 14.8% ▪ 3 or more times: 3.4%

✅ Use more than 2 times to surpass 82% of users

14/ Identify big and heavy users

▪ Large Project Accounts: Users with the most deposits ▪ Heavy users: users who use the most frequently

👇 ▪ SELECT 3 fields: address, total deposit amount, frequency of use ▪ ORDER BY sort by total, frequency, DESC in reverse order ▪ HAVING for GROUP BY qualification ▪ LIMIT 100 Show TOP

✅ The total deposit of large accounts is 480ETH ✅ Heavy users use 85 times