def raw_pacing_and_dr(dr, pacing_data):
sites = '|'.join(list(['ASG', 'Amazon', 'Magnetic', 'eBay']))
cd = '|'.join(list(['C Pages', 'D Pages']))
t2t = '|'.join(list(['Tablet to Tablet']))
fbx = '|'.join(list(['FBX']))
search = '|'.join(list(['Search']))
pros = '|'.join(list(['Prospecting']))
#Add-a-Line
ddr = drop_columns(dr)
pacing_dr = ddr.rename(columns={'Placement Messaging Type': 'Type', 'NTC Media Cost': 'Spend', 'Total GAs': 'GAs'})
pacing_dr['Type.Agg'] = np.where(
(pacing_dr['Type'].str.contains(cd) == True) & (pacing_dr['Type'].str.contains(fbx) == False), 'CD.Remessaging',
np.where(pacing_dr['Type'].str.contains(t2t) == True, 'Tablet-to-Tablet',
np.where(pacing_dr['Type'].str.contains(fbx) == True, 'FBX.Remessaging',
np.where(pacing_dr['Type'].str.contains(search) == True, 'Search.Remessaging',
np.where(pacing_dr['Type'].str.contains(pros) == True, 'Prospecting',
'NA')))))
pacing_dr = pacing_dr[(pacing_dr['Date'] >= main.quarter_start()) & (pacing_dr['Campaign'] == 'DR') & (
pacing_dr['Site'].str.contains(sites) == True)]
pacing_dr = pacing_dr.groupby(['Site', 'Type.Agg', 'Type', 'Date'])
pacing_dr = pd.DataFrame(pacing_dr.sum()).reset_index()
pacing_dr['Site_Type'] = pacing_dr['Site'] + '.' + pacing_dr['Type.Agg']
pacing_pivoted = pd.pivot_table(pacing_dr, values=['Spend', 'GAs'], index=['Date'], columns=['Site_Type'],
aggfunc=np.sum)
pacing_pivoted.fillna(0, inplace=True)
spend = pacing_pivoted['Spend']
gas = pacing_pivoted['GAs']
spend.columns = pd.Series(spend.columns).astype(str) + '.Spend'
gas.columns = pd.Series(gas.columns).astype(str) + '.GAs'
merged = pd.merge(spend, gas, how= 'left', right_index= True, left_index= True)
data_merged = pd.merge(merged, pacing_data, how='right', left_index=True, right_index=True)
data_merged.reset_index(inplace=True)
data_merged['Weekday'] = data_merged['Date'].apply(lambda x: arrow.get(x).format('dddd'))
cols = ['ASG.Prospecting.Spend', 'ASG.Search.Remessaging.Spend', 'ASG.Prospecting.GAs',
'ASG.Search.Remessaging.GAs']
data_merged.drop(cols, axis= 1, inplace= True)
return data_merged
def transform_dr(dr):
dr = dr[dr['Campaign'] == 'DR']
ddr = drop_columns(dr)
cd, t2t, fbx, search, pros, aal = dr_placement_types()
pacing_dr = ddr.rename(columns={'Placement Messaging Type': 'Type', 'NTC Media Cost': 'Spend', 'Total GAs': 'GAs'})
pacing_dr['Type.Agg'] = np.where(
pacing_dr['Type'].str.contains(cd) == True, 'CD.Remessaging',
np.where(pacing_dr['Type'].str.contains(t2t) == True, 'Tablet-to-Tablet',
np.where(pacing_dr['Type'].str.contains(fbx) == True, 'FBX.Remessaging',
np.where(pacing_dr['Type'].str.contains(search) == True, 'Search.Remessaging',
np.where(pacing_dr['Type'].str.contains(pros) == True, 'Prospecting',
'Add-A-Line')))))
pacing_dr = pacing_dr[(pacing_dr['Date'] >= main.quarter_start()) &
(pacing_dr['Site'].str.contains(dr_sites()) == True)]
pacing_dr = pacing_dr.groupby(['Site', 'Type.Agg', 'Type', 'Date'])
pacing_dr = pd.DataFrame(pacing_dr.sum()).reset_index()
pacing_dr['Site_Type'] = pacing_dr['Site'] + '.' + pacing_dr['Type.Agg']
pacing_pivoted = pd.pivot_table(pacing_dr, values=['Spend', 'GAs'], index=['Date'], columns=['Site_Type'],
aggfunc=np.sum)
pacing_pivoted.fillna(0, inplace=True)
spend = pacing_pivoted['Spend']
gas = pacing_pivoted['GAs']
spend.columns = pd.Series(spend.columns).astype(str) + '.Spend'
gas.columns = pd.Series(gas.columns).astype(str) + '.GAs'
merged = pd.merge(spend, gas, how= 'left', right_index= True, left_index= True)
merged.reset_index(inplace=True)
merged['Weekday'] = merged['Date'].apply(lambda x: arrow.get(x).format('dddd'))
return merged
def pacing():
a = '2015-09-28'
b = '2015-10-04'
c = '2015-10-05'
d = '2015-12-27'
e = '2015-12-28'
f = '2015-12-31'
date_rng = pd.date_range(main.quarter_start(), periods=92, freq='D')
date_df = pd.DataFrame(date_rng, columns=['Date'])
pacing_wb = Workbook(main.dr_pacing_path())
pacing_wb.set_current()
Application(wkb=pacing_wb).xl_app.Run('Clean_Pacing_Data')
pacing_data = pd.DataFrame(Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.value,
columns=Range('Q4 DDR Pacing by sub-tactic', 'Z1').horizontal.value)
pacing_data.drop(0, inplace=True)
Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.clear_contents()
pacing_wb.close()
pacing_data.set_index('Week', inplace=True)
pacing_data = pacing_data.resample('1D', fill_method='pad')
pacing_data.fillna(method='ffill', inplace= True)
pacing_data.reset_index(inplace=True)
pacing_data.rename(columns={'Week': 'Date'}, inplace=True)
pacing_data = pd.merge(pacing_data, date_df, how='right', left_on='Date', right_on='Date')
pacing_data.fillna(method='ffill', inplace=True)
pacing_data.ix[a:b] = pacing_data.ix[a:b] / 4
pacing_data.ix[c:d] = pacing_data.ix[c:d] / 7
pacing_data.ix[e:f] = pacing_data.ix[e:f] / 4
pacing_data.set_index('Date', inplace= True)
return pacing_data
def publishers(dr):
cd, t2t, fbx, search, pros, aal = data_transform.dr_placement_types()
week = week_of(dr)
# Publisher Performance
pub_dr = dr[(dr['Campaign'] == 'DR') & (dr['Date'] >= main.quarter_start())]
pub_dr = pub_dr.groupby(['Site', 'Placement Messaging Type', 'Week', 'Date'])
pub_dr = pd.DataFrame(pub_dr.sum()).reset_index()
#pub_dr = pub_dr[(pub_dr['NTC Media Cost'] != 0)]
pub_dr['Tactic'] = np.where((pub_dr['Placement Messaging Type'].str.contains(cd) == True) &
(pub_dr['Placement Messaging Type'].str.contains(fbx) != True), 'C/D Remessaging',
np.where(pub_dr['Placement Messaging Type'].str.contains(fbx) == True,
'FBX Remessaging', pub_dr['Placement Messaging Type']))
# Quarter
q_dr = pub_dr[pub_dr['Date'] >= main.quarter_start()]
q_dr = q_dr.groupby(['Site', 'Tactic'])
q_dr = pd.DataFrame(q_dr.sum()).reset_index()
q_dr = goals(q_dr)
# Last Week
last_week = pub_dr[pub_dr['Week'] == pub_dr['Week'].max()]
last_week = last_week.groupby(['Site', 'Tactic'])
last_week = pd.DataFrame(last_week.sum()).reset_index()
last_week.rename(columns={'NET Media Cost': week}, inplace= True)
# Publishers Overall
sites = q_dr.groupby('Site')
sites = pd.DataFrame(sites.sum()).reset_index()
sites['CPGA'] = sites['NET Media Cost'] / sites['Total GAs']
# Brand Remessaging
br = dr[dr['Campaign'] == 'Brand Remessaging']
br_quarter = br[br['Date'] >= main.quarter_start()]
br_lw = br[br['Week'] == br['Week'].max()]
br_quarter = br_quarter.groupby('Site')
br_quarter = pd.DataFrame(br_quarter.sum().reset_index())
br_quarter['Traffic Yield'] = br_quarter['Total Traffic Actions'].astype(float) / \
br_quarter['Impressions'].astype(float)
br_lw = br_lw.groupby('Site')
br_lw = pd.DataFrame(br_lw.sum().reset_index())
pacing_wb = Workbook(main.dr_pacing_path())
pacing_wb.set_current()
Range('Publisher Performance', 'A13', index=False, header=False).value = q_dr[
['Site', 'Tactic', 'Total GAs']]
Range('Publisher Performance', 'E13', index= False, header= False).value = q_dr['NET Media Cost']
Range('Publisher Performance', 'D13', index= False, header= False).value = last_week[week]
Range('Publisher Performance', 'D12').value = week
Range('Publisher Performance', 'A32', index= False, header= False).value = sites[['Site', 'Total GAs', 'CPGA']]
Range('Publisher Performance', 'C7').value = pub_dr['Week'].max().strftime('%m/%d/%Y')
Range('Publisher Performance', 'B45', index= False, header= False).value = \
br_quarter[['Traffic Yield', 'Impressions', 'Total Traffic Actions']]
Range('Publisher Performance', 'G45', index= False, header= False).value = br_quarter['NET Media Cost']
Range('Publisher Performance', 'F45', index= False, header= False).value = br_lw['NET Media Cost']
Range('Publisher Performance', 'F44').value = week
pacing_wb.save()
pacing_wb.close()