gistfile1.sql

SELECT
	event_property.value,
	COALESCE(ad_clicks.count,0) AS `clicks`,
	COUNT(sale.id) AS `conversions`,
	SUM(ep_sale.value) AS `revenue`
FROM
	event sale
INNER JOIN
	event_property ep_sale
ON
	ep_sale.event_id = sale.id
AND
	ep_sale.name = 'revenue'
INNER JOIN
	event adclick
ON
	adclick.person_id = sale.person_id
AND
	adclick.event_type_id = 2
INNER JOIN
	(
	SELECT
		sale.id AS `sale_id`,
		MAX(adclick.id) AS `adclick_id`
	FROM
		event sale
	INNER JOIN
		event adclick
	ON
		sale.person_id = adclick.person_id
	AND
		adclick.event_type_id = 2
	AND
		adclick.created_at < sale.created_at
	INNER JOIN
		event_property
	ON
		event_property.event_id = sale.id
	AND
		event_property.name = 'revenue'
	WHERE
		sale.created_at BETWEEN '2011-07-07 00:00:00' AND '2011-08-05 18:16:02'
	GROUP BY
		sale.id
	) last_click
ON
	adclick.id = last_click.adclick_id
AND
	sale.id = last_click.sale_id
INNER JOIN
	event_property
ON
	event_property.event_id = adclick.id
AND
	event_property.name = 'Campaign'
LEFT OUTER JOIN
	(
	SELECT
		event_property.value,
		COUNT(*) AS `count`
	FROM
		event
	INNER JOIN
		event_property
	ON
		event_property.event_id = event.id AND event_property.name = 'Campaign'
	WHERE
		event.event_type_id = 2
	AND
		event.created_at BETWEEN '2011-07-07 00:00:00' AND '2011-08-05 18:16:02'
	GROUP BY
		event_property.value
	) ad_clicks
ON
	ad_clicks.value = event_property.value
WHERE
	sale.product_id = 1
AND
	sale.created_at BETWEEN '2011-07-07 00:00:00' AND '2011-08-05 18:16:02'
GROUP BY
	event_property.value