Create a query which reports translations per locale

[psmits1567]

Currently we have the top 500 plugin and theme stats for translations per locale
But that is a report we cannot do anything but complete it. This report does not show the values per country. Also it does not show more then 500 records.
If this report is 100% translated, we still do have important projects that are only used in a specific locale
So we need a new report that generates the following
First determine the locale, then report the below columns

1. project name
2. % of translation
3. Link to the project

That gives us the opportunity to find project that needs to be checked or translated.
The global database knows the installations of plugins and themes, and also the locale as the update requests are sending the locale to this global database.
Basically this is also important for developers to know where their projects are used.

[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!

[psmits1567]

The filter for locale is required not an option. It is the intention of the report to use the data found for the locale selected. You could pick it from the URL you logged in to translate.wordpress.org. But to make it more general usable a selector would be mandatory. A filter on projectname is not necessary, as you already have statistics for that per locale

[Marc4]

Some time ago, I suggested this same approach to dedicate limited translation resources to the plugins or themes that are most in demand within each language.

At the time, I also proposed a voting system, but it's clearly much simpler to use a basic ranking based on the demand for each language.

https://meta.trac.wordpress.org/ticket/7121