-
-
Save cprieto/1d9b7fd293c12629064d9c139c3a5ad3 to your computer and use it in GitHub Desktop.
This is the data population for the first prototype
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 IF EXISTS poc_dw.keywords; | |
| CREATE TABLE IF NOT EXISTS poc_dw.keywords ( | |
| id BIGINT IDENTITY (0, 1) PRIMARY KEY, | |
| source_keyword_id VARCHAR(64), | |
| keyword VARCHAR(1024), | |
| tier_label VARCHAR(2), | |
| data_source_id INT REFERENCES poc_dw.data_sources | |
| ); | |
| COMMIT TRANSACTION; | |
| ROLLBACK; | |
| BEGIN TRANSACTION; | |
| DROP TABLE IF EXISTS poc_dw.fact_keywords; | |
| CREATE TABLE IF NOT EXISTS poc_dw.fact_keywords ( | |
| 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), | |
| source_keyword_id VARCHAR(64), | |
| 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 (source_keyword_id, keyword, tier_label, data_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; | |
| -- These functions are for this import, they will be remove later | |
| CREATE OR REPLACE FUNCTION remove_qs(VARCHAR(MAX)) | |
| RETURNS VARCHAR(MAX) | |
| STABLE AS | |
| $$ | |
| SELECT SUBSTRING($1, 0, POSITION('?' IN $1)); | |
| $$ | |
| LANGUAGE SQL; | |
| CREATE OR REPLACE FUNCTION process_final_url(VARCHAR(MAX), VARCHAR(MAX), VARCHAR(MAX)) | |
| RETURNS VARCHAR(MAX) | |
| STABLE AS | |
| $$ | |
| SELECT REPLACE(REPLACE(REPLACE( | |
| CASE WHEN $1 = 'DESKTOP' | |
| THEN $2 | |
| ELSE $3 | |
| END, | |
| '"', ''), '[', ''), ']', ''); | |
| $$ | |
| LANGUAGE SQL; | |
| BEGIN TRANSACTION; | |
| INSERT INTO poc_dw.fact_keywords (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, source_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, | |
| ak.keywordid AS source_keyword_id, | |
| ad_group_id, | |
| ad_group, | |
| ak.keyword, | |
| 'NA' AS ad_id, | |
| process_final_url(d.name, finalurl, mobilefinalurl) AS final_url, | |
| process_final_url(d.name, remove_qs(finalurl), remove_qs(mobilefinalurl)) 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.source_keyword_id = ak.keywordid; | |
| COMMIT TRANSACTION; | |
| BEGIN TRANSACTION; | |
| INSERT INTO poc_dw.fact_keywords (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, source_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, | |
| bk.keywordid AS source_keyword_id, | |
| CAST(bk.adgroupid AS BIGINT), | |
| adgroupname, | |
| bk.keyword, | |
| bk.adid AS ad_id, | |
| process_final_url( | |
| CASE | |
| WHEN UPPER(bk.device) = 'COMPUTER' | |
| THEN 'DESKTOP' | |
| ELSE bk.device | |
| END, finalurl, finalmobileurl) AS final_url, | |
| process_final_url( | |
| CASE | |
| WHEN UPPER(bk.device) = 'COMPUTER' | |
| THEN 'DESKTOP' | |
| ELSE bk.device | |
| END, remove_qs(finalurl), remove_qs(finalmobileurl) | |
| ) 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.source_keyword_id = bk.keywordid; | |
| COMMIT TRANSACTION; | |
| ROLLBACK; | |
| SELECT DISTINCT device | |
| FROM dw_src.bing_keyword; | |
| BEGIN TRANSACTION; | |
| INSERT INTO poc_dw.fact_keywords (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, source_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.keyword_id AS source_keyword_id, | |
| yk.adgroup_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 = | |
| CASE | |
| WHEN yk.device_type = 'SmartPhone' | |
| THEN 'MOBILE' | |
| ELSE UPPER(yk.device_type) | |
| END | |
| 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.source_keyword_id = yk.keyword_id; | |
| COMMIT TRANSACTION; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment