# from airflow import DAG from ewah.dag_factories.dag_factory_idempotent import dag_factory_idempotent from ewah.operators.amazon_ads import EWAHAmazonAdsOperator from ewah.constants import EWAHConstants from datetime import timedelta, datetime import pytz dwh_engine = EWAHConstants.DWH_ENGINE_SNOWFLAKE target_schema_suffix = "_NEXT" dwh_conn_id = "snowflake_conn" default_args = {} additional_task_args = {} schedule_interval = timedelta(hours=1) schedule_interval_backfill = timedelta(days=1) start_date = datetime(2022, 10, 1, tzinfo=pytz.utc) target_schema_name = "AMAZON_ADS" source_conn_id = "amazon_ads_fn" # "amazon_ads_ne" tables_set_1 = { # "test_report": { # "primary_key": ["keywordId", "_report_date", "_profile_id", "query"], # "profile_id": 1670412383219212, # "ads_type": "sp", # "report_type": "keywords", # "additional_params": { # "segment": "query", # }, # }, "test_report_no_profile_sp_keywords_query": { "primary_key": ["keywordId", "_report_date", "_profile_id", "query"], # "profile_id": 1670412383219212, "ads_type": "sp", "report_type": "keywords", "additional_params": { "segment": "query", }, }, "test_report_profile_de_fn_sp_keywords_query": { "primary_key": [ "keywordId", "_report_date", "_profile_id", "query", ], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sp", "report_type": "keywords", "additional_params": { "segment": "query", "metrics": ",".join( [ "keywordId", "keywordStatus", "keywordText", "matchType", "clicks", "cost", "currency", "impressions", "campaignName", "campaignId", "adGroupId", "adGroupName", "attributedSales7d", ] ), }, }, } # sd: requires some kind of "tacti"? tables_set_2 = { "sp_keyword_report_fn": { "primary_key": ["keywordId", "_report_date", "_profile_id"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sp", "report_type": "keywords", "additional_params": { "metrics": ",".join( [ "keywordId", "keywordStatus", "keywordText", "matchType", "clicks", "cost", "currency", "impressions", "campaignName", "campaignId", "adGroupId", "adGroupName", "attributedSales7d", ] ), }, }, "sb_campaign_report_fn": { "primary_key": ["campaignId", "_report_date", "_profile_id"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "hsa", # sponsored brands are type "hsa" "report_type": "campaigns", "additional_params": { "metrics": ",".join( [ "applicableBudgetRuleId", "applicableBudgetRuleName", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedDetailPageViewsClicks14d", "attributedOrderRateNewToBrand14d", "attributedOrdersNewToBrand14d", "attributedOrdersNewToBrandPercentage14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSalesNewToBrand14d", "attributedSalesNewToBrandPercentage14d", "attributedUnitsOrderedNewToBrand14d", "attributedUnitsOrderedNewToBrandPercentage14d", "campaignBudget", "campaignBudgetType", "campaignId", "campaignName", "campaignRuleBasedBudget", "campaignStatus", "clicks", "cost", "dpv14d", "impressions", "unitsSold14d", "attributedBrandedSearches14d", ] ), }, }, "sb_keyword_report_fn": { "primary_key": ["keywordId", "_report_date", "_profile_id"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "hsa", # sponsored brands are type "hsa" "report_type": "keywords", "additional_params": { "metrics": ",".join( [ "adGroupId", "adGroupName", "applicableBudgetRuleId", "applicableBudgetRuleName", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedDetailPageViewsClicks14d", "attributedOrderRateNewToBrand14d", "attributedOrdersNewToBrand14d", "attributedOrdersNewToBrandPercentage14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSalesNewToBrand14d", "attributedSalesNewToBrandPercentage14d", "attributedUnitsOrderedNewToBrand14d", "attributedUnitsOrderedNewToBrandPercentage14d", "campaignBudget", "campaignBudgetType", "campaignId", "campaignName", "campaignRuleBasedBudget", "campaignStatus", "clicks", "cost", "dpv14d", "impressions", "keywordBid", "keywordId", "keywordStatus", "keywordText", "matchType", "searchTermImpressionRank", "searchTermImpressionShare", "unitsSold14d", "attributedBrandedSearches14d", ] ), }, }, "sb_search_term_report_fn": { "primary_key": ["keywordId", "_report_date", "_profile_id", "query"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "hsa", # sponsored brands are type "hsa" "report_type": "keywords", "additional_params": { "segment": "query", "metrics": ",".join( [ "adGroupId", "adGroupName", "attributedConversions14d", "attributedSales14d", "campaignBudget", "campaignBudgetType", "campaignId", "campaignName", "campaignStatus", "clicks", "cost", "impressions", "keywordBid", "keywordId", "keywordStatus", "keywordText", "matchType", # "query", "searchTermImpressionRank", "searchTermImpressionShare", ] ), }, }, "sd_advertised_product_report_fn": { "primary_key": ["adId", "_report_date", "_profile_id"], # Primary key could also include "sku" or "asin", or both! "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sd", "report_type": "productAds", "additional_params": { "tactic": "T00020", "metrics": ",".join( [ "adGroupId", "adGroupName", "adId", "asin", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedConversions1d", "attributedConversions1dSameSKU", "attributedConversions30d", "attributedConversions30dSameSKU", "attributedConversions7d", "attributedConversions7dSameSKU", "attributedDetailPageView14d", "attributedOrdersNewToBrand14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSales1d", "attributedSales1dSameSKU", "attributedSales30d", "attributedSales30dSameSKU", "attributedSales7d", "attributedSales7dSameSKU", "attributedSalesNewToBrand14d", "attributedUnitsOrdered14d", "attributedUnitsOrdered1d", "attributedUnitsOrdered30d", "attributedUnitsOrdered7d", "attributedUnitsOrderedNewToBrand14d", "campaignId", "campaignName", "clicks", "cost", "currency", "impressions", "sku", # Primary key? Unclear "viewAttributedConversions14d", "viewImpressions", "viewAttributedDetailPageView14d", "viewAttributedSales14d", "viewAttributedUnitsOrdered14d", "viewAttributedOrdersNewToBrand14d", "viewAttributedSalesNewToBrand14d", "viewAttributedUnitsOrderedNewToBrand14d", "attributedBrandedSearches14d", "viewAttributedBrandedSearches14d", ] ), }, }, "sd_campaign_report_fn": { "primary_key": ["campaignId", "_report_date", "_profile_id"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sd", "report_type": "campaigns", "additional_params": { "tactic": "T00020", "metrics": ",".join( [ "attributedConversions14d", "attributedConversions14dSameSKU", "attributedConversions1d", "attributedConversions1dSameSKU", "attributedConversions30d", "attributedConversions30dSameSKU", "attributedConversions7d", "attributedConversions7dSameSKU", "attributedDetailPageView14d", "attributedOrdersNewToBrand14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSales1d", "attributedSales1dSameSKU", "attributedSales30d", "attributedSales30dSameSKU", "attributedSales7d", "attributedSales7dSameSKU", "attributedSalesNewToBrand14d", "attributedUnitsOrdered14d", "attributedUnitsOrdered1d", "attributedUnitsOrdered30d", "attributedUnitsOrdered7d", "attributedUnitsOrderedNewToBrand14d", "campaignBudget", "campaignId", "campaignName", "campaignStatus", "clicks", "cost", "costType", "currency", "impressions", "viewAttributedConversions14d", "viewAttributedDetailPageView14d", "viewAttributedSales14d", "viewAttributedUnitsOrdered14d", "viewImpressions", "viewAttributedOrdersNewToBrand14d", "viewAttributedSalesNewToBrand14d", "viewAttributedUnitsOrderedNewToBrand14d", "attributedBrandedSearches14d", "viewAttributedBrandedSearches14d", ] ), }, }, "sd_asin_report_fn": { # Assumption is that this report = sd purchased product report "primary_key": ["asin", "_report_date", "_profile_id", "sku"], # Primary key could either be SKU or ASIN, or both "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sd", "report_type": "asins", "additional_params": { "tactic": "T00020", "metrics": ",".join( [ "adGroupId", "adGroupName", "asin", # "attributedKindleEditionNormalizedPagesRead14d", # "attributedKindleEditionNormalizedPagesRoyalties14d", "attributedSales14dOtherSKU", "attributedSales1dOtherSKU", "attributedSales30dOtherSKU", "attributedSales7dOtherSKU", # "attributedUnitsOrdered14d", "attributedUnitsOrdered14dOtherSKU", # "attributedUnitsOrdered1d", "attributedUnitsOrdered1dOtherSKU", # "attributedUnitsOrdered30d", "attributedUnitsOrdered30dOtherSKU", # "attributedUnitsOrdered7d", "attributedUnitsOrdered7dOtherSKU", "campaignId", "campaignName", "currency", # "keywordId", # "keywordText", # "matchType", "otherAsin", "sku", # "targetId", # Possible that we might have to exclude in future # "targetingExpression", # "targetingText", # "targetingType", ] ), }, }, "sd_targeting_report_fn": { "primary_key": ["targetId", "_report_date", "_profile_id"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sd", "report_type": "targets", "additional_params": { "tactic": "T00020", "metrics": ",".join( [ "adGroupId", "adGroupName", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedConversions1d", "attributedConversions1dSameSKU", "attributedConversions30d", "attributedConversions30dSameSKU", "attributedConversions7d", "attributedConversions7dSameSKU", "attributedDetailPageView14d", "attributedOrdersNewToBrand14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSales1d", "attributedSales1dSameSKU", "attributedSales30d", "attributedSales30dSameSKU", "attributedSales7d", "attributedSales7dSameSKU", "attributedSalesNewToBrand14d", "attributedUnitsOrdered14d", "attributedUnitsOrdered1d", "attributedUnitsOrdered30d", "attributedUnitsOrdered7d", "attributedUnitsOrderedNewToBrand14d", "campaignId", "campaignName", "clicks", "cost", "currency", "impressions", "targetId", "targetingExpression", "targetingText", "targetingType", "viewImpressions", "viewAttributedConversions14d", "viewAttributedDetailPageView14d", "viewAttributedSales14d", "viewAttributedUnitsOrdered14d", "viewAttributedOrdersNewToBrand14d", "viewAttributedSalesNewToBrand14d", "viewAttributedUnitsOrderedNewToBrand14d", "attributedBrandedSearches14d", "viewAttributedBrandedSearches14d", ] ), }, }, "sp_advertised_product_report_fn": { "primary_key": ["adId", "_report_date", "_profile_id"], # Primary key could also be either "sku" or "asin" or both "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sp", "report_type": "productAds", "additional_params": { "metrics": ",".join( [ "adGroupId", "adGroupName", # "adId", ? auto-added? fails when included "asin", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedConversions1d", "attributedConversions1dSameSKU", "attributedConversions30d", "attributedConversions30dSameSKU", "attributedConversions7d", "attributedConversions7dSameSKU", # "attributedKindleEditionNormalizedPagesRead14d", # "attributedKindleEditionNormalizedPagesRoyalties14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSales1d", "attributedSales1dSameSKU", "attributedSales30d", "attributedSales30dSameSKU", "attributedSales7d", "attributedSales7dSameSKU", "attributedUnitsOrdered14d", "attributedUnitsOrdered14dSameSKU", "attributedUnitsOrdered1d", "attributedUnitsOrdered1dSameSKU", "attributedUnitsOrdered30d", "attributedUnitsOrdered30dSameSKU", "attributedUnitsOrdered7d", "attributedUnitsOrdered7dSameSKU", "campaignBudget", "campaignBudgetType", "campaignId", "campaignName", "campaignStatus", "clicks", "cost", "currency", "impressions", "sku", ] ), }, }, "sp_campaign_report_fn": { "primary_key": ["campaignId", "_report_date", "_profile_id"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sp", "report_type": "campaigns", "additional_params": { "metrics": ",".join( [ "applicableBudgetRuleId", "applicableBudgetRuleName", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedConversions1d", "attributedConversions1dSameSKU", "attributedConversions30d", "attributedConversions30dSameSKU", "attributedConversions7d", "attributedConversions7dSameSKU", # "attributedKindleEditionNormalizedPagesRead14d", # "attributedKindleEditionNormalizedPagesRoyalties14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSales1d", "attributedSales1dSameSKU", "attributedSales30d", "attributedSales30dSameSKU", "attributedSales7d", "attributedSales7dSameSKU", "attributedUnitsOrdered14d", "attributedUnitsOrdered14dSameSKU", "attributedUnitsOrdered1d", "attributedUnitsOrdered1dSameSKU", "attributedUnitsOrdered30d", "attributedUnitsOrdered30dSameSKU", "attributedUnitsOrdered7d", "attributedUnitsOrdered7dSameSKU", "campaignBudget", "campaignBudgetType", "campaignId", "campaignName", "campaignRuleBasedBudget", "campaignStatus", "clicks", "cost", "currency", "impressions", ] ), }, }, "sp_asin_report_fn": { # Assumption is that this report = sp purchased product report "primary_key": ["asin", "_report_date", "_profile_id", "sku"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sp", "report_type": "asins", "additional_params": { "campaignType": "sponsoredProducts", "metrics": ",".join( [ "adGroupId", "adGroupName", "asin", # "attributedKindleEditionNormalizedPagesRead14d", # "attributedKindleEditionNormalizedPagesRoyalties14d", "attributedSales14dOtherSKU", "attributedSales1dOtherSKU", "attributedSales30dOtherSKU", "attributedSales7dOtherSKU", "attributedUnitsOrdered14d", "attributedUnitsOrdered14dOtherSKU", "attributedUnitsOrdered1d", "attributedUnitsOrdered1dOtherSKU", "attributedUnitsOrdered30d", "attributedUnitsOrdered30dOtherSKU", "attributedUnitsOrdered7d", "attributedUnitsOrdered7dOtherSKU", "campaignId", "campaignName", "currency", "keywordId", "keywordText", "matchType", "otherAsin", "sku", # Cannot combine keyword and target fields # "targetId", # "targetingExpression", # "targetingText", # "targetingType", ] ), }, }, "sp_search_term_report_fn": { "primary_key": ["keywordId", "_report_date", "_profile_id", "query"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sp", "report_type": "keywords", "additional_params": { "segment": "query", "metrics": ",".join( [ "adGroupId", "adGroupName", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedConversions1d", "attributedConversions1dSameSKU", "attributedConversions30d", "attributedConversions30dSameSKU", "attributedConversions7d", "attributedConversions7dSameSKU", # "attributedKindleEditionNormalizedPagesRead14d", # "attributedKindleEditionNormalizedPagesRoyalties14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSales1d", "attributedSales1dSameSKU", "attributedSales30d", "attributedSales30dSameSKU", "attributedSales7d", "attributedSales7dSameSKU", "attributedUnitsOrdered14d", "attributedUnitsOrdered14dSameSKU", "attributedUnitsOrdered1d", "attributedUnitsOrdered1dSameSKU", "attributedUnitsOrdered30d", "attributedUnitsOrdered30dSameSKU", "attributedUnitsOrdered7d", "attributedUnitsOrdered7dSameSKU", "campaignBudget", "campaignBudgetType", "campaignId", "campaignName", "campaignStatus", "clicks", "cost", "currency", "impressions", "keywordId", "keywordStatus", "keywordText", "matchType", # "query", ] ), }, }, "sp_targeting_report_fn": { "primary_key": ["targetId", "_report_date", "_profile_id"], "profile_id": 1584776301397454, "source_conn_id": "amazon_ads_fn", "ads_type": "sp", "report_type": "targets", "additional_params": { "metrics": ",".join( [ "adGroupId", "adGroupName", "attributedConversions14d", "attributedConversions14dSameSKU", "attributedConversions1d", "attributedConversions1dSameSKU", "attributedConversions30d", "attributedConversions30dSameSKU", "attributedConversions7d", "attributedConversions7dSameSKU", # "attributedKindleEditionNormalizedPagesRead14d", # "attributedKindleEditionNormalizedPagesRoyalties14d", "attributedSales14d", "attributedSales14dSameSKU", "attributedSales1d", "attributedSales1dSameSKU", "attributedSales30d", "attributedSales30dSameSKU", "attributedSales7d", "attributedSales7dSameSKU", "attributedUnitsOrdered14d", "attributedUnitsOrdered14dSameSKU", "attributedUnitsOrdered1d", "attributedUnitsOrdered1dSameSKU", "attributedUnitsOrdered30d", "attributedUnitsOrdered30dSameSKU", "attributedUnitsOrdered7d", "attributedUnitsOrdered7dSameSKU", "campaignBudget", "campaignBudgetType", "campaignId", "campaignName", "campaignStatus", "clicks", "cost", "impressions", "targetId", "targetingExpression", "targetingText", "targetingType", ] ), }, }, } # API version 3 table setting examples: # Extra params like api_version (v3 has to be set implicit), groupBy, columns (instead of metrics). # Many matrics (here: columns) are renamed from v2 to v3. # Supported endpoints for now: sp (sponsored_products) - only! No creativeType = video either. tables_set_3 = { "sp_advertised_product_report": { "primary_key": ["adId", "_report_date", "_profile_id"], # Primary key could also be either "sku" or "asin" or both "api_version": "v3", "ads_type": "SPONSORED_PRODUCTS", # v3 has different labels "report_type": "spAdvertisedProduct", "additional_params": { "groupBy": ["advertiser"], "columns": [ "adGroupId", "adGroupName", "adId", "advertisedAsin", "purchases14d", "purchasesSameSku14d", "purchases7d", "purchasesSameSku7d", "sales14d", "attributedSalesSameSku14d", "sales7d", "attributedSalesSameSku7d", "unitsSoldClicks14d", "unitsSoldSameSku14d", "unitsSoldClicks7d", "unitsSoldSameSku7d", "campaignBudgetAmount", "campaignBudgetType", "campaignId", "campaignName", "campaignStatus", "clicks", "cost", "campaignBudgetCurrencyCode", "impressions", "advertisedSku", ], }, }, "sp_campaign_report": { "primary_key": ["campaignId", "_report_date", "_profile_id"], "api_version": "v3", "ads_type": "SPONSORED_PRODUCTS", # v3 has different labels "report_type": "spCampaigns", "additional_params": { "groupBy": ["campaign"], "columns": [ "campaignApplicableBudgetRuleId", "campaignApplicableBudgetRuleName", "purchases14d", "purchasesSameSku14d", "purchases7d", "purchasesSameSku7d", "sales14d", "attributedSalesSameSku14d", "sales7d", "attributedSalesSameSku7d", "unitsSoldClicks14d", "unitsSoldSameSku14d", "unitsSoldClicks7d", "unitsSoldSameSku7d", "campaignBudgetAmount", "campaignBudgetType", "campaignId", "campaignName", "campaignRuleBasedBudgetAmount", "campaignStatus", "clicks", "cost", "campaignBudgetCurrencyCode", "impressions", ], }, }, } dag1, dag2, dag3 = dag_factory_idempotent( dag_name="Snow_EL_Amazon_Ads", el_operator=EWAHAmazonAdsOperator, target_schema_name=target_schema_name, start_date=start_date, task_timeout_factor=None, schedule_interval_backfill=schedule_interval_backfill, schedule_interval_future=schedule_interval, operator_config={ "general_config": { "source_conn_id": source_conn_id, }, "tables": tables_set_2, }, dwh_conn_id=dwh_conn_id, dwh_engine=dwh_engine, target_schema_suffix=target_schema_suffix, default_args=default_args, additional_task_args=additional_task_args, )