Skip to content

Instantly share code, notes, and snippets.

@silv3rm00n
Created May 30, 2012 12:52
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save silv3rm00n/2836118 to your computer and use it in GitHub Desktop.
/*
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 , disp)
(
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 , src.source_display_name
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
LEFT JOIN engine en ON c.source = en.engine_name
LEFT JOIN source src ON en.source_id = src.source_id
GROUP BY referring_domain , landing_domain , campaign , source , the_date
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment