sync_amz_data/sync_amz_data/DataTransform/Data_ETL.py

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()
    ###