def insert_dsp_partner_creatives(data): config = configs.get("db_credentials") db = database.Connection(database=config.get('database'), user=config.get('user'), password=config.get('password'), host=config.get('host')) code = data.get('code') name = data.get('name', '') size = data.get('size', '') # Get Taboola Platform ID platform_id = getPlatformId() # check if already exist then update it else insert data = db.query( "SELECT * FROM dsp_partner_creatives WHERE code = '{}'".format(code)) if (data): LOGGER.info("Updating Creative for code : {} ".format(code)) statement = "UPDATE dsp_partner_creatives set name = %s, size = '{}', updated_at = now() WHERE code = '{}'".format( size, code) db.execute(statement, name) else: LOGGER.info("Inserting Creative for code : {} ".format(code)) # Insert Flight in dsp_partner_flights statement = "INSERT INTO dsp_partner_creatives (name,size,code,id,created_at,updated_at) VALUES (%s,'{}','{}',unhex(replace(uuid(),'-','')),now(), now())".format( size, code) db.execute(statement, name) return True
def insert_partner_creatives(): 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() LOGGER.info("Inserting Partner Creative") statement = "TRUNCATE table dim_partner_creatives" db.execute(statement) statement = "INSERT into dim_partner_creatives (id, code, name, size) (SELECT c.id, c.code, c.name, c.size FROM dsp_partner_creatives c)" db.execute(statement) return True
def insert_partner_flights(): 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() LOGGER.info("Inserting Partner Flights") statement = "TRUNCATE table dim_partner_flights" db.execute(statement) statement = "INSERT INTO dim_partner_flights (dsp_partner_flight_id, dsp_partner_flight_name, 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 f.id, f.NAME, c.id, c.NAME, b.id, b.NAME, d.id, d.NAME FROM dsp_partner_flights f LEFT JOIN dsp_partner_campaigns c ON f.dsp_partner_campaign_id = c.id 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) " db.execute(statement) return True
def insert_dsp_partner_campaigns(data): config = configs.get("db_credentials") db = database.Connection(database=config.get('database'), user=config.get('user'), password=config.get('password'), host=config.get('host')) code = str(data.get('code')) name = str(data.get('name', '')) advertiser_code = data.get('advertiser_code') # Get Partner Brand Id brand_id = getDSPPartnerBrandId(advertiser_code) # Get Taboola Platform ID platform_id = getPlatformId() # check if already exist then update it else insert data = db.query( "SELECT * FROM dsp_partner_campaigns WHERE code = '{}' AND dsp_partner_brand_id = unhex('{}')" .format(code, brand_id)) if (data): LOGGER.info("Updating Campaign for code : {} ".format(code)) statement = "UPDATE dsp_partner_campaigns set name = %s, updated_at = now() WHERE code = '{}' AND dsp_partner_brand_id = unhex('{}')".format( code, brand_id) db.execute(statement, name) else: LOGGER.info("Inserting Campaign for code : {} ".format(code)) # Insert campaign in dsp_partner_campaigns statement = "INSERT INTO dsp_partner_campaigns (name,code,dsp_partner_brand_id,id,created_at,updated_at) VALUES (%s,'{}',unhex('{}'),unhex(replace(uuid(),'-','')),now(), now())".format( code, brand_id) db.execute(statement, name) campaign_id = getDSPPartnerCampaignId(code, brand_id) # Insert campaign in dsp_partner_campaign_demand_side_platforms statement = "INSERT INTO dsp_partner_campaign_demand_side_platforms (dsp_partner_campaign_id,dsp_demand_side_platform_id) VALUES (unhex('{}'),unhex('{}'))".format( campaign_id, platform_id) db.execute(statement) return True
def getPlatformId(): global platform_id if (platform_id is None): 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)) if (data): platform_id = data.get('platform_id') return platform_id
def getDSPPartnerCampaignId(code, brand_id): 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')) # Get Taboola Platform ID platform_id = getPlatformId() data = db.get( "SELECT hex(id) as campaign_id FROM dsp_partner_campaigns WHERE code = '{}' AND dsp_partner_brand_id = unhex('{}')" .format(code, brand_id)) if (data): campaign_id = data.get('campaign_id') return campaign_id
def getDSPPartnerCreativeId(code): creative_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')) # Get Taboola Platform ID platform_id = getPlatformId() data = db.get( "SELECT hex(id) as creative_id FROM dsp_partner_creatives WHERE code = '{}'" .format(code)) if (data): creative_id = data.get('creative_id') return creative_id
def getDSPPartnerBrandId(code): brand_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')) # Get Taboola Platform ID platform_id = getPlatformId() # data = db.get("SELECT hex(id) as brand_id FROM dsp_partner_brands WHERE code = '{}' AND dsp_demand_side_platform_id = unhex('{}')" .format(code, platform_id)) data = db.get( "SELECT hex(id) as brand_id FROM dsp_partner_brands WHERE code = '{}' " .format(code)) if (data): brand_id = data.get('brand_id') return brand_id
def insert_fact_platform_campaigns(data): 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_code = data.get('campaign_code') advertiser_code = data.get('advertiser_code') flight_code = data.get('flight_code') creative_code = data.get('creative_code') # Get Partner Brand Id brand_id = getDSPPartnerBrandId(advertiser_code) # Get Partner Campaign Id campaign_id = getDSPPartnerCampaignId(campaign_code, brand_id) # Get Partner Flight Id flight_id = getDSPPartnerFlightId(flight_code, campaign_id) # Get Partner Creative Id creative_id = getDSPPartnerCreativeId(creative_code) # Get Taboola Platform ID platform_id = getPlatformId() campaign_date = str( datetime.strptime(data.get('campaign_date'), '%Y-%m-%d %H:%M').date()) campaign_timestamp = str( datetime.strptime(data.get('campaign_date'), '%Y-%m-%d %H:%M').timestamp()) campaign_action_hour = str( datetime.strptime(data.get('campaign_date'), '%Y-%m-%d %H:%M').hour) # campaign_action_hour = str(data.get('action_hour')) # print("ACTION HOUR ::: {}".format(campaign_action_hour)) impressions = data.get('impressions', '') clicks = data.get('clicks', '') last_view = data.get('last_view', '') last_click = data.get('last_click', '') spend = data.get('spend', '') currency = data.get('currency', '') # check if already exist then update it else insert data = db.query( "SELECT * FROM fact_platform_campaigns WHERE unix_timestamp = '{}' AND dsp_partner_flight_id = unhex('{}') AND dsp_partner_creative_id = unhex('{}') AND dsp_demand_side_platform_id = unhex('{}')" .format(campaign_timestamp, flight_id, creative_id, platform_id)) if (data): LOGGER.info( "Updating Campaign Performace for campaign_id : {} and campaign_date = {}" .format(campaign_id, campaign_date)) statement = "UPDATE fact_platform_campaigns set impressions = '{}', clicks = '{}', last_view = '{}' , last_click = '{}', spend = '{}', currency = '{}' WHERE dsp_partner_brand_id = unhex('{}') AND dsp_partner_campaign_id = unhex('{}') AND dsp_partner_flight_id = unhex('{}') AND dsp_partner_creative_id = unhex('{}') AND dsp_demand_side_platform_id = unhex('{}') AND unix_timestamp = '{}'".format( impressions, clicks, last_view, last_click, spend, currency, brand_id, campaign_id, flight_id, creative_id, platform_id, campaign_timestamp) 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 (unix_timestamp,action_date,action_hour,impressions,clicks,last_view,last_click,spend,currency,dsp_partner_brand_id,dsp_partner_campaign_id,dsp_partner_flight_id,dsp_partner_creative_id,dsp_demand_side_platform_id) VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}',unhex('{}'),unhex('{}'),unhex('{}'),unhex('{}'),unhex('{}'))".format( campaign_timestamp, campaign_date, campaign_action_hour, impressions, clicks, last_view, last_click, spend, currency, brand_id, campaign_id, flight_id, creative_id, platform_id) # LOGGER.info("Insert Statement : {}".format(statement)) db.execute(statement) return True