import time import pandas as pd from retry import retry from sync_amz_data.settings import LOG_CONF import logging.config logging.config.dictConfig(LOG_CONF) from sync_amz_data.public.amz_ad_client import shop_infos from sync_amz_data.DataTransform.Data_ETL import Common_ETLMethod,SP_ETL,SB_ETL,SD_ETL from apscheduler.schedulers.blocking import BlockingScheduler from datetime import datetime,timedelta def pre_func(AWS_CREDENTIALS,date_): conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report = SB_ETL(**AWS_CREDENTIALS) try: sb_report.reportV3_campaign_sbCampaigns_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_campaign_sbCampaigns_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV3_adGroup_sbAdGroup_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_adGroup_sbAdGroup_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV3_sbCampaignPlacement_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_sbCampaignPlacement_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV3_sbTargeting_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_sbTargeting_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV3_sbSearchTerm_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_sbSearchTerm_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV3_sbAds_ETL(conn, params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_sbAds_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV3_purchasedAsinRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_purchasedAsinRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV3_purchasedAsinRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV3_purchasedAsinRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_campaignsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_campaignsRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_campaignsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_campaignsVideo_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_adGroupsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_adGroupsRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_adGroupsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_adGroupsVideo_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_adsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_adsRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_adsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_adsVideo_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_keywordsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_keywordsRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_keywordsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_keywordsVideo_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_placementRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_placementRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_placementVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_placementVideo_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_searchtermsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_searchtermsRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_searchtermsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_searchtermsVideo_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_targetsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_targetsRecord_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sb_report.reportV2_targetsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() sb_report.reportV2_targetsVideo_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) conn.close() time.sleep(5) conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report = SP_ETL(**AWS_CREDENTIALS) try: sp_report.reportV3_campaign_spCampaignsETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report.reportV3_campaign_spCampaignsETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sp_report.reportV3_adGroup_spCampaignsETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report.reportV3_adGroup_spCampaignsETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sp_report.reportV3_campaignPlacement_spCampaignsETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report.reportV3_campaignPlacement_spCampaignsETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sp_report.reportV3_targeting_spTargetingETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report.reportV3_targeting_spTargetingETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sp_report.reportV3_searchTerm_spSearchTermETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report.reportV3_searchTerm_spSearchTermETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sp_report.reportV3_advertiser_spAdvertisedProductETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report.reportV3_advertiser_spAdvertisedProductETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sp_report.reportV3_asin_spPurchasedProductETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() sp_report.reportV3_asin_spPurchasedProductETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) conn.close() time.sleep(5) conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report = SD_ETL(**AWS_CREDENTIALS) try: sd_report.reportV3_campaign_sdCampaigns_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_campaign_sdCampaigns_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sd_report.reportV3_campaignMT_sdCampaigns_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_campaignMT_sdCampaigns_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sd_report.reportV3_adgroup_sdAdGroup_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_adgroup_sdAdGroup_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sd_report.reportV3_adgroupMT_sdAdGroup_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_adgroupMT_sdAdGroup_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sd_report.reportV3_targeting_sdTargeting_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_targeting_sdTargeting_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sd_report.reportV3_targetingMT_sdTargeting_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_targetingMT_sdTargeting_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sd_report.reportV3_asin_sdPurchasedProduct_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_asin_sdPurchasedProduct_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) try: sd_report.reportV3_advertiser_sdAdvertisedProduct_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")}) except: conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect() sd_report.reportV3_advertiser_sdAdvertisedProduct_ETL(conn, params={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) # sd_report.reportV2_campaignsRecord_t2_ETL(conn,params=para) # sd_report.reportV2_campaignsRecord_t3_ETL(conn,params=para) # sd_report.reportV2_adGroupsRecord_t2_ETL(conn,params=para) # sd_report.reportV2_adGroupsRecord_t3_ETL(conn,params=para) # sd_report.reportV2_asins_t2_ETL(conn,params=para) # sd_report.reportV2_asins_t3_ETL(conn,params=para) # sd_report.reportV2_productAds_t2_ETL(conn,params=para) # sd_report.reportV2_productAds_t3_ETL(conn,params=para) # sd_report.reportV2_targets_t2_ETL(conn,params=para) # sd_report.reportV2_targets_t3_ETL(conn,params=para) # sd_report.reportV2_campaign_matchedTarget_t2_ETL(conn,params=para) # sd_report.reportV2_campaign_matchedTarget_t3_ETL(conn,params=para) # sd_report.reportV2_adGroups_matchedTarget_t2_ETL(conn,params=para) # sd_report.reportV2_adGroups_matchedTarget_t3_ETL(conn,params=para) # sd_report.reportV2_targets_matchedTarget_t2_ETL(conn,params=para) # sd_report.reportV2_targets_matchedTarget_t3_ETL(conn,params=para) conn.close() time.sleep(5) def amz_report(AWS_CREDENTIALS,para=None): count = 1 list_date = [((datetime.today()+timedelta(days=i)).date()).isoformat() for i in range(-2,-20,-1)] conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect() # conn.command('') if para is not None: list_date = [para['startDate']] # print(list_date) delete_list = [ 'SB_sbAdGroup_adGroupV3', 'SB_sbAds_adsV3', 'SB_sbCampaigns_campaignV3', 'SB_sbCampaigns_placementV3', 'SB_sbPurchasedProduct_asinV3', 'SB_sbSearchTerm_searchTermV3', 'SB_sbTargeting_targetingV3', 'SD_advertiser_sdAdvertisedProductV3', 'SD_asin_sdPurchasedProductV3', 'SD_sdAdGroup_adGroupMatchedTargetV3', 'SD_sdAdGroup_adGroupV3', 'SD_sdCampaigns_campaignMatchedTargetV3', 'SD_sdCampaigns_campaignV3', 'SD_targeting_sdTargetingMatchedTargetV3', 'SD_targeting_sdTargetingV3', 'SP_spAdvertisedProduct_advertiserV3', 'SP_spCampaigns_adGroupV3', 'SP_spCampaigns_campaignV3', 'SP_spCampaigns_placementV3', 'SP_spPurchasedProduct_asinV3', 'SP_spSearchTerm_searchTermV3', 'SP_spTargeting_targetingV3'] for date_ in list_date: print(date_) print(date_.replace("-", "")) # amz_report(conn, AWS_CREDENTIALS, para={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")}) if count==1: for i in delete_list: conn.command(f"delete from AmazonReport.{i} where date='{list_date[-2]}' and profileId='{AWS_CREDENTIALS['profile_id']}'") count+=1 try: refresh_token = shop_infos(AWS_CREDENTIALS['profile_id'])['refresh_token'] AWS_CREDENTIALS['refresh_token'] = refresh_token pre_func(AWS_CREDENTIALS,date_) except Exception as e: print(e) pre_func(AWS_CREDENTIALS,date_) try: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() date_list = [] for table_name in delete_list: try: locals()[table_name] = conn.query_df( f"select date,SUM(impressions) as impressions from AmazonReport.`{table_name}` group by date,profileId") except: try: locals()[table_name] = conn.query_df( f"select date,SUM(salesOtherSku1d) as salesOtherSku1d from AmazonReport.`{table_name}` group by date,profileId") except: try: locals()[table_name] = conn.query_df( f"select date,SUM(salesBrandHalo) as salesBrandHalo from AmazonReport.`{table_name}` group by date,profileId") except: locals()[table_name] = conn.query_df( f"select date,SUM(sales14d) as sales14d from AmazonReport.`{table_name}` group by date,profileId") locals()[table_name].columns = ['date', 'refer_'] locals()[table_name]['refer_'] = locals()[table_name]['refer_'].astype('float64') todayMinus2 = (datetime.today().date() + timedelta(days=-2)) reback_21days = (todayMinus2 + timedelta(days=-23)).isoformat() df = pd.date_range(start=reback_21days, end=todayMinus2.isoformat(), freq='1d') df1 = pd.DataFrame(df, columns=['date_']) merge_df = df1.merge(locals()[table_name], left_on=['date_'], right_on=['date'], how='left') rel = merge_df.query("date_!=date or refer_<1 or refer_.isna()")['date_'].map( lambda x: x.date().isoformat()).tolist() print(rel,date_list) if len(rel)>0: for date in rel: date_list.extend([date]) conn.command( f"delete from AmazonReport.`{table_name}` where date='{date}' and profileId='{AWS_CREDENTIALS['profile_id']}'") if len(date_list)>0: print(list(set(date_list))) for date_retry in list(set(date_list)): try: pre_func(AWS_CREDENTIALS, date_retry) except: pre_func(AWS_CREDENTIALS, date_retry) except: conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect() date_list = [] for table_name in delete_list: try: locals()[table_name] = conn.query_df( f"select date,SUM(impressions) as impressions from AmazonReport.`{table_name}` group by date,profileId") except: try: locals()[table_name] = conn.query_df( f"select date,SUM(salesOtherSku1d) as salesOtherSku1d from AmazonReport.`{table_name}` group by date,profileId") except: try: locals()[table_name] = conn.query_df( f"select date,SUM(salesBrandHalo) as salesBrandHalo from AmazonReport.`{table_name}` group by date,profileId") except: locals()[table_name] = conn.query_df( f"select date,SUM(sales14d) as sales14d from AmazonReport.`{table_name}` group by date,profileId") locals()[table_name].columns = ['date', 'refer_'] locals()[table_name]['refer_'] = locals()[table_name]['refer_'].astype('float64') todayMinus2 = (datetime.today().date() + timedelta(days=-2)) reback_21days = (todayMinus2 + timedelta(days=-23)).isoformat() df = pd.date_range(start=reback_21days, end=todayMinus2.isoformat(), freq='1d') df1 = pd.DataFrame(df, columns=['date_']) merge_df = df1.merge(locals()[table_name], left_on=['date_'], right_on=['date'], how='left') rel = merge_df.query("date_!=date or refer_<1 or refer_.isna()")['date_'].map( lambda x: x.date().isoformat()).tolist() print(rel, date_list) if len(rel) > 0: for date in rel: date_list.extend([date]) conn.command( f"delete from AmazonReport.`{table_name}` where date='{date}' and profileId='{AWS_CREDENTIALS['profile_id']}'") if len(date_list) > 0: print(list(set(date_list))) for date_retry in list(set(date_list)): try: pre_func(AWS_CREDENTIALS, date_retry) except: pre_func(AWS_CREDENTIALS, date_retry) if __name__ == '__main__': # AccountTask("3006125408623189").do({"record": "portfolios"}) 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" } try: refresh_token = shop_infos(AWS_CREDENTIALS['profile_id'])['refresh_token'] AWS_CREDENTIALS['refresh_token'] = refresh_token except Exception as e: print(e) timezone_ = Common_ETLMethod(**AWS_CREDENTIALS).timeZone() print(timezone_) # amz_report(AWS_CREDENTIALS) sched = BlockingScheduler() sched.add_job(amz_report,'cron',hour=17,minute=0,second=0,timezone=timezone_, args=(AWS_CREDENTIALS, ))#,params={"startDate":"2023-11-04","endDate":"2023-11-04","date":"20231104"} sched.start()