[Impact Metric Submission]: Project Engagement Over Time

[nutkung1]

Project Engagement Over Time

Tags

Add one or more keywords to help us categorize your metric. Don't overthink this!

• Open Source Project Engagement
• User Activity Metrics

Brief description

Describe how your impact metric is calculated in 2-3 sentences: This metric provides a comprehensive overview of project engagement over time by analyzing monthly data from various events. It calculates monthly active users, forks, opened and closed issues, opened and merged pull requests for each project. This data allows you to assess user activity, development velocity, and overall project health over time.

SQL code block

Insert the SQL that we should run to replicate your impact metric:

WITH project_engagement AS (
  SELECT
    e.project_id,
    DATE(e.bucket_month) AS month,
    SUM(CASE WHEN e.event_type = 'ACTIVE_ADDRESS' THEN e.amount ELSE 0 END) AS active_users,
    SUM(CASE WHEN e.event_type = 'FORKED' THEN e.amount ELSE 0 END) AS forks,
    SUM(CASE WHEN e.event_type = 'ISSUE_OPENED' THEN e.amount ELSE 0 END) AS issues_opened,
    SUM(CASE WHEN e.event_type = 'ISSUE_CLOSED' THEN e.amount ELSE 0 END) AS issues_closed,
    SUM(CASE WHEN e.event_type = 'PULL_REQUEST_OPENED' THEN e.amount ELSE 0 END) AS pull_requests_opened,
    SUM(CASE WHEN e.event_type = 'PULL_REQUEST_MERGED' THEN e.amount ELSE 0 END) AS pull_requests_merged
  FROM `opensource-observer.oso.events_monthly_to_project` e
  GROUP BY e.project_id, month
)
SELECT
  pe.project_id,
  pe.month,
  pe.active_users,
  pe.forks,
  pe.issues_opened,
  pe.issues_closed,
  pe.pull_requests_opened,
  pe.pull_requests_merged
FROM project_engagement pe
ORDER BY pe.project_id, pe.month;

Optional: link

Add a link to script/notebook or longer form write-up about the metric: