xhs Data Analysis
2025-11-18
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 23.73 RMB over the last week.
##
## The average CPC was 0.78 RMB over the last week.
##
## The average Cost per DM Entry(ads only) was Inf RMB over the last week.
##
## The average Cost per DM Entry(Integrated) was 33.22 RMB over the last week.
##
## The average Cost per DM Contact(ads only) was Inf RMB over the last week.
##
## The average Cost per DM Contact(Integrated) was 166.08 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 Infcetrend_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_line()`).## Warning: Removed 8 rows containing missing values or values outside the scale range
## (`geom_point()`).
p4## Warning: Removed 1 row containing missing values or values outside the scale range
## (`geom_line()`).## Warning: Removed 4 rows containing missing values or values outside the scale range
## (`geom_point()`).
p5## Warning: Removed 11 rows containing missing values or values outside the scale range
## (`geom_line()`).## Warning: Removed 11 rows containing missing values or values outside the scale range
## (`geom_point()`).
p6## Warning: Removed 10 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 10 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", 1 / averageCT, "ads exposure.\n
The average Conversion Tunnel(Integrated) was", averageCTIntegrated, "% over the last week, meaning that there will be one conversion per", 1 / averageCTIntegrated, "exposure.\n")## The average Daily Exposure was 702.71 over the last week.
##
## The average Daily Click was 30.43 over the last week.
##
## The average CR was 4.33 % over the last week.
##
## The average DM Entry Rate(ads only) was 0 % over the last week.
##
## The average DM Entry Rate(Integrated) was 2.35 % over the last week.
##
## The average DM Contact Rate(ads only) was 0 % over the last week.
##
## The average DM Contact Rate(Integrated) was 20 % over the last week.
##
## The average Conversion Tunnel(ads only) was 0 % over the last week, meaning that there will be one conversion per Inf ads exposure.
##
## The average Conversion Tunnel(Integrated) was 0.020351 % over the last week, meaning that there will be one conversion per 49.13763 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$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 0crtrend_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.41 1.24 0.33 3.39 0 0
## happy_boss 0.17 0.33 0.50 1.72 0 0
## official_cat 4.53 0.83 5.43 3.50 0 0
## official_logo 13.94 1.02 13.71 4.70 0 0
## logo_wall 1.92 1.10 1.75 1.99 0 0
## CP_cat 1.43 0.72 2.00 3.65 0 0
## CR_dog 0.01 0.02 0.50 2.17 0 0
## night_shift_cat 1.30 0.81 1.60 4.76 0 0
## cheap_cat 2.20 0.24 9.00 7.02 0 0
## official_copied 0.25 0.42 0.60 3.37 0 0notescomparison_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
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 Daily_Contact
## 251107_Search 14.95 0.86 17.29 4.41 0 0 0
## 251107_Push 5.91 1.48 4.00 2.63 0 0 0
## 251113_Push 1.49 0.47 3.20 2.67 0 0 0
## 251113_Search 3.70 0.36 10.40 7.83 0 0 0
## ConR
## 251107_Search 0
## 251107_Push 0
## 251113_Push 0
## 251113_Search 0planscomparison_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
p2
p3
p4
p5
p6
p7