[Blockchain] Ethereum On-chain Data: DAU, MAU, Stickiness, and Retention
12 min readMay 29, 2023
There are already many Ethereum on-chain data vendor platforms such as Messari, Glassnode, and of course, Etherscan.
Unfortunately, The charts above aren’t very helpful if you try to deeply analyze the on-chain data and find a specific action point regarding any blockchain services.
In this article, I’m going to deep-dive into Ethereum On-chain Data Analysis not just with the DAU and MAU, but also with Stickiness, and Retention Rate, which are the most basic metrics when it comes to Data Analysis. Let’s go dive!
DAU, MAU, and Stickiness (All the Transactions)
1. Ethereum DAU and 30-day Moving MAU
- I can’t figure out any of the meaningful flow from this chart.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address, nonce,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
),
CTE_dau_mau AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date,
COUNT(DISTINCT from_address) AS dau,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mau
FROM CTE_raw MAIN
GROUP BY block_date
ORDER BY block_date DESC
)
SELECT *
FROM CTE_dau_mau
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
;2. Ethereum DAU (grouped by new and old addresses)
- Here, I separated the daily new addresses from old ones with the Nonce value.
- If a transaction’s nonce = 0, then it is regarded as being broadcast by a new address.
- If a transaction’s nonce <> 0, then it is regarded as being broadcast by an old address.
- I can’t figure out any of the meaningful flow from this chart.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address,
CASE
WHEN nonce = 0 THEN 'new_address'
ELSE 'old_address'
END AS new_old,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
),
CTE_dnu_mnu AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date,
new_old,
COUNT(DISTINCT from_address) AS dnu,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mnu
FROM CTE_raw MAIN
GROUP BY block_date, new_old
ORDER BY block_date DESC, new_old
)
SELECT *
FROM CTE_dnu_mnu
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
;3. Ethereum DAU (grouped by transaction types)
- DynamicFee means the transaction has been broadcast in compliance with the EIP-1559.
- Legacy means the transaction has been broadcast with the traditional data format.
- I can’t figure out any of the meaningful flow from this chart.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address, nonce,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
),
CTE_dau_mau AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date, type,
COUNT(DISTINCT from_address) AS dau,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mau
FROM CTE_raw MAIN
GROUP BY block_date, type
ORDER BY block_date DESC, type
)
SELECT *
FROM CTE_dau_mau
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
;4. Ethereum Stickiness
- Stickiness is, by definition, the proportion of users who started their session on a particular day among all the monthly users.
- Users in many social media apps like Facebook, for example, genetically have a tremendously high value of Stickiness Ratio.
- I started to notice from this chart that there could be an upward trend since May 2023
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address, nonce,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
),
CTE_dau_mau AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date,
COUNT(DISTINCT from_address) AS dau,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mau
FROM CTE_raw MAIN
GROUP BY block_date
ORDER BY block_date DESC
),
CTE_dau_mau_stickiness AS ( -- Add "DAU ÷ MAU" Field to CTE_dau_mau
SELECT
block_date, dau, mau,
CAST(dau AS DOUBLE) / CAST(mau AS DOUBLE) * 100.0 AS stickiness
FROM CTE_dau_mau
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
)
SELECT * FROM CTE_dau_mau_stickiness;DAU, MAU, and Stickiness (Contract Call Only)
Wait, I missed one important thing from all the above.
- The number one essence of the Ethereum Network is whether it works properly as an application platform.
- That means it would be much more insightful if I only extract transaction data that has called any contract at least one time, rather than just all the transaction data.
- Let’s draw the same charts again, but only with the contract-calling transactions.
1. Ethereum DAU and 30-day Moving MAU (Contract Call Only)
- I can’t figure out any of the meaningful flow from this chart.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address, nonce,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
AND data <> 0x
),
CTE_dau_mau AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date,
COUNT(DISTINCT from_address) AS dau,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mau
FROM CTE_raw MAIN
GROUP BY block_date
ORDER BY block_date DESC
)
SELECT *
FROM CTE_dau_mau
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
;2. Ethereum DAU (grouped by new and old addresses) (Contract Call Only)
- I can’t figure out any of the meaningful flow from this chart.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address,
CASE
WHEN nonce = 0 THEN 'new_address'
ELSE 'old_address'
END AS new_old,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
AND data <> 0x
),
CTE_dnu_mnu AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date,
new_old,
COUNT(DISTINCT from_address) AS dnu,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mnu
FROM CTE_raw MAIN
GROUP BY block_date, new_old
ORDER BY block_date DESC, new_old
)
SELECT *
FROM CTE_dnu_mnu
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
;3. Ethereum DAU (grouped by transaction types) (Contract Call Only)
- I can’t figure out any of the meaningful flow from this chart.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address, nonce,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
AND data <> 0x
),
CTE_dau_mau AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date, type,
COUNT(DISTINCT from_address) AS dau,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mau
FROM CTE_raw MAIN
GROUP BY block_date, type
ORDER BY block_date DESC, type
)
SELECT *
FROM CTE_dau_mau
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
;4. Ethereum Stickiness (Contract Call Only)
- I got more convinced that the upward trend that I noticed earlier was right here, as well.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address, nonce,
success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-12-01'
AND success = true
AND data <> 0x
),
CTE_dau_mau AS ( -- Aggregate All the Unique FromAddresses Each Day and Each 30-day-long Basket
SELECT
block_date,
COUNT(DISTINCT from_address) AS dau,
(
SELECT COUNT(DISTINCT from_address)
FROM CTE_raw SUB
WHERE
DATE_ADD('DAY', -29, MAIN.block_date) <= SUB.block_date
AND SUB.block_date <= DATE_TRUNC('DAY', MAIN.block_date)
) AS mau
FROM CTE_raw MAIN
GROUP BY block_date
ORDER BY block_date DESC
),
CTE_dau_mau_stickiness AS ( -- Add "DAU ÷ MAU" Field to CTE_dau_mau
SELECT
block_date, dau, mau,
CAST(dau AS DOUBLE) / CAST(mau AS DOUBLE) * 100.0 AS stickiness
FROM CTE_dau_mau
WHERE block_date >= TIMESTAMP '2023-01-01'
ORDER BY block_date DESC
)
SELECT * FROM CTE_dau_mau_stickiness;Cohord Retention (Contract Call Only)
Retention is basically an indicator of how many users come back to the platform over time.
- There are many methodologies to measure the Retention rate, but here I’ll use the Cohort Retention rate.
Cohort Retention is one method of measuring the Retention trends by dividing it based on the first visit date of the new users.
1. Ethereum Cohort Retention (Contract Call Only, Since Jan 2023)
- The new addresses, which has at least one time called contracts, clearly show us a high retention rate over at least a couple of weeks.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address,
nonce, success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2023-01-01'
AND success = true
AND data <> 0x
),
CTE_raw_new AS ( -- Only for the New Addresses
SELECT *
FROM CTE_raw
WHERE from_address IN (
SELECT DISTINCT from_address
FROM CTE_raw
WHERE nonce = 0
)
),
CTE_address_date_first AS ( -- Address's Active Dates & First Date
SELECT
from_address,
block_date AS active_date,
MIN(block_date) OVER (PARTITION BY from_address) AS first_date
FROM CTE_raw_new
GROUP BY from_address, block_date
),
CTE_address_date_first_length AS ( -- Address's Active Dates, First Date, Active Dates - First Date
SELECT
from_address,
active_date, first_date,
DATE_DIFF('DAY', first_date, active_date) AS length
FROM CTE_address_date_first
),
CTE_address_date_first_length7d AS ( -- Address's Active Dates, Weekly(First Date), Weekly(Active Dates - First Date)
SELECT
from_address,
MAX(active_date) AS active_date,
DATE_TRUNC('WEEK', MIN(first_date)) AS first_week,
CEIL(length / 7.0) AS length
FROM CTE_address_date_first_length
GROUP BY from_address, CEIL(length / 7.0)
),
CTE_final AS (
SELECT
first_week,
SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS new_address,
CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_0,
CAST(SUM(CASE WHEN length = 1 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_1,
CAST(SUM(CASE WHEN length = 2 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_2,
CAST(SUM(CASE WHEN length = 3 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_3,
CAST(SUM(CASE WHEN length = 4 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_4
FROM CTE_address_date_first_length7d
GROUP BY first_week
ORDER BY first_week
)
SELECT * FROM CTE_final;2. Ethereum Cohort Retention (Contract Call Only, Since Jan 2022)
- Let’s do the same as above, but this time starting from January 2022, so that we can get convinced a lot more.
- The retention rate on May 2023 was the second highest since last year, which deserves more analysis.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address,
nonce, success, type,
value, data
FROM ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-01-01'
AND success = true
AND data <> 0x
),
CTE_raw_new AS ( -- Only for the New Addresses
SELECT *
FROM CTE_raw
WHERE from_address IN (
SELECT DISTINCT from_address
FROM CTE_raw
WHERE nonce = 0
)
),
CTE_address_date_first AS ( -- Address's Active Dates & First Date
SELECT
from_address,
block_date AS active_date,
MIN(block_date) OVER (PARTITION BY from_address) AS first_date
FROM CTE_raw_new
GROUP BY from_address, block_date
),
CTE_address_date_first_length AS ( -- Address's Active Dates, First Date, Active Dates - First Date
SELECT
from_address,
active_date, first_date,
DATE_DIFF('DAY', first_date, active_date) AS length
FROM CTE_address_date_first
),
CTE_address_date_first_length7d AS ( -- Address's Active Dates, Weekly(First Date), Weekly(Active Dates - First Date)
SELECT
from_address,
MAX(active_date) AS active_date,
DATE_TRUNC('WEEK', MIN(first_date)) AS first_week,
CEIL(length / 7.0) AS length
FROM CTE_address_date_first_length
GROUP BY from_address, CEIL(length / 7.0)
),
CTE_final AS (
SELECT
first_week,
SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS new_address,
CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_0,
CAST(SUM(CASE WHEN length = 1 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_1,
CAST(SUM(CASE WHEN length = 2 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_2,
CAST(SUM(CASE WHEN length = 3 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_3,
CAST(SUM(CASE WHEN length = 4 THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN length = 0 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 AS week_4
FROM CTE_address_date_first_length7d
GROUP BY first_week
ORDER BY first_week
)
SELECT * FROM CTE_final;Why the Retention rate was high on May 2023?
The fact that the Retention rate has increased a lot shows there should be a sign that the users have just started to have a positive experience that they have never done so before.
- Now, let’s extract the contract address list that has been called the most time by the new addresses from May 1 to May 8, 2023.
- Let’s find out what are the top 2 contracts in the table shown below.
WITH
CTE_raw AS ( -- All the required Fields
SELECT
DATE_TRUNC('DAY', block_time) AS block_date,
block_number,
"from" AS from_address,
"to" AS to_address,
nonce, success, type,
value, data
FROM ethereum.transactions
WHERE
TIMESTAMP '2023-05-01' <= block_time AND block_time < TIMESTAMP '2023-05-08'
AND success = true
AND data <> 0x
),
CTE_raw_new AS ( -- Only for the New Addresses
SELECT *
FROM CTE_raw
WHERE from_address IN (
SELECT DISTINCT from_address
FROM CTE_raw
WHERE nonce = 0
)
),
CTE_ca_new_list AS (
SELECT
to_address,
COUNT(DISTINCT from_address) AS address_cnt
FROM CTE_raw_new
GROUP BY to_address
ORDER BY address_cnt DESC
LIMIT 100
)
SELECT * FROM CTE_ca_new_list;Rank 1. Tether USD (USDT) Contract
- Tether USDT is one of the most traded tokens in the Ethereum Network as a stablecoin, so this contract does not show us any insightful change in the new user experience.
Rank 2. Uniswap Universal Router Contract
Now we gotcha. The Uniswap Universal Router, which was introduced on November 2022, has been contributing to the positive user experience in Ethereum Network.
- As seen above, we are able to execute multiple separate swaps in a single transaction with less gas fee.
Let’s Conclude!
- The new addresses on May 2023 show the second-highest Retention rate since January 2022.
- This sudden rise in the Retention rate seems to have been greatly influenced by the introduction and quick-and-wide adoption of the Uniswap Universal Router.
Joshua Kim
- Data Analyst
- Master in AI Big Data
- LinkedIn Bio | LinkTree
- Dune Analytics Dashboard
- joshuajkim413@gmail.com
