Skip to main content

Specification

info

An impact metric is a quantitative measure of impact over a discrete period of time. Impact metrics are most commonly queried by project (eg, uniswap), although they can also be queried by individual artifact or at the collection level.

Requirements


An impact metric must be:

  • A quantitative measure of impact on a continuous scale. Common examples include counts, sums, averages, and medians.
  • Available for all projects in the OSO database (even if many projects have a null value). Metrics should be extendable to any collection in the OSO database as well as any artifact in the same artifact namespace (eg, GitHub-related metrics, npm-related metrics, etc.).
  • Aggregated over a discrete period of time. Common time intervals include last 90 days, last 180 days, and since inception. OSO has already bucketed event data in daily, weekly, and monthly intervals to facilitate this.
  • Constructed from data that is available in one or more intermediate or mart models. Common models include projects_v1, events_monthly_to_project, int_devs, and first_contribution_to_project_v1.

Schema

Every impact metric must include the following fields: project_id, impact_metric, and amount. For example:

{
"project_id": "jUda1pi-FdNlaUmgKq51B4h8x4wX3QTN2fZkKq6N0vw\u003d",
"impact_metric": "FORKED_6M",
"amount": 125
}

Some may also include a namespace field to be explicit about the source of the event data to calculate the impact metric. This is useful, for instance, in the case of projects on multiple blockchain networks.

Sample Metrics


Forks in the Last 6 Months (Project Level)

The following is an example of a valid impact metric, expressed in SQL:

  -- Sum of fork events to a project in the last 6 months
SELECT
project_id,
'FORKED_6M' AS impact_metric,
SUM(amount) AS amount
FROM `oso.events_monthly_to_project`
WHERE
event_type = 'FORKED'
AND DATE(bucket_month) >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
GROUP BY project_id

Forks in the Last 6 Months (Artifact Level)

Here is the same metric calculated at the artifact level:

  -- Sum of fork events to an artifact in the last 6 months
SELECT
artifact_id,
'FORKED_6M' AS impact_metric,
SUM(amount) AS amount
FROM `oso.events_monthly_to_artifact`
WHERE
event_type = 'FORKED'
AND DATE(bucket_month) >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
GROUP BY artifact_id

Days from First Commit to First Onchain Transaction

Here's a more complex impact metric that uses a CTE to calculate the number of days from the first commit to a project to its first onchain transaction. Note that this query returns null for projects that have not yet had an onchain transaction. It also returns a negative value for projects where the first onchain transaction occurred before the first commit to a public repo.

  -- Number of days from the first commit to a project to its first onchain transaction
WITH first_events AS (
SELECT
project_id,
from_namespace AS namespace,
MIN(CASE WHEN event_type = 'COMMIT_CODE' THEN bucket_day END) AS first_commit,
MIN(CASE WHEN event_type = 'CONTRACT_INVOCATION_DAILY_COUNT' THEN bucket_day END) AS first_txn
FROM `oso.events_daily_to_project_by_source`
GROUP BY project_id
)
SELECT
project_id,
namespace,
'DAYS_FROM_FIRST_COMMIT_TO_FIRST_TXN' AS impact_metric,
DATE_DIFF(first_txn, first_commit, DAY) AS days_from_first_commit_to_first_txn
FROM first_events

Issues Closed to Full-Time Contributors in the Last 90 Days

Finally, here's an example of a metric that utilizes one of the users mart models to calculate the ratio of issues closed to full-time contributors to a project in the last 90 days:

  -- Ratio of issues closed to full-time contributors to a project in the last 90 days
WITH full_time_contributors AS (
SELECT
project_id,
SUM(amount) AS full_time_contributors
FROM `oso.users_monthly_to_project`
WHERE
user_segment_type = 'FULL_TIME_DEV'
AND DATE(bucket_month) >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
GROUP BY project_id
)
, issues_closed AS (
SELECT
project_id,
SUM(amount) AS issues_closed
FROM `oso.events_monthly_to_project`
WHERE
event_type = 'ISSUE_CLOSED'
AND DATE(bucket_month) >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
GROUP BY project_id
)
SELECT
ftc.project_id,
ic.issues_closed / ftc.full_time_contributors AS issues_closed_to_full_time_contributors_90_days
FROM full_time_contributors ftc
LEFT JOIN issues_closed ic ON ftc.project_id = ic.project_id

To contribute new metrics, please see our guide here.