[wp-meta] [Making WordPress.org] #8237: Create a query which reports translations per locale

[Making WordPress.org]

#8237: Create a query which reports translations per locale
--------------------------------------+---------------------
 Reporter:  psmits1567                |       Owner:  (none)
     Type:  enhancement               |      Status:  new
 Priority:  normal                    |   Milestone:
Component:  Translate Site & Plugins  |  Resolution:
 Keywords:                            |
--------------------------------------+---------------------

Comment (by darshitrajyaguru97):

 == Proposed Next Steps ==

 Based on the current requirement, I would like to suggest a structured
 approach to move this forward and make the report actionable and scalable.

 === **1. Define Expected Output ===

 The report should be generated per locale and include the following
 columns:

 * Locale
 * Project Name (plugin/theme)
 * Translation Percentage
 * Project Link (translate.wordpress.org)

 **Example:

 || Locale || Project || % Translated || Link ||
 || gu || WooCommerce || 82% || https://translate.wordpress.org/projects
 /wp-plugins/woocommerce ||
 || gu || Contact Form 7 || 65% || https://translate.wordpress.org/projects
 /wp-plugins/contact-form-7 ||

 This will help contributors quickly identify projects that need attention.


 === **2. Data Source Mapping ===

 To build this report, we need to combine data from:

 * Global Stats (for locale + active installs per project)
 * Translate DB (GlotPress tables for translation status)

 Key join points:

 * Project slug (plugin/theme)
 * Locale (from stats ↔ translation sets)

 === **3. Suggested Implementation Approaches ===

 ==== **Option A: Aggregated Table (Recommended) ====

 * Create a scheduled job (cron) to generate and store results
 * Pre-calculate translation percentages per locale + project
 * Store results in a dedicated reporting table

 Benefits:
 * Better performance
 * Scalable for large datasets
 * Easier to query and extend


 ==== **Option B: On-Demand Query / API ====

 * Generate report dynamically via an endpoint (e.g. JSON API)
 * Useful for smaller datasets or initial prototype

 Trade-off:
 * May have a performance impact on large-scale queries

 === **4. Enhancements (Optional but Valuable) ===

 * Add filters:
   * Locale
   * Project type (plugin/theme)
   * Translation % threshold (e.g. < 80%)

 * Add sorting:
   * By translation percentage
   * By active installs (to prioritize impact)

 * Priority scoring idea:
   * priority = installs * (100 - translation%)
   * Helps identify high-impact untranslated projects


 I’m happy to help move this forward by:

 * Prototyping the SQL query
 * Assisting with aggregation logic

 Please let me know the preferred approach, and I can start working on the
 initial implementation.

 Thanks!

-- 
Ticket URL: <https://meta.trac.wordpress.org/ticket/8237#comment:1>
Making WordPress.org <https://meta.trac.wordpress.org/>
Making WordPress.org