def campaign_status(campaign_id):
mydb = mysql_adactivity_save.connectDB("dev_facebook_test")
df_camp = pd.read_sql(
"SELECT * FROM campaign_target WHERE campaign_id=%s" %
(campaign_id),
con=mydb)
df_camp['charge_per_day'] = df_camp['target'] / df_camp['period']
df_camp['campaign_bid'] = df_camp['spend_cap'] / df_camp['target']
# df_temp = pd.concat( df_camp[['campaign_id', 'charge_per_day', 'budget_per_day', 'daily_budget','']])
insights = facebook_datacollector.Campaigns(
campaign_id).get_campaign_insights()
for insight in insights:
spend = insight.get("spend")
campaign_cpc = insight.get("cpc")
campaign_charge = insight.get("clicks")
impressions = insight.get("impressions")
df = pd.DataFrame({
'campaign_id': [campaign_id],
'campaign_cpc': [campaign_cpc],
'campaign_charge': [campaign_charge],
'impressions': [impressions],
'campaign_bid': [df_camp['campaign_bid'].iloc[0]],
'spend': [spend],
'daily_budget': [df_camp['budget_per_day'].iloc[0]],
'charge_per_day': [df_camp['charge_per_day'].iloc[0]],
'budget_per_day': [df_camp['budget_per_day'].iloc[0]]
})
df = df.convert_objects(convert_numeric=True)
mydb.close()
return df
def get_sorted_adset(campaign):
mydb = mysql_adactivity_save.connectDB(DATABASE)
try:
df = pd.read_sql("select * from adset_score where campaign_id=%s" %
(campaign),
con=mydb)
df = df[df.request_time.dt.date == DATE].sort_values(by=['score'],
ascending=False)
adset_list = df['adset_id']
assert adset_list, 'Empty List'
except:
df_camp = mysql_adactivity_save.get_campaign_target(campaign)
charge_type = df_camp['charge_type'].iloc[0]
adset_list = Campaigns(campaign, charge_type).get_adsets()
return adset_list
def ga_optimal_weight(campaign_id):
request_time = datetime.datetime.now().date()
mydb = mysql_adactivity_save.connectDB("dev_facebook_test")
df_weight = pd.read_sql(
"SELECT * FROM optimal_weight WHERE campaign_id=%s " % (campaign_id),
con=mydb)
df_camp = pd.read_sql(
"SELECT * FROM campaign_target WHERE campaign_id=%s " % (campaign_id),
con=mydb)
charge_type = df_camp['charge_type'].iloc[0]
adset_list = facebook_datacollector.Campaigns(campaign_id,
charge_type).get_adsets()
for adset_id in adset_list:
# print(ad_id)
df = ObjectiveFunc.adset_status(adset_id)
r = ObjectiveFunc.adset_fitness(df_weight, df)
# print('[score]', r, ad_id)
df_final = pd.DataFrame(
{
'campaign_id': campaign_id,
'adset_id': adset_id,
'score': r,
'request_time': request_time
},
index=[0])
mysql_adactivity_save.intoDB("adset_score", df_final)
# try:
# # print(ad_id)
# df = ObjectiveFunc.adset_status(ad_id)
# r = ObjectiveFunc.adset_fitness( df_weight, df )
# print('[score]', r)
# df_ad=pd.read_sql("SELECT adset_id FROM ad_insights WHERE ad_id=%s LIMIT 1" %(ad_id), con=mydb)
# adset_id = df_ad['adset_id'].iloc[0].astype(dtype=object)
# df_final = pd.DataFrame({'campaign_id':campaign_id, 'adset_id':adset_id, 'ad_id':ad_id, 'score':r, 'request_time':request_time}, index=[0])
# mysql_adactivity_save.intoDB("adset_score", df_final)
# except:
# pass
mydb.close()
return
def adset_status(adset_id):
mydb = mysql_adactivity_save.connectDB("dev_facebook_test")
df = pd.DataFrame({
'adset_id': [],
'target': [],
'impressions': [],
'bid_amount': []
})
# df_ad = pd.read_sql("SELECT * FROM ad_insights WHERE ad_id=%s ORDER BY request_time DESC LIMIT 1" %(ad_id), con=mydb)
# df_ad = df_ad.apply(pd.to_numeric)
df_adset = pd.read_sql(
"SELECT * FROM adset_insights WHERE adset_id=%s ORDER BY request_time DESC LIMIT 1"
% (adset_id),
con=mydb)
df_camp = pd.read_sql(
"SELECT * FROM campaign_target WHERE campaign_id=%s" %
(df_adset['campaign_id'].iloc[0]),
con=mydb)
# df_camp['charge_per_day'] = df_camp['target']/df_camp['campaign_days']
df_temp = pd.merge(
df_adset[[
'campaign_id', 'adset_id', 'target', 'cost_per_target',
'impressions'
]],
df_adset[['adset_id', 'spend', 'bid_amount', 'daily_budget']],
on=['adset_id'])
df_camp['campaign_daily_budget'] = df_camp['daily_budget']
df_status = pd.merge(
df_temp,
df_camp[['campaign_id', 'daily_charge', 'campaign_daily_budget']],
on=['campaign_id'])
df = pd.concat([df, df_status], ignore_index=True, sort=True)
# print(ad_id)
# print(df[['adset_id', 'charge', 'charge_cpc','bid_amount', 'impressions', 'spend']])
mydb.close()
return df
def account_status(campaign_id):
mydb = mysql_adactivity_save.connectDB("dev_facebook_test")
df_camp = pd.read_sql(
"SELECT * FROM campaign_target WHERE campaign_id=%s" %
(campaign_id),
con=mydb)
df_camp['charge_per_day'] = df_camp['target'] / df_camp['period']
df_camp['campaign_bid'] = df_camp['spend_cap'] / df_camp['target']
charge_type = df_camp['charge_type'].iloc[0]
acc_id = facebook_datacollector.Campaigns(
campaign_id, charge_type).get_account_id()
insights = facebook_datacollector.Accounts(
"act_" + str(acc_id)).get_account_insights()
if bool(insights):
spend = int(insights.get("spend"))
account_cpc = float(insights.get("cpc"))
account_charge = int(insights.get("clicks"))
impressions = int(insights.get("impressions"))
else:
spend = 0
account_cpc = 0
account_charge = 0
impressions = 0
df = pd.DataFrame({
'campaign_id': [campaign_id],
'campaign_cpc': [account_cpc],
'campaign_charge': [account_charge],
'impressions': [impressions],
'campaign_bid': [df_camp['campaign_bid'].iloc[0]],
'spend': [spend],
'daily_budget': [df_camp['daily_budget'].iloc[0]],
'charge_per_day': [df_camp['daily_charge'].iloc[0]],
'budget_per_day': [df_camp['daily_budget'].iloc[0]]
})
df = df.convert_objects(convert_numeric=True)
mydb.close()
return df