Refactor S7 metrics models

[ccerv1]

What is it?

@ryscheng prepared an explainer on the data flow and how the models fit together. Would appreciate feedback on how to go about this!

Basic Data Flow

1. Base transaction data is processed through traces_txs_joined
2. Events are attributed to onchain projects in trace_level_events
3. Onchain projects are evaluated for eligibility
4. Cross-project relationships are established through dependency and developer graphs between onchain projects and devtooling projects
5. Devtooling projects are evaluated for eligibility (based partly on how many onchain projects depend on them)
6. Metrics are calculated for eligible projects
7. Algorithms (not here) are run on the metrics to determine eventual rewards

Onchain Builder Models

Base Data Models

int_superchain_traces_txs_joined

• Joins transaction and trace data from the Superchain
• Matches transactions with their internal traces
• Calculates gas fees and maintains chain-specific addressing

int_superchain_trace_level_events_by_project

• Attributes transactions and traces to specific projects
• Identifies Account Abstraction (AA) events via entry point contracts
• Categorizes events as:
  • TRANSACTION_EVENT: Direct contract interactions
  • TRACE_EVENT: Internal contract calls
  • AA_EVENT: Interactions through entry points
• Important: unlike our previous models, it is now possible for a transaction to be attributed to multiple projects (because of the trace-level events)

int_superchain_onchain_user_labels

• Labels onchain addresses with user attributes
• Identifies Farcaster users
• Tags bot addresses for filtering
• Note: This model still touches the BigQuery bots models

Onchain Builder Analysis

int_superchain_s7_onchain_builder_eligibility

• Determines project eligibility based on onchain activity
• Key thresholds:
  • Transaction counts (different for single/multi-chain)
  • Gas fees
  • Unique users (bot-filtered)
  • Active days
• Aggregates metrics regardless of transaction types (direct, trace, AA)
• We should expect business logic to change (eg, thresholds for eligibility).
• This model will be monitored very closely for changes in the metrics over time, as projects will know exactly what they need to do to get "eligible" and will likely expect a quick update to their eligibility status.

int_superchain_s7_onchain_metrics_by_project

• Comprehensive metrics for eligible onchain builders
• Calculates:
  • Transaction counts (raw and bot-filtered)
  • Trace-level activity
  • Gas usage
  • User metrics (including Farcaster users)
• These metrics include a way of weighting the transaction counts by the number of projects that the transaction is attributed to. That number is a function of the projects who are currently eligible for the round, so it always need knowledge of eligibility status.
• Some of these metrics are standard "key metrics" (like gas fees and normal transaction counts) but others are more unique (like the weighted ones).

Devtooling Models

Base Developer Activity

int_superchain_s7_trusted_developers

• Identifies trusted developers based on:
  • Commit history to major onchain projects
  • Activity thresholds (commits, duration)
  • Repository quality filters (stars, updates)
• Focus on TypeScript, Solidity, and Rust contributions
• This model may be dependent on knowing the eligibility status of the projects, so it needs to run after the onchain builder eligibility model and capture the sample date from that model. Alternatively, it might just look at all onchain projects regardless of eligibility status above a certain threshold of economic activity.

Relationship Models

int_superchain_s7_project_to_dependency_graph

• Maps dependencies between onchain builders and devtools
• Tracks package usage across ecosystems
• Maintains temporal relationships with sample dates, but could be changed to a more standard created_at, updated_at, schema.

int_superchain_s7_project_to_developer_graph

• Maps developer connections between projects
• Tracks different contribution types:
  • Code contributions (PRs, commits)
  • Issue engagement
  • Project interest (stars, forks)
• Same as above, but for developers.

Devtooling Analysis

int_superchain_s7_devtooling_repo_eligibility

• Determines eligible devtool repositories based on:
  • Recent releases
  • Package adoption
  • Repository dependencies
• Filters out forks and inactive projects
• This model may also be dependent on knowing the eligibility status of the projects, so it needs to run after the onchain builder eligibility model and capture the sample date from that model.

int_superchain_s7_devtooling_metrics_by_project

• Comprehensive metrics for devtool projects
• Measures:
  • Package adoption (NPM, Cargo)
  • Developer engagement
  • Repository activity
  • Trusted developer interactions
• Nothing very hard here, once the dependency graph is established.

[linear]

OSO-403 Refactor S7 metrics models

[ccerv1]

Also linking the previous PR where the first version of these models was created.
#2953

[ccerv1]

Event types we care about

• CONTRACT_INVOCATION_GAS_FEE
• (CONTRACT_INTERNAL_INVOCATION)
• ACCOUNT_ABSTRACTION_USEROP