def __init__(self, account_id):
self.account_id = account_id
self.local_dates = LocalDates(account_id)
self.budget_commander = BudgetCommander(account_id)
self.budget = self.budget_commander.budget
self.cost = self.budget_commander.this_month_spend
self.main()
def __init__(self, account_id):
self.account_id = account_id
self.budget_commander = BudgetCommander(account_id)
self.local_dates = LocalDates(account_id)
if not self.budget_commander.user_settings['emergency_stop']:
Log("info", "Emergency stop is disabled.", "", self.account_id)
return
self.budget = self.budget_commander.budget
if self.budget_commander.this_month_spend >= self.budget:
Log("info", "this month spend (%s) is over this month's budget (%s). Exiting." %(self.budget_commander.this_month_spend, self.budget), "", self.account_id)
return
self.costs = GetCostFromApi(account_id)
self.today_cost = self.costs.today_cost
self.day_budget_percentage = self.costs.day_budget_percentage
self.day_limit = self.getDayLimit()
self.main()
def __init__(self, account_id):
self.account_id = account_id
self.envvars = (Settings()).getEnv()
self.budget_commander = BudgetCommander(account_id)
self.user_settings = self.budget_commander.user_settings
class EmergencyStop(object):
"""
* Will run hourly
* Decides whether today's spend has spiked enough to warrant pausing campaigns
* Re-enables campaigns if spend is under the limit
"""
def __init__(self, account_id):
self.account_id = account_id
self.budget_commander = BudgetCommander(account_id)
self.local_dates = LocalDates(account_id)
if not self.budget_commander.user_settings['emergency_stop']:
Log("info", "Emergency stop is disabled.", "", self.account_id)
return
self.budget = self.budget_commander.budget
if self.budget_commander.this_month_spend >= self.budget:
Log("info", "this month spend (%s) is over this month's budget (%s). Exiting." %(self.budget_commander.this_month_spend, self.budget), "", self.account_id)
return
self.costs = GetCostFromApi(account_id)
self.today_cost = self.costs.today_cost
self.day_budget_percentage = self.costs.day_budget_percentage
self.day_limit = self.getDayLimit()
self.main()
def main(self):
if not self.budget or self.budget==0:
Log("info", "No budget set, cannot run emergency stop.", "", self.account_id)
return
Log("info", "today_cost: %s, day_limit: %s, budget: %s" %(self.today_cost, self.day_limit, self.budget), "", self.account_id)
campaigns_are_enabled_day = self.budget_commander.campaignsAreEnabledDay()
spend_is_over_emergency_limit = self.spendIsOverEmergencyLimit()
if spend_is_over_emergency_limit and campaigns_are_enabled_day:
(PauseEnableCampaigns(self.account_id)).pauseForToday()
self.sendEmail()
return
campaigns_are_paused_day = self.budget_commander.campaignsArePausedDay()
spend_is_under_emergency_limit = self.spendIsUnderEmergencyLimit()
if spend_is_under_emergency_limit and campaigns_are_paused_day:
(PauseEnableCampaigns(self.account_id)).enableForToday()
return
Log("info", "Emergency stop: no actions", "", self.account_id)
def spendIsOverEmergencyLimit(self):
if self.today_cost > self.day_limit:
return True
return False
def spendIsUnderEmergencyLimit(self):
if self.today_cost <= self.day_limit:
return True
return False
def getDayLimit(self):
"""Get the day limit
* The day budget based on day of the week phasing
* Then multiply the phasing based on forecast Vs budget
"""
if self.local_dates.is_first_of_month:
vs_budget_multiplier = 1
else:
forecast = (self.budget_commander.this_month_spend/(self.local_dates.today.date().day-1))*self.local_dates.days_in_this_month
vs_budget_multiplier = self.get_vs_budget_multiplier(self.budget_commander.this_month_spend, forecast)
day_limit = self.budget * (self.day_budget_percentage*vs_budget_multiplier)
minimum = self.budget/30.4
if day_limit < minimum:
day_limit = minimum
return day_limit
def get_vs_budget_multiplier(self, this_month_spend, forecast):
if this_month_spend >= self.budget:
return 0
if forecast==0:
return 1
if self.budget==0:
return 1
vs_budget = self.budget/float(forecast)
vs_budget_multiplier = vs_budget
if vs_budget_multiplier > 3:
vs_budget_multiplier = 3
if vs_budget_multiplier < 0:
vs_budget_multiplier = 0
return vs_budget_multiplier
def getHtmlContent(self):
email_html_template = "budget_commander_emergency_stop_paused.html"
template_path = os.path.abspath(os.path.join(Settings().python_dir,"email_templates", email_html_template))
template = Template(open(template_path).read())
html_content = template.render(
username=self.budget_commander.username,
account_name=self.budget_commander.name,
account_id=self.budget_commander.account_id,
google_account_id=self.budget_commander.google_id,
day_limit=round(self.day_limit, 2),
today_cost=round(self.today_cost, 2),
currency_symbol=self.budget_commander.currency_symbol,
)
return html_content
def sendEmail(self):
subject = "Account '%s' (%s) | All Campaigns were paused for the rest of the day" % (self.budget_commander.name, self.budget_commander.google_id)
html_content = self.getHtmlContent()
email_addresses = self.budget_commander.getEmailAddresses()
Log("info", "Sending email(s)", "%s - send to: %s" % (subject, ",".join(email_addresses),), self.account_id)
assert len(email_addresses) > 0
for email_address in email_addresses:
# print email_address
Email.send(("*****@*****.**", "AdEvolver Budget Commander"), str(email_address), subject, html_content)
class MonthlyStop(object):
"""
* Runs daily
* Pauses campaigns if spend is over budget
* Re-enables campaigns if spend is under the limit (e.g. new month)
"""
def __init__(self, account_id):
self.account_id = account_id
self.local_dates = LocalDates(account_id)
self.budget_commander = BudgetCommander(account_id)
self.budget = self.budget_commander.budget
self.cost = self.budget_commander.this_month_spend
self.main()
def main(self):
self.store_excess_budget()
if not self.budget_commander.user_settings["pause_campaigns"]:
Log("info", "pause_campaigns is disabled", '', self.account_id)
return
campaigns_are_enabled_month = self.budget_commander.campaignsAreEnabledMonth(
)
spend_is_over_budget = self.spendIsOverBudget()
if spend_is_over_budget and campaigns_are_enabled_month:
(PauseEnableCampaigns(self.account_id)).pauseForMonth()
Log(
"info",
"Budget commander monthly stop: campaigns paused for the month",
"", self.account_id)
self.sendEmail('Paused')
return
campaigns_are_paused_month = self.budget_commander.campaignsArePausedMonth(
)
spend_is_under_budget = self.spendIsUnderBudget()
if spend_is_under_budget and campaigns_are_paused_month and self.budget_commander.user_settings[
"enable_campaigns"]:
(PauseEnableCampaigns(self.account_id)).enableForMonth()
Log(
"info",
"Budget commander monthly stop: campaigns enabled for the month",
"", self.account_id)
self.sendEmail('Enabled')
return
Log("info", "Budget commander monthly stop: no actions", "",
self.account_id)
def getHtmlContent(self, new_status):
if new_status == 'Paused':
email_html_template = "budget_commander_monthly_campaign_status_update_paused.html"
if new_status == 'Enabled':
email_html_template = "budget_commander_monthly_campaign_status_update_enabled.html"
template_path = os.path.abspath(
os.path.join(Settings().python_dir, "email_templates",
email_html_template))
template = Template(open(template_path).read())
html_content = template.render(
username=self.budget_commander.username,
account_name=self.budget_commander.name,
account_id=self.budget_commander.account_id,
google_account_id=self.budget_commander.google_id,
budget=float(self.budget),
spend=float(self.budget_commander.this_month_spend),
currency_symbol=self.budget_commander.currency_symbol,
new_status=new_status)
return html_content
def sendEmail(self, new_status):
subject = "Account '%s' (%s) | All Campaigns were %s" % (
self.budget_commander.name, self.budget_commander.google_id,
new_status)
html_content = self.getHtmlContent(new_status)
email_addresses = self.budget_commander.getEmailAddresses()
Log("info", "Sending email(s)", "%s - send to: %s" % (
subject,
",".join(email_addresses),
), self.account_id)
assert len(email_addresses) > 0
for email_address in email_addresses:
# print email_address
Email.send(("*****@*****.**", "AdEvolver Budget Commander"),
str(email_address), subject, html_content)
def store_excess_budget(self):
"""Only run on the 1st of the month
* - Take the budget
* - Take away last month's spend
* - Any remaining budget is stored as the excess
"""
def update_excess_budget(excess_budget):
Log('info', 'Storing excess budget',
"excess_budget: %s" % (excess_budget), self.account_id)
Database().setValue('budget_commander', 'excess_budget',
excess_budget,
'where account_id = "%s"' % (self.account_id))
if not self.budget_commander.user_settings['rollover_spend']:
Log('info', 'rollover_spend is disabled. Setting excess to 0', '',
self.account_id)
update_excess_budget(0)
return
if not self.local_dates.is_first_of_month:
return
if self.budget_commander.budget_group_id: #no rollover for budget groups
return
remaining = float(self.budget) - float(
self.budget_commander.last_month_spend)
if remaining < 0:
return 0
update_excess_budget(remaining)
def spendIsOverBudget(self):
if self.cost > self.budget:
return True
return False
def spendIsUnderBudget(self):
if self.cost <= self.budget:
return True
return False
def __init__(self, account_id):
BudgetCommander.__init__(self, account_id)
self.account_id = account_id
class ControlSpend:
"""If an account is forecasting over budget reduce bids
Note if pause_campaigns is enabled and the account is over budget don't update bids"""
def __init__(self, account_id):
self.account_id = account_id
self.local_dates = LocalDates(account_id)
self.envvars = (Settings()).getEnv()
self.budget_commander = BudgetCommander(account_id)
self.user_settings = self.budget_commander.user_settings
def main(self):
print("running spend controller")
self.revertToOriginalBids()
if self.user_settings["control_spend"]:
return self.controlSpend()
def controlSpend(self):
self.storeOriginalBids()
self.getTotalSpendForecast()
Log('info', "Forecast: %s" % (self.total_spend_forecast, ), '',
self.account_id)
Log(
'info', "Spend Vs limit: %s" %
(self.getForecastOverBudgetPercentage() * 100), '',
self.account_id)
if self.forecastIsOverLimit() and self.spendIsUnderBudget():
Log('info', "Reducing bids...", '', self.account_id)
df = self.reduceBids()
self.updateBids(df) # push to mutations queue
self.updateKeywordsTable(
df) # reflect the new bids in the keywords table
return df
def storeOriginalBids(self):
"""Store original bids so we can revert back tomorrow"""
query = "UPDATE keywords SET original_cpc_bid = cpc_bid"
Database().createEngine().execute(query)
def revertToOriginalBids(self):
"""Before the update bids we'll store the originals
Change back to the original bids before we re-update them (if we do)
"""
if not self.user_settings["control_spend"]:
return
df = self.getKeywordsOriginalBidsDataframe()
df["new_bid"] = df["original_cpc_bid"]
Log("info", "Reverting back %s bids" % (df.shape[0]), "",
self.account_id)
self.updateBids(df)
keywords_query = "select original_cpc_bid, id from keywords where original_cpc_bid > cpc_bid and account_id = '%s'" % (
self.account_id)
engine = Database().createEngine()
df = pd.read_sql(keywords_query, engine)
for i, row in df.iterrows():
update_query = "update keywords set cpc_bid = %s where id = '%s'" % (
row["original_cpc_bid"], row["id"])
engine.execute(update_query)
def getTotalSpendForecast(self):
seven_day_spend = self.budget_commander.getSevenDaySpend()
self.this_month_spend = self.budget_commander.getThisMonthSpend()
self.total_spend_forecast = float(
(self.this_month_spend) + ((seven_day_spend) / 7) *
self.local_dates.days_remaining_in_this_month)
def getForecastOverBudgetPercentage(self):
self.remaining_budget = (
self.budget_commander.budget / self.local_dates.days_in_this_month
) * self.local_dates.days_remaining_in_this_month
return (self.total_spend_forecast -
self.budget_commander.budget) / self.budget_commander.budget
def forecastIsOverLimit(self):
if self.getForecastOverBudgetPercentage() * 100 > 5:
return True
return False
def spendIsUnderBudget(self):
"""Is the current total spend for the month under the budget"""
if self.this_month_spend < self.budget_commander.budget:
return True
return False
def reduceBids(self):
"""New bid decision making
Returns a df with the new bids"""
df = self.getKeywordsDataframe()
if functions.dfIsEmpty(df):
Log('info', "no keywords found. Can't change bids", '',
self.account_id)
return
remaining_spend_forecast = self.total_spend_forecast - float(
self.this_month_spend)
spend_vs_remaining_budget_percentage = self.remaining_budget / remaining_spend_forecast
def updateBid(cpc_bid, reduction_percentage, min_bid=0.1):
"""Df lambda function
Reduce bid by percentage.
Accepts reduction_percentage as whole number e.g. 98.
Checks a min bid limit (optional)
"""
try:
cpc_bid = float(cpc_bid)
cpc_bid = cpc_bid * ((100 - reduction_percentage) / 100)
if cpc_bid < min_bid:
cpc_bid = min_bid
return cpc_bid
except ValueError:
return cpc_bid
def updateForecast(row):
"""Df lambda function"""
try:
reduction = row["new_bid"] / row["cpc_bid"]
except TypeError:
return float(row["cpc_bid"])
return float(((row["cost"] * reduction) / 7) *
self.local_dates.days_remaining_in_this_month)
start_reduction = 10 - int(spend_vs_remaining_budget_percentage * 10)
for i in range(start_reduction, 10):
reduction_percentage = i * 5
# print reduction_percentage
df["new_bid"] = df["cpc_bid"].apply(
lambda cpc_bid: updateBid(cpc_bid, reduction_percentage))
df["forecast"] = df[["cpc_bid", "new_bid", "cost"
]].apply(lambda row: updateForecast(row),
axis=1)
# print "Forecast: %s" %(df.forecast.sum())
if df.forecast.sum() <= self.remaining_budget:
break
return df
def getKeywordsOriginalBidsDataframe(self):
query = """
SELECT keywords.id as entity_id,adgroups.google_id as adgroup_google_id, keywords.google_id, keywords.keyword_text,
keywords.keyword_match_type, keywords.cpc_bid, keywords.original_cpc_bid
FROM keywords
join adgroups on adgroups.id = keywords.adgroup_id
where keywords.account_id = "%s"
and keywords.google_id != "3000006"
and keywords.google_id != "3000000"
and keywords.bidding_strategy_type = "cpc"
and keywords.cpc_bid != keywords.original_cpc_bid
""" % (self.account_id)
df = pd.read_sql(query, (Database()).createEngine())
df.cpc_bid = df.cpc_bid.astype("str")
df.cpc_bid = df.cpc_bid.str.replace("--", "0")
df.cpc_bid = df.cpc_bid.astype("float")
return df
def getKeywordsDataframe(self):
query = """
SELECT keywords.id as entity_id,adgroups.google_id as adgroup_google_id, keywords.google_id,keyword_performance.clicks, keyword_performance.conversions,keyword_performance.search_impression_share,
keyword_performance.cost,keyword_performance.conversion_value,keywords.keyword_text
,keywords.keyword_match_type, keywords.cpc_bid, keywords.original_cpc_bid
FROM keyword_performance
join keywords on keywords.id = keyword_performance.keyword_id
join adgroups on adgroups.id = keywords.adgroup_id
where date_range = "last_30_days"
and keywords.account_id = "%s"
and keywords.status = "enabled"
and keyword_performance.conversions = 0
and keyword_performance.clicks > 0
and keywords.google_id != "3000006"
and keywords.google_id != "3000000"
and keywords.bidding_strategy_type = "cpc"
order by cost desc
""" % (self.account_id)
df = pd.read_sql(query, (Database()).createEngine())
df.cpc_bid = df.cpc_bid.astype("str")
df.cpc_bid = df.cpc_bid.str.replace("--", "0")
df.cpc_bid = df.cpc_bid.astype("float")
df['forecast'] = (df.cost /
7) * self.local_dates.days_remaining_in_this_month
df.forecast = df.forecast.astype("str")
df.forecast = df.forecast.str.replace("--", "0")
df.forecast = df.forecast.astype("float")
return df
def updateBids(self, df):
if functions.dfIsEmpty(df):
return
mutations = df.copy()
mutations["entity_google_id"] = mutations[
"adgroup_google_id"] + "," + mutations["google_id"]
mutations["account_id"] = self.account_id
mutations["type"] = "keyword"
mutations["action"] = "set"
mutations["attribute"] = "bid"
mutations["value"] = mutations["new_bid"]
mutations["created_at"] = datetime.now()
mutations["updated_at"] = datetime.now()
mutations = mutations[[
"entity_google_id", "entity_id", "account_id", "type", "action",
"attribute", "value", "created_at", "updated_at"
]]
mutations = mutations.reset_index(drop=True)
mutations["id"] = pd.Series(
[uuid.uuid1() for i in range(len(mutations))]).astype(str)
print("updating %s bids" % mutations.shape[0])
# print mutations["entity_id"]
Database().appendDataframe("mutations", mutations)
def updateKeywordsTable(self, df):
df["id"] = df["entity_id"]
for i, row in df[["id", "new_bid"]].iterrows():
query = "UPDATE keywords SET cpc_bid = %s WHERE id = '%s'" % (
row["new_bid"], row["id"])
Database().createEngine().execute(query)