def main(): client = GoogleAdsClient.load_from_env() mariadb_engine = sql_utils.create_engine(settings.MARIADB_CONFIG, db_name='output', db_type='mysql') LAG_TIME = settings.LAG_TIME # put google_campaign_report to mysql database if sql_utils.table_exists_notempty(mariadb_engine, 'output', 'google_campaign_report'): latest_date = sql_utils.get_latest_date_in_table(mariadb_engine, 'google_campaign_report') else: latest_date = datetime.today() LAG_TIME = 365 from_date = (latest_date-timedelta(days=LAG_TIME)).strftime('%Y-%m-%d') to_date = (datetime.today()+timedelta(days=1)).strftime('%Y-%m-%d') google_campaign_report = get_campaign_report(client, settings.YOUSEE_CUSTOMER_ID, time_period_query=f'BETWEEN "{from_date}" AND "{to_date}"') google_campaign_report['platform'], google_campaign_report['campaign_type'], \ google_campaign_report['brandorproduct'], google_campaign_report['campaign'] = \ get_platform_type_brandorproduct_campaign_from_naming(google_campaign_report['name']) cols = ['name', 'platform', 'campaign_type', 'brandorproduct', 'campaign', 'date', 'start_date', 'clicks', 'impressions', 'cost', 'ctr'] google_campaign_report = google_campaign_report[cols] dtype_trans = sql.get_dtype_trans(google_campaign_report) dtype_trans.update({'name': String(150)}) dtype_trans.update({'campaign': String(80)}) dtype_trans.update({'date': DateTime()}) if sql_utils.table_exists_notempty(mariadb_engine, 'output', 'google_campaign_report'): sql_utils.delete_date_entries_in_table(mariadb_engine, from_date, 'google_campaign_report') google_campaign_report.to_sql('google_campaign_report', con=mariadb_engine, dtype=dtype_trans, if_exists='append', index=False) #mariadb_engine.execute('CREATE INDEX google_campaign_report_date_IDX USING BTREE ON `output`.google_campaign_report (date);') #mariadb_engine.execute('CREATE INDEX google_campaign_report_name_IDX USING HASH ON `output`.google_campaign_report (name, platform, campaign_type, brandorproduct, campaign);') # put google_device_campaign_report to mysql database if sql_utils.table_exists_notempty(mariadb_engine, 'output', 'google_device_campaign_report'): latest_date = sql_utils.get_latest_date_in_table(mariadb_engine, 'google_device_campaign_report') else: latest_date = datetime.today() LAG_TIME = 365 from_date = (latest_date-timedelta(days=LAG_TIME)).strftime('%Y-%m-%d') to_date = (datetime.today()+timedelta(days=1)).strftime('%Y-%m-%d') google_device_campaign_report = get_campaign_report_by_device(client, settings.YOUSEE_CUSTOMER_ID, time_period_query=f'BETWEEN "{from_date}" AND "{to_date}"') google_device_campaign_report['platform'], google_device_campaign_report['campaign_type'], \ google_device_campaign_report['brandorproduct'], google_device_campaign_report['campaign'] = \ get_platform_type_brandorproduct_campaign_from_naming(google_device_campaign_report['name']) cols = ['name', 'platform', 'campaign_type', 'brandorproduct', 'campaign', 'date', 'device', 'start_date', 'clicks', 'impressions', 'cost', 'ctr'] google_device_campaign_report = google_device_campaign_report[cols] dtype_trans = sql.get_dtype_trans(google_device_campaign_report) dtype_trans.update({'name': String(150)}) dtype_trans.update({'campaign': String(80)}) dtype_trans.update({'date': DateTime()}) if sql_utils.table_exists_notempty(mariadb_engine, 'output', 'google_device_campaign_report'): sql_utils.delete_date_entries_in_table(mariadb_engine, from_date, 'google_device_campaign_report') google_device_campaign_report.to_sql('google_device_campaign_report', con=mariadb_engine, dtype=dtype_trans, if_exists='append', index=False) #mariadb_engine.execute('CREATE INDEX device_campaign_report_date_IDX USING BTREE ON `output`.google_device_campaign_report (date);') #mariadb_engine.execute('CREATE INDEX device_campaign_report_name_IDX USING HASH ON `output`.google_device_campaign_report (name, platform, campaign_type, brandorproduct, campaign);') #mariadb_engine.execute('CREATE INDEX device_campaign_report_device_IDX USING HASH ON `output`.google_device_campaign_report (device);') # put google_conversion_campaign_report to mysql database if sql_utils.table_exists_notempty(mariadb_engine, 'output', 'google_conversion_campaign_report'): latest_date = sql_utils.get_latest_date_in_table(mariadb_engine, 'google_conversion_campaign_report') else: latest_date = datetime.today() LAG_TIME = 365 from_date = (latest_date-timedelta(days=LAG_TIME)).strftime('%Y-%m-%d') to_date = (datetime.today()+timedelta(days=1)).strftime('%Y-%m-%d') google_conversion_campaign_report = get_conversion_campaign_report(client, settings.YOUSEE_CUSTOMER_ID, time_period_query=f'BETWEEN "{from_date}" AND "{to_date}"') google_conversion_campaign_report['platform'], google_conversion_campaign_report['campaign_type'], \ google_conversion_campaign_report['brandorproduct'], google_conversion_campaign_report['campaign'] = \ get_platform_type_brandorproduct_campaign_from_naming(google_conversion_campaign_report['name']) cols = ['name', 'platform', 'campaign_type', 'brandorproduct', 'campaign', 'date', 'start_date', 'conversion_action_name', 'conversion_action_category', 'conversions', 'conversions_value'] google_conversion_campaign_report = google_conversion_campaign_report[cols] dtype_trans = sql.get_dtype_trans(google_conversion_campaign_report) dtype_trans.update({'name': String(150)}) dtype_trans.update({'campaign': String(80)}) dtype_trans.update({'date': DateTime()}) mariadb_engine = sql_utils.create_engine(settings.MARIADB_CONFIG, db_name='output', db_type='mysql') if sql_utils.table_exists_notempty(mariadb_engine, 'output', 'google_conversion_campaign_report'): sql_utils.delete_date_entries_in_table(mariadb_engine, from_date, 'google_conversion_campaign_report') google_conversion_campaign_report.to_sql('google_conversion_campaign_report', con=mariadb_engine, dtype=dtype_trans, if_exists='append', index=False)