Automate Site Serve Reporting with Prisma Media Plan and Python for Media AgenciesHarrison ChawBlockedUnblockFollowFollowingApr 12Photo by Franck V.
on UnsplashIn order to have effective media, it is essential for a media plan to utilize multiple distribution channels (ie digital display, TV, OOH).
There are studies that show that having cross-channel marketing campaigns have a significant lift in performance.
One key component of these marketing plans are the use of publishers who track the ads themselves rather than through an ad server.
In the industry, the ads that are tracked manually by publishers are labeled as “site served”.
In these cases, agencies are dependent on the publisher to provide the relevant data (impressions, clicks, etc) associated with these ads.
From my experience, the process of gathering this data from publishers often follow these certain steps: Subset media plan of site serve placement for specific publisher, create a excel reporting template with all the required metrics (impressions, video views, etc), send excel templates to publisher to fill out, publisher sends data back.
Traditionally, this is a highly manual process often conducted by the media buyers themselves.
The scope of this article is focused around automating all the steps leading up to the sending off of the templates to the publisher.
We will be using python as our language of choice.
This is part 1 of 2 part series on automating site serve reporting.
Part 2 will go over sending the templates within an email.
We start off by importing the packages we need for this project.
Because of the tabular nature of most excel spreadsheets, and in our case a media plan, the Pandas package’s DataFrame will be an excellent choice to manipulate the data within a python environment.
xlsxwriter is a package that will then allow us to write a dataframe in to an excel worksheet.
Although, we can achieve this through simply Pandas, xlsxwriter allows for more in-depth excel manipulation.
import pandas as pd import xlsxwriterI am also working off the assumption that your media plan has some level of cleaned data (placement level granularity, please read this article for reference, if needed).
#Read in Prisma Media Plan filemp = pd.
read_excel(‘Media Plan File’)"""Create day level of granularity"""#Create date range of placements mp[‘Date Range’] = (pd.
to_datetime(mp[‘End Date’]) — pd.
to_datetime(mp[‘Start Date’])).
dt.
days#Use Date Range to repeat placement mp = mp.
reindex(mp.
index.
repeat(mp[‘Date Range’]))#Filter out placements that are site servedmp = mp['Placement Name'].
str.
contains('_SiteServed_')After some light manipulation of the the media plan, we need to group the media plan by publishers, as that each publisher is responsible for only their trafficked placements.
From there we use a list comprehension to break out the publishers and their respective content.
SiteGroup = mp.
groupby(‘Site’)vendorList= [contents for vendor, contents in SiteGroup]One of the best aspects of computer programming is the ability to loop functions effectively leveraging the power of computers to do what they do best: compute.
So in this example we will loop through vendorList list in order to create templates for each vendor.
for vendor in vendorList: #Creating the export excelName; setting by the first Campaign name and Supplier valuePlease note that all of the following code will be nested in the previous loop.
In this step, we will be grabbing the name of the campaign and publisher from our vendorList list to be used to name our excel template file.
campaignName = vendor[‘Campaign Name’].
iloc[0]supplierName = vendor[‘Site’].
iloc[0]string = (‘{0}_{1}’+’.
xlsx’).
format(campaignName, supplierName)Now we begin actually creating the excel templates.
We start off by calling an instance with a pandas dataframe with xlsxwriter as the engine.
Then we take our vendor variable from vendorList list and insert that data into our writer instance, thus filling the excel sheet with data.
Afterwards we create a workbook and worksheet instance to interact with the excel sheets.
We name our excel sheet ‘SITE’.
writer = pd.
ExcelWriter(string, engine=’xlsxwriter’) vendor.
to_excel(writer, sheet_name=’SITE’,index=False)workbook = writer.
bookworksheet = writer.
sheets[‘SITE’]One key issue when dealing with publishers with site serve reporting is that they will sometimes modify our provided templates to include other metrics that might be helpful.
However, if for some reason this poses as hindrance, we can apply a macro to prevent any modifications to our excel template.
This is accomplished through the protect attribute that we tack on to our worksheet instance.
But since we do need the publishers to input data within the excel template, we create a format that allows some columns to be unlocked and thus allow for data to be inputted.
worksheet.
protect()unlocked = workbook.
add_format({‘locked’: 0})In the next chunk of code we modify, the columns that will appear in the template.
We create text wrap format to ensure all text within cells a properly displayed.
We then label four column headers for where the publisher will input their data.
We then unlock those columns to allow for the data to be entered with our previously created unlock format.
text_format = workbook.
add_format({‘text_wrap’: True})worksheet.
write(‘F1’,’Publisher Impressions’)worksheet.
write(‘G1’,’Publisher Clicks’)worksheet.
write(‘H1’,’Video: Starts’)worksheet.
write(‘L1’,’Video: 100% Complete’)#where the vendors will input dataworksheet.
set_column(‘F2:O2’,25,unlocked) Seeing any text being cut off by an unexpanded cell in spreadsheet might be in one of the unsightly things to be seen in MS excel.
So in the next few lines we adjust the columns to display the appropriate length to house the full text.
worksheet.
set_column(‘A:B’,vendor[‘Campaign Name’].
map(len).
max(),text_format)worksheet.
set_column(‘C:C’,vendor[‘Placement’].
map(len).
max(),text_format)worksheet.
set_column(‘D:D’,15)worksheet.
set_column(‘E:Z’,25)We finish up by closing the workbook and saving the excel template.
workbook.
close()writer.
save()See below for the full codeIn review, we have written a python script that parses out placements that are site-served from the media plan and creates a excel template for publishers to then fill out for reporting needs.
The hiccups that can occur during the implementation of this project would be issues with the naming convention of the placements, sudden changes to the placements but not reflected in the media plan, and many other minute variations.
The next steps in this project would be automating the emailing associated with sending off the excel templates to the publishers.
In an ideal world (media agency centric world), site serve would be done away and all trafficking would be done through your ad server of choice (probably DCM, actually most likely DCM…) .
However the next best thing, in my opinion, would be unified a API across publishers in which we can then manually call their servers for impressions and other metrics.
But the best we can do at the moment is to minimize the errors and standardize the process of data hand-offs between publishers and agencies.
For questions, comments or concerns (or glaring issues), please contact me on linkedin https://www.
linkedin.
com/in/harrychaw/.. More details