916229 - Automatically update community stats on homepage

Status: RESOLVED FIXED

(developer.mozilla.org Graveyard :: General, defect, P3)

Description

[Janet Swisher]

What problems would this solve?
===============================
The mockup of the redesigned front page (http://cl.ly/image/2r2P342g0c2F/o) includes a "Get Involved" box with community stats:

X contributors from Y countries in Z languages

The purpose of this box is to show that MDN is a community-created, global resource.

Who would use this?
===================
These stats would be displayed on the front page of MDN. 

What would users see?
=====================
Something like http://cl.ly/image/2r2P342g0c2F/o

What would users do? What would happen as a result?
===================================================
Users would think "Oh, cool, I could contribute to this, too." and would click the "Contribute to MDN" button to learn more.

Is there anything else we should know?
======================================
These stats can be specified as:

* X: Number of unique contributors (users who have made an edit) in the last 12 months
* Y: Number of countries, based on IP address, for contributors in the last 12 months. This could be for logged-in users, rather than editors, if that's easier.
* Z: Number of locales that have been edited in the last 12 months.

These do not need to be updated frequently. Weekly or even monthly is fine.

Comment 1

[John Karahalis [:openjck]]

Final copy:

Join 3,414 contributors in 31 languages and locales around the world.

Comment 2

[Janet Swisher]

This bug is not about the initial stats. This bug is about updating the stats on an ongoing, periodic basis. If we have to do it manually, it won't happen and will get stale.

Comment 3

[John Karahalis [:openjck]]

Ah, I closed the wrong one. Thanks Janet.

Comment 4

[David Walsh :davidwalsh]

Jannis -- can you hook me up with the python / optimized queries to get these numbers?  Will these DB calls slow down the homepage?  We should consider speed since it's the homepage

Comment 5

[Jannis Leidel [:jezdez]]

:davidwalsh Sure thing, this is likely going to be a costly query. But I'll wrap it in a caching call on Python side and write a periodic Celery task to update the cache (assuming we'll have Memcache soon). In other words it's a little more involved due to it being the homepage. Can we do it like with the search where you work with dummy data and I'll just hook into the query and caching code once you're done?

Comment 6

[David Walsh :davidwalsh]

Sweet!  We're actually using hardcoded numbers right now, so my part should be done, unless the  numbers are drastically wider somehow.  My part should be good to go in the template already, we just need to replace hardcoded numbers with real ones.

Comment 7

[Jean-Yves Perrier [:teoli]]

Note that even if we need up-to-date numbers, we don't need live numbers. Have a task caching the values once a day or once a week is perfectly fine. (feedback Janet to have confirmation of this)

Comment 8

[Janet Swisher]

Yup:

(In reply to Janet Swisher from comment #0)

> These do not need to be updated frequently. Weekly or even monthly is fine.

Comment 9

[Jannis Leidel [:jezdez]]

FWIW, I realized I overlooked one minor detail that makes this a non-trivial feature.

The feature descriptions says:

"Y: Number of countries, based on IP address, for contributors in the last 12 months. This could be for logged-in users, rather than editors, if that's easier."

We don't currently collect IP addresses in our database and I think we shouldn't due to privacy issues. That leaves us with little other options to figure out which countries the contributors came from (let alone retroactively figure that out):

- check the contributor user profile if she has set the country field. problem is that at least I can't even see the country dropdown in the profile edit view

- somehow query Google Analytics for a general number of *visitors* for a page that is only used by editors (which would that be?) so that we can fake that a bit

There may be other options that I haven't thought about and I'd appreciate input from other devs. Adding Luke to the mix..

Comment 10

[Janet Swisher]

Yes, we encountered that issue in creating the original stats box. It currently only lists number of contributors and number of locales (i.e., localizations). Updating that is all that is required, so you can ignore the request for number of countries.

Comment 11

[Luke Crouch [:groovecoder]]

The queries for https://bugzilla.mozilla.org/show_bug.cgi?id=916229#c10 are not too hard. Making myself a mentor for any contributor who may want to make that content dynamic. SQL queries for these metrics:

X: select count(creator_id) from (select distinct creator_id from wiki_revision where created >= DATE_SUB(NOW(), INTERVAL 1 YEAR)) as contributors_last_12_months;

Y: select count(locale) from (select distinct wd.locale from wiki_document wd, wiki_revision wr where wd.id = wr.document_id and wr.created >= DATE_SUB(NOW(), INTERVAL 1 YEAR)) as locales_last_12_months;

Comment 12

[Francisco Ambrozio]

> The queries for https://bugzilla.mozilla.org/show_bug.cgi?id=916229#c10 are
> not too hard. Making myself a mentor for any contributor who may want to
> make that content dynamic.

Hi, I would like to contribute on this.

Comment 13

[Luke Crouch [:groovecoder]]

Thanks Francisco. Did you get my email about the patch? If you can, please send it as a GitHub pull request, or you can attach it as a patch to this bug. Thanks!

Comment 14

[Francisco Ambrozio]

Ok, pull request sent:

https://github.com/mozilla/kuma/pull/2852

Comment 15

[Luke Crouch [:groovecoder]]

Thanks Francisco!

Comment 16

[MDN Team (:mdn-dev)]

Commits pushed to master at https://github.com/mozilla/kuma

https://github.com/mozilla/kuma/commit/870376ba5d5ac375e9a00cb709eb057172e3072a
bug 916229 - proposing a patch to fix the bug

https://github.com/mozilla/kuma/commit/b5cdb1a1fe26f64137787f3e3f7cfe93d556aec8
Merge remote-tracking branch 'mozilla/master' into bug916229

https://github.com/mozilla/kuma/commit/c43aeb378dc04f76548625533427c71f752a16c1
bug 916229 - formatting sql queries and adding {% trans %} block

https://github.com/mozilla/kuma/commit/2fce6cb60e126a50a0b3bf9302495402bfe902fb
bug 916229 - moving the querying into Celery task

https://github.com/mozilla/kuma/commit/a15a90fcd966e7dbd1ede5ebcd1b412e1215dc12
bug 916229 - changing cache.add to cache.set

https://github.com/mozilla/kuma/commit/2e68728cc1968aa1a364a33fc0b75c9ce32e5756
bug 916229 - starting tests

https://github.com/mozilla/kuma/commit/828928e250c03e8ce952fcae45b71b670993ac83
bug 916229 - Tests

https://github.com/mozilla/kuma/commit/c42d56e42fa4b1862ee8d1ac2fd9f531aed49a3b
Merge branch 'bug916229'

Comment 17

[Jannis Leidel [:jezdez]]

This is fixed and deployed for a while now.

Comment 18

[[github robot]]

Commit pushed to master at https://github.com/mozilla/kuma

https://github.com/mozilla/kuma/commit/80a3bcc4c2248ea87b7cf9073efb7ac4ba10dc88
bug 916229: Add tests for community stats