gistfile1.sql

/*
	This will generate the data mart for following report/tables
	
	1. report1 - grouped by referring_domain , landing_domain , campaign , source , click_date
*/
-- Create a temporary table to hold zip counts
create temporary table r_zip_cnt
(
click_id mediumint unsigned  ,
cnt smallint unsigned ,

index(click_id)
);

-- Insert data into the table
insert into r_zip_cnt(click_id , cnt) ( SELECT zy.click_id , Count(zy.search_id) AS cnt FROM zip_searches zy GROUP BY zy.click_id );

-- Create a temporary table to hold direct sales + revenue counts
create temporary table r_ds_cnt
(
click_id mediumint unsigned  ,
conv smallint unsigned ,
conv_value decimal(10,3) ,

index(click_id)
);

-- Insert data into the table r_ds_count
insert into r_ds_cnt(click_id , conv , conv_value) (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 );

-- Now join the above 2 tables with clicks table to prepare the report1 table
insert into report1(referring_domain , landing_domain , campaign , source , click_date , zip_searches , visits , conversions , conv_value) 

(
SELECT c.referring_domain , c.landing_domain , c.campaign , c.source , DATE(c.click_date) as the_date , 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 as c 

LEFT JOIN r_zip_cnt zs on zs.click_id = c.click_id 
LEFT JOIN r_ds_cnt ds on ds.click_id = c.click_id 

GROUP BY referring_domain , landing_domain , campaign , source , the_date
);