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)
