Difference between revisions of "Talk:Stats and Testimonials"

From FreekiWiki
Jump to navigation Jump to search
(moved common SQL queries to own page)
 
(2 intermediate revisions by the same user not shown)
Line 49: Line 49:
  
 
== SQL queries ==
 
== SQL queries ==
−
=== the basics ===
 
−
*psql fgdb -U fgdb (gets you in, once ssh'd to flowers)
 
−
*\dt (list tables)
 
−
*\d (describe a table)
 
  
−
=== number of money donations, by year and amount ===
+
See [[FGdb SQL queries]].
−
;first column of table on main page (Number of donations over $500, by year.)
 
−
:fgdb=# SELECT EXTRACT( YEAR FROM created ), COUNT(*) FROM donation WHERE cashdonation + monitorfee >= 500 GROUP BY 1 ORDER BY 1;
 
−
 
 
−
;second column (note alternate syntax for column names, and for range of values)
 
−
:SELECT EXTRACT( YEAR FROM created ) '''as Year''', COUNT(*) '''as DonCount''' FROM donation WHERE cashdonation + monitorfee '''between 400 and 499.99''' GROUP BY '''Year''' ORDER BY '''Year''';
 
−
 
 
−
=== number of hours per volunteer, by year ===
 
−
These queries show some of the pieces involved. Still working toward useful queries to use in real life.
 
−
 
 
−
;number of new adoption volunteers signed up, by year
 
−
:select EXTRACT( YEAR FROM created ), count(*) from contact where adopter='Y' group by 1 order by 1;
 
−
 
 
−
;how many hours Pete volunteered per year
 
−
:select extract( year from created), sum(hours) from memberhour where memberid=460 group by 1 order by 1;
 
−
 
 
−
;an attempt to modify the above query, to report only years where Martin logged more than 100 hours. DOES NOT WORK, needs some fixin.
 
−
:select extract( year from created), sum(hours) as total from memberhour where memberid=5989 and total>100 group by 1 order by 1;
 
−
 
 
−
;number of folks who have volunteered for us, ever
 
−
:select count( distinct memberid ) from memberhour;
 
−
 
 
−
;total number of hours logged for each volunteer in each year (yields a very long list!) I'm surprised by number of negative values. Need to look into that.
 
−
:select extract( year from created ) as year, memberid, sum( hours ) from memberhour where memberid in (select distinct memberid from memberhour) group by year, memberid order by year;
 

Latest revision as of 15:53, 29 January 2006

Quantifying our accomplishments:

(brainstorming possible stats that would need to be gathered.)

  • Richard points out that sometime in 2005, we stopped giving many miscellaneous gizmos ID numbers. This could impact the gathering of many statistics; anything that needs any measure of accuracy should be based on "major" gizmos (PCs, Monitors, Printers.)
  • Free Geek has properly disposed of X tons of hazardous materials (lead, etc) which would have likely ended up in landfills. (in what time period?)
    • (Psst! It is possible to get these stats: Oso would need prodding. Shawn)
  • Free Geek has assisted X aspiring computer professionals in successfully (and affordably!) obtaining Linux certification. (in what time period?) (Note: According to Reid Leike, the "who passed" stat is considered proprietary by LPI, so the only way to get it would be to poll all FG test-takers.)
  • Free Geek has "graduated" X volunteers from the Build program, providing workforce skills at no monetary cost. (in what time period?)
  • Free Geek has been assisted by X number of volunteers. (in what time period?)
  • On average, X volunteers worked 25 or more hours in a month during the last calendar year. This has increased from X in 2000.
  • Free Geek has given x computers to local non-profits, and x to international non-profits. Testimonials would be nice - Brad St. of the hardware grants group might be a good person to ask.
  • Free Geek has provided x hours of tech support to volunteers and non-profits. This equals X hours per FreekBox (only include that number if it's impressive).
  • Free Geek has received $X in donations from private citizens. (In what time period?)
  • Free Geek has received grants from organizations like DEQ, Meyer, McKenzie River Gathering Foundation... (dates? amounts?) Successful Grants
  • Free Geek has made thus-and-such successful steps toward financial sustainability. (increases in dollars-from-recycling, dollars-from-thriftstore...)
  • Free Geek efficiency score ( what % of each $ goes toward admin cost) get rated by charity navigator.com

kinds of measurements

We want to present Free Geek in a positive light in terms of the following. we could use stats and/or testimonials supporting each of these points.

  • we recycle a lot of stuff that would be in landfills
  • we point the way toward larger-scale recycling, for other organizations or gov't agencies
  • we give people an opportunity to get free computers
  • we give people an opportunity to buy computer stuff at super low prices
  • we give people an opportunity to learn job skills
  • we give "ordinary folks" an avenue toward open source software, which makes available a wide variety of opportunities, not limited to finacial "free-as-in-beer"ness
  • we grease the wheels of other nonprofits (hardware grants, etc)
  • we make Portland a better place to live (umbrella: GAPs, expertise nexus, ...)
  • your monetary contribution will be put to good use, and not frivolous administrative costs
  • we have a good long-term plan (including cash reserves!) so we'll be around for a while

debunking FUD

In fall '04, some dude named JG said on the Willy Week forum that, basically, Free Geek makes all the computers they distribute useless by putting Linux on 'em. He was chastised from all quarters, but still, I think it would be good to have stats handy that could easily prove him wrong. For instance, what percentage of FreekBox adopters are still running Linux a year later? Maybe some GAP stats too. --Pete 22:28, 4 Dec 2005 (PST)

i'd be willing to be interviewed on freegeek as a playground for free software/sofware libre experimentation and development, but i don't know about that open source stuff, if you'd like. Vagrant

SQL queries

See FGdb SQL queries.

Retrieved from "http://wiki.freegeek.org/index.php?title=Talk:Stats_and_Testimonials&oldid=17597"