FGdb Reports

From FreekiWiki
Jump to navigation Jump to search

Trend Reports

(These show trends over time)

Volunteer Related

  • Numbers of volunteers volunteering over time.
    • filter on program (adoption, build, other)
    • filter on job (receiving, recycling, data entry)
  • Same thing, but total of hours volunteered instead of volunteer count.
  • Number of active volunteers over time (an active volunteer has logged >= 4 hrs in the last 90 days.) DONE
  • hours by task category/program (does that exist yet?) OR distinct person count by task category/program over date range grouped by date

Gizmo Related

  • Numbers of (gizmo_types) received over time.
  • Numbers of (gizmo_types) sold over time.
  • Numbers of (gizmo_types) disbursed over time.
  • Same as above but for (gizmo_categories) instead of (gizmo_types).
  • Same as the above (gizmo_types) but be able to pick >1 type, so you can see (for example) the relation between laptops and systems received, sold, whatever.
  • gizmos by type or category flow type (incoming or outgoing) over date range grouped by date, week, month, quarter, or year

Income

  • Average donations, fees, and total (i.e. front desk income) over time (DONE, pretty much).
  • Average amount of sale over time. (Be able to select a (gizmo_type) or (gizmo_types) for comparison. OR Be able to look at all sales, regardless of (gizmo_type).)
  • over date range grouped by day, week, month, or year donation income by fee and contribution sale income by discount type and totals


Other

  • - # systems, monitors, printers granted
  • - # systems, monitors, printers adopted
  • - # systems, monitors, printers sold
  • - # systems, monitors, printers recycled
  • - % of systems, monitors, and printers granted, adopted, sold, and recycled
  • * extra swanky: these numbers (or at least the non-recycle numbers) for laptops, macs
  • - # new volunteers in build, adoption, other
  • - # volunteers who logged hours in build, adoption, other
  • - # hours logged in build, adoption,
  • - # donations
  • - # unique donors
  • - avg. amount of money given per donation
  • - # sales
  • - # unique store customers
  • - avg. amount spent per store transaction
  • Total sales ($) by gizmo_type (and gizmo_category) over a period of time. (This is a trends or "over time" type report.)
  • Same as above, but limit to discount type -- that is I want to see trends on systems that were sold at a volunteer discount.

Not trend reports

Some reports mentioned that are not trend report ("over time"):

  • List all volunteers that have worked (more than | less than | between) a certain number of hours.
  • List all organizations that have donated gizmos in a given date range that (are | are not) covered entities.

Questions PR wants answered (that could be reports)

Would need additional changes to database

  • Recycling tonnage: How many tons of e-waste did we recycle last year?
  • Collection events: "I helped organize 3 collection events (Hillsboro Library, Elementary School, and ECOHAUS) in 2008, how many others were there? For instance, I know that Hasson Realtors partnered with Free Geek on one."

Don't need additional changes

  • How many folks volunteered at Free Geek in 2008?
  • How many hours total did they collectively volunteer?

Not sure

  • We donated approx. 1200 computers last year - how many were given to adoptees & how many to grantees?
  • Do we keep track of numbers of equipment/gizmos processed for 2008: computers, printers, keyboards, etc.
  • A list of organizations that received Free Geek computers in 2008.
  • A list of all Free Geek cash donors and amounts for 2008-I've got the spreadsheet for the Give Guide donors. Can you combine the Causes and all others into one e-report? This would also include in kind donations from Companies/individuals. I would like to exclude the front desk donations unless they were notable amounts - $50+

Notes

One more report-request that I've heard of.

If the part of the gizmo report that lists disbursements could be a link to the actual disbursements showing the contacts who are getting disbursed to, that would be good and would save Dave a lot of time.


SQL samples for use in creating reports

Gizmo type sales and counts

(Note by changing "WHERE sale_id IS NOT NULL" to "WHERE donation_id IS NOT NULL", we could turn the count of gizmos sold report into a count of gizmos received report quite easily. Same for disbursements and recyclings, obviously.) 

-- 6 is a sample gizmo_type (laptops)
-- 2009-04-01 is a sample beginning date
-- 2009-04-30 is a sample end date

-- sales amount by type example:
SELECT 
  SUM( unit_price_cents )/100.0 AS due 
  FROM gizmo_events 
  WHERE sale_id IS NOT NULL 
  AND gizmo_type_id = 6 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

-- sales count by type example:
SELECT 
  SUM( gizmo_count ) AS count 
  FROM gizmo_events 
  WHERE sale_id IS NOT NULL 
  AND gizmo_type_id = 6 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

Gizmo category sales and counts

Similar to above, but based on broad category instead of specific gizmo type:

(Note again, by changing "WHERE sale_id IS NOT NULL" to "WHERE donation_id IS NOT NULL", we could turn the count of gizmos sold report into a count of gizmos received report quite easily. Same for disbursements and recyclings, obviously.) 

-- 2 is a sample gizmo_category (monitors)
-- 2009-04-01 is a sample beginning date
-- 2009-04-30 is a sample end date

-- sales amount by category example:
SELECT 
  SUM( unit_price_cents )/100.0 AS due 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_types.gizmo_category_id = 2 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

-- sales count by category example:
SELECT 
  SUM( gizmo_count ) AS count 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_types.gizmo_category_id = 2 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

Since there are so few categories, we could show multiple lines on the same graph if we want. This might help do that: 

-- sales amount by all categories example:
SELECT 
  gizmo_categories.name,
  SUM( unit_price_cents )/100.0 AS due 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  LEFT JOIN gizmo_categories ON gizmo_types.gizmo_category_id = gizmo_categories.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  GROUP BY 1
  ;

-- sales count by all categories example:
SELECT 
  gizmo_categories.name,
  SUM( gizmo_count ) AS count 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  LEFT JOIN gizmo_categories ON gizmo_types.gizmo_category_id = gizmo_categories.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  GROUP BY 1
  ;
Retrieved from "http://wiki.freegeek.org/index.php?title=FGdb_Reports&oldid=40005"