This schema contains comprehensive datasets for tracking Abstract fundamental data across multiple metrics categories, including network activity, Layer 2 economics, revenue distribution, and cash flow allocation between Layer 1 costs and foundation operations.
Available Tables
Abstract data is available in the main metrics table:
- ez_metrics: Aggregated metrics for the Abstract network including Layer 2 economics and cash flow distribution
Table Schema
Network and Usage Metrics
| Table Name | Column Name | Description |
|---|
| ez_metrics | chain_dau | Daily unique users on Abstract |
| ez_metrics | chain_txns | Daily transactions on Abstract |
| ez_metrics | dau | Same as chain_dau (legacy naming) |
| ez_metrics | txns | Same as chain_txns (legacy naming) |
Fee and Revenue Metrics
| Table Name | Column Name | Description |
|---|
| ez_metrics | ecosystem_revenue | The total USD value generated by Abstract from all user-paid fees |
| ez_metrics | ecosystem_revenue_native | The total native value generated by Abstract from all user-paid fees |
| ez_metrics | fees | Same as ecosystem_revenue (legacy naming) |
| ez_metrics | fees_native | Same as ecosystem_revenue_native (legacy naming) |
Layer 2 Economics and Cash Flow Distribution
| Table Name | Column Name | Description |
|---|
| ez_metrics | l1_cash_flow | The total USD value of L1 cash flow - fees paid to Ethereum for data posting and settlement |
| ez_metrics | l1_cash_flow_native | The total native value of L1 cash flow - fees paid to Ethereum in native tokens |
| ez_metrics | foundation_cash_flow | Revenue allocated to the Abstract foundation for grants, partnerships, operations, and legal expenses |
| ez_metrics | foundation_cash_flow_native | Foundation revenue in native tokens |
| ez_metrics | cost | Same as l1_cash_flow (legacy naming) |
| ez_metrics | cost_native | Same as l1_cash_flow_native (legacy naming) |
| ez_metrics | revenue | Same as foundation_cash_flow (legacy naming) |
| ez_metrics | revenue_native | Same as foundation_cash_flow_native (legacy naming) |
Sample Queries
Basic Network Activity Query
-- Pull fundamental network activity data for Abstract
SELECT
date,
chain_txns,
chain_dau,
ecosystem_revenue,
foundation_cash_flow,
l1_cash_flow
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
date ASC
Layer 2 Economics Analysis
-- Analyze Abstract's Layer 2 economics and profitability
SELECT
date,
ecosystem_revenue,
l1_cash_flow,
foundation_cash_flow,
l1_cash_flow / NULLIF(ecosystem_revenue, 0) * 100 as l1_cost_percentage,
foundation_cash_flow / NULLIF(ecosystem_revenue, 0) * 100 as foundation_percentage,
(ecosystem_revenue - l1_cash_flow) as net_l2_revenue,
foundation_cash_flow / NULLIF((ecosystem_revenue - l1_cash_flow), 0) * 100 as foundation_of_net_revenue
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
AND ecosystem_revenue > 0
ORDER BY
date ASC
Cash Flow Distribution Analysis
-- Track how Abstract distributes its revenue between L1 costs and foundation
SELECT
date,
ecosystem_revenue,
l1_cash_flow,
foundation_cash_flow,
chain_txns,
l1_cash_flow / NULLIF(chain_txns, 0) as l1_cost_per_txn,
ecosystem_revenue / NULLIF(chain_txns, 0) as revenue_per_txn,
foundation_cash_flow / NULLIF(chain_txns, 0) as foundation_revenue_per_txn
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
AND chain_txns > 0
ORDER BY
date ASC
Network Growth and Usage Trends
-- Track Abstract network growth and user adoption
SELECT
date,
chain_dau,
chain_txns,
ecosystem_revenue,
chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
ecosystem_revenue / NULLIF(chain_dau, 0) as revenue_per_user,
LAG(chain_dau, 7) OVER (ORDER BY date) as dau_7d_ago,
LAG(chain_txns, 7) OVER (ORDER BY date) as txns_7d_ago,
(chain_dau - LAG(chain_dau, 7) OVER (ORDER BY date)) / NULLIF(LAG(chain_dau, 7) OVER (ORDER BY date), 0) * 100 as dau_growth_7d,
(chain_txns - LAG(chain_txns, 7) OVER (ORDER BY date)) / NULLIF(LAG(chain_txns, 7) OVER (ORDER BY date), 0) * 100 as txns_growth_7d
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
date ASC
Revenue Efficiency Analysis
-- Analyze Abstract's revenue generation efficiency
SELECT
date,
ecosystem_revenue,
foundation_cash_flow,
l1_cash_flow,
chain_txns,
chain_dau,
foundation_cash_flow / NULLIF(ecosystem_revenue, 0) * 100 as foundation_take_rate,
l1_cash_flow / NULLIF(ecosystem_revenue, 0) * 100 as l1_cost_rate,
(ecosystem_revenue - l1_cash_flow - foundation_cash_flow) / NULLIF(ecosystem_revenue, 0) * 100 as other_allocation_rate
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -1, CURRENT_DATE())
AND ecosystem_revenue > 0
ORDER BY
date DESC
Foundation Revenue Tracking
-- Track foundation revenue and operational funding
SELECT
date,
foundation_cash_flow,
foundation_cash_flow_native,
ecosystem_revenue,
chain_dau,
foundation_cash_flow / NULLIF(chain_dau, 0) as foundation_revenue_per_user,
SUM(foundation_cash_flow) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as foundation_revenue_30d,
AVG(foundation_cash_flow) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as foundation_revenue_7d_avg
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
date ASC
L1/L2 Cost Analysis
-- Deep dive into Layer 1 vs Layer 2 cost structure
SELECT
date,
l1_cash_flow,
l1_cash_flow_native,
ecosystem_revenue,
chain_txns,
l1_cash_flow / NULLIF(chain_txns, 0) as l1_cost_per_transaction,
ecosystem_revenue / NULLIF(chain_txns, 0) as total_revenue_per_transaction,
(ecosystem_revenue - l1_cash_flow) / NULLIF(chain_txns, 0) as net_l2_revenue_per_transaction,
l1_cash_flow / NULLIF(ecosystem_revenue, 0) * 100 as l1_cost_as_percentage_of_revenue
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
AND chain_txns > 0
ORDER BY
date ASC
-- Comprehensive daily performance summary
SELECT
date,
chain_dau,
chain_txns,
ecosystem_revenue,
l1_cash_flow,
foundation_cash_flow,
-- Efficiency metrics
chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
ecosystem_revenue / NULLIF(chain_txns, 0) as avg_fee_per_txn,
foundation_cash_flow / NULLIF(ecosystem_revenue, 0) * 100 as foundation_percentage,
l1_cash_flow / NULLIF(ecosystem_revenue, 0) * 100 as l1_cost_percentage
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(week, -4, CURRENT_DATE())
ORDER BY
date DESC
Revenue Breakdown Over Time
-- Track revenue allocation trends over time
SELECT
date,
ecosystem_revenue,
l1_cash_flow,
foundation_cash_flow,
ecosystem_revenue - l1_cash_flow - foundation_cash_flow as unallocated_revenue,
-- 30-day moving averages
AVG(l1_cash_flow / NULLIF(ecosystem_revenue, 0) * 100) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_l1_percentage_30d,
AVG(foundation_cash_flow / NULLIF(ecosystem_revenue, 0) * 100) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_foundation_percentage_30d
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -6, CURRENT_DATE())
AND ecosystem_revenue > 0
ORDER BY
date ASC
Network Economics Comparison
-- Compare different periods of network economics
WITH monthly_summary AS (
SELECT
DATE_TRUNC('month', date) as month,
SUM(ecosystem_revenue) as total_revenue,
SUM(l1_cash_flow) as total_l1_cost,
SUM(foundation_cash_flow) as total_foundation_revenue,
AVG(chain_dau) as avg_dau,
SUM(chain_txns) as total_txns
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
month,
total_revenue,
total_l1_cost,
total_foundation_revenue,
avg_dau,
total_txns,
total_l1_cost / NULLIF(total_revenue, 0) * 100 as l1_cost_percentage,
total_foundation_revenue / NULLIF(total_revenue, 0) * 100 as foundation_percentage,
total_revenue / NULLIF(total_txns, 0) as avg_revenue_per_txn
FROM
monthly_summary
ORDER BY
month DESC
