First time customers in Magento, and subsequent purchases within that period

first-and-subsequent-within-month.sql

/* New customers and all their orders within that month */
SELECT
	p.increment_id,
	p.store_id,
	p.created_at,
	p.state,
	p.customer_firstname,
	p.customer_lastname,
	p.customer_email,
	(p.base_subtotal + p.base_shipping_amount) AS grandtotal_minus_tax,
	p.base_currency_code
FROM
	sales_flat_order AS p,
	(SELECT
		a.increment_id,
		a.created_at,
		a.customer_email
	FROM
		sales_flat_order a
	WHERE a.state <> 'canceled'
	GROUP BY a.customer_email
	ORDER BY a.created_at ASC
	LIMIT 999999) AS first_purchase
WHERE
	p.customer_email = first_purchase.customer_email
	AND p.created_at >= DATE('2014-05-01')
	AND p.created_at < DATE('2014-06-01')
	AND p.state <> 'canceled'
	AND first_purchase.created_at >= DATE('2014-05-01')
ORDER BY p.customer_email, p.created_at
LIMIT 999999;

first-orders.sql

/* New customers first orders within that month */
SELECT
	*
FROM
	(SELECT
		f.increment_id,
		f.store_id,
		f.created_at,
		f.state,
		f.customer_firstname,
		f.customer_lastname,
		f.customer_email,
		(f.base_subtotal + f.base_shipping_amount) AS grandtotal_minus_tax,
		f.base_currency_code
	FROM
			sales_flat_order f
	WHERE
		f.state <> 'canceled'
	GROUP BY f.customer_email
	ORDER BY f.created_at ASC
	LIMIT 999999
	) as t
WHERE
	1 = 1
	AND YEAR(t.created_at) = YEAR('2014-05-01')
	AND MONTH(t.created_at) = MONTH('2014-05-01')
ORDER BY t.customer_email ASC
LIMIT 999999;

unique-customers.sql

/* Unique customers within a month */
SELECT
	u.store_id,
	u.customer_firstname,
	u.customer_lastname,
	u.customer_email
FROM
	sales_flat_order u
WHERE
	u.state <> 'canceled'
	AND YEAR(u.created_at) = YEAR('2014-05-01')
	AND MONTH(u.created_at) = MONTH('2014-05-01')
GROUP BY u.customer_email
LIMIT 999999;