xhs Data Analysis
2025-11-25
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 86.05 RMB over the last week.
##
## The average CPC was 0.75 RMB over the last week.
##
## The average Cost per DM Entry(ads only) was 40.16 RMB over the last week.
##
## The average Cost per DM Entry(Integrated) was 40.16 RMB over the last week.
##
## The average Cost per DM Contact(ads only) was 602.37 RMB over the last week.
##
## The average Cost per DM Contact(Integrated) was 602.37 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.89
## 2025-11-21 117.96 1.33 39.32 39.32 Inf Inf
## 2025-11-22 23.46 0.56 Inf Inf Inf Inf
## 2025-11-23 34.51 0.82 34.51 34.51 Inf Inf
## 2025-11-24 71.42 0.62 35.71 35.71 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 Trend") + # 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 Trend") + # 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) Trend") + # 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) Trend") + # 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) Trend") + # 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) Trend") + # 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 9 rows containing missing values or values outside the scale range
## (`geom_point()`).
p4## Warning: Removed 5 rows containing missing values or values outside the scale range
## (`geom_point()`).
p5## Warning: Removed 17 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 17 rows containing missing values or values outside the scale range
## (`geom_point()`).
p6## Warning: Removed 11 rows containing missing values or values outside the scale range
## (`geom_line()`).## Warning: Removed 16 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 1537.29 over the last week.
##
## The average Daily Click was 114.71 over the last week.
##
## The average CR was 7.46 % over the last week.
##
## The average DM Entry Rate(ads only) was 1.87 % over the last week.
##
## The average DM Entry Rate(Integrated) was 1.87 % over the last week.
##
## The average DM Contact Rate(ads only) was 6.67 % over the last week.
##
## The average DM Contact Rate(Integrated) was 6.67 % over the last week.
##
## The average Conversion Tunnel(ads only) was 0.009304783 % over the last week, meaning that there will be one conversion per 10747 ads exposure.
##
## The average Conversion Tunnel(Integrated) was 0.009304783 % over the last week, meaning that there will be one conversion per 10747 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 20
## 2025-11-21 1107 89 8.04 3.37 3.37 0 0
## 2025-11-22 508 42 8.27 0.00 0.00 0 0
## 2025-11-23 673 42 6.24 2.38 2.38 0 0
## 2025-11-24 1696 116 6.84 1.72 1.72 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 Trend") + # 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 Trend") + # 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 Trend") + # 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) Trend") + # 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) Trend") + # 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) Trend") + # 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) Trend") + # 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("CostYesterday", "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){
yesterday <- max(df$Date)
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)
} else if (tocalculate == "CostYesterday"){
df <- df[df$Date == yesterday, ]
ifelse(nrow(df) == 0, 0, sum(df$Cost))
}
}
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## CostYesterday Daily_Cost CPC Daily_Click CR Daily_Entry ER
## 1111 0.00 0.00 NaN 0.00 0.00 0.00 0.00
## happy_boss 0.00 3.69 2.77 1.33 6.40 0.00 0.00
## official_cat 5.78 11.48 1.18 9.71 6.04 0.43 4.41
## official_logo 2.20 3.58 0.43 8.29 5.79 0.29 3.45
## CP_cat 16.99 11.34 0.79 14.29 6.44 0.29 2.00
## CR_dog 0.00 5.54 2.59 2.14 5.68 0.00 0.00
## night_shift_cat 1.81 2.95 0.47 6.29 7.24 0.00 0.00
## cheap_cat 38.79 42.90 0.65 66.43 9.02 1.00 1.51
## official_copied 5.85 5.11 0.79 6.43 4.85 0.14 2.22notescomparison_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
p6 <- ggplot(notescomparison_processed, aes(x = "", y = CostYesterday, fill = Note)) +
geom_col(width = 0.5) +
coord_polar(theta = "y") +
theme_void() +
labs(title = "Money Consumed by Each Note Yesterday")
p6
p1## Warning: Removed 1 row 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("CostYesterday", "Daily_Cost", "CPC", "Daily_Click", "CR", "Daily_Entry", "ER", "CostPerEntry", "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){
yesterday <- max(df$Date)
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)
} else if (tocalculate == "CostPerEntry"){
round(sum(df$Cost) / sum(df$DM_Entry), 2)
} else if (tocalculate == "CostYesterday"){
df <- df[df$Date == yesterday, ]
ifelse(nrow(df) == 0, 0, sum(df$Cost))
}
}
for(i in 1:length(allplans)){
currentplan <- allplans[i]
for(j in 1:length(colnames)){
planscomparison[i, j] <- CP(data_lastweek, currentplan, colnames[j])
}
}
planscomparison## CostYesterday Daily_Cost CPC Daily_Click CR Daily_Entry
## 251118_Cheap_Search 3.77 18.54 0.74 25.14 6.65 0.43
## 251118_Cheap_Push 0.00 0.35 0.52 0.67 4.88 0.17
## 251118_Pricy_Push 0.00 5.74 6.70 0.86 4.51 0.14
## 251118_Pricy_Search 67.65 61.48 0.70 88.14 7.81 1.43
## ER CostPerEntry Daily_Contact ConR
## 251118_Cheap_Search 1.70 43.26 0.00 0
## 251118_Cheap_Push 25.00 2.08 0.00 0
## 251118_Pricy_Push 16.67 40.19 0.00 0
## 251118_Pricy_Search 1.62 43.03 0.14 10planscomparison_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
p8 <- ggplot(planscomparison_processed, aes(x = Plan, y = CostPerEntry)) +
geom_col(fill = "darkgreen", width = .5) + # Bar plot for CPC with green color
scale_y_continuous(name = "Cost Per DM Entry") + # Label for y-axis
labs(title = "Cost Per DM Entry 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
p9 <- ggplot(planscomparison_processed, aes(x = "", y = CostYesterday, fill = Plan)) +
geom_col(width = 0.5) +
coord_polar(theta = "y") +
theme_void() +
labs(title = "Money Consumed by Each Plan Yesterday")
p9
p1
p2
p3
p8
p4
p5
p6
p7