Skip to content

Instantly share code, notes, and snippets.

@obedespinoza
Created May 1, 2018 01:36
Show Gist options
  • Save obedespinoza/7485ca178ef1e8343fa792748666885e to your computer and use it in GitHub Desktop.
Save obedespinoza/7485ca178ef1e8343fa792748666885e to your computer and use it in GitHub Desktop.
This is the data population for the first prototype
BEGIN TRANSACTION;
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'NONE');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'OTHER');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'MOBILE NEW FEED');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'DESKTOP NEW FEED');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'RIGHT SIDE HAND');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'SITE LINK');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'ACQUISITION');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'QUOTES');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'CONTENT');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'CONVERSIONS');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'PRICE EXTENSIONS');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'NEWS FEED');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'CAROUSEL');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'YAHOO SPONSORED MAIL ADS');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'DOMAIN RETARGETIGN');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'CUSTOM SEGMENT TARGETING');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'PARTNER OFFER');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'PROSPECTING');
INSERT INTO poc_dw.campaign_types(name) VALUES ( 'AD_HOC');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO poc_dw.carriers(name) VALUES ( 'COVENTRY');
INSERT INTO poc_dw.carriers(name) VALUES ( 'EXCELLUS');
INSERT INTO poc_dw.carriers(name) VALUES ( 'FIDELIS');
INSERT INTO poc_dw.carriers(name) VALUES ( 'HUMANA');
INSERT INTO poc_dw.carriers(name) VALUES ( 'JOHNS HOPKINS');
INSERT INTO poc_dw.carriers(name) VALUES ( 'KAISER');
INSERT INTO poc_dw.carriers(name) VALUES ( 'MEMORIAL HERMANN');
INSERT INTO poc_dw.carriers(name) VALUES ( 'PROVIDENCE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'REGENCE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'SCAN');
INSERT INTO poc_dw.carriers(name) VALUES ( 'TUFTS');
INSERT INTO poc_dw.carriers(name) VALUES ( 'WELLCARE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'AARP');
INSERT INTO poc_dw.carriers(name) VALUES ( 'ANTHEM');
INSERT INTO poc_dw.carriers(name) VALUES ( 'GEISINGER');
INSERT INTO poc_dw.carriers(name) VALUES ( 'HARVARD PILGRIM');
INSERT INTO poc_dw.carriers(name) VALUES ( 'HIGHMARK');
INSERT INTO poc_dw.carriers(name) VALUES ( 'HORIZON');
INSERT INTO poc_dw.carriers(name) VALUES ( 'MEDICAL MUTUAL');
INSERT INTO poc_dw.carriers(name) VALUES ( 'UNIVERA');
INSERT INTO poc_dw.carriers(name) VALUES ( 'VERIZON');
INSERT INTO poc_dw.carriers(name) VALUES ( 'MUTUAL OF OMAHA');
INSERT INTO poc_dw.carriers(name) VALUES ( 'UNITED HEALTHCARE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'HEALTH INSURANCE INNOVATIONS');
INSERT INTO poc_dw.carriers(name) VALUES ( 'AARP & UNITED HEALTHCARE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'NONE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'BLUE CROSS BLUE SHIELD');
INSERT INTO poc_dw.carriers(name) VALUES ( 'OSCAR');
INSERT INTO poc_dw.carriers(name) VALUES ( 'MOLINA');
INSERT INTO poc_dw.carriers(name) VALUES ( 'WELLPOINT');
INSERT INTO poc_dw.carriers(name) VALUES ( 'PIVOT');
INSERT INTO poc_dw.carriers(name) VALUES ( 'AGILE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'PLAN-F');
INSERT INTO poc_dw.carriers(name) VALUES ( 'COVERAGE-GAPS');
INSERT INTO poc_dw.carriers(name) VALUES ( 'MEDICARE-ADVANTAGE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'FIRST-TIME-MEDICARE');
INSERT INTO poc_dw.carriers(name) VALUES ( 'EASYMEDICARE');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO poc_dw.channels(name) VALUES ( 'EMAIL');
INSERT INTO poc_dw.channels(name) VALUES ( 'MEDIA BUYS');
INSERT INTO poc_dw.channels(name) VALUES ( 'AFFILIATE');
INSERT INTO poc_dw.channels(name) VALUES ( 'DIRECT MAIL');
INSERT INTO poc_dw.channels(name) VALUES ( 'PUSH NOTIFICATION');
INSERT INTO poc_dw.channels(name) VALUES ( 'SMS');
INSERT INTO poc_dw.channels(name) VALUES ( 'SOCIAL');
INSERT INTO poc_dw.channels(name) VALUES ( 'NATIVE');
INSERT INTO poc_dw.channels(name) VALUES ( 'CONTEXTUAL');
INSERT INTO poc_dw.channels(name) VALUES ( 'EXPERIMENTAL');
INSERT INTO poc_dw.channels(name) VALUES ( 'PROMOTED CONTENT');
COMMIT TRANSACTION ;
BEGIN TRANSACTION;
INSERT INTO poc_dw.click_types(name) VALUES ( 'HEADLINE');
INSERT INTO poc_dw.click_types(name) VALUES ( 'PROMOTION EXTENSION');
INSERT INTO poc_dw.click_types(name) VALUES ( 'SITELINK');
INSERT INTO poc_dw.click_types(name) VALUES ( 'PRICE EXTENSION');
INSERT INTO poc_dw.click_types(name) VALUES ( 'PHONE CALLS');
INSERT INTO poc_dw.click_types(name) VALUES ( 'NONE');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO poc_dw.data_sources(name) VALUES ( 'SUREHITS ADVERTISER');
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP TABLE poc_dw.keywords;
create table poc_dw.keywords(
id bigint identity(0,1) PRIMARY KEY,
keyword_id varchar(64),
keyword varchar(1024),
tier_label varchar(2),
source_id INT REFERENCES poc_dw.data_sources
);
COMMIT TRANSACTION;
rollback;
BEGIN TRANSACTION;
DROP TABLE poc_dw.fact_costs;
CREATE TABLE poc_dw.fact_costs (
process_date DATE DISTKEY, -- Raw no-compression, if compressed the blocks get skewed.
product_id INT REFERENCES poc_dw.products(id),
monetization_id INT REFERENCES poc_dw.monetization(id), -- Remember, default encoding is LZO
traffic_source_id INT REFERENCES poc_dw.traffic_sources(id),
match_type_id INT REFERENCES poc_dw.match_type(id),
channel_id INT REFERENCES poc_dw.channels(id),
vertical_id INT REFERENCES poc_dw.verticals(id),
device_id INT REFERENCES poc_dw.devices(id),
carrier_id INT REFERENCES poc_dw.carriers(id),
campaign_type_id INT REFERENCES poc_dw.campaign_types(id),
theme_id INT REFERENCES poc_dw.themes(id),
click_type_id INT REFERENCES poc_dw.click_types(id),
keyword_id INT REFERENCES poc_dw.keywords(id),
keyword VARCHAR(512) ENCODE ZSTD,
adgroup_id BIGINT,
adgroup_name VARCHAR(256) ENCODE ZSTD,
ad_id VARCHAR(128) ENCODE ZSTD,
final_url VARCHAR(MAX) ENCODE ZSTD, -- Text is synonym of VARCHAR(256), not a real text!
base_landing_page VARCHAR(MAX) ENCODE ZSTD,
sub_id VARCHAR(64) ENCODE ZSTD,
clicks INT ENCODE ZSTD,
impressions INT ENCODE ZSTD,
quality_score INT ENCODE ZSTD,
cpc DECIMAL(20,2) ENCODE ZSTD,
cost DECIMAL(20,2) ENCODE ZSTD,
avg_position DECIMAL(10,2) ENCODE ZSTD,
conversions DECIMAL(10,2) ENCODE ZSTD,
current_max_cpc DECIMAL(10,2)
)
COMPOUND SORTKEY (process_date, product_id);
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO poc_dw.keywords(keyword_id, keyword, tier_label, source_id)
SELECT
keyword_id,
keyword,
keyword_tier_type AS tier_label,
ds.id AS source_id
FROM dw.keyword_tier_traffic_source ktts
INNER JOIN poc_dw.data_sources ds ON ktts.traffic_source = CASE WHEN ds.name LIKE 'GOOGLE%'
THEN 'GOOGLE'
WHEN ds.name LIKE 'BING%'
THEN 'BING'
ELSE ds.name END
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE SUPPLEMENT');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE OTHER');
INSERT INTO poc_dw.themes(name) VALUES ( 'PLAN F');
INSERT INTO poc_dw.themes(name) VALUES ( 'PLAN G');
INSERT INTO poc_dw.themes(name) VALUES ( 'PLAN N');
INSERT INTO poc_dw.themes(name) VALUES ( 'PLAN J');
INSERT INTO poc_dw.themes(name) VALUES ( 'PART B');
INSERT INTO poc_dw.themes(name) VALUES ( 'DENTAL');
INSERT INTO poc_dw.themes(name) VALUES ( 'SENIOR INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'WHAT IS MEDIGAP');
INSERT INTO poc_dw.themes(name) VALUES ( 'OBAMACARE');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFORDABLE CARE ACT/ACA');
INSERT INTO poc_dw.themes(name) VALUES ( 'INDIVIDUAL');
INSERT INTO poc_dw.themes(name) VALUES ( 'EXCHANGE');
INSERT INTO poc_dw.themes(name) VALUES ( 'MARKETPLACE');
INSERT INTO poc_dw.themes(name) VALUES ( 'GOVERNMENT');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICAL INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'COMPANIES');
INSERT INTO poc_dw.themes(name) VALUES ( 'SELF EMPLOYED');
INSERT INTO poc_dw.themes(name) VALUES ( 'SHORT TERM');
INSERT INTO poc_dw.themes(name) VALUES ( 'TRUMPCARE');
INSERT INTO poc_dw.themes(name) VALUES ( 'AMERICAN HEALTHCARE ACT');
INSERT INTO poc_dw.themes(name) VALUES ( 'DEADLINE');
INSERT INTO poc_dw.themes(name) VALUES ( 'OPEN ENROLLMENT');
INSERT INTO poc_dw.themes(name) VALUES ( 'MAJOR MEDICAL');
INSERT INTO poc_dw.themes(name) VALUES ( 'CATASTROPHIC/HDHP');
INSERT INTO poc_dw.themes(name) VALUES ( 'SPANISH TERMS');
INSERT INTO poc_dw.themes(name) VALUES ( 'LONG TERM');
INSERT INTO poc_dw.themes(name) VALUES ( 'OBAMACARE EXCHANGE');
INSERT INTO poc_dw.themes(name) VALUES ( 'ADVANTAGE INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'ADVANTAGE MEDICARE');
INSERT INTO poc_dw.themes(name) VALUES ( 'ADVANTAGE PART C');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE PART C');
INSERT INTO poc_dw.themes(name) VALUES ( 'ADVANTAGE PLANS');
INSERT INTO poc_dw.themes(name) VALUES ( 'ADVANTAGE SENIOR');
INSERT INTO poc_dw.themes(name) VALUES ( 'ADVANTAGE OTHER');
INSERT INTO poc_dw.themes(name) VALUES ( 'PART C OTHER');
INSERT INTO poc_dw.themes(name) VALUES ( 'SUPPLEMENTAL');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE');
INSERT INTO poc_dw.themes(name) VALUES ( 'SUPPLEMENT');
INSERT INTO poc_dw.themes(name) VALUES ( 'CARRIER');
INSERT INTO poc_dw.themes(name) VALUES ( 'COMPLETE');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICAL');
INSERT INTO poc_dw.themes(name) VALUES ( 'GMAIL');
INSERT INTO poc_dw.themes(name) VALUES ( 'BRAND');
INSERT INTO poc_dw.themes(name) VALUES ( 'HEALTH INSURANCE INNOVATIONS');
INSERT INTO poc_dw.themes(name) VALUES ( 'HEALTH INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'GOV');
INSERT INTO poc_dw.themes(name) VALUES ( 'SPANISH');
INSERT INTO poc_dw.themes(name) VALUES ( 'ENROLLMENT');
INSERT INTO poc_dw.themes(name) VALUES ( 'PRIVATE');
INSERT INTO poc_dw.themes(name) VALUES ( 'HEALTHCARE');
INSERT INTO poc_dw.themes(name) VALUES ( 'SUBSIDY');
INSERT INTO poc_dw.themes(name) VALUES ( 'HDHP');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFORDABLE CARE ACT');
INSERT INTO poc_dw.themes(name) VALUES ( 'CATASTROPHIC');
INSERT INTO poc_dw.themes(name) VALUES ( 'LOW INCOME');
INSERT INTO poc_dw.themes(name) VALUES ( 'HEALTH PLAN FINDER');
INSERT INTO poc_dw.themes(name) VALUES ( 'GET COVERED');
INSERT INTO poc_dw.themes(name) VALUES ( 'GOLD');
INSERT INTO poc_dw.themes(name) VALUES ( 'PPO');
INSERT INTO poc_dw.themes(name) VALUES ( 'TRUMP');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE ELIGIBILITY');
INSERT INTO poc_dw.themes(name) VALUES ( 'GENERIC');
INSERT INTO poc_dw.themes(name) VALUES ( 'FAMILY HEALTH INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'HEALTH INSURANCCE COMPANIES');
INSERT INTO poc_dw.themes(name) VALUES ( 'INDIVIDUAL HEALTH INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'PRIVATE HEALTH INSURANSE');
INSERT INTO poc_dw.themes(name) VALUES ( 'COVERED CA');
INSERT INTO poc_dw.themes(name) VALUES ( 'HIGH DEDUCTIBLE HEALTH PLAN');
INSERT INTO poc_dw.themes(name) VALUES ( 'INTERVIEW1-30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'LANDING-PAGE-30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'LOOKALIKE-LEADS');
INSERT INTO poc_dw.themes(name) VALUES ( 'LOOKALIKE-VISITORS');
INSERT INTO poc_dw.themes(name) VALUES ( 'NONE');
INSERT INTO poc_dw.themes(name) VALUES ( 'SUBSIDY EXCHANGE');
INSERT INTO poc_dw.themes(name) VALUES ( 'SUBSIDY MARKETPLACE');
INSERT INTO poc_dw.themes(name) VALUES ( 'DRIP');
INSERT INTO poc_dw.themes(name) VALUES ( 'WELCOME');
INSERT INTO poc_dw.themes(name) VALUES ( 'CONFIRMATION');
INSERT INTO poc_dw.themes(name) VALUES ( 'O64');
INSERT INTO poc_dw.themes(name) VALUES ( 'O65');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_60D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_90D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_180D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_3D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_7D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_14D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_60D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_90D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_180D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_3D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_7D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_14D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_60D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_90D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_180D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-NEWFORM-TRAFFIC_3D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-NEWFORM-TRAFFIC_7D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-NEWFORM-TRAFFIC_14D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-NEWFORM-TRAFFIC_30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-NEWFORM-TRAFFIC_60D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-NEWFORM-TRAFFIC_90D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-NEWFORM-TRAFFIC_180D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-LEADS_90D');
INSERT INTO poc_dw.themes(name) VALUES ( 'IN-MARKET-HEALTH-INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'IN-MARKET-RETIREMENT-PLANNING');
INSERT INTO poc_dw.themes(name) VALUES ( 'IN-MARKET-EMPLOYMENT');
INSERT INTO poc_dw.themes(name) VALUES ( 'IN-MARKET-TRAVEL-HOSPITALITY');
INSERT INTO poc_dw.themes(name) VALUES ( 'IN-MARKET-HOME-DECOR');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-INTEREST-ONLY');
INSERT INTO poc_dw.themes(name) VALUES ( 'CM-LEADS');
INSERT INTO poc_dw.themes(name) VALUES ( 'CM-SIMILAR-AUDIENCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'KTC-COMPETITORS');
INSERT INTO poc_dw.themes(name) VALUES ( 'KTC-KEYWORDS');
INSERT INTO poc_dw.themes(name) VALUES ( 'TOPICS-HEALTH-INSURANCE');
INSERT INTO poc_dw.themes(name) VALUES ( 'PROMO EXTENSIONS');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-NEWS-JUNKIES');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-TV-LOVERS');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-COOKING-ENTHUSIASTS');
INSERT INTO poc_dw.themes(name) VALUES ( 'PLAN B');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE EBOOK');
INSERT INTO poc_dw.themes(name) VALUES ( 'COVERAGE GAPS');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE ADVANTAGE');
INSERT INTO poc_dw.themes(name) VALUES ( 'MEDICARE QUIZ');
INSERT INTO poc_dw.themes(name) VALUES ( 'All-Website-Traffic_30D_18-24');
INSERT INTO poc_dw.themes(name) VALUES ( 'All-Website-Traffic_30D_25-40');
INSERT INTO poc_dw.themes(name) VALUES ( 'All-Website-Traffic_30D_41-54');
INSERT INTO poc_dw.themes(name) VALUES ( 'All-Website-Traffic_30D_55-63');
INSERT INTO poc_dw.themes(name) VALUES ( 'Customer-Match_Seed-List_30D_18-24');
INSERT INTO poc_dw.themes(name) VALUES ( 'Customer-Match_Seed-List_30D_25-40');
INSERT INTO poc_dw.themes(name) VALUES ( 'Customer-Match_Seed-List_30D_41-54');
INSERT INTO poc_dw.themes(name) VALUES ( 'Customer-Match_Seed-List_30D_55-63');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-BARGAIN-SHOPPERS');
INSERT INTO poc_dw.themes(name) VALUES ( 'CUSTOMER-MATCH_SEED-LIST');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-BARGAIN-HUNTERS');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-SHUTTERBUGS');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-AVID-INVESTORS');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-BOOK-LOVERS');
INSERT INTO poc_dw.themes(name) VALUES ( 'AFFINITY-PET-LOVERS');
INSERT INTO poc_dw.themes(name) VALUES ( 'CUSTOM-AUDIENCE-1');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_14D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_7D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_3D');
INSERT INTO poc_dw.themes(name) VALUES ( 'CUSTOMER-MATCH_SEED-LIST');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-WEBSITE-TRAFFIC_30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-1-TRAFFIC_30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'ALL-INTERVIEW-2-TRAFFIC_30D');
INSERT INTO poc_dw.themes(name) VALUES ( 'OLD CAMPAIGNS');
INSERT INTO poc_dw.themes(name) VALUES ( 'COBRA');
INSERT INTO poc_dw.themes(name) VALUES ( 'HMO');
INSERT INTO poc_dw.themes(name) VALUES ( 'METAL');
INSERT INTO poc_dw.themes(name) VALUES ( 'SILVER');
INSERT INTO poc_dw.themes(name) VALUES ( 'BRONZE');
INSERT INTO poc_dw.themes(name) VALUES ( 'PLATINIUM');
INSERT INTO poc_dw.themes(name) VALUES ( 'QUALIFYING LIFE EVENT');
INSERT INTO poc_dw.themes(name) VALUES ( 'AD_HOC');
COMMIT TRANSACTION;
BEGIN TRANSACTION ;
CREATE TABLE poc_dw.match_type (
id BIGINT IDENTITY(0,1) PRIMARY KEY,
name VARCHAR(50)
) DISTSTYLE ALL;
INSERT INTO poc_dw.match_type(name)
SELECT distinct UPPER(matchtype) from dw_src.adwords_keyword WHERE matchtype is not null;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP TABLE poc_dw.campaign;
CREATE TABLE poc_dw.campaign (
id BIGINT IDENTITY(0,1) PRIMARY KEY,
campaign_id varchar(32),
name VARCHAR(256),
source_id INT REFERENCES poc_dw.data_sources
) DISTSTYLE ALL;
INSERT INTO poc_dw.campaign(campaign_id, name, source_id)
select distinct campaign_id, campaign,0 as data_source_id from dw_src.adwords_keyword;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
INSERT INTO poc_dw.campaign(campaign_id, name, source_id)
select distinct campaignid, campaign_name,2 as data_source_id from dw_src.bing_keyword where process_date >= '2017-01-01';
COMMIT TRANSACTION;
BEGIN TRANSACTION ;
INSERT INTO poc_dw.campaign(campaign_id, name, source_id)
select distinct campaign_id, campaign_name,4 as data_source_id from dw_src.tron_yahoo_keyword;
COMMIT TRANSACTION;
ROLLBACK ;
BEGIN TRANSACTION ;
INSERT INTO poc_dw.fact_costs(process_date,
product_id,
monetization_id,
traffic_source_id, match_type_id, channel_id, vertical_id, device_id, carrier_id, campaign_type_id, theme_id, click_type_id, keyword_id, adgroup_id, adgroup_name, keyword, ad_id, final_url, base_landing_page, sub_id, clicks, impressions, quality_score, cpc, cost, avg_position, conversions)
SELECT
process_date,
p.id AS product_id,
m.id AS monetization_id,
ts.id AS traffic_source_id,
mt.id AS match_type_id,
c.id AS campaign_id,
ch.id AS channel_id,
v.id AS vertical_id,
d.id AS device_id,
ct.id AS campaign_type_id,
t.id AS theme_id,
clt.id as click_type_id,
k.id as keyword_id,
ad_group_id,
ad_group,
ak.keyword,
'NA' AS ad_id,
REPLACE(REPLACE(REPLACE(CASE WHEN d.name = 'DESKTOP'
THEN finalurl
ELSE mobilefinalurl END, '"', ''), '[', ''), ']', '') AS final_url,
REPLACE(REPLACE(REPLACE(CASE WHEN d.name = 'DESKTOP'
THEN SUBSTRING(finalurl, 0, POSITION('?' IN finalurl))
ELSE SUBSTRING(mobilefinalurl, 0, POSITION('?' IN mobilefinalurl)) END, '"', ''), '[', ''),
']', '') AS base_landing_page,
sub_id,
clicks,
impressions,
quality_score,
maxcpc/ 1000000.00 as cpc,
cost,
avgposition as avg_position,
conversions
FROM dw_src.adwords_keyword ak LEFT JOIN poc_dw.products p ON ak.customer_id = replace(p.account_id, '-', '')
LEFT JOIN poc_dw.monetization m ON m.name = 'CLICK'
LEFT JOIN poc_dw.traffic_sources ts ON ts.name = 'GOOGLE'
LEFT JOIN poc_dw.match_type mt ON mt.name = UPPER(ak.matchtype)
LEFT JOIN poc_dw.campaign c ON c.campaign_id = ak.campaign_id AND c.source_id = 0
LEFT JOIN poc_dw.channels ch ON ch.name = 'SEARCH'
LEFT JOIN poc_dw.verticals v ON p.name = v.name
LEFT JOIN poc_dw.devices d ON d.name = ak.device
LEFT JOIN poc_dw.carriers ca ON ca.name = ak.carrier_name
LEFT JOIN poc_dw.campaign_types ct ON ct.name = ak.campaign_type_name
LEFT JOIN poc_dw.themes t ON t.name = ak.theme_name
LEFT JOIN poc_dw.click_types clt ON clt.name = UPPER(ak.clicktype)
LEFT JOIN poc_dw.keywords k ON k.keyword_id = ak.keywordid
-- WHERE AK.process_date >= '2018
COMMIT TRANSACTION;
BEGIN TRANSACTION ;
INSERT INTO poc_dw.fact_costs(process_date,
product_id,
monetization_id,
traffic_source_id, match_type_id, channel_id, vertical_id, device_id, carrier_id, campaign_type_id, theme_id, click_type_id, keyword_id, adgroup_id, adgroup_name, keyword, ad_id, final_url, base_landing_page, sub_id, clicks, impressions, quality_score, cpc, cost, avg_position, conversions)
SELECT
process_date,
p.id AS product_id,
m.id AS monetization_id,
ts.id AS traffic_source_id,
mt.id AS match_type_id,
c.id AS campaign_id,
ch.id AS channel_id,
v.id AS vertical_id,
CASE WHEN bk.device = 'Computer' THEN 1 WHEN bk.device = 'Tablet' THEN 4 WHEN bk.device = 'Smartphone' THEN 2 END AS device_id,
ct.id AS campaign_type_id,
t.id AS theme_id,
clt.id as click_type_id,
k.id as keyword_id,
CAST(bk.adgroupid AS BIGINT),
adgroupname,
bk.keyword,
bk.adid AS ad_id,
REPLACE(REPLACE(REPLACE(CASE WHEN bk.device = 'Computer'
THEN finalurl
ELSE bk.finalmobileurl END, '"', ''), '[', ''), ']', '') AS final_url,
REPLACE(REPLACE(REPLACE(CASE WHEN bk.device = 'Computer'
THEN SUBSTRING(finalurl, 0, POSITION('?' IN finalurl))
ELSE SUBSTRING(finalmobileurl, 0, POSITION('?' IN finalmobileurl)) END, '"', ''), '[', ''),
']', '') AS base_landing_page,
sub_id,
clicks,
impressions,
quality_score,
bk.average_cpc as cpc,
bk.spend,
bk.average_position as avg_position,
conversions
FROM dw_src.bing_keyword bk LEFT JOIN poc_dw.products p ON bk.accountnumber = p.account_id
LEFT JOIN poc_dw.monetization m ON m.name = 'CLICK'
LEFT JOIN poc_dw.traffic_sources ts ON ts.name = 'BING'
LEFT JOIN poc_dw.match_type mt ON mt.name = UPPER(bk.biddedmatchtype)
LEFT JOIN poc_dw.campaign c ON c.campaign_id = bk.campaignid AND c.source_id = 2
LEFT JOIN poc_dw.channels ch ON ch.name = 'SEARCH'
LEFT JOIN poc_dw.verticals v ON p.name = v.name
LEFT JOIN poc_dw.carriers ca ON ca.name = bk.carrier_name
LEFT JOIN poc_dw.campaign_types ct ON ct.name = bk.campaign_type_name
LEFT JOIN poc_dw.themes t ON t.name = bk.theme_name
LEFT JOIN poc_dw.click_types clt ON clt.name = 'NONE'
LEFT JOIN poc_dw.keywords k ON k.keyword_id = bk.keywordid
where process_date >= '2017-01-01'
COMMIT TRANSACTION;
ROLLBACK ;
select distinct device from dw_src.bing_keyword;
BEGIN TRANSACTION ;
/*INSERT INTO poc_dw.fact_costs(process_date,
product_id,
monetization_id,
traffic_source_id, match_type_id, channel_id, vertical_id, device_id, carrier_id, campaign_type_id, theme_id, click_type_id, keyword_id, adgroup_id, adgroup_name, keyword, ad_id, final_url, base_landing_page, sub_id, clicks, impressions, quality_score, cpc, cost, avg_position, conversions)*/
SELECT
yk.date,
p.id AS product_id,
m.id AS monetization_id,
ts.id AS traffic_source_id,
mt.id AS match_type_id,
c.id AS campaign_id,
ch.id AS channel_id,
v.id AS vertical_id,
d.id AS device_id,
ct.id AS campaign_type_id,
t.id AS theme_id,
clt.id as click_type_id,
k.id as keyword_id,
yk.adgroup_id,--ad_group_id,
yk.adgroup_name,
yk.keywod,
yk.ad_id AS ad_id,
yk.destination_url AS final_url,
SUBSTRING(destination_url, 0, POSITION('?' IN destination_url)) AS base_landing_page,
yk.subid,
clicks,
impressions,
0 as quality_score,
yk.cpc as cpc,
yk.spend,
yk.average_position as avg_position,
conversions
FROM dw_src.tron_yahoo_keyword yk LEFT JOIN poc_dw.products p ON yk.advertiser_id = p.account_id
LEFT JOIN poc_dw.monetization m ON m.name = 'CLICK'
LEFT JOIN poc_dw.traffic_sources ts ON ts.name = 'YAHOO SEARCH'
LEFT JOIN poc_dw.match_type mt ON mt.name = UPPER(yk.keyword_match_type)
LEFT JOIN poc_dw.campaign c ON c.campaign_id = yk.campaign_id AND c.source_id = 4
LEFT JOIN poc_dw.channels ch ON ch.name = 'SEARCH'
LEFT JOIN poc_dw.verticals v ON p.name = v.name
LEFT JOIN poc_dw.devices d ON d.name = yk.device_type
LEFT JOIN poc_dw.carriers ca ON ca.name = yk.carrier_name
LEFT JOIN poc_dw.campaign_types ct ON ct.name = yk.campaign_type_name
LEFT JOIN poc_dw.themes t ON t.name = yk.theme_name
LEFT JOIN poc_dw.click_types clt ON clt.name = 'NONE'
LEFT JOIN poc_dw.keywords k ON k.keyword_id = yk.keyword_id
-- WHERE AK.process_date >= '2018
COMMIT TRANSACTION;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment