the initial join of the db orders and ga orders

master_view.sql

-- View: master_view

-- DROP VIEW master_view;

CREATE OR REPLACE VIEW master_view AS 
 SELECT ga.trans_id,
    ga.source_medium,
    ga.campaign,
    od.country,
    od.first_mo_sales,
    od.first_order_id,
    od.pond_user,
    od.first_order_date,
    date_part('month'::text, od.first_order_date) AS first_order_month,
    date_part('year'::text, od.first_order_date) AS first_order_year,
    (date_part('month'::text, od.first_order_date) || '-'::text) || date_part('year'::text, od.first_order_date) AS month_year,
        CASE
            WHEN ga.source_medium = 'google / cpc'::text AND ga.campaign ~~ '%Pond5%'::text THEN 'google_sem_brand'::text
            WHEN ga.source_medium = 'google / cpc'::text AND lower(ga.campaign) ~ similar_escape('%(remarketing|display|gsp|youtube)%'::text, NULL::text) THEN 'google_display'::text
            WHEN ga.source_medium = 'google / cpc'::text AND lower(ga.campaign) ~~ '%search%'::text THEN 'google_non_brand'::text
            WHEN ga.source_medium = 'google / cpc'::text THEN 'google_non_brand'::text
            WHEN lower(ga.source_medium) = 'bing / cpc'::text THEN 'bing_sem'::text
            WHEN ga.source_medium ~~ '%direct%'::text THEN 'direct_load'::text
            WHEN ga.source_medium ~~ '%sailthru%'::text THEN 'internal_email'::text
            WHEN ga.source_medium ~~ '%email%'::text AND ga.source_medium !~~ '%sailthru%'::text THEN '3rd_party_email'::text
            WHEN ga.source_medium ~ similar_escape('%(facebook.com|socialfree|t.co)%'::text, NULL::text) THEN 'organic_social'::text
            WHEN ga.source_medium ~ similar_escape('%(socialpaid)%'::text, NULL::text) THEN 'paid_social'::text
            WHEN ga.source_medium ~~ '%organic%'::text THEN 'organic_search'::text
            WHEN ga.source_medium ~~ '%referral%'::text THEN 'other_referral'::text
            ELSE 'other_source'::text
        END AS traffic_type,
        CASE
            WHEN od.country = 'US'::bpchar OR od.country = 'USA'::bpchar THEN 'USA'::text
            WHEN od.country = 'CA'::bpchar OR od.country = 'CAN'::bpchar THEN 'Canada'::text
            WHEN od.country = 'DE'::bpchar OR od.country = 'DEU'::bpchar THEN 'Germany'::text
            WHEN od.country = 'FR'::bpchar OR od.country = 'FRA'::bpchar THEN 'France'::text
            WHEN od.country = 'AU'::bpchar OR od.country = 'AUS'::bpchar THEN 'Australia'::text
            WHEN od.country = 'GB'::bpchar OR od.country = 'GBR'::bpchar THEN 'UK'::text
            ELSE 'Other INTL'::text
        END AS geo_rollup
   FROM ga_orders ga
     JOIN db_orders od ON ga.trans_id = od.first_order_id;

ALTER TABLE master_view
  OWNER TO postgres;
GRANT ALL ON TABLE master_view TO postgres;
GRANT ALL ON TABLE master_view TO jeffjames;