def do_sync(args):
LOGGER.info("Starting sync.")
global configs
configs = load_config(args.config)
config = configs.get("api_credentials")
campaign_sync_start_date = None if args.start_date is None else args.start_date
campaign_sync_end_date = None if args.start_date is None else args.end_date
access_token = get_access_token()
marketers = sync_marketers()
if (marketers):
for marketer in marketers:
merketer_id = marketer.get("id", "")
LOGGER.info("Fetching campaign reports for marketer : {}".format(
merketer_id))
reports = sync_campaign_performance(merketer_id, marketer,
campaign_sync_start_date,
campaign_sync_end_date)
# Insert partner dim_partner_campaigns
insert_partner_campaigns()
def insert_marketer(marketer):
config = configs.get("db_credentials")
db = database.Connection(database=config.get('database'),
user=config.get('user'),
password=config.get('password'),
host=config.get('host'))
marketer_id = marketer.get('id')
name = marketer.get('name', '')
# Get Outbrain Platform ID
platform_id = getPlatformId()
# check if already exist then update it else insert
data = db.query(
"SELECT * FROM dsp_partner_brands WHERE code = '{}' ".format(
marketer_id))
if (data):
LOGGER.info("Updating marketer_id : {} ".format(marketer_id))
statement = "UPDATE dsp_partner_brands set name = '{}', updated_at = now() WHERE code = '{}'".format(
name, marketer_id)
db.execute(statement)
else:
LOGGER.info("Inserting marketer_id : {} ".format(marketer_id))
statement = "INSERT INTO dsp_partner_brands (name,code,dsp_demand_side_platform_id,id,created_at,updated_at) VALUES ('{}','{}',unhex('{}'),unhex(replace(uuid(),'-','')),now(), now())".format(
name, marketer_id, platform_id)
db.execute(statement)
return True
def insert_partner_campaigns():
config = configs.get("db_credentials")
db = database.Connection(database=config.get('database'),
user=config.get('user'),
password=config.get('password'),
host=config.get('host'))
platform_id = getPlatformId()
taboola_platform_id = getTaboolaPlatformId()
LOGGER.info("Inserting Partner Campaigns")
statement = "TRUNCATE table dim_partner_campaigns"
db.execute(statement)
statement = "INSERT INTO dim_partner_campaigns (dsp_partner_campaign_id, dsp_partner_campaign_name, dsp_partner_brand_id, dsp_partner_brand_name, dsp_demand_side_platform_id, dsp_demand_side_platform_name) (SELECT c.id, c.NAME, b.id, b.NAME, d.id, d.NAME FROM dsp_partner_campaigns c LEFT JOIN dsp_partner_brands b ON c.dsp_partner_brand_id = b.id LEFT JOIN dsp_partner_campaign_demand_side_platforms cd ON c.id = cd.dsp_partner_campaign_id LEFT JOIN dsp_demand_side_platforms d ON cd.dsp_demand_side_platform_id = d.id WHERE cd.dsp_demand_side_platform_id = unhex('{}'))".format(
platform_id)
db.execute(statement)
statement = "INSERT INTO dim_partner_campaigns (dsp_partner_campaign_id, dsp_partner_campaign_name, dsp_partner_brand_id, dsp_partner_brand_name, dsp_demand_side_platform_id, dsp_demand_side_platform_name) (SELECT c.id, c.NAME, b.id, b.NAME, d.id, d.NAME FROM dsp_partner_campaigns c LEFT JOIN dsp_partner_brands b ON c.dsp_partner_brand_id = b.id LEFT JOIN dsp_partner_campaign_demand_side_platforms cd ON c.id = cd.dsp_partner_campaign_id LEFT JOIN dsp_demand_side_platforms d ON cd.dsp_demand_side_platform_id = d.id WHERE cd.dsp_demand_side_platform_id = unhex('{}'))".format(
taboola_platform_id)
db.execute(statement)
return True
def get_access_token():
global access_token
token_days = None
config_api = configs.get("api_credentials")
access_token_day_limit = int(config_api.get('access_token_day_limit',
"30"))
if (access_token is None):
LOGGER.info(
"Reading access_token from Database & if its generation date is < 30 days then generate new"
)
config_db = configs.get("db_credentials")
db = database.Connection(database=config_db.get('database'),
user=config_db.get('user'),
password=config_db.get('password'),
host=config_db.get('host'))
data = db.get(
"SELECT access_token, TIMESTAMPDIFF(DAY, token_created_at, CURDATE()) as token_days FROM dsp_demand_side_platforms WHERE name = '{}' "
.format(PLATFORM_NAME))
if (data):
access_token = data.get('access_token')
token_days = data.get('token_days')
if (token_days is None):
token_days = access_token_day_limit
else:
token_days = int(token_days)
if (access_token is None):
access_token = ""
if (access_token == "" or token_days >= access_token_day_limit):
LOGGER.info("Generating new token since it get expired")
access_token = generate_access_token(
config_api.get('username'), config_api.get('password'))
if (access_token is not None):
LOGGER.info("Save access token into database")
statement = "UPDATE dsp_demand_side_platforms set access_token = '{}', token_created_at=now() WHERE name = '{}'".format(
access_token, PLATFORM_NAME)
db.execute(statement)
return access_token
def getTaboolaPlatformId():
LOGGER.info("Reading platform_id from Database")
config = configs.get("db_credentials")
db = database.Connection(database=config.get('database'),
user=config.get('user'),
password=config.get('password'),
host=config.get('host'))
data = db.get(
"SELECT hex(id) as platform_id FROM dsp_demand_side_platforms WHERE name = '{}' "
.format(PLATFORM_NAME_TABOOLA))
if (data):
platform_id = data.get('platform_id')
return platform_id
def getDSPPartnerCampaignId(code):
campaign_id = ''
config = configs.get("db_credentials")
db = database.Connection(database=config.get('database'),
user=config.get('user'),
password=config.get('password'),
host=config.get('host'))
data = db.get(
"SELECT hex(id) as campaign_id FROM dsp_partner_campaigns WHERE code = '{}' "
.format(code))
if (data):
campaign_id = data.get('campaign_id')
return campaign_id
def insert_campaign_performance(campaign_performance):
config = configs.get("db_credentials")
db = database.Connection(database=config.get('database'),
user=config.get('user'),
password=config.get('password'),
host=config.get('host'))
campaign_id = str(campaign_performance.get('campaign_id'))
campaign_date = str(
datetime.strptime(campaign_performance.get('campaign_date'),
'%Y-%m-%d').date())
campaign_timestamp = str(
datetime.strptime(campaign_performance.get('campaign_date'),
'%Y-%m-%d').timestamp())
campaign_action_hour = str(
datetime.strptime(campaign_performance.get('campaign_date'),
'%Y-%m-%d').time())
impressions = campaign_performance.get('impressions', '')
clicks = campaign_performance.get('clicks', '')
last_click = campaign_performance.get('last_click', '')
spend = campaign_performance.get('spend', '')
dsp_brand_id = ''
dsp_campaign_id = ''
dsp_campaign_data = db.get(
"SELECT hex(id) as campaign_id, hex(dsp_partner_brand_id) as brand_id FROM dsp_partner_campaigns WHERE code = '{}' "
.format(campaign_id))
if (dsp_campaign_data):
dsp_campaign_id = dsp_campaign_data.get('campaign_id')
dsp_brand_id = dsp_campaign_data.get('brand_id')
platform_id = getPlatformId()
# check if already exist then update it else insert
data = db.query(
"SELECT * FROM fact_platform_campaigns_taboola WHERE dsp_partner_campaign_id = unhex('{}') AND action_date = '{}'"
.format(dsp_campaign_id, campaign_date))
if (data):
LOGGER.info(
"Updating Campaign Performace for campaign_id : {} and campaign_date = {}"
.format(campaign_id, campaign_date))
statement = "UPDATE fact_platform_campaigns_taboola set impressions = '{}', clicks = '{}', last_click = '{}', spend = '{}' WHERE dsp_partner_campaign_id = unhex('{}') AND action_date = '{}'".format(
impressions, clicks, last_click, spend, dsp_campaign_id,
campaign_date)
db.execute(statement)
else:
LOGGER.info(
"Inserting Campaign Performace for campaign_id : {} and campaign_date = {}"
.format(campaign_id, campaign_date))
statement = "INSERT INTO fact_platform_campaigns_taboola (unix_timestamp,action_date,action_hour,impressions,clicks,last_click,spend,dsp_partner_brand_id,dsp_partner_campaign_id,dsp_demand_side_platform_id) VALUES ('{}','{}','0', '{}','{}','{}','{}',unhex('{}'),unhex('{}'),unhex('{}'))".format(
campaign_timestamp, campaign_date, impressions, clicks, last_click,
spend, dsp_brand_id, dsp_campaign_id, platform_id)
# LOGGER.info("Insert Statement : {}".format(statement))
db.execute(statement)
return True
