gistfile1.sql

select
c.referring_domain , SUM(zs.cnt) as zip_searches ,
Count(c.click_id) as visits ,
SUM(ds.conv) as conversions ,
SUM(ds.conv_value) as conv_value

from clicks c

left join
(
 SELECT
 zy.click_id ,
 Count(zy.search_id) AS cnt
 FROM zip_searches zy
 GROUP BY zy.click_id
)
zs on zs.click_id = c.click_id

left join
(
 SELECT
 ds2.click_id ,
 Count(ds2.sale_id) AS conv ,
 SUM(ds2.revenue) AS conv_value
 FROM direct_sales ds2
 GROUP BY ds2.click_id
)
ds on ds.click_id = c.click_id

WHERE c.source = '7search'

group by c.referring_domain
order by conv_value DESC