feedback <- read_csv("https://github.com/course-files/DataVisualization/raw/refs/heads/main/data/siwaka_dishes_view_customerfeedback_data.csv")
## Rows: 10888 Columns: 43
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (21): customer_customerName, customer_contactFirstName, customer_contac...
## dbl (19): customer_customerNumber, customer_postalCode, customer_status, cu...
## dttm (3): customerorder_orderDate, customerorder_requiredDate, customerorde...
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
customers <- read_csv("https://github.com/course-files/DataVisualization/raw/refs/heads/main/data/siwaka_dishes_customer.csv")
## Rows: 770 Columns: 14
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (11): customer_customerName, customer_customerType, customer_contactFirs...
## dbl (3): customer_customerNumber, customer_postalCode, customer_status
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
head(feedback)
## # A tibble: 6 × 43
## customer_customerNumber customer_customerName customer_contactFirstName
## <dbl> <chr> <chr>
## 1 111 Achieng Ochieng Achieng
## 2 491 Michel Bizimana Michel
## 3 491 Michel Bizimana Michel
## 4 491 Michel Bizimana Michel
## 5 711 [Business] Ole Sereni Lodge Mwangi
## 6 711 [Business] Ole Sereni Lodge Mwangi
## # ℹ 40 more variables: customer_contactLastName <chr>, customer_phone <chr>,
## # customer_addressLine1 <chr>, customer_addressLine2 <chr>,
## # customer_postalCode <dbl>, customer_county <chr>, customer_subCounty <chr>,
## # customer_status <dbl>, customerorder_orderNumber <dbl>,
## # customerorder_orderDate <dttm>, customerorder_requiredDate <dttm>,
## # customerorder_dispatchDate <dttm>, customerorder_orderStatusID <dbl>,
## # customerorder_customerNumber <dbl>, …
head(customers)
## # A tibble: 6 × 14
## customer_customerNumber customer_customerName customer_customerType
## <dbl> <chr> <chr>
## 1 1 Njeri Kagame Individual
## 2 2 Felicite Zuma Individual
## 3 3 [Business] Swahili Hotel Business
## 4 4 [Business] Sarova Lodge Business
## 5 5 [Business] Swahili Manor Business
## 6 6 [Business] Leopard Beach Suites Business
## # ℹ 11 more variables: customer_contactFirstName <chr>,
## # customer_contactLastName <chr>, customer_phone <chr>,
## # customer_addressLine1 <chr>, customer_addressLine2 <chr>,
## # customer_postalCode <dbl>, customer_county <chr>, customer_subCounty <chr>,
## # customer_customerLocation <chr>, customer_status <dbl>,
## # customer_statusText <chr>
merged_data <- left_join(feedback, customers, by = "customer_customerNumber")
# Check actual column names first
colnames(merged_data)
## [1] "customer_customerNumber" "customer_customerName.x"
## [3] "customer_contactFirstName.x" "customer_contactLastName.x"
## [5] "customer_phone.x" "customer_addressLine1.x"
## [7] "customer_addressLine2.x" "customer_postalCode.x"
## [9] "customer_county.x" "customer_subCounty.x"
## [11] "customer_status.x" "customerorder_orderNumber"
## [13] "customerorder_orderDate" "customerorder_requiredDate"
## [15] "customerorder_dispatchDate" "customerorder_orderStatusID"
## [17] "customerorder_customerNumber" "customerfeedback_customerfeedbackID"
## [19] "customerfeedback_foodquality" "customerfeedback_servicequality"
## [21] "customerfeedback_pricetovalue" "customerfeedback_ambiance"
## [23] "customerfeedback_comment" "orderdetail_productCode"
## [25] "orderdetail_quantityOrdered" "orderdetail_priceEach"
## [27] "product_productName" "product_productDescription"
## [29] "product_quantityInStock" "product_costOfProduction"
## [31] "product_sellingPrice" "productcategory_productCategoryID"
## [33] "productcategory_categoryName" "productcategory_categoryDescription"
## [35] "orderstatus_orderStatusID" "orderstatus_status"
## [37] "branch_branchCode" "branch_phone"
## [39] "branch_addressLine1" "branch_addressLine2"
## [41] "branch_postalCode" "branch_county"
## [43] "branch_subCounty" "customer_customerName.y"
## [45] "customer_customerType" "customer_contactFirstName.y"
## [47] "customer_contactLastName.y" "customer_phone.y"
## [49] "customer_addressLine1.y" "customer_addressLine2.y"
## [51] "customer_postalCode.y" "customer_county.y"
## [53] "customer_subCounty.y" "customer_customerLocation"
## [55] "customer_status.y" "customer_statusText"
# Then create binary column using correct column name
merged_data <- merged_data %>%
mutate(customer_status_binary = ifelse(customer_status.x == "active", 1, 0))
merged_data <- merged_data %>%
mutate(customer_status_binary = ifelse(customer_status.y == "active", 1, 0))
model <- glm(customer_status_binary ~ customerfeedback_foodquality +
customerfeedback_servicequality +
customerfeedback_pricetovalue +
customerfeedback_ambiance,
data = merged_data, family = "binomial")
## Warning: glm.fit: algorithm did not converge
summary(model)
##
## Call:
## glm(formula = customer_status_binary ~ customerfeedback_foodquality +
## customerfeedback_servicequality + customerfeedback_pricetovalue +
## customerfeedback_ambiance, family = "binomial", data = merged_data)
##
## Coefficients:
## Estimate Std. Error z value Pr(>|z|)
## (Intercept) -2.657e+01 8.580e+03 -0.003 0.998
## customerfeedback_foodquality -5.395e-13 8.586e+03 0.000 1.000
## customerfeedback_servicequality 3.212e-13 6.086e+03 0.000 1.000
## customerfeedback_pricetovalue -1.523e-13 6.142e+03 0.000 1.000
## customerfeedback_ambiance 3.376e-13 6.186e+03 0.000 1.000
##
## (Dispersion parameter for binomial family taken to be 1)
##
## Null deviance: 0.0000e+00 on 10887 degrees of freedom
## Residual deviance: 6.3168e-08 on 10883 degrees of freedom
## AIC: 10
##
## Number of Fisher Scoring iterations: 25
merged_data <- left_join(feedback, customers, by = "customer_customerNumber")
merged_data <- merged_data %>%
mutate(customer_status_binary = ifelse(customer_status.y == "active", 1, 0))
clean_data <- merged_data %>%
filter(
!is.na(customer_status_binary),
!is.na(customerfeedback_foodquality),
!is.na(customerfeedback_servicequality),
!is.na(customerfeedback_pricetovalue),
!is.na(customerfeedback_ambiance)
)
model <- glm(customer_status_binary ~ customerfeedback_foodquality +
customerfeedback_servicequality +
customerfeedback_pricetovalue +
customerfeedback_ambiance,
data = clean_data, family = "binomial")
## Warning: glm.fit: algorithm did not converge
summary(model)
##
## Call:
## glm(formula = customer_status_binary ~ customerfeedback_foodquality +
## customerfeedback_servicequality + customerfeedback_pricetovalue +
## customerfeedback_ambiance, family = "binomial", data = clean_data)
##
## Coefficients:
## Estimate Std. Error z value Pr(>|z|)
## (Intercept) -2.657e+01 8.580e+03 -0.003 0.998
## customerfeedback_foodquality -5.395e-13 8.586e+03 0.000 1.000
## customerfeedback_servicequality 3.212e-13 6.086e+03 0.000 1.000
## customerfeedback_pricetovalue -1.523e-13 6.142e+03 0.000 1.000
## customerfeedback_ambiance 3.376e-13 6.186e+03 0.000 1.000
##
## (Dispersion parameter for binomial family taken to be 1)
##
## Null deviance: 0.0000e+00 on 10887 degrees of freedom
## Residual deviance: 6.3168e-08 on 10883 degrees of freedom
## AIC: 10
##
## Number of Fisher Scoring iterations: 25
table(clean_data$customer_status_binary)
##
## 0
## 10888
# Download the customer feedback data
download.file(
url = "https://github.com/course-files/DataVisualization/raw/refs/heads/main/data/siwaka_dishes_view_customerfeedback_data.csv",
destfile = "data/siwaka_dishes_view_customerfeedback_data.csv",
mode = "wb"
)
# Download the customer info data
download.file(
url = "https://github.com/course-files/DataVisualization/raw/refs/heads/main/data/siwaka_dishes_customer.csv",
destfile = "data/siwaka_dishes_customer.csv",
mode = "wb"
)
feedback <- readr::read_csv("data/siwaka_dishes_view_customerfeedback_data.csv")
## Rows: 10888 Columns: 43
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (21): customer_customerName, customer_contactFirstName, customer_contac...
## dbl (19): customer_customerNumber, customer_postalCode, customer_status, cu...
## dttm (3): customerorder_orderDate, customerorder_requiredDate, customerorde...
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
customers <- readr::read_csv("data/siwaka_dishes_customer.csv")
## Rows: 770 Columns: 14
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (11): customer_customerName, customer_customerType, customer_contactFirs...
## dbl (3): customer_customerNumber, customer_postalCode, customer_status
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# Check the column names
colnames(feedback)
## [1] "customer_customerNumber" "customer_customerName"
## [3] "customer_contactFirstName" "customer_contactLastName"
## [5] "customer_phone" "customer_addressLine1"
## [7] "customer_addressLine2" "customer_postalCode"
## [9] "customer_county" "customer_subCounty"
## [11] "customer_status" "customerorder_orderNumber"
## [13] "customerorder_orderDate" "customerorder_requiredDate"
## [15] "customerorder_dispatchDate" "customerorder_orderStatusID"
## [17] "customerorder_customerNumber" "customerfeedback_customerfeedbackID"
## [19] "customerfeedback_foodquality" "customerfeedback_servicequality"
## [21] "customerfeedback_pricetovalue" "customerfeedback_ambiance"
## [23] "customerfeedback_comment" "orderdetail_productCode"
## [25] "orderdetail_quantityOrdered" "orderdetail_priceEach"
## [27] "product_productName" "product_productDescription"
## [29] "product_quantityInStock" "product_costOfProduction"
## [31] "product_sellingPrice" "productcategory_productCategoryID"
## [33] "productcategory_categoryName" "productcategory_categoryDescription"
## [35] "orderstatus_orderStatusID" "orderstatus_status"
## [37] "branch_branchCode" "branch_phone"
## [39] "branch_addressLine1" "branch_addressLine2"
## [41] "branch_postalCode" "branch_county"
## [43] "branch_subCounty"
colnames(customers)
## [1] "customer_customerNumber" "customer_customerName"
## [3] "customer_customerType" "customer_contactFirstName"
## [5] "customer_contactLastName" "customer_phone"
## [7] "customer_addressLine1" "customer_addressLine2"
## [9] "customer_postalCode" "customer_county"
## [11] "customer_subCounty" "customer_customerLocation"
## [13] "customer_status" "customer_statusText"
library(dplyr)
merged_data <- left_join(feedback, customers, by = "customer_customerNumber")
table(merged_data$customer_status)
## Warning: Unknown or uninitialised column: `customer_status`.
## < table of extent 0 >
colnames(merged_data)
## [1] "customer_customerNumber" "customer_customerName.x"
## [3] "customer_contactFirstName.x" "customer_contactLastName.x"
## [5] "customer_phone.x" "customer_addressLine1.x"
## [7] "customer_addressLine2.x" "customer_postalCode.x"
## [9] "customer_county.x" "customer_subCounty.x"
## [11] "customer_status.x" "customerorder_orderNumber"
## [13] "customerorder_orderDate" "customerorder_requiredDate"
## [15] "customerorder_dispatchDate" "customerorder_orderStatusID"
## [17] "customerorder_customerNumber" "customerfeedback_customerfeedbackID"
## [19] "customerfeedback_foodquality" "customerfeedback_servicequality"
## [21] "customerfeedback_pricetovalue" "customerfeedback_ambiance"
## [23] "customerfeedback_comment" "orderdetail_productCode"
## [25] "orderdetail_quantityOrdered" "orderdetail_priceEach"
## [27] "product_productName" "product_productDescription"
## [29] "product_quantityInStock" "product_costOfProduction"
## [31] "product_sellingPrice" "productcategory_productCategoryID"
## [33] "productcategory_categoryName" "productcategory_categoryDescription"
## [35] "orderstatus_orderStatusID" "orderstatus_status"
## [37] "branch_branchCode" "branch_phone"
## [39] "branch_addressLine1" "branch_addressLine2"
## [41] "branch_postalCode" "branch_county"
## [43] "branch_subCounty" "customer_customerName.y"
## [45] "customer_customerType" "customer_contactFirstName.y"
## [47] "customer_contactLastName.y" "customer_phone.y"
## [49] "customer_addressLine1.y" "customer_addressLine2.y"
## [51] "customer_postalCode.y" "customer_county.y"
## [53] "customer_subCounty.y" "customer_customerLocation"
## [55] "customer_status.y" "customer_statusText"
library(dplyr)
clean_data <- merged_data %>%
filter(!is.na(customer_status.y)) %>%
mutate(customer_status_binary = ifelse(customer_status.y == "active", 1, 0)) %>%
select(customer_status_binary,
customerfeedback_foodquality,
customerfeedback_servicequality,
customerfeedback_pricetovalue,
customerfeedback_ambiance)
clean_data <- na.omit(clean_data)
table(clean_data$customer_status_binary)
##
## 0
## 10888
unique(merged_data$customer_status.y)
## [1] 0 1
table(customers$customer_status)
##
## 0 1
## 384 386
customers <- customers %>%
mutate(
customer_status_clean = ifelse(customer_status == 1, "active", "dormant")
)
active_customers <- customers %>%
filter(customer_status_clean == "active")
table(active_customers$customer_customerNumber %in% feedback$customer_customerNumber)
##
## FALSE TRUE
## 1 385
# Load libraries
library(tidyverse)
# Load the data
feedback <- read_csv("data/siwaka_dishes_view_customerfeedback_data.csv")
## Rows: 10888 Columns: 43
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (21): customer_customerName, customer_contactFirstName, customer_contac...
## dbl (19): customer_customerNumber, customer_postalCode, customer_status, cu...
## dttm (3): customerorder_orderDate, customerorder_requiredDate, customerorde...
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
customers <- read_csv("data/siwaka_dishes_customer.csv")
## Rows: 770 Columns: 14
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (11): customer_customerName, customer_customerType, customer_contactFirs...
## dbl (3): customer_customerNumber, customer_postalCode, customer_status
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# Clean customer status
customers <- customers %>%
mutate(
customer_status_clean = ifelse(customer_status == 1, "active", "dormant")
)
# Merge feedback and customer data
merged_data <- feedback %>%
left_join(customers, by = c("customer_customerNumber" = "customer_customerNumber"))
# Filter relevant columns and convert status to binary
clean_data <- merged_data %>%
filter(!is.na(customer_status.y)) %>%
mutate(
customer_status_binary = ifelse(customer_status.y == 1, 1, 0)
) %>%
select(customer_status_binary,
customerfeedback_foodquality,
customerfeedback_servicequality,
customerfeedback_pricetovalue,
customerfeedback_ambiance)
# Check balance of active vs dormant
table(clean_data$customer_status_binary)
##
## 0 1
## 5453 5435
model <- glm(customer_status_binary ~ customerfeedback_foodquality +
customerfeedback_servicequality +
customerfeedback_pricetovalue +
customerfeedback_ambiance,
data = clean_data, family = binomial)
summary(model)
##
## Call:
## glm(formula = customer_status_binary ~ customerfeedback_foodquality +
## customerfeedback_servicequality + customerfeedback_pricetovalue +
## customerfeedback_ambiance, family = binomial, data = clean_data)
##
## Coefficients:
## Estimate Std. Error z value Pr(>|z|)
## (Intercept) -3.89359 0.07582 -51.354 < 2e-16 ***
## customerfeedback_foodquality 0.59450 0.05847 10.168 < 2e-16 ***
## customerfeedback_servicequality 0.22102 0.04090 5.404 6.50e-08 ***
## customerfeedback_pricetovalue 0.24606 0.04103 5.996 2.02e-09 ***
## customerfeedback_ambiance 0.26982 0.04148 6.506 7.74e-11 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## (Dispersion parameter for binomial family taken to be 1)
##
## Null deviance: 15094 on 10887 degrees of freedom
## Residual deviance: 10328 on 10883 degrees of freedom
## AIC: 10338
##
## Number of Fisher Scoring iterations: 4
exp(coef(model))
## (Intercept) customerfeedback_foodquality
## 0.02037202 1.81211815
## customerfeedback_servicequality customerfeedback_pricetovalue
## 1.24734774 1.27897002
## customerfeedback_ambiance
## 1.30972814