def publisher_tactic_data():
pub_performance = pd.DataFrame(Range('Publisher Performance', 'A12:E12').vertical.value,
columns= Range('Publisher Performance', 'A12:E12').value)
pub_performance.drop(0, inplace= True)
pub_performance['CPGA'] = pub_performance['Spend'].astype(float) / pub_performance['Total GAs'].astype(float)
pub_performance.rename(columns= {'Total GAs':'Total ' + main.qquarter() + ' GAs',
'Spend':'Total ' + main.qquarter() + ' Spend',
'Placement Messaging Type':'Tactic'}, inplace= True)
pub_performance = goals(pub_performance)
return pub_performance
def generate_publisher_tables(pub_performance):
Range('Publisher Performance', 'A48:A1000').clear_contents()
site_list = list(pub_performance['Site'].unique())
week = Range('Publisher Performance', 'D12').value
start_column = 'B'
site_column = 'A'
cell_number = 50
for i in site_list:
df = pub_performance[pub_performance['Site'] == i]
df = df[['Tactic', 'Total ' + main.qquarter() + ' GAs', week,
'Total ' + main.qquarter() + ' Spend', 'CPGA', main.qquarter() + ' CPGA Goal']]
Range('Publisher Performance', start_column + str(cell_number), index = False).value = df
Range('Publisher Performance', site_column + str(cell_number), index = False).value = i
cell_number += 7
def generate_publisher_emails(pubs_combined, contacts, br):
week_end = Range('Publisher Performance', 'C7').value + datetime.timedelta(days=7)
week_end = week_end.strftime('%m/%d')
week_start = Range('Publisher Performance', 'C7').value.strftime('%m/%d')
headerstyle = '<p style = "font-family: Calibri; font-size: 11pt; font-weight: bold; text-decoration: underline;">'
bodystyle = '<p style = "font-family: Calibri; font-size: 11pt;">'
boldstyle = '<p style = "font-family: Calibri; font-size: 11pt; font-weight: bold;">'
merged = pd.merge(pubs_combined, contacts, how= 'left', on= 'Publisher')
pub_list = list(merged['Publisher'].unique())
pub_emails = list(merged['cc_emails'].unique())
contact_emails = list(merged['Contact Email'].unique())
outlook = win32.Dispatch('Outlook.Application')
for i in range(0, len(pub_list)):
if pub_list[i] == 'ASG' or pub_list[i] == 'AOD':
br_performance = headerstyle + \
'Brand Remessaging</p>' + \
bodystyle + \
'Traffic Yield - ' + str(float(br['Traffic Yield'])) + '%' + \
'<br>' + \
'Traffic Actions - ' + str(br['Traffic Actions'][1]) + \
'</p>' + \
boldstyle + \
'Brand Remessaging Performance Chart ' + quarter_start() + ' - ' + week_end + \
'</p><br><br><br>'
else:
br_performance = ''
mail = outlook.CreateItem(0)
df = merged[merged['Publisher'] == pub_list[i]]
if pub_list[i] == 'ASG' or pub_list[i] == 'AOD':
greeting = 'Hi Dan and Team,'
elif pub_list[i] == 'Amazon':
greeting = 'Hi Lauren and Team,'
elif pub_list[i] == 'eBay':
greeting = 'Hi Katie,'
elif pub_list[i] == 'Magnetic':
greeting = 'Hi Melissa,'
elif pub_list[i] == 'Yahoo!':
greeting = 'Hi Krystal,'
elif pub_list[i] == 'Bazaar Voice':
greeting = 'Hi Alexa and Todd,'
else:
greeting = 'Hello,'
mail.To = str(contact_emails[i]).encode('utf-8')
mail.CC = str(pub_emails[i]).encode('utf-8')
mail.subject = main.qquarter() + ' 2016 DDR Performance Update: ' + \
week_start + '-' + week_end + ' - ' + str(pub_list[i])
mail.HTMLBody = '<body>' + \
bodystyle + \
greeting + \
'<br><br>' + \
'Below you will find your campaign performance breakout for the beginning of ' + \
main.qquarter() + ' through the week ' + \
'ending ' + week_end + '.</p>' + \
headerstyle + \
'DDR Performance ' + quarter_start() + ' - ' + week_end + ' (All Tactics Combined) </p>' + \
bodystyle + \
'CPGA - $' + str(float(df['CPGA']))[:-2] + '<br>' + 'GAs - ' + str(float(df['GAs'])) + \
'</p>' + \
boldstyle + \
'DDR Performance Chart by Tactic ' + quarter_start() + ' - ' + week_end + \
'<br><br><br><br><br><br><br>' + \
'Optimization Notes</p>' + \
br_performance + \
bodystyle + \
'<br><br><br>' + \
'Let me know if you have any questions.' + \
'<br><br>' + \
'Best,' + \
'</body>'
mail.Display()