The code below brings in packages from our library that each have different coding abilities. We will be using various commands from each of the packages listed below.
library(tidyverse)
library(dplyr)
library(scales)
library(openxlsx)
library(rmarkdown)
In this section we are creating a few objects that will be used throughout our code. First, we are creating an object named ‘today’ which is equal to the system date on our computer.
Next, we are setting the calendar year for the data that we wish to bring into the scorecard. In this case it is 2024 - if we use this next year we will want to update that line to be 2025.
Finally, we are loading in our two data sets that all of our analysis will be based off. These data are the ‘feasibility requests’ and ‘attribution workflow’ lists downloaded from SharePoint. We will be working from the ‘feasibility’ and ‘Activation’ objects.
(original_feas and original_activation are not touched throughout our code. They are just here so that we can refer back to the original, raw, unedited data if we would like to do that at any point.)
today <- Sys.Date()
year <- 2024
feasibility <- read.csv("Scorecard Data/Feasibility Requests.csv")
original_feas <- read.csv("Scorecard Data/Feasibility Requests.csv")
Activation <- read.csv("Scorecard Data/Attribution_Workflow.csv")
original_activation <- read.csv("Scorecard Data/Attribution_Workflow.csv")
In Part 2 we will be cleaning the request data from our feasibility file to get full spend for all submitted requests in 2024
The code chunk below creates columns called ‘startDate’, ‘endDate’, and ‘DateCreated’ based off of the existing columns ‘Campaign.Start.Date’, ‘Campaign.End.Date’, and ‘Created’. This code is also taking that existing data (which reads in as character data) and formats it to be read as a date.
feasibility$startDate <- as.Date(feasibility$Campaign.Start.Date, "%m/%d/%Y")
feasibility$endDate <- as.Date(feasibility$Campaign.End.Date, "%m/%d/%Y")
feasibility$DateCreated <- as.Date(feasibility$Created, "%m/%d/%Y")
We are only interested in the requests that came in this calendar year, so we will need to subset our current data to reflect the requests from this year. You can see below that I am creating a subset of the ‘feasibility’ data to only include rows where the ‘DateCreated’ column is greater than or equal to January 1st of the year we identified in our general setup section.(i.e. when year is set to 2024 our code will subset the feasibility data to include only rows that have a feasibility request submission date of Jan. 1 2024 or more recent - nothing from 2023 will be captured.)
requests <- subset(feasibility, DateCreated >= paste0(year,"-01-01"))
We will now convert the column ‘Total.Campaign.Spend..Net.’ (which is read in as character data) to a numeric variable named FullSpend so that we are able to run calculations based on the values.
requests$FullSpend <- as.numeric(requests$Total.Campaign.Spend..Net.)
Some advertisers come in all caps, some are submitted all lowercase,
some have a combination of both - so to account for that we will be
changing all advertiser names to all caps. We will use the toupper
function to complete this and will save it in a column named
‘Upped’.
Along with that, some advertisers are submitted with either leading or
trailing spaces which are arbitrary to the advertiser name. The second
line in the code below removes leading and trailing spaces so that we
are just left with the advertiser name and again saves these changes in
the column ‘Upped’.
requests$Upped <- toupper(requests$Advertiser)
requests$Upped <- trimws(requests$Upped, which = c("both"))
For computing spend we must create a hierarchy for where we want the code to look first in the event that there is an advertiser with two submissions under the same name but different study types for the same campaign flight. The hierarchy is Brand Lift then Incremental Reach followed by Web Lift, Inc. Reach - Competitive, and finally Foot Traffic. The code below establishes this hierarchy.
(i.e.) If an advertiser is running both a Web Lift study as well as a Brand Lift study, we want to have the Brand Lift study show up first in our data before the Web Lift, and any approved studies trump in progress or rejected studies
requests$Client.s.Measurement.s.Needs <-
ordered(requests$Client.s.Measurement.s.Needs,
levels = rev(c("Brand Awareness/Lift",
"Incremental Reach",
"Web Lift/Attribution",
"Incremental Reach – Competitive",
"Foot Traffic")))
requests$Request.Status <- ordered(requests$Request.Status,
levels = rev(c("APPROVED",
"IN PROGRESS",
"REJECTED")))
In the code chunk below we are ordering our 2024 request data first by alphabetical name (“Upped”), then by study type (“Client.s.Measurement.s.Needs”) based off of our hierarchy created above.
requests <- arrange(requests, Upped, desc(Request.Status),
desc(Client.s.Measurement.s.Needs))
To remove any duplicate rows where advertiser name, start date, and end date all match in two or more separate rows, we will run the code below. We do this so that we are only calculating the spend for that campaign one time. The code will keep the first instance of the duplication and remove all following duplicated rows that match on advertiser name(Upped) as well as same start and end date.
This creates a new data set called requestsDeDupe.
requestsDeDupe <- requests[!duplicated(requests[c("Upped", "startDate",
"endDate")]),]
We now have our request data nice and cleaned up and are ready to run some calculations. First we sum up the ‘FullSpend’ column from the requestsDeDupe data so that we do not double count any advertisers that are running multiple studies. Next, we count the number of rows (nrow) in the requests data set to give us a number of how many total studies have been requested in 2024.
# calculate spend for the deduped request list
sum(requestsDeDupe$FullSpend)
# calculate how many requests have come in this calendar year
nrow(requests)
Similar to the requests section, this section also pulls data from the ‘feasibility’ data set. Instead of all requests, we are now just interested in those that have not been rejected. This will include only requests that are either still ‘In Progress’ or have been ‘Approved’.
Just like we did earlier, the code chunk below creates columns called ‘startDate’, ‘endDate’, and ‘DateCreated’ based off of the existing columns ‘Campaign.Start.Date’, ‘Campaign.End.Date’, and ‘Created’. This code is also taking that existing data (which reads in as character data) and formats it to be read as a date.
feasibility$startDate <- as.Date(feasibility$Campaign.Start.Date, "%m/%d/%Y")
feasibility$endDate <- as.Date(feasibility$Campaign.End.Date, "%m/%d/%Y")
feasibility$DateCreated <- as.Date(feasibility$Created, "%m/%d/%Y")
We now need to subset the feasibility data to only include those rows that have the column ‘Request.Status’ equal to either “APPROVED” or “IN PROGRESS”. This can be seen done with the below code which creates a new object (data set) called ApprovedInProg which is exactly the same as the ‘feasibility’ data set but only includes observations with Request.Status equal to APPROVED or IN PROGRESS.
ApprovedInProg <- subset(feasibility,
Request.Status == "APPROVED" |
Request.Status == "IN PROGRESS")
Again, We are only interested in the requests that came in this calendar year, so we will need to subset our current data to reflect the requests from this year. You can see below that I am creating a subset of the ‘ApprovedInProg’ data to only include rows where the ‘DateCreated’ column is greater than or equal to January 1st of the year we identified in our general setup section.
ApprovedInProg <- subset(ApprovedInProg, DateCreated >= paste0(year,"-01-01"))
We will now convert the column ‘Total.Campaign.Spend..Net.’ (which is read in as character data) to a numeric variable named FullSpend so that we are able to run calculations based on the numeric values.
ApprovedInProg$FullSpend <- as.numeric(
ApprovedInProg$Total.Campaign.Spend..Net.)
Again, cleaning up advertiser names for this section of our code we need to convert all advertiser names to fully uppercase so that matching on advertiser name is not case sensitive and then we will remove any leading or trailing spaces from advertiser name.
ApprovedInProg$Upped <- toupper(ApprovedInProg$Advertiser)
ApprovedInProg$Upped <- trimws(ApprovedInProg$Upped, which = c("both"))
For computing spend we must create a hierarchy for where we want the code to look first in the event that there is an advertiser with two submissions under the same name but different study types for the same campaign flight. The hierarchy is Brand Lift then Incremental Reach followed by Web Lift, Inc. Reach - Competitive, and finally Foot Traffic. The code below establishes this hierarchy.
(i.e.) If an advertiser is running both a Web Lift study as well as a Brand Lift study, we want to have the Brand Lift study show up first in our data before the Web Lift.
ApprovedInProg$Client.s.Measurement.s.Needs <-
ordered(ApprovedInProg$Client.s.Measurement.s.Needs,
levels = rev(c("Brand Awareness/Lift",
"Incremental Reach",
"Web Lift/Attribution",
"Incremental Reach – Competitive",
"Foot Traffic"
)))
In the code chunk below we are ordering our 2024 Approved/In Progress data first by alphabetical name (“Upped”), then by study type (“Client.s.Measurement.s.Needs”) based off of our hierarchy created above.
ApprovedInProg <- arrange(ApprovedInProg, Upped,
desc(Client.s.Measurement.s.Needs))
To remove any duplicate rows where advertiser name, start date, and end date all match in two or more separate rows, we will run the code below. We do this so that we are only calculating the spend for that overall campaign one time. The code will keep the first instance of the duplication and remove all following duplicated rows that match on advertiser name(Upped) as well as same start and end date.
This creates a new data set called ApprovedProgDeDupe.
ApprovedProgDeDupe <- ApprovedInProg[!duplicated(ApprovedInProg[c(
"Upped", "startDate", "endDate")]),]
We now have our Approved/In Progress data nice and cleaned up and are ready to run some calculations. First we sum up the ‘FullSpend’ column from the ApprovedProgDeDupe data so that we do not double count any advertisers that are running multiple studies. Next, we count the number of rows (nrow) in the ApprovedInProg data set to give us a number of how many total studies have been Approved or are still In Progress for 2024.
# Calculate spend for all Approved/In Progress studies
sum(ApprovedProgDeDupe$FullSpend)
# Calculate number of Approved/In Progress studies
nrow(ApprovedInProg)
This section pulls data from the ‘activation’ data set. We are now just interested in those studies that have been submitted for activation and have any flight dates in 2024 that are not starting past 2024-03-15.
The code chunk below creates columns called ‘startDate’ and ‘endDate’ based off of the existing columns ‘Flight.Start.Date’ and ‘Flight.End.Date’. This code is also taking that existing data (which reads in as character data) and formats it to be read as a date.
Activation$startDate <- as.Date(Activation$Flight.Start.Date, "%m/%d/%Y")
Activation$endDate <- as.Date(Activation$Flight.End.Date, "%m/%d/%Y")
This section subsets the data to only include studies that have already ended, but were live at some point in 2024 as well as studies that are currently live. This does not account for activated studies whose start date has not happened yet.
Live <- subset(Activation,
(today >= startDate &
today <= endDate) |
(endDate >= paste0(year,"-01-01") &
endDate <= today))
Since the activation data set does not include a Total Spend column, we need to compute this using the Linear Spend and Digital Spend. Before we can create that variable, we have to change the current LinearSpend and DigitalSpend columns from character data to numeric. In order to do this, we need to remove the dollar sign and set the values to numeric.
From there, we create a new column in the ‘Live’ data set (that we created in the previous step) which is a combination of the LinearSpend + DigitalSpend. This new column is named TotalSpend.
Live$LinearSpend <- as.numeric(gsub(
'[$,]', '', Live$Compaign.Spend.Linear..Net.))
Live$DigitalSpend <- as.numeric(gsub(
'[$,]', '', Live$Compaign.Spend.Digital..Net.))
# Combine digital and linear spend for a total spend column
Live$TotalSpend <- Live$LinearSpend + Live$DigitalSpend
Again, to clean up advertiser names for this section of our code we need to convert all advertiser names to fully uppercase so that matching on advertiser name is not case sensitive and then we will remove any leading or trailing spaces from advertiser name.
Live$Upped <- toupper(Live$Client)
Live$Upped <- trimws(Live$Upped, which = c("both"))
For computing spend we must create a hierarchy for where we want the code to look first in the event that there is an advertiser with two submissions under the same name but different study types for the same campaign flight. The hierarchy is Brand Lift then Incremental Reach followed by Web Lift, Inc. Reach - Competitive, and finally Foot Traffic. The code below establishes this hierarchy.
Live$Type.of.Attribution <-
ordered(Live$Type.of.Attribution,
levels = rev(c("Brand Lift (25M Impression Minimum)",
"Incremental Reach",
"Website Attribution/Lift",
"Incremental Reach – Competitive",
"Foot Traffic")))
In the code chunk below we are ordering our 2024 Live data first by alphabetical name (“Upped”), then by study type (“Type.of.Attribution”) based off of our hierarchy created above.
Live <- arrange(Live, Upped,
desc(Type.of.Attribution))
To remove any duplicate rows where advertiser name, start date, and end date all match in two or more separate rows, we will run the code below. We do this so that we are only calculating the spend for that overall campaign one time. The code will keep the first instance of the duplication and remove all following duplicated rows that match on advertiser name(Upped) as well as same start and end date.
This creates a new data set called LiveDeDupe.
LiveDeDupe <- Live[!duplicated(Live[c("Upped", "startDate", "endDate")]),]
We now have our Live data nice and cleaned up and are ready to run some calculations. First we sum up the ‘FullSpend’ column from the LiveDeDupe data so that we do not double count any advertisers that are running multiple studies. Next, we count the number of rows (nrow) in the Live data set to give us a number of how many total studies have been Approved or are still In Progress for 2024.
# Calculate total spend for live campaigns
sum(LiveDeDupe$TotalSpend)
# Calculate total number of live studies
nrow(Live)
Now that all of our data has been cleaned and is able to report on, we will produce some desired outputs.
The code below will print outputs to the console in this order:
print(paste0("Total number of requests: ", nrow(requests)))
totalrequests <- paste0("Total number of requests: ", nrow(requests))
print(paste0("Total spend for requested studies: $",
formatC(sum(requestsDeDupe$FullSpend), big.mark = ',',
format = 'f')))
totalrequestspend <- paste0("Total spend for requested studies: $",
formatC(sum(requestsDeDupe$FullSpend),
big.mark = ',',
format = 'f'))
print(paste0("Total number of Approved/In Progress requests: ",
nrow(ApprovedInProg)))
totalapprovedinprog <- paste0("Total number of Approved/In Progress requests: ",
nrow(ApprovedInProg))
print(paste0("Total spend for Approved/In Progress studies: $",
formatC(sum(ApprovedProgDeDupe$FullSpend), big.mark = ',',
format = 'f')))
totalapprovedinprogspend <- paste0(
"Total spend for Approved/In Progress studies: $",
formatC(sum(ApprovedProgDeDupe$FullSpend),
big.mark = ',',format = 'f'))
print(paste0("Total number of live studies: ", nrow(Live)))
totallive <- paste0("Total number of live studies: ", nrow(Live))
print(paste0("Total spend for live studies: $",
formatC(sum(LiveDeDupe$TotalSpend), big.mark = ',', format = 'f')))
totallivespend <- paste0("Total spend for live studies: $",
formatC(sum(LiveDeDupe$TotalSpend), big.mark = ',', format = 'f'))
Total number of requests: 89
Total spend for requested studies: $74,004,202.9800
Total number of Approved/In Progress requests: 84
Total spend for Approved/In Progress studies: $73,699,341.7800
Total number of live studies: 84
Total spend for live studies: $72,451,777.0000
Because our data is now clean for requests, approved/in progress, and live, we now want to export those data sets so that we can easily view them and send them to anyone that may need them. To do this, please see the code below. We are writing the data to an Excel document, and saving it in our working directory.
requestsTable <- write.xlsx(requests,
file = paste0('Output/requestsTable',year,'.xlsx'),
asTable = TRUE, overwrite = TRUE)
requestsTablededupe <- write.xlsx(requestsDeDupe,
file = paste0('Output/requestsTablededupe',
year,'.xlsx'),
asTable = TRUE, overwrite = TRUE)
ApprovedInProgTable <- write.xlsx(ApprovedInProg,
file = paste0('Output/ApprovedInProgTable',
year, '.xlsx'),
asTable = TRUE, overwrite = TRUE)
ApprovedInProgDedupe <- write.xlsx(ApprovedProgDeDupe,
file =
paste0('Output/ApprovedInProgTablededupe',
year,'.xlsx'),
asTable = TRUE, overwrite = TRUE)
LiveTable <- write.xlsx(Live, file = paste0('Output/LiveTable',
year,'.xlsx'),
asTable = TRUE, overwrite = TRUE)
LiveTableDedupe <- write.xlsx(LiveDeDupe,
file = paste0('Output/LiveTableDedupe',
year,'.xlsx'),
asTable = TRUE, overwrite = TRUE)