Page MenuHomePhabricator

Overall Engagement output rank metric
Closed, ResolvedPublic

Description

SQL / Python code to transform input data in to Output Rank Metrics

Based on

  • Affiliate Leadership (Percentile)
  • Grants Leadership $ weighted by PPP + triangulating counts (Percentile)
  • EDITORSHIP (CORRECTED RANK)
  • Readership Percentile

Event Timeline

KCVelaga_WMF renamed this task from Overall Engagement Metric (Transformation) to Overall Engagement output rank metric.Nov 14 2022, 10:32 AM
KCVelaga_WMF moved this task from In Code Review to In QA Review on the Equity-Landscape board.

@JAnstee_WMF The QA of overall engagement metric is ready for your review: https://docs.google.com/spreadsheets/d/1GnKHC9yT5tN_xmEltCGdHEONI5GqjTiWaVI9zXNp4rQ/edit#gid=1551753866&range=E2

WITH
    inputs AS (
        SELECT * FROM readership_output_ranks WHERE metric = 'readership'
        UNION ALL
        SELECT * FROM editorship_output_ranks WHERE metric = 'editorship'
        -- UNION ALL
        -- SELECT * FROM programs_output_ranks WHERE metric = 'programs_leadership'
        UNION ALL
        SELECT * FROM affiliates_output_ranks WHERE metric = 'affiliates_leadership'
        UNION ALL
        SELECT * FROM grants_output_ranks WHERE metric = 'grants_leadership')

SELECT
    geography_level,
    country_code,
    ROUND(
        PERCENT_RANK() OVER (
            PARTITION BY geography_level
            ORDER BY AVG(metric_value))*10, 2) AS metric_value,
    'overall_engagement' AS metric
FROM 
    inputs
GROUP BY 
    geography_level, 
    country_code

to move to next steps for production.