from sync_amz_data.public.amz_ad_client import SPClient, Account, SBClient, SDClient, BaseClient import pandas as pd import numpy as np from dateutil.parser import parse pd.set_option('display.max_columns', None) import warnings warnings.filterwarnings('ignore') pd.set_option('expand_frame_repr', False) from datetime import datetime, timezone, timedelta import clickhouse_connect import pytz from typing import Literal class Common_ETLMethod(BaseClient): def timeZone(self): rel = self.get_profilesInfo() profileId = int(self.profile_id) df_info = pd.json_normalize(rel) return df_info.query("profileId==@profileId")['timezone'].values[0] def clickhouse_connect(self): conn = clickhouse_connect.get_client(host='3.93.43.158', port=8123, username='root', password='6f0eyLuiVn3slzbGWpzI') return conn def columnsName_modify(self, df): """ 列名.换_，设置全部小写 """ df.columns = [i.replace(".", "_").lower() for i in df.columns] return df def time_stamp_convert(self, df, time_columns: list): """ 时间戳转换为utc """ for time_column in time_columns: df[time_column] = pd.to_datetime(df[time_column] * 1000000).map(lambda x: x.strftime("%Y-%m-%d %H:%M:%S")) df[time_columns] = df[time_columns].astype("datetime64") return df def TZ_Deal(self, df, time_columns): """ TZ时间格式转换为utc """ for time_column in time_columns: df[time_column] = df[time_column].map(lambda x: parse(x).strftime("%Y-%m-%d %H:%M:%S")) df[time_columns] = df[time_columns].astype("datetime64") return df def placement_segmentsplit(self, df, segment): """ 拆分placement与percentage列 """ df[segment] = df[segment].astype("string") df[segment + str("_percentage")] = df[segment].str.extract("'percentage':.+([\d\.]{1,}),").astype('float32') df[segment + str("_placement")] = df[segment].str.extract("'placement':.+'(.+)'") df.replace(['nan', 'Nan', 'NaN'], np.nan, inplace=True) df.drop(columns=[segment], inplace=True) return df def expression_split(self, df, segment): """ 拆分type，value列 """ df[segment] = df[segment].astype("string") df[segment + str("_type")] = df[segment].str.extract(r"'type':\s{0,1}'(.+?)',") df[segment + str("_value")] = df[segment].str.extract(r"'value':\s{0,1}[\'\[\{](.+)'") df[segment + str("_value")] = df[segment + str("_value")].map( lambda x: x if pd.isna(x) or "," not in x else "[{" + x + "'}]").str.replace("{{","{") df.replace(['nan', 'Nan', 'NaN'], np.nan, inplace=True) df.drop(columns=[segment], inplace=True) return df def get_keyOvalue(self,expressions, result: Literal['value', 'type']): if expressions not in [None,pd.NA,pd.NaT]: if len(expressions) > 1: return [i[result] for i in expressions] if len(expressions) == 1: sub_ = expressions[0][result] if type(sub_) == str: return sub_ elif type(sub_) == list: return [i[result] for i in sub_] else: return expressions else: return '-' def id_type_trans(self,df): id_columns = [i for i in df.columns if 'id' in i.lower() and 'bid' not in i.lower()] df[id_columns] = df[id_columns].fillna(-1).applymap(lambda x: round(int(x), 0)).astype("string") df[id_columns] = df[id_columns].applymap(lambda x: '' if x == '-1' else x) return df def type_trans(self, df_report, columns: list, timeZone_: str, extra_columns: list = []): # 添加字段 df_report['profileId'] = self.profile_id df_report['timeZone'] = timeZone_ df_needManualAdd = [i for i in columns if i not in df_report.columns] if len(df_needManualAdd) > 0: df_report[df_needManualAdd] = None columns.extend(extra_columns) columns.append("date") if "date" not in columns else columns columns.extend(["timeZone", "profileId"]) # 修改字段类型 df_report = self.id_type_trans(df_report) df_report['date'] = df_report['date'].astype("datetime64") # df_report[df_report.select_dtypes('O').columns] = df_report[df_report.select_dtypes('O').columns].astype('string') toFloat = [i for i in columns if 'sales' in i.lower() or 'percent' in i.lower() or 'video' in i.lower()] if len(toFloat) > 0: df_report[toFloat] = df_report[toFloat].applymap(lambda x: np.nan if pd.isna(x) or x == '' else float(x)) df_report.fillna(np.nan, inplace=True) # print(df_report[columns].info()) # df_report.to_excel("df.xlsx") return df_report class Acount_ETL(Account, Common_ETLMethod): def portfolio_ETL(self): list_portfolio = self.get_portfolios() df_portfolio = pd.json_normalize(list_portfolio) # print(self.columnsName_modify(df_portfolio)) return self.columnsName_modify(df_portfolio) class SP_ETL(SPClient, Common_ETLMethod): def campaigns_ETL(self): list_campaign_SP = list(self.iter_campaigns(**{"includeExtendedDataFields": True})) df_campaign = pd.json_normalize(list_campaign_SP) df_campaign = self.placement_segmentsplit(df_campaign, "dynamicBidding.placementBidding") df_campaign = self.TZ_Deal(df_campaign, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"]) # print(df_campaign) return self.columnsName_modify(df_campaign) def adGroups_ETL(self): list_adGroup_SP = list(self.iter_adGroups(**{"includeExtendedDataFields": True})) df_adGroup_SP = pd.json_normalize(list_adGroup_SP) df_adGroup_SP = self.TZ_Deal(df_adGroup_SP, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"]) return self.columnsName_modify(df_adGroup_SP) def ads_ETL(self): list_adId_SP = list(self.iter_ads(**{"includeExtendedDataFields": True})) df_adId_SP = pd.json_normalize(list_adId_SP) df_adId_SP = self.TZ_Deal(df_adId_SP, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"]) return self.columnsName_modify(df_adId_SP) def keywords_ETL(self): list_keywords_SP = list(self.iter_keywords(**{"includeExtendedDataFields": True})) df_keywords_SP = pd.json_normalize(list_keywords_SP) df_keywords_SP = self.TZ_Deal(df_keywords_SP, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"]) return self.columnsName_modify(df_keywords_SP) def targets_ETL(self): list_targets = list(self.iter_targets()) df_targets = pd.json_normalize(list_targets) df_targets = self.TZ_Deal(df_targets, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"]) return self.columnsName_modify(df_targets) def budget_ETL(self, campaign_ids: list): list_budget = self.get_budget(campaign_ids=campaign_ids)['success'] df_budget = pd.json_normalize(list_budget) df_budget = self.TZ_Deal(df_budget, ["usageUpdatedTimestamp"]) return self.columnsName_modify(df_budget) def reportV3_campaign_spCampaignsETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "spCampaigns" params['columns'] = [ 'campaignName', 'campaignId', 'campaignStatus', 'campaignBudgetAmount', 'campaignBudgetType', 'campaignRuleBasedBudgetAmount', 'campaignApplicableBudgetRuleId', 'campaignApplicableBudgetRuleName', 'campaignBudgetCurrencyCode', 'topOfSearchImpressionShare', 'impressions', 'clicks', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'date', 'campaignBiddingStrategy', 'costPerClick', 'clickThroughRate', 'spend' ] # 'startDate', 'endDate', params['groupby'] = ['campaign'] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SP_spCampaigns_campaignV3", df_report[params['columns']]) print("插入完成SP_spCampaigns_campaignV3") return df_report[params['columns']] def reportV3_adGroup_spCampaignsETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "spCampaigns" params['columns'] = [ 'adGroupName', 'adGroupId', 'adStatus','campaignName', 'campaignId', 'campaignStatus', 'campaignBudgetAmount', 'campaignBudgetType', 'campaignRuleBasedBudgetAmount', 'campaignApplicableBudgetRuleId', 'campaignApplicableBudgetRuleName', 'campaignBudgetCurrencyCode', 'impressions', 'clicks', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'date', 'campaignBiddingStrategy', 'costPerClick', 'clickThroughRate', 'spend' ] # 'startDate', 'endDate', params['groupby'] = ['adGroup',"campaign"] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SP_spCampaigns_adGroupV3", df_report[params['columns']]) print("插入完成SP_spCampaigns_adGroupV3") return df_report[params['columns']] # def reportV3_campaignPlacement_spCampaignsETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "spCampaigns" params['columns'] = [ 'placementClassification','campaignName', 'campaignId','campaignStatus', 'campaignBudgetAmount', 'campaignBudgetType', 'campaignRuleBasedBudgetAmount', 'campaignApplicableBudgetRuleId', 'campaignApplicableBudgetRuleName', 'campaignBudgetCurrencyCode', 'impressions', 'clicks', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'date', 'campaignBiddingStrategy', 'costPerClick', 'clickThroughRate', 'spend' ] # 'startDate', 'endDate', params['groupby'] = ['campaign','campaignPlacement'] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) # print(df_report) # print(df_report.columns) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SP_spCampaigns_placementV3", df_report[params['columns']]) print("插入完成SP_spCampaigns_placementV3") return df_report[params['columns']] def reportV3_targeting_spTargetingETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "spTargeting" params['columns'] = [ 'adKeywordStatus', 'impressions', 'clicks', 'costPerClick', 'clickThroughRate', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'salesOtherSku7d', 'unitsSoldOtherSku7d', 'acosClicks7d', 'acosClicks14d', 'roasClicks7d', 'roasClicks14d', 'keywordId', 'keyword', 'campaignBudgetCurrencyCode', 'date', 'portfolioId', 'campaignName', 'campaignId', 'campaignBudgetType', 'campaignBudgetAmount', 'campaignStatus', 'keywordBid', 'adGroupName', 'adGroupId', 'keywordType', 'matchType', 'targeting', 'topOfSearchImpressionShare' ] # 'startDate', 'endDate', params['groupby'] = ['targeting'] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SP_spTargeting_targetingV3", df_report[params['columns']]) print("插入完成SP_spTargeting_targetingV3") return df_report[params['columns']] def reportV3_searchTerm_spSearchTermETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "spSearchTerm" params['columns'] = [ 'adKeywordStatus', 'impressions', 'clicks', 'costPerClick', 'clickThroughRate', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'salesOtherSku7d', 'unitsSoldOtherSku7d', 'acosClicks7d', 'acosClicks14d', 'roasClicks7d', 'roasClicks14d', 'keywordId', 'keyword', 'campaignBudgetCurrencyCode', 'date', 'portfolioId', 'searchTerm', 'campaignName', 'campaignId', 'campaignBudgetType', 'campaignBudgetAmount', 'campaignStatus', 'keywordBid', 'adGroupName', 'adGroupId', 'keywordType', 'matchType', 'targeting' ] # 'startDate', 'endDate', params['groupby'] = ['searchTerm'] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SP_spSearchTerm_searchTermV3", df_report[params['columns']]) print("插入完成SP_spSearchTerm_searchTermV3") return df_report[params['columns']] def reportV3_advertiser_spAdvertisedProductETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "spAdvertisedProduct" params['columns'] = [ 'date', 'campaignName', 'campaignId', 'adGroupName', 'adGroupId', 'adId', 'portfolioId', 'impressions', 'clicks', 'costPerClick', 'clickThroughRate', 'cost', 'spend', 'campaignBudgetCurrencyCode', 'campaignBudgetAmount', 'campaignBudgetType', 'campaignStatus', 'advertisedAsin', 'advertisedSku', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'salesOtherSku7d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'unitsSoldOtherSku7d', 'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'acosClicks7d', 'acosClicks14d', 'roasClicks7d', 'roasClicks14d' ] # 'startDate', 'endDate', params['groupby'] = ['advertiser'] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SP_spAdvertisedProduct_advertiserV3", df_report[params['columns']]) print("插入完成SP_spAdvertisedProduct_advertiserV3") return df_report[params['columns']] def reportV3_asin_spPurchasedProductETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "spPurchasedProduct" params['columns'] = [ 'date', 'portfolioId', 'campaignName', 'campaignId', 'adGroupName', 'adGroupId', 'keywordId', 'keyword', 'keywordType', 'advertisedAsin', 'purchasedAsin', 'advertisedSku', 'campaignBudgetCurrencyCode', 'matchType', 'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'unitsSoldOtherSku1d', 'unitsSoldOtherSku7d', 'unitsSoldOtherSku14d', 'unitsSoldOtherSku30d', 'salesOtherSku1d', 'salesOtherSku7d', 'salesOtherSku14d', 'salesOtherSku30d', 'purchasesOtherSku1d', 'purchasesOtherSku7d', 'purchasesOtherSku14d', 'purchasesOtherSku30d', 'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d' ] # 'startDate', 'endDate', params['groupby'] = ['asin'] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SP_spPurchasedProduct_asinV3", df_report[params['columns']]) print("插入完成SP_spPurchasedProduct_asinV3") return df_report[params['columns']] class SB_ETL(SBClient, Common_ETLMethod): def campaigns_ETL(self): list_campaign_SB = list(self.iter_campaigns(**{"includeExtendedDataFields": True})) df_campaign = pd.json_normalize(list_campaign_SB) df_campaign = self.placement_segmentsplit(df_campaign, "bidding.bidAdjustmentsByPlacement") df_campaign = self.time_stamp_convert(df_campaign, ["extendedData.creationDate", "extendedData.lastUpdateDate"]) # print(df_campaign) return self.columnsName_modify(df_campaign) def adGroups_ETL(self): list_adGroup_SB = list(self.iter_adGroups(**{"includeExtendedDataFields": True})) df_adGroup_SP = pd.json_normalize(list_adGroup_SB) df_adGroup_SP = self.time_stamp_convert(df_adGroup_SP, ["extendedData.creationDate", "extendedData.lastUpdateDate"]) return self.columnsName_modify(df_adGroup_SP) def ads_ETL(self): list_adId_SB = list(self.iter_ads(**{"includeExtendedDataFields": True})) df_adId_SP = pd.json_normalize(list_adId_SB) df_adId_SP = self.time_stamp_convert(df_adId_SP, ["extendedData.creationDate", "extendedData.lastUpdateDate"]) return self.columnsName_modify(df_adId_SP) def keywords_ETL(self): list_keywords_SB = [row for _ in list(self.iter_keywords()) for row in _] df_keywords_SP = pd.json_normalize(list_keywords_SB) return self.columnsName_modify(df_keywords_SP) def targets_ETL(self): list_targets = list(self.iter_targets()) df_targets = pd.json_normalize(list_targets) # df_targets = self.TZ_Deal(df_targets, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"]) # df_targets = self.expression_split(df_targets, "resolvedExpressions") df_targets = self.id_type_trans(df_targets) df_targets['resolvedExpressions_type'] = df_targets['resolvedExpressions'].map(lambda x:self.get_keyOvalue(x,'type')) df_targets['resolvedExpressions_value'] = df_targets['resolvedExpressions'].map(lambda x:self.get_keyOvalue(x,'value')) return self.columnsName_modify(df_targets) def budget_ETL(self, campaign_ids: list): list_budget = self.get_budget(campaignIds=campaign_ids)['success'] df_budget = pd.json_normalize(list_budget) df_budget = self.TZ_Deal(df_budget, ["usageUpdatedTimestamp"]) return self.columnsName_modify(df_budget) def reportV3_purchasedAsinRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("endDate") == None: params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d") if params.get("startDate") == None: params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y-%m-%d") params['reportType'] = "sbPurchasedProduct" params['columns'] = [ 'campaignId', 'adGroupId', 'date', 'campaignBudgetCurrencyCode', 'campaignName', 'adGroupName', 'attributionType', 'purchasedAsin', 'productName', 'productCategory', 'sales14d', 'orders14d', 'unitsSold14d', 'newToBrandSales14d', 'newToBrandPurchases14d', 'newToBrandUnitsSold14d', 'newToBrandSalesPercentage14d', 'newToBrandPurchasesPercentage14d', 'newToBrandUnitsSoldPercentage14d' ] # 'startDate', 'endDate', params['groupby'] = ['purchasedAsin'] params['timeUnit'] = 'DAILY' list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'], columns=params['columns'], startDate=params['startDate'], endDate=params['endDate'], reportType=params['reportType']) # print(list_report) df_report = pd.json_normalize(list_report) df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SB_sbPurchasedProduct_asinV3", df_report[params['columns']]) print("插入完成SB_sbPurchasedProduct_asinV3") return df_report[params['columns']] def reportV2_campaignsRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = ['campaignId','campaignName', 'impressions', 'clicks', 'cost', 'attributedBrandedSearches14d','applicableBudgetRuleId','applicableBudgetRuleName', 'attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d', 'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d', 'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d', 'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget', 'campaignBudgetType','campaignRuleBasedBudget','campaignStatus', 'dpv14d','topOfSearchImpressionShare','unitsSold14d' ] list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics']) # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SB_campaignsV2", df_report[params['metrics']]) print("插入完成SB_campaignsV2") return df_report[params['metrics']] def reportV2_campaignsVideo_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU', 'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d', 'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU', 'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d', 'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus', 'currency','dpv14d','topOfSearchImpressionShare','vctr','vtr','video5SecondViewRate','video5SecondViews', 'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes', 'viewableImpressions' ] list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], creative_type='video') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SB_campaignsVideoV2", df_report[params['metrics']]) print("插入完成SB_campaignsVideoV2") # print(df_report[params['metrics']].info()) return df_report[params['metrics']] def reportV2_placementRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = ['campaignId','campaignName','impressions','clicks','cost', 'attributedBrandedSearches14d','applicableBudgetRuleId','applicableBudgetRuleName', 'attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d', 'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d', 'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d', 'attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d', 'campaignBudget','campaignBudgetType','campaignRuleBasedBudget','campaignStatus','dpv14d','unitsSold14d' ] # 'placement' # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], segment='placement') df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['placement']) conn.insert_df("AmazonReport.SB_campaignsPlacementV2", df_report[params['metrics']]) print("插入完成SB_campaignsPlacementV2") # print(df_report[params['metrics']].info()) return df_report[params['metrics']] def reportV2_placementVideo_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU', 'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d', 'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU', 'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d', 'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus', 'currency','dpv14d','vctr','vtr','video5SecondViewRate','video5SecondViews','videoCompleteViews', 'videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes','viewableImpressions', ] # 'placement' # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], segment='placement', creative_type='video') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['placement']) # print(df_report.info()) conn.insert_df("AmazonReport.SB_campaignsPlacementVideoV2", df_report[params['metrics']]) print("插入完成SB_campaignsPlacementVideoV2") return df_report[params['metrics']] def reportV2_adGroupsRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'adGroups' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','impressions', 'clicks', 'cost', 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU', 'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d', 'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU', 'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d', 'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus', 'dpv14d','unitsSold14d', ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics']) # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) # print(df_report.info()) conn.insert_df("AmazonReport.SB_adGroupsV2", df_report[params['metrics']]) print("插入完成SB_adGroupsV2") return df_report[params['metrics']] def reportV2_adGroupsVideo_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'adGroups' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU', 'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d', 'attributedOrdersNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d', 'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d', 'campaignBudget','campaignBudgetType','campaignStatus','currency','vctr','vtr','video5SecondViewRate', 'video5SecondViews','videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews', 'videoUnmutes','viewableImpressions','dpv14d' ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], creative_type='video') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) # print(df_report.info()) conn.insert_df("AmazonReport.SB_adGroupsVideoV2", df_report[params['metrics']]) print("插入完成SB_adGroupsVideoV2") return df_report[params['metrics']] def reportV2_targetsRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'targets' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions', 'clicks', 'cost', 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d', 'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d','attributedSales14d', 'attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d', 'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget', 'campaignBudgetType','campaignStatus','dpv14d','targetingExpression','targetingType', 'topOfSearchImpressionShare','unitsSold14d' ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics']) # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) # print(df_report.info()) conn.insert_df("AmazonReport.SB_targetsV2", df_report[params['metrics']]) print("插入完成SB_targetsV2") return df_report[params['metrics']] def reportV2_targetsVideo_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'targets' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU', 'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d', 'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU', 'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d', 'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus', 'currency','dpv14d','targetingExpression','targetingType','topOfSearchImpressionShare','vctr','vtr', 'video5SecondViewRate','video5SecondViews','videoCompleteViews','videoFirstQuartileViews','videoMidpointViews', 'videoThirdQuartileViews','videoUnmutes','viewableImpressions', ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], creative_type='video') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) # print(df_report.info()) conn.insert_df("AmazonReport.SB_targetsVideoV2", df_report[params['metrics']]) print("插入完成SB_targetsVideoV2") return df_report[params['metrics']] def reportV2_keywordsRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'keywords' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions', 'clicks', 'cost', 'attributedBrandedSearches14d','applicableBudgetRuleId','applicableBudgetRuleName','attributedConversions14d', 'attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d', 'attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d','attributedSales14d', 'attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d', 'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget', 'campaignBudgetType','campaignRuleBasedBudget','campaignStatus','dpv14d','keywordBid','keywordStatus', 'matchType','searchTermImpressionRank','searchTermImpressionShare','topOfSearchImpressionShare','unitsSold14d', ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics']) # print(list_campaigns_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) # print(df_report.info()) conn.insert_df("AmazonReport.SB_keywordsV2", df_report[params['metrics']]) print("插入完成SB_keywordsV2") return df_report[params['metrics']] def reportV2_keywordsVideo_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'keywords' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions', 'clicks', 'cost', 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU', 'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d', 'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU', 'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d', 'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType', 'campaignStatus','currency','dpv14d','keywordBid','keywordStatus','matchType','topOfSearchImpressionShare', 'vctr','vtr','video5SecondViewRate','video5SecondViews','videoCompleteViews','videoFirstQuartileViews', 'videoMidpointViews','videoThirdQuartileViews','videoUnmutes','viewableImpressions', ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], creative_type='video') # print(list_campaigns_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) # print(df_report.info()) conn.insert_df("AmazonReport.SB_keywordsVideoV2", df_report[params['metrics']]) print("插入完成SB_keywordsVideoV2") return df_report[params['metrics']] def reportV2_searchtermsRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'keywords' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions', 'clicks', 'cost', 'attributedConversions14d','attributedSales14d','campaignBudget','campaignBudgetType','campaignStatus','keywordBid', 'keywordStatus','matchType','searchTermImpressionRank','searchTermImpressionShare' ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], segment='query') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['query']) # print(df_report.info()) conn.insert_df("AmazonReport.SB_keywordsQueryV2", df_report[params['metrics']]) print("插入完成SB_keywordsQueryV2") return df_report[params['metrics']] def reportV2_searchtermsVideo_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'keywords' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions','clicks','cost', 'attributedConversions14d','attributedSales14d','campaignBudget','campaignBudgetType','campaignStatus', 'keywordBid','keywordStatus','matchType','vctr','vtr','video5SecondViewRate','video5SecondViews', 'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews', 'videoUnmutes','viewableImpressions', ] # 'query','currency' # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], segment='query', creative_type='video') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['query']) # print(df_report.info()) conn.insert_df("AmazonReport.SB_keywordsQueryVideoV2", df_report[params['metrics']]) print("插入完成SB_keywordsQueryVideoV2") return df_report[params['metrics']] def reportV2_adsRecord_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'ads' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','adId','impressions','clicks','cost', 'applicableBudgetRuleId','applicableBudgetRuleName','attributedBrandedSearches14d','attributedConversions14d', 'attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d', 'attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d','attributedSales14d', 'attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d', 'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d', 'campaignBudget','campaignBudgetType','campaignRuleBasedBudget','campaignStatus', 'dpv14d','unitsSold14d','vctr', ] # list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics']) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) conn.insert_df("AmazonReport.SB_adsV2", df_report[params['metrics']]) print("插入完成SB_adsV2") return df_report[params['metrics']] def reportV2_adsVideo_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'ads' if params.get('metrics') == None: params['metrics'] = [ 'campaignId','campaignName','adGroupId','adGroupName','adId', 'impressions', 'clicks', 'cost', 'applicableBudgetRuleId','applicableBudgetRuleName','attributedBrandedSearches14d', 'attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d', 'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d', 'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d', 'attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d', 'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType', 'campaignRuleBasedBudget','campaignStatus','currency','dpv14d','vctr','vtr', 'video5SecondViewRate','video5SecondViews','videoCompleteViews','videoFirstQuartileViews', 'videoMidpointViews','videoThirdQuartileViews','videoUnmutes','viewableImpressions', ] # # print(date) list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], creative_type='video') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=[]) # print(df_report.info()) conn.insert_df("AmazonReport.SB_adsVideoV2", df_report[params['metrics']]) print("插入完成SB_adsVideoV2") return df_report[params['metrics']] class SD_ETL(SDClient, Common_ETLMethod): def campaigns_ETL(self): list_campaign_SD = self.get_campaigns() df_campaign = pd.json_normalize(list_campaign_SD) df_campaign['startDate'] = df_campaign['startDate'].map( lambda x: datetime.strptime(x, "%Y%m%d").date()) # 转换为标准时间格式 df_campaign['portfolioId'] = df_campaign['portfolioId'].fillna(-1).astype("int64") # 将portfolio列为空的填充为-1 return self.columnsName_modify(df_campaign) def adGroups_ETL(self, **param): list_adGroups_SD = [row for _ in list(self.iter_adGroups(**param)) for row in _] df_adGroups_SD = pd.json_normalize(list_adGroups_SD) tactic = {"T00020": "Contextual targeting", "T00030": "Audiences targeting"} df_adGroups_SD["tactic_type"] = df_adGroups_SD['tactic'].map(tactic) # T00020、T00030解释字段 return self.columnsName_modify(df_adGroups_SD) def ads_ETL(self): list_ads_SD = [row for _ in list(self.iter_ads()) for row in _] df_ads_SD = pd.json_normalize(list_ads_SD) return self.columnsName_modify(df_ads_SD) def targets_ETL(self, **param): list_targets = [row for _ in list(self.iter_targets(**param)) for row in _] df_targets = pd.json_normalize(list_targets) # df_targets = self.expression_split(df_targets, "resolvedExpression") df_targets = self.id_type_trans(df_targets) df_targets['resolvedExpressions_type'] = df_targets['resolvedExpressions'].map(lambda x:self.get_keyOvalue(x,'type')) df_targets['resolvedExpressions_value'] = df_targets['resolvedExpressions'].map(lambda x:self.get_keyOvalue(x,'value')) return self.columnsName_modify(df_targets) def budget_ETL(self, campaignsIds: list): list_budget = self.get_budget(campaignIds=campaignsIds)['success'] df_budget = pd.json_normalize(list_budget) df_budget = self.TZ_Deal(df_budget, ["usageUpdatedTimestamp"]) return self.columnsName_modify(df_budget) campaigns_metrics = [ 'campaignId','campaignName','impressions','clicks','cost','attributedBrandedSearches14d', 'attributedConversions1d','attributedConversions1dSameSKU','attributedConversions7d', 'attributedConversions7dSameSKU','attributedConversions14d','attributedConversions14dSameSKU', 'attributedConversions30d','attributedConversions30dSameSKU','attributedDetailPageView14d', 'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU', 'attributedSales7d','attributedSales7dSameSKU','attributedSales14d', 'attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU', 'attributedSalesNewToBrand14d','attributedUnitsOrdered1d','attributedUnitsOrdered7d', 'attributedUnitsOrdered14d','attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d', 'avgImpressionsFrequency','campaignBudget','campaignStatus','cumulativeReach','costType', 'currency','vctr','vtr', 'viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d', 'viewAttributedSales14d','viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d', 'viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d', 'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes', ] def reportV2_campaignsRecord_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = self.campaigns_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020") # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) conn.insert_df("AmazonReport.SD_campaignsV2", df_report[params['metrics']]) print("插入完成SD_campaignsV2") return df_report[params['metrics']] def reportV2_campaignsRecord_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = self.campaigns_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00030") df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) conn.insert_df("AmazonReport.SD_campaignsV2", df_report[params['metrics']]) print("插入完成SD_campaignsV2") return df_report[params['metrics']] adGroups_metrics = [ 'campaignId','campaignName','adGroupId','adGroupName','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU', 'attributedConversions7d','attributedConversions7dSameSKU','attributedConversions14d', 'attributedConversions14dSameSKU','attributedConversions30d','attributedConversions30dSameSKU', 'attributedDetailPageView14d','attributedOrdersNewToBrand14d','attributedSales1d', 'attributedSales1dSameSKU','attributedSales7d','attributedSales7dSameSKU', 'attributedSales14d','attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU', 'attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d','attributedUnitsOrdered30d', 'attributedUnitsOrderedNewToBrand14d','avgImpressionsFrequency','bidOptimization','cumulativeReach', 'currency','vctr','vtr', 'viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d', 'viewAttributedSales14d','viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d', 'viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d', 'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes', ] def reportV2_adGroupsRecord_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'adGroups' if params.get('metrics') == None: params['metrics'] = self.adGroups_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020") df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) conn.insert_df("AmazonReport.SD_adGroupsV2", df_report[params['metrics']]) print("插入完成SD_adGroupsV2") return df_report[params['metrics']] def reportV2_adGroupsRecord_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'adGroups' if params.get('metrics') == None: params['metrics'] = self.adGroups_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020") df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) # print(df_report.info()) conn.insert_df("AmazonReport.SD_adGroupsV2", df_report[params['metrics']]) print("插入完成SD_adGroupsV2") return df_report[params['metrics']] productAds_metrics = [ 'campaignId','campaignName','adGroupId','adGroupName','adId','asin','sku', 'impressions', 'clicks','cost', 'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU','attributedConversions7d', 'attributedConversions7dSameSKU','attributedConversions14d','attributedConversions14dSameSKU', 'attributedConversions30d','attributedConversions30dSameSKU','attributedDetailPageView14d', 'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU','attributedSales7d', 'attributedSales7dSameSKU','attributedSales14d','attributedSales14dSameSKU','attributedSales30d', 'attributedSales30dSameSKU','attributedSalesNewToBrand14d','attributedUnitsOrdered1d','attributedUnitsOrdered7d', 'attributedUnitsOrdered14d','attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','avgImpressionsFrequency', 'cumulativeReach','currency','vctr','vtr','viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d', 'viewAttributedSales14d','viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d', 'viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d','videoCompleteViews','videoFirstQuartileViews', 'videoMidpointViews','videoThirdQuartileViews','videoUnmutes', ] def reportV2_productAds_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'productAds' if params.get('metrics') == None: params['metrics'] = self.productAds_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020") # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) conn.insert_df("AmazonReport.SD_adsV2", df_report[params['metrics']]) print("插入完成SD_adsV2") return df_report[params['metrics']] def reportV2_productAds_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'productAds' if params.get('metrics') == None: params['metrics'] = self.productAds_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00030") # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) # print(df_report.info()) conn.insert_df("AmazonReport.SD_adsV2", df_report[params['metrics']]) print("插入完成SD_adsV2") return df_report[params['metrics']] targets_metrics = [ 'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU','attributedConversions7d', 'attributedConversions7dSameSKU','attributedConversions14d','attributedConversions14dSameSKU','attributedConversions30d', 'attributedConversions30dSameSKU','attributedDetailPageView14d','attributedOrdersNewToBrand14d', 'attributedSales1d','attributedSales1dSameSKU','attributedSales7d','attributedSales7dSameSKU', 'attributedSales14d','attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU', 'attributedSalesNewToBrand14d','attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d', 'attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','currency','targetingExpression','targetingType', 'vctr','vtr','viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d','viewAttributedSales14d', 'viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d', 'viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d','videoCompleteViews', 'videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes', ] def reportV2_targets_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'targets' if params.get('metrics') == None: params['metrics'] = self.targets_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020") # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' # {"T00020":"","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) # print(df_report[params['metrics']].info()) conn.insert_df("AmazonReport.SD_targetsV2", df_report[params['metrics']]) print("插入完成SD_targetsV2") return df_report[params['metrics']] def reportV2_targets_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'targets' if params.get('metrics') == None: params['metrics'] = self.targets_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00030") # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) # print(df_report.info()) conn.insert_df("AmazonReport.SD_targetsV2", df_report[params['metrics']]) print("插入完成SD_targetsV2") return df_report[params['metrics']] asins_metrics = [ 'campaignId','campaignName','adGroupId','adGroupName','asin','sku','attributedConversions1dOtherSKU', 'attributedConversions7dOtherSKU','attributedConversions14dOtherSKU','attributedConversions30dOtherSKU', 'attributedSales1dOtherSKU','attributedSales7dOtherSKU','attributedSales14dOtherSKU','attributedSales30dOtherSKU', 'attributedUnitsOrdered1dOtherSKU','attributedUnitsOrdered7dOtherSKU','attributedUnitsOrdered14dOtherSKU', 'attributedUnitsOrdered30dOtherSKU','currency','otherAsin','viewAttributedUnitsOrdered1dOtherSKU', 'viewAttributedUnitsOrdered7dOtherSKU','viewAttributedUnitsOrdered14dOtherSKU','viewAttributedUnitsOrdered30dOtherSKU', 'viewAttributedSales1dOtherSKU','viewAttributedSales7dOtherSKU','viewAttributedSales14dOtherSKU', 'viewAttributedSales30dOtherSKU','viewAttributedConversions1dOtherSKU','viewAttributedConversions7dOtherSKU', 'viewAttributedConversions14dOtherSKU','viewAttributedConversions30dOtherSKU', ] def reportV2_asins_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'asins' if params.get('metrics') == None: params['metrics'] = self.asins_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020") # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) # print(df_report.info()) conn.insert_df("AmazonReport.SD_asinsV2", df_report[params['metrics']]) print("插入完成SD_asinsV2") return df_report[params['metrics']] def reportV2_asins_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'asins' if params.get('metrics') == None: params['metrics'] = self.asins_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00030") # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['tactic']) conn.insert_df("AmazonReport.SD_asinsV2", df_report[params['metrics']]) print("插入完成SD_asinsV2") return df_report[params['metrics']] campaigns_MT_metrics = [ 'campaignId','campaignName','impressions','clicks','cost','attributedBrandedSearches14d', 'attributedConversions1d','attributedConversions1dSameSKU', 'attributedConversions7d', 'attributedConversions7dSameSKU', 'attributedConversions14d','attributedConversions14dSameSKU', 'attributedConversions30d','attributedConversions30dSameSKU','attributedDetailPageView14d', 'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU', 'attributedSales7d', 'attributedSales7dSameSKU', 'attributedSales14d','attributedSales14dSameSKU','attributedSales30d', 'attributedSales30dSameSKU','attributedSalesNewToBrand14d', 'attributedUnitsOrdered1d', 'attributedUnitsOrdered7d', 'attributedUnitsOrdered14d', 'attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','campaignBudget', 'campaignStatus','costType','currency', 'viewImpressions','viewAttributedBrandedSearches14d', 'viewAttributedConversions14d','viewAttributedDetailPageView14d','viewAttributedSales14d', 'viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d', 'viewAttributedUnitsOrderedNewToBrand14d', ] # 'matchedTarget' def reportV2_campaign_matchedTarget_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = self.campaigns_MT_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020", segment='matchedTarget') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['matchedTarget', 'tactic']) conn.insert_df("AmazonReport.SD_campaignsMatchedTargetV2", df_report[params['metrics']]) print("插入完成SD_campaignsMatchedTargetV2") return df_report[params['metrics']] def reportV2_campaign_matchedTarget_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'campaigns' if params.get('metrics') == None: params['metrics'] = self.campaigns_MT_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00030", segment='matchedTarget') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['matchedTarget', 'tactic']) conn.insert_df("AmazonReport.SD_campaignsMatchedTargetV2", df_report[params['metrics']]) print("插入完成SD_campaignsMatchedTargetV2") return df_report[params['metrics']] adGroups_MT_metrics = [ 'campaignId','campaignName','adGroupId','adGroupName','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions1d', 'attributedConversions1dSameSKU', 'attributedConversions7d', 'attributedConversions7dSameSKU','attributedConversions14d', 'attributedConversions14dSameSKU','attributedConversions30d','attributedConversions30dSameSKU', 'attributedDetailPageView14d', 'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU', 'attributedSales7d','attributedSales7dSameSKU','attributedSales14d','attributedSales14dSameSKU','attributedSales30d', 'attributedSales30dSameSKU','attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d', 'attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','bidOptimization', 'currency','viewImpressions','viewAttributedBrandedSearches14d','viewAttributedConversions14d', 'viewAttributedDetailPageView14d','viewAttributedSales14d','viewAttributedUnitsOrdered14d', 'viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d', ] # , 'matchedTarget' def reportV2_adGroups_matchedTarget_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'adGroups' if params.get('metrics') == None: params['metrics'] = self.adGroups_MT_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020", segment='matchedTarget') # print(list_report) df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['matchedTarget', 'tactic']) conn.insert_df("AmazonReport.SD_adGroupsMatchedTargetV2", df_report[params['metrics']]) print("插入完成SD_adGroupsMatchedTargetV2") return df_report[params['metrics']] def reportV2_adGroups_matchedTarget_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'adGroups' if params.get('metrics') == None: params['metrics'] = self.adGroups_MT_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00030", segment='matchedTarget') df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['matchedTarget', 'tactic']) conn.insert_df("AmazonReport.SD_adGroupsMatchedTargetV2", df_report[params['metrics']]) print("插入完成SD_adGroupsMatchedTargetV2") return df_report[params['metrics']] targets_MT_metrics = [ 'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions','clicks','cost', 'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU', 'attributedConversions7d','attributedConversions7dSameSKU','attributedConversions14d', 'attributedConversions14dSameSKU','attributedConversions30d', 'attributedConversions30dSameSKU', 'attributedDetailPageView14d','attributedOrdersNewToBrand14d','attributedSales1d', 'attributedSales1dSameSKU','attributedSales7d','attributedSales7dSameSKU','attributedSales14d', 'attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU','attributedSalesNewToBrand14d', 'attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d','attributedUnitsOrdered30d', 'attributedUnitsOrderedNewToBrand14d','currency','targetingExpression','targetingType','viewAttributedBrandedSearches14d', 'viewAttributedConversions14d','viewAttributedDetailPageView14d','viewAttributedSales14d','viewAttributedUnitsOrdered14d', 'viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d', ] def reportV2_targets_matchedTarget_t2_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime( "%Y%m%d") params['record_type'] = 'targets' if params.get('metrics') == None: params['metrics'] = self.targets_MT_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00020", segment='matchedTarget') df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Contextual targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['matchedTarget', 'tactic']) conn.insert_df("AmazonReport.SD_targetsMatchedTargetV2", df_report[params['metrics']]) print("插入完成SD_targetsMatchedTargetV2") return df_report[params['metrics']] def reportV2_targets_matchedTarget_t3_ETL(self, conn, params={}): timeZone_ = self.timeZone() today = datetime.now(tz=pytz.timezone(timeZone_)) if params.get("date") == None: params["date"] = ( datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d") params['record_type'] = 'targets' if params.get('metrics') == None: params['metrics'] = self.targets_MT_metrics list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"], metrics=params['metrics'], tactic="T00030", segment='matchedTarget') df_report = pd.json_normalize(list_report) date = datetime.strptime(params['date'], '%Y%m%d') df_report['date'] = date df_report['tactic'] = 'Audiences targeting' # {"T00020":"Contextual targeting","T00030":"Audiences targeting"} df_report = self.type_trans(df_report, params['metrics'], timeZone_, extra_columns=['matchedTarget', 'tactic']) conn.insert_df("AmazonReport.SD_targetsMatchedTargetV2", df_report[params['metrics']]) print("插入完成SD_targetsMatchedTargetV2") return df_report[params['metrics']] if __name__ == '__main__': AWS_CREDENTIALS = { 'lwa_client_id': 'amzn1.application-oa2-client.ebd701cd07854fb38c37ee49ec4ba109', 'refresh_token': "Atzr|IwEBIL4ur8kbcwRyxVu_srprAAoTYzujnBvA6jU-0SMxkRgOhGjYJSUNGKvw24EQwJa1jG5RM76mQD2P22AKSq8qSD94LddoXGdKDO74eQVYl0RhuqOMFqdrEZpp1p4bIR6_N8VeSJDHr7UCuo8FiabkSHrkq7tsNvRP-yI-bnpQv4EayPBh7YwHVX3hYdRbhxaBvgJENgCuiEPb35Q2-Z6w6ujjiKUAK2VSbCFpENlEfcHNsjDeY7RCvFlwlCoHj1IeiNIaFTE9yXFu3aEWlExe3LzHv6PZyunEi88QJSXKSh56Um0e0eEg05rMv-VBM83cAqc5POmZnTP1vUdZO8fQv3NFLZ-xU6e1WQVxVPi5Cyqk4jYhGf1Y9t98N654y0tVvw74qNIsTrB-8bGS0Uhfe24oBEWmzObvBY3zhtT1d42myGUJv4pMTU6yPoS83zhPKm3LbUDEpBA1hvvc_09jHk7vUEAuFB-UAZzlht2C1yklzQ", 'lwa_client_secret': 'cbf0514186db4df91e04a8905f0a91b605eae4201254ced879d8bb90df4b474d', 'profile_id': "3006125408623189" } conn = Common_ETLMethod(**AWS_CREDENTIALS).clickhouse_connect() # SD ac_etl = SP_ETL(**AWS_CREDENTIALS) ls = ac_etl.reportV3_campaignPlacement_spCampaignsETL(conn,params={"startDate":"2023-10-28","endDate":"2023-10-28"}) print(ls) # print(ls.to_excel('obse11.xlsx')) # ac_etl.reportV2_campaignsRecord_t2_ETL(conn) # ac_etl.reportV2_campaignsRecord_t3_ETL(conn) # ac_etl.reportV2_adGroupsRecord_t2_ETL(conn) # ac_etl.reportV2_adGroupsRecord_t3_ETL(conn) # ac_etl.reportV2_asins_t2_ETL(conn) # ac_etl.reportV2_asins_t3_ETL(conn) # ac_etl.reportV2_productAds_t2_ETL(conn) # ac_etl.reportV2_productAds_t3_ETL(conn) # ac_etl.reportV2_targets_t2_ETL(conn) # ac_etl.reportV2_productAds_t3_ETL(conn) # ac_etl.reportV2_campaign_matchedTarget_t2_ETL(conn) # ac_etl.reportV2_campaign_matchedTarget_t3_ETL(conn) # ac_etl.reportV2_adGroups_matchedTarget_t2_ETL(conn) # ac_etl.reportV2_adGroups_matchedTarget_t3_ETL(conn) # ac_etl.reportV2_targets_matchedTarget_t2_ETL(conn) # ac_etl.reportV2_targets_matchedTarget_t3_ETL(conn) # SB # ac_etl = SB_ETL(**AWS_CREDENTIALS) # ac_etl.reportV3_purchasedAsinRecord_ETL(conn) # ac_etl.reportV2_campaignsRecord_ETL(conn) # ac_etl.reportV2_campaignsVideo_ETL(conn) # ac_etl.reportV2_adGroupsRecord_ETL(conn) # ac_etl.reportV2_adGroupsVideo_ETL(conn) # ac_etl.reportV2_adsRecord_ETL(conn) # ac_etl.reportV2_adsVideo_ETL(conn) # ac_etl.reportV2_keywordsRecord_ETL(conn) # ac_etl.reportV2_keywordsVideo_ETL(conn) # ac_etl.reportV2_placementRecord_ETL(conn) # ac_etl.reportV2_placementVideo_ETL(conn) # ac_etl.reportV2_searchtermsRecord_ETL(conn) # ac_etl.reportV2_searchtermsVideo_ETL(conn) # ac_etl.reportV2_targetsRecord_ETL(conn) # ac_etl.reportV2_targetsVideo_ETL(conn) # SP # ac_etl = SP_ETL(**AWS_CREDENTIALS) # ac_etl.reportV3_campaign_spCampaignsETL(conn) # ac_etl.reportV3_adGroup_spCampaignsETL(conn) # ac_etl.reportV3_campaignPlacement_spCampaignsETL(conn) # ac_etl.reportV3_targeting_spTargetingETL(conn) # ac_etl.reportV3_searchTerm_spSearchTermETL(conn) # ac_etl.reportV3_advertiser_spAdvertisedProductETL(conn) # ac_etl.reportV3_asin_spPurchasedProductETL(conn) conn.close() ###