gistfile1.sql

All revenue data by month / year
SELECT sum(oi.line_item_total), date_format( o.date, '%m-%Y' ) FROM `order_item` as oi join `order` as o on o.id = oi.order_id group by date_format( o.date, '%m-%Y' ) order by o.date asc

All revenue data by month / year and product
SELECT sum(oi.line_item_total), date_format( o.date, '%m-%Y' ), oi.product_id FROM `order_item` as oi join `order` as o on o.id = oi.order_id group by date_format( o.date, '%m-%Y' ), oi.product_id order by o.date asc

All recurring revenue data by month / year and product
SELECT sum(oi.line_item_total), date_format( o.date, '%m-%Y' ), oi.product_id FROM `order_item` as oi join `order` as o on o.id = oi.order_id where oi.recurring = 1 group by date_format( o.date, '%m-%Y' ), oi.product_id order by o.date asc

Customers added
SELECT COUNT( * ) , DATE_FORMAT( date_registered, '%m-%Y' )
FROM `customer`
GROUP BY DATE_FORMAT( date_registered, '%m-%Y' )
ORDER BY date_registered ASC

Unique customers and total spent in year
SELECT count(DISTINCT o.customer_id), sum(line_item_total) FROM `order_item` as oi join `order` as o on o.id = oi.order_id where year(date) = 2014 and line_item_total > 0

Users on orders
SELECT * FROM `customer` as c join `order` as o on o.customer_id = c.id where date(date_registered) > '2013-12-31' order by date_registered asc

Get unique customer sign ups in period and how much they spent
SELECT count(distinct c.id), sum(oi.line_item_total) FROM `order_item` as oi join `order` as o on o.id = oi.order_id join customer as c on o.customer_id = c.id where date(c.date_registered) < '2014-01-01' and year(o.date) > '2013-12-31' order by date_registered asc

Total for a year
SELECT SUM( oi.line_item_total )
FROM `order_item` AS oi
JOIN `order` AS o ON o.id = oi.order_id
WHERE YEAR( o.date ) = '2014'

New customer and spend
SELECT DATE_FORMAT( c.date_registered, '%Y-%m' ) as 'Year/Month', count( DISTINCT c.id ) as 'Number of new users', SUM( oi.line_item_total ) as 'Gross Revenue'
FROM order_item as oi
LEFT JOIN `order` as o on oi.order_id = o.id
left join customer as c on o.customer_id = c.id
WHERE c.date_registered >= '2012-01-01'
AND DATE_FORMAT( o.date, '%Y-%m' ) = DATE_FORMAT( c.date_registered, '%Y-%m' )
AND oi.line_item_total > 0
GROUP BY DATE_FORMAT( c.date_registered, '%Y-%m' )

Category Revenue Totals
SELECT SUM( oi.line_item_total ) AS total, cat.title
FROM `order_item` AS oi
JOIN `order` AS o ON o.id = oi.order_id
LEFT OUTER JOIN product AS p ON p.id = oi.product_id
LEFT OUTER JOIN product_product_cat_rel AS rel ON p.id = rel.product_id
LEFT OUTER JOIN product_cat AS cat ON cat.id = rel.product_cat_id
GROUP BY cat.title

Revenue By Renewed Product 
SELECT p.title, SUM( oi.line_item_total ) AS total
FROM `order_item` AS oi
JOIN product AS p ON p.id = oi.product_id
WHERE oi.recurring =1
GROUP BY p.title
ORDER BY total DESC