xhs Data Analysis
2025-11-20
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(ggplot2)
# library(gridExtra)
library(readxl)Data Cleaning
#1. remove unwanted variables/entries
data <- read_excel("~/Downloads/official_account/DM_Data.xlsx")
natural <- read_excel("~/Downloads/official_account/Natural_Flow_Conversion_Data.xlsx")
variables_to_remove <- c("Plan-Note_ID", "Note_ID", "Note_Link", "Unit", "Unit_ID",
"Plan_ID", "DM_Entry_Person", "DM_Contact_Person",
"DM_Message_Person", "Pinned_Comment_Exposure",
"Pinned_Comment_Click", "Pinned_Comment_CR",
"Bottom_Left_Exposure", "Bottom_Left_Click",
"Bottom_Left_CR")
data <- data[-1, !(names(data) %in% variables_to_remove)]
#2. adjust variable type
data$Date <- as.Date(data$Date)
natural$Date <- as.Date(natural$Date)
data[, c("CR", "MR")] <- lapply(data[, c("CR", "MR")], function(x) sub("%", "", x))
data[, 4:16] <- lapply(data[, 4:16], as.numeric)
#3. add additional variables
data$ER <- round(ifelse(data$DM_Entry == 0, 0, data$DM_Entry / data$Click), 2) * 100
data$ConR <- round(ifelse(data$DM_Contact == 0, 0, data$DM_Contact / data$DM_Entry), 2) * 100Cost-Effectiveness
Overall cost-effectiveness last week
lastweek <- tail(sort(unique(data$Date)), 7)
data_lastweek <- data[data$Date %in% lastweek, ]
natural_lastweek <- natural[natural$Date %in% lastweek, ]
averageCPD <- round(sum(data_lastweek$Cost) / length(lastweek), 2)
averageCPC <- round(sum(data_lastweek$Cost) / sum(data_lastweek$Click), 2)
# averageCPM <- round(sum(data_lastweek$Cost) / sum(data_lastweek$Exposure) *1000, 2)
# cat("The average CPM was", averageCPM, "RMB over the last week.\n")
averageCPE <- round(sum(data_lastweek$Cost) / sum(data_lastweek$DM_Entry), 2)
averageCPEIntegrated <- round(sum(data_lastweek$Cost) / sum(data_lastweek$DM_Entry, natural_lastweek$DM_Entry), 2)
averageCPCon <- round(sum(data_lastweek$Cost) / sum(data_lastweek$DM_Contact), 2)
averageCPConIntegrated <- round(sum(data_lastweek$Cost) / sum(data_lastweek$DM_Contact, natural_lastweek$DM_Contact), 2)
cat("The average daily cost was", averageCPD, "RMB over the last week.\n
The average CPC was", averageCPC, "RMB over the last week.\n
The average Cost per DM Entry(ads only) was", averageCPE, "RMB over the last week.\n
The average Cost per DM Entry(Integrated) was", averageCPEIntegrated, "RMB over the last week.\n
The average Cost per DM Contact(ads only) was", averageCPCon, "RMB over the last week.\n
The average Cost per DM Contact(Integrated) was", averageCPConIntegrated, "RMB over the last week.\n")## The average daily cost was 53.35 RMB over the last week.
##
## The average CPC was 0.65 RMB over the last week.
##
## The average Cost per DM Entry(ads only) was 41.5 RMB over the last week.
##
## The average Cost per DM Entry(Integrated) was 33.95 RMB over the last week.
##
## The average Cost per DM Contact(ads only) was 373.46 RMB over the last week.
##
## The average Cost per DM Contact(Integrated) was 186.73 RMB over the last week.Cost-effectiveness daily trend
alldates <- sort(unique(data$Date))
colnames <- c("daily_cost", "CPC", "CPE", "CPE_Integrated", "CPCon", "CPCon_Integrated")
cetrend <- as.data.frame(matrix(numeric(0), length(alldates), length(colnames)))
rownames(cetrend) <- alldates
colnames(cetrend) <- colnames
tocalculate <- c("Cost", "Click", "DM_Entry", "DM_Entry_Integrated", "DM_Contact", "DM_Contact_Integrated")
CP <- function(df, date, tocalculate){
df <- df[df$Date == date, ]
cost <- sum(df$Cost)
if(tocalculate == "Cost"){
cost
} else if (tocalculate %in% c("Click","DM_Entry", "DM_Contact")){
divider <- sum(df[[tocalculate]])
round(cost/divider, 2)
} else if (tocalculate == "DM_Entry_Integrated"){
divider <- sum(df[["DM_Entry"]], natural[natural$Date == date, "DM_Entry"][[1]])
round(cost/divider, 2)
} else if (tocalculate == "DM_Contact_Integrated"){
divider <- sum(df[["DM_Contact"]], natural[natural$Date == date, "DM_Contact"][[1]])
round(cost/divider, 2)
}
}
for(i in 1:length(alldates)){
currentdate <- alldates[i]
for(j in 1:length(tocalculate)){
cetrend[i, j] <- CP(data, currentdate, tocalculate[j])
}
}
cetrend## daily_cost CPC CPE CPE_Integrated CPCon CPCon_Integrated
## 2025-11-07 11.26 0.63 11.26 11.26 Inf Inf
## 2025-11-08 7.59 0.15 2.53 2.53 Inf Inf
## 2025-11-09 21.24 0.64 21.24 21.24 Inf Inf
## 2025-11-10 62.43 0.74 Inf Inf Inf Inf
## 2025-11-11 55.69 1.18 Inf Inf Inf Inf
## 2025-11-12 47.20 1.12 Inf 47.20 Inf Inf
## 2025-11-13 44.75 0.75 Inf 22.38 Inf Inf
## 2025-11-14 9.44 0.35 Inf 9.44 Inf 9.44
## 2025-11-15 2.98 0.25 Inf Inf Inf Inf
## 2025-11-16 0.78 0.16 Inf 0.78 Inf Inf
## 2025-11-17 5.24 0.26 Inf Inf Inf Inf
## 2025-11-18 66.00 0.43 66.00 66.00 Inf Inf
## 2025-11-19 117.13 0.56 39.04 39.04 Inf Inf
## 2025-11-20 171.89 1.12 34.38 34.38 171.89 171.89cetrend_processed <- cetrend
cetrend_processed$Date <- alldates
cetrend_processed[cetrend_processed$CPE == Inf, "CPE"] <- NA
cetrend_processed[cetrend_processed$CPE_Integrated == Inf, "CPE_Integrated"] <- NA
cetrend_processed[cetrend_processed$CPCon == Inf, "CPCon"] <- NA
cetrend_processed[cetrend_processed$CPCon_Integrated == Inf, "CPCon_Integrated"] <- NA
#visualization
p1 <- ggplot(cetrend_processed, aes(x = Date)) +
geom_line(aes(y = daily_cost), color = "green", linewidth = .5) + # Adding color aesthetic mapped to "CPC"
scale_y_continuous(
name = "Daily Cost", # Label for left y-axis
) +
labs(title = "Daily Cost over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p2 <- ggplot(cetrend_processed, aes(x = Date)) +
geom_line(aes(y = CPC), color = "blue", linewidth = .5) + # Adding color aesthetic mapped to "CPC"
scale_y_continuous(
name = "CPC", # Label for left y-axis
) +
labs(title = "CPC over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p3 <- ggplot(cetrend_processed, aes(x = Date)) +
geom_line(aes(y = CPE), color = "red", linewidth = .5) +
geom_point(aes(y = CPE), color = "red", size = 1.5) +
scale_y_continuous(
name = "Cost Per Entry(ads only)", # Label for left y-axis
) +
labs(title = "Cost per DM Entry(ads only) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p4 <- ggplot(cetrend_processed, aes(x = Date)) +
geom_line(aes(y = CPE_Integrated), color = "purple", linewidth = .5) +
geom_point(aes(y = CPE_Integrated), color = "purple", size = 1.5) +
scale_y_continuous(
name = "CPE(Integrated)", # Label for left y-axis
) +
labs(title = "Cost per DM Entry(Integrated) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p5 <- ggplot(cetrend_processed, aes(x = Date)) +
geom_line(aes(y = CPCon), color = "orange", linewidth = .5) +
geom_point(aes(y = CPCon), color = "orange", size = 1.5) +
scale_y_continuous(
name = "Cost Per Contact(ads only)", # Label for left y-axis
) +
labs(title = "Cost per DM Contact(ads only) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p6 <- ggplot(cetrend_processed, aes(x = Date)) +
geom_line(aes(y = CPCon_Integrated), color = "pink", linewidth = .5) +
geom_point(aes(y = CPCon_Integrated), color = "pink", size = 1.5) +
scale_y_continuous(
name = "Cost Per Contact(Integrated)", # Label for left y-axis
) +
labs(title = "Cost per DM Contact(Integrated) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
# grid.arrange(p2, p3)
p1
p2
p3## Warning: Removed 8 rows containing missing values or values outside the scale range
## (`geom_point()`).
p4## Warning: Removed 4 rows containing missing values or values outside the scale range
## (`geom_point()`).
p5## Warning: Removed 13 rows containing missing values or values outside the scale range
## (`geom_line()`).## `geom_line()`: Each group consists of only one observation.
## ℹ Do you need to adjust the group aesthetic?## Warning: Removed 13 rows containing missing values or values outside the scale range
## (`geom_point()`).
p6## Warning: Removed 7 rows containing missing values or values outside the scale range
## (`geom_line()`).## Warning: Removed 12 rows containing missing values or values outside the scale range
## (`geom_point()`).
Conversion Rate
Overall conversion rate last week
averageExposure <- round(sum(data_lastweek$Exposure) / length(lastweek), 2)
averageClick <- round(sum(data_lastweek$Click) / length(lastweek), 2)
averageCR <- round(sum(data_lastweek$Click) / sum(data_lastweek$Exposure) * 100, 2)
averageER <- round(sum(data_lastweek$DM_Entry) / sum(data_lastweek$Click) * 100, 2)
averageERIntegrated <- round(sum(data_lastweek$DM_Entry, natural_lastweek$DM_Entry) / sum(data_lastweek$Click) * 100, 2)
averageConR <- ifelse(sum(data_lastweek$DM_Contact) == 0, 0, round(sum(data_lastweek$DM_Contact) / sum(data_lastweek$DM_Entry) * 100, 2))
averageConRIntegrated <- ifelse(sum(data_lastweek$DM_Contact, natural_lastweek$DM_Contact) == 0, 0,
round(sum(data_lastweek$DM_Contact, natural_lastweek$DM_Contact) / sum(data_lastweek$DM_Entry, natural_lastweek$DM_Entry) * 100, 2))
averageCT <- averageCR * averageER * averageConR * 10^-4
averageCTIntegrated <- averageCR * averageERIntegrated * averageConRIntegrated * 10^-4
cat("The average Daily Exposure was", averageExposure, "over the last week.\n
The average Daily Click was", averageClick, "over the last week.\n
The average CR was", averageCR, "% over the last week.\n
The average DM Entry Rate(ads only) was", averageER, "% over the last week.\n
The average DM Entry Rate(Integrated) was", averageERIntegrated, "% over the last week.\n
The average DM Contact Rate(ads only) was", averageConR, "% over the last week.\n
The average DM Contact Rate(Integrated) was", averageConRIntegrated, "% over the last week.\n
The average Conversion Tunnel(ads only) was", averageCT, "% over the last week, meaning that there will be one conversion per", round(1 / averageCT * 100, 0), "ads exposure.\n
The average Conversion Tunnel(Integrated) was", averageCTIntegrated, "% over the last week, meaning that there will be one conversion per", round(1 / averageCTIntegrated * 100, 0), "exposure.\n")## The average Daily Exposure was 1123.71 over the last week.
##
## The average Daily Click was 82.57 over the last week.
##
## The average CR was 7.35 % over the last week.
##
## The average DM Entry Rate(ads only) was 1.56 % over the last week.
##
## The average DM Entry Rate(Integrated) was 1.9 % over the last week.
##
## The average DM Contact Rate(ads only) was 11.11 % over the last week.
##
## The average DM Contact Rate(Integrated) was 18.18 % over the last week.
##
## The average Conversion Tunnel(ads only) was 0.01273873 % over the last week, meaning that there will be one conversion per 7850 ads exposure.
##
## The average Conversion Tunnel(Integrated) was 0.02538837 % over the last week, meaning that there will be one conversion per 3939 exposure.Conversion rate daily trend
colnames <- c("Exposure", "Click", "CR", "ER", "ER_Integrated", "ConR", "ConR_Integrated")
crtrend <- as.data.frame(matrix(numeric(0), length(alldates), length(colnames)))
rownames(crtrend) <- alldates
colnames(crtrend) <- colnames
CP <- function(df, date, tocalculate){
df <- df[df$Date == date, ]
if(tocalculate == "Exposure"){
sum(df$Exposure)
} else if (tocalculate == "Click"){
sum(df$Click)
} else if (tocalculate == "CR"){
round(sum(df$Click) / sum(df$Exposure) * 100, 2)
} else if (tocalculate == "ER"){
round(ifelse(sum(df$DM_Entry) == 0, 0, sum(df$DM_Entry) / sum(df$Click) * 100), 2)
} else if (tocalculate == "ER_Integrated"){
round(ifelse(sum(df$DM_Entry, natural[natural$Date == date, "DM_Entry"][[1]]) == 0, 0, sum(df$DM_Entry, natural[natural$Date == date, "DM_Entry"][[1]]) / sum(df$Click) * 100), 2)
} else if (tocalculate == "ConR"){
round(ifelse(sum(df$DM_Contact) == 0, 0, sum(df$DM_Contact) / sum(df$DM_Entry) * 100), 2)
} else if (tocalculate == "ConR_Integrated"){
round(ifelse(sum(df$DM_Contact, natural[natural$Date == date, "DM_Contact"][[1]]) == 0, 0, sum(df$DM_Contact, natural[natural$Date == date, "DM_Contact"][[1]]) / sum(df$DM_Entry, natural[natural$Date == date, "DM_Entry"][[1]]) * 100), 2)
}
}
for(i in 1:length(alldates)){
currentdate <- alldates[i]
for(j in 1:length(colnames)){
crtrend[i, j] <- CP(data, currentdate, colnames[j])
}
}
crtrend## Exposure Click CR ER ER_Integrated ConR ConR_Integrated
## 2025-11-07 971 18 1.85 5.56 5.56 0 0
## 2025-11-08 2112 50 2.37 6.00 6.00 0 0
## 2025-11-09 1039 33 3.18 3.03 3.03 0 0
## 2025-11-10 2142 84 3.92 0.00 0.00 0 0
## 2025-11-11 1218 47 3.86 0.00 0.00 0 0
## 2025-11-12 981 42 4.28 0.00 2.38 0 0
## 2025-11-13 1631 60 3.68 0.00 3.33 0 0
## 2025-11-14 583 27 4.63 0.00 3.70 0 100
## 2025-11-15 174 12 6.90 0.00 0.00 0 0
## 2025-11-16 70 5 7.14 0.00 20.00 0 0
## 2025-11-17 262 20 7.63 0.00 0.00 0 0
## 2025-11-18 2041 152 7.45 0.66 0.66 0 0
## 2025-11-19 2822 208 7.37 1.44 1.44 0 0
## 2025-11-20 1914 154 8.05 3.25 3.25 20 20crtrend_processed <- crtrend
crtrend_processed$Date <- alldates
#visualization
p1 <- ggplot(crtrend_processed, aes(x = Date)) +
geom_line(aes(y = Exposure), color = "green", linewidth = .5) +
scale_y_continuous(
name = "Exposure", # Label for left y-axis
) +
labs(title = "Daily Exposure over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p2 <- ggplot(crtrend_processed, aes(x = Date)) +
geom_line(aes(y = Click), color = "orange", linewidth = .5) +
scale_y_continuous(
name = "Click", # Label for left y-axis
) +
labs(title = "Daily Click over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p3 <- ggplot(crtrend_processed, aes(x = Date)) +
geom_line(aes(y = CR), color = "blue", linewidth = .5) +
scale_y_continuous(
name = "Click Rate", # Label for left y-axis
) +
labs(title = "Click Rate over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p4 <- ggplot(crtrend_processed, aes(x = Date)) +
geom_line(aes(y = ER), color = "red", linewidth = .5) +
scale_y_continuous(
name = "DM Entry Rate(ads only)", # Label for left y-axis
) +
labs(title = "DM Entry Rate(ads only) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p5 <- ggplot(crtrend_processed, aes(x = Date)) +
geom_line(aes(y = ER_Integrated), color = "pink", linewidth = .5) +
scale_y_continuous(
name = "DM Entry Rate(Integrated)", # Label for left y-axis
) +
labs(title = "DM Entry Rate(Integrated) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p6 <- ggplot(crtrend_processed, aes(x = Date)) +
geom_line(aes(y = ConR), color = "purple", linewidth = .5) +
scale_y_continuous(
name = "DM Contact Rate(ads only)", # Label for left y-axis
) +
labs(title = "DM Contact Rate(ads only) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
p7 <- ggplot(crtrend_processed, aes(x = Date)) +
geom_line(aes(y = ConR_Integrated), color = "yellow", linewidth = .5) +
scale_y_continuous(
name = "DM Contact Rate(Integrated)", # Label for left y-axis
) +
labs(title = "DM Contact Rate(Integrated) over Dates") + # Legend title
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Optional: Rotate x-axis labels for readability
# grid.arrange(p2, p3)
p1
p2
p3
p4
p5
p6
p7
Note Analysis
Overall performance of each note over last week
allnotes <- unique(data_lastweek$Note)
colnames <- c("Daily_Cost", "CPC", "Daily_Click", "CR", "Daily_Entry", "ER")
notescomparison <- as.data.frame(matrix(numeric(0), length(allnotes), length(colnames)))
rownames(notescomparison) <- allnotes
colnames(notescomparison) <- colnames
CP <- function(df, note, tocalculate){
df <- df[df$Note == note, ]
days <- length(unique(df$Date))
if(tocalculate == "Daily_Cost"){
round(sum(df$Cost) / days, 2)
} else if (tocalculate == "Daily_Click"){
round(sum(df$Click) / days, 2)
} else if (tocalculate == "CPC"){
round(sum(df$Cost) / sum(df$Click), 2)
} else if (tocalculate == "CR"){
round(sum(df$Click) / sum(df$Exposure) * 100, 2)
} else if (tocalculate == "Daily_Entry"){
round(sum(df$DM_Entry) / days, 2)
} else if (tocalculate == "ER"){
round(ifelse(sum(df$Click) == 0, 0, sum(df$DM_Entry) / sum(df$Click) * 100), 2)
}
}
for(i in 1:length(allnotes)){
currentnote <- allnotes[i]
for(j in 1:length(colnames)){
notescomparison[i, j] <- CP(data_lastweek, currentnote, colnames[j])
}
}
notescomparison## Daily_Cost CPC Daily_Click CR Daily_Entry ER
## 1111 0.00 NaN 0.00 0.00 0.00 0.00
## happy_boss 5.53 3.16 1.75 6.09 0.00 0.00
## official_cat 10.25 1.35 7.57 6.87 0.43 5.66
## official_logo 3.32 0.42 7.86 5.27 0.14 1.82
## logo_wall 0.00 NaN 0.00 0.00 0.00 0.00
## CP_cat 3.43 0.43 8.00 6.29 0.00 0.00
## CR_dog 6.47 2.28 2.83 5.54 0.00 0.00
## night_shift_cat 3.40 0.59 5.71 7.18 0.00 0.00
## cheap_cat 20.76 0.46 45.57 8.93 0.57 1.25
## official_copied 3.48 0.79 4.43 5.22 0.14 3.23notescomparison_processed <- notescomparison
notescomparison_processed$Note <- factor(
allnotes,
levels = allnotes
)
# visualization
p1 <- ggplot(notescomparison_processed, aes(x = Note, y = CPC)) +
geom_col(fill = "green", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "CPC") + # Label for y-axis
labs(title = "CPC of Each Note") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p2 <- ggplot(notescomparison_processed, aes(x = Note, y = CR)) +
geom_col(fill = "red", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "CR") + # Label for y-axis
labs(title = "CR of Each Note") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p3 <- ggplot(notescomparison_processed, aes(x = Note, y = ER)) +
geom_col(fill = "orange", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "ER") + # Label for y-axis
labs(title = "DM Entry Rate of Each Note") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p4 <- ggplot(notescomparison_processed, aes(x = Note, y = Daily_Click)) +
geom_col(fill = "blue", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "Daily_Click") + # Label for y-axis
labs(title = "Daily Click of Each Note (Disregarding the effect of Plan)") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p5 <- ggplot(notescomparison_processed, aes(x = Note, y = Daily_Entry)) +
geom_col(fill = "purple", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "Daily_Entry") + # Label for y-axis
labs(title = "Daily DM Entry of Each Note (Disregarding the effect of Plan)") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p1## Warning: Removed 2 rows containing missing values or values outside the scale range
## (`geom_col()`).
p2
p3
p4
p5
Plan Analysis
Overall performance of each plan over last week
allplans <- unique(data_lastweek$Plan)
colnames <- c("Daily_Cost", "CPC", "Daily_Click", "CR", "Daily_Entry", "ER", "Daily_Contact", "ConR")
planscomparison <- as.data.frame(matrix(numeric(0), length(allplans), length(colnames)))
rownames(planscomparison) <- allplans
colnames(planscomparison) <- colnames
CP <- function(df, plan, tocalculate){
df <- df[df$Plan == plan, ]
days <- length(unique(df$Date))
if(tocalculate == "Daily_Cost"){
round(sum(df$Cost) / days, 2)
} else if (tocalculate == "Daily_Click"){
round(sum(df$Click) / days, 2)
} else if (tocalculate == "CPC"){
round(sum(df$Cost) / sum(df$Click), 2)
} else if (tocalculate == "CR"){
round(sum(df$Click) / sum(df$Exposure) * 100, 2)
} else if (tocalculate == "Daily_Entry"){
round(sum(df$DM_Entry) / days, 2)
} else if (tocalculate == "ER"){
round(ifelse(sum(df$Click) == 0, 0, sum(df$DM_Entry) / sum(df$Click) * 100), 2)
} else if (tocalculate == "Daily_Contact"){
round(sum(df$DM_Contact) / days, 2)
} else if (tocalculate == "ConR"){
round(ifelse(sum(df$DM_Contact) == 0, 0, sum(df$DM_Contact) / sum(df$DM_Entry) * 100), 2)
}
}
for(i in 1:length(allplans)){
currentplan <- allplans[i]
for(j in 1:length(colnames)){
planscomparison[i, j] <- CP(data_lastweek, currentplan, colnames[j])
}
}
CP(data_lastweek, "251107_Search", "ConR")## [1] 0planscomparison## Daily_Cost CPC Daily_Click CR Daily_Entry ER
## 251107_Search 0.81 0.23 3.50 5.02 0.00 0.00
## 251107_Push 0.00 NaN 0.00 0.00 0.00 0.00
## 251113_Push 0.50 0.22 2.25 2.62 0.00 0.00
## 251113_Search 3.30 0.32 10.25 9.01 0.00 0.00
## 251118_Cheap_Search 40.03 0.83 48.33 6.75 1.00 2.07
## 251118_Cheap_Push 0.61 0.92 0.67 2.53 0.33 50.00
## 251118_Pricy_Push 13.40 6.70 2.00 5.17 0.33 16.67
## 251118_Pricy_Search 64.31 0.53 120.33 8.14 1.33 1.11
## Daily_Contact ConR
## 251107_Search 0.00 0
## 251107_Push 0.00 0
## 251113_Push 0.00 0
## 251113_Search 0.00 0
## 251118_Cheap_Search 0.00 0
## 251118_Cheap_Push 0.00 0
## 251118_Pricy_Push 0.00 0
## 251118_Pricy_Search 0.33 25planscomparison_processed <- planscomparison
planscomparison_processed$Plan <- allplans
# visualization
p1 <- ggplot(planscomparison_processed, aes(x = Plan, y = CPC)) +
geom_col(fill = "green", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "CPC") + # Label for y-axis
labs(title = "CPC of Each Plan") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p2 <- ggplot(planscomparison_processed, aes(x = Plan, y = CR)) +
geom_col(fill = "red", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "CR") + # Label for y-axis
labs(title = "CR of Each Plan") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p3 <- ggplot(planscomparison_processed, aes(x = Plan, y = ER)) +
geom_col(fill = "orange", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "DM Entry Rate") + # Label for y-axis
labs(title = "DM Entry Rate of Each Plan") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p4 <- ggplot(planscomparison_processed, aes(x = Plan, y = ConR)) +
geom_col(fill = "yellow", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "DM Contact Rate") + # Label for y-axis
labs(title = "DM Contact Rate of Each Plan") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p5 <- ggplot(planscomparison_processed, aes(x = Plan, y = Daily_Click)) +
geom_col(fill = "blue", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "Daily_Click") + # Label for y-axis
labs(title = "Daily Click of Each Plan (Disregarding the effect of Price)") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p6 <- ggplot(planscomparison_processed, aes(x = Plan, y = Daily_Entry)) +
geom_col(fill = "purple", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "Daily DM Entry") + # Label for y-axis
labs(title = "Daily DM Entry of Each Plan (Disregarding the effect of Price)") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p7 <- ggplot(planscomparison_processed, aes(x = Plan, y = Daily_Contact)) +
geom_col(fill = "pink", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "Daily DM Contact") + # Label for y-axis
labs(title = "Daily DM Contact of Each Plan (Disregarding the effect of Price)") + # Title for the plot
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for readability
p1## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_col()`).
p2
p3
p4
p5
p6
p7