437151 - Pledges vs. Country Population Tool

Status: RESOLVED FIXED

(Webtools Graveyard :: Download Day, defect, P2)

Description

[Mary]

Hi guys:  Ehsan has created a table that tracks pledges vs. country populations.  If we had over our pledge count data he can show the pledge count history per region.  Can we do this?  Check out what he has done:

http://ehsanakhgari.org/mozilla/downloadday/stats

Comment 1

[Ryan Doherty (:rdoherty)]

We will have a json feed soon of the pledge counts. See bug 436967.

Comment 2

[(no longer active)]

Bug 436967 would help me with the current implementation (what I'm currently doing is grab this page <http://www.spreadfirefox.com/en-US/worldrecord> and parse the HTML code and extract the data).  What I'm planning to build is a set of graphs which shows the history of each country's pledges, so that one can compare the progress rate of two countries, for example.  For that purpose, I would need the following data:

DATE         COUNTRY    MAX_PLEDGE
----------------------------------
2008-06-01   FRANCE     10,542
2008-06-01   ITALY      8,421
2008-06-02   FRANCE     11,067
2008-06-03   NORWAY     7,916
...

Where MAX_PLEDGE is the maximum pledge count of that day for the specified country (I'm showing only a handful of sample tuples there.)

I can start collecting this data from now on (and in fact, I have started doing so for about 12 hours now) but I don't have the data before that date.  If I have access to that data, it would help me to build complete charts, starting from the beginning of the project, for each country.  Is there any chance you can provide feeds for this data as well?

Comment 3

[(no longer active)]

Attachment: Patch to make the history data available as both XML and JSON

OK, I gave this a shot myself.  This patch is created based on <http://svn.mozilla.org/projects/spreadfirefox.com/branches/download-day/> (which I hope is the right path).  This patch produces the pledge count per day per country, from which the MAX_PLEDGE field can be constructed via a cumulative sum.

I have added a history_data action which will output either an XML or JSON representation of the history data (based on the presence of the format=json GET argument).

The JSON idea was borrowed from l.m.orchard's implementation in bug 436967.

I have not been able to test this patch, and I have made some small guessing of the database schema, but I think it would work.

Comment 4

[Ryan Doherty (:rdoherty)]

Comment on attachment 323684 [details] [diff] [review]
Patch to make the history data available as both XML and JSON

The patch looks pretty good. I'm r- it because we're on trunk now and you'll want to add proper expires headers or the output will be cached for 2 weeks.

Take a look at the mapdata action on line 249 in index.php. That will have the expires code you need. Then create the diff from trunk. Thanks!

Comment 5

[Les Orchard [:lorchard]]

One other thing I'd say is that you might want to limit the number of days of history - say, to 14 days or so.  

Probably not a big issue for this particular site since its lifetime is limited, but fetching and serving up an open-ended entirety of any given data set or query tends to be asking for trouble eventually.

Comment 6

[(no longer active)]

Attachment: Attempt 2

OK.  I made a patch against trunk, and fixed the Expires header.  I also limited the number of days appearing on the result set to 14 by default, and I allow the users to specify up to 30 days via the `days' GET param, but no more than that.

Comment 7

[(no longer active)]

Ping?

Comment 8

[Ryan Doherty (:rdoherty)]

(In reply to comment #7)
> Ping?
> 

Hi, sorry. Will get to this as soon as I can. Been really busy here.

Comment 9

[(no longer active)]

I finally created the graphs I was talking about; it's available at <http://ehsanakhgari.org/mozilla/downloadday/stats/pledge-ranking-graph>.  I could still use the patch here to create another similar graph of the daily pledge count for each country, from the beginning of the project.

Comment 10

[Ryan Doherty (:rdoherty)]

Reviewing the patch now, we do have a json feed of pledge counts if that helps:

http://spreadfirefox.com/en-US/worldrecord/mapdata?format=json

Comment 11

[(no longer active)]

(In reply to comment #10)
> Reviewing the patch now, we do have a json feed of pledge counts if that helps:
> 
> http://spreadfirefox.com/en-US/worldrecord/mapdata?format=json

This looks good as well, but I was more concerned with getting historical data.

Anyway since the Download Day's tomorrow, this issue may be moot as well.  ;)

Comment 12

[Ryan Doherty (:rdoherty)]

I know this is late, but I'm trying to get the sql query to run and I get this error: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY DATE( FROM_UNIXTIME( pledges.date_submitted ) ), countries.country_code' at line 6 

FROM_UNIXTIME works, so I'm not sure what the error is. Any ideas? I'm on MySQL 5.0.41.

Comment 13

[(no longer active)]

I didn't have access to the DB schema, so I had to do a bit of guesswork here...  Can you post the schema for the countries table?  A few (maybe 10) rows from this table would be nice as well, so that I can debug the query here...

Thanks!

Comment 14

[Ryan Doherty (:rdoherty)]

Attachment: Schema and a few rows

Here's the schema and a few rows from the db with personal info removed.

Comment 15

[(no longer active)]

Attachment: Attempt 3

OK, figured out the problem.  Turns out GROUP BY should precede ORDER BY.

Now I feel bad, officially!  :-/

Comment 16

[Ryan Doherty (:rdoherty)]

Comment on attachment 326096 [details] [diff] [review]
Attempt 3

It works, but it takes 74 seconds to run on my dev machine. We have about 1.5 million rows, so a join across the 2 tables is very expensive. 

I've tried a few other queries where only the pledges table is used, but can't get the type of results I think you want. 

I'll see if we can give you a dump of the pledge data with any personal info removed.

Comment 17

[(no longer active)]

(In reply to comment #16)
> I'll see if we can give you a dump of the pledge data with any personal info
> removed.

Thanks, that would be nice!

Comment 18

[Brian Krausz]

DD is over, closing out all these bugs