I have the following dataset to be used with Transactional data with the package rfm:
rfmdata <- tibble::tribble(
~CustomerID, ~Date, ~Revenue,
14159, "2011-07-13 12:06:00", 419.48,
14110, "2011-07-13 12:06:00", 317.4,
15530, "2011-07-13 12:15:00", 177.85,
16779, "2011-07-13 12:18:00", 660.01,
16225, "2011-07-13 12:18:00", 353.08,
12528, "2011-07-13 12:24:00", 281.71,
13089, "2011-07-13 12:27:00", 214.2,
18121, "2011-07-13 12:53:00", 23.25,
14502, "2011-07-13 13:14:00", 105.78,
12935, "2011-07-13 13:16:00", 353.09,
13149, "2011-07-13 13:20:00", 506.16,
15311, "2011-07-13 13:27:00", 152.81,
18219, "2011-07-13 13:33:00", 185.85,
12766, "2011-07-13 13:37:00", 936.48,
14088, "2011-07-13 13:56:00", -76.32,
18245, "2011-07-13 14:28:00", 330.92,
18245, "2011-07-13 14:29:00", 35.6,
13777, "2011-07-13 14:39:00", 270.9,
14221, "2011-07-13 15:04:00", 357.78,
14221, "2011-07-13 15:05:00", 17.4,
14201, "2011-07-13 15:18:00", 110.14,
18225, "2011-07-13 15:29:00", 488.4,
12415, "2011-07-13 15:30:00", 2735.16,
12415, "2011-07-13 15:31:00", 61.2,
13667, "2011-07-13 15:42:00", 305.02,
14400, "2011-07-13 15:50:00", 574.7,
17644, "2011-07-13 16:47:00", 316.85,
17644, "2011-07-13 16:48:00", 39.6,
17567, "2011-07-13 17:03:00", 461.08,
12405, "2011-07-14 08:52:00", 1710.39,
14265, "2011-07-14 09:10:00", 252.05,
17738, "2011-07-14 09:22:00", 585.62,
17738, "2011-07-14 09:23:00", 205.95,
17389, "2011-07-14 09:26:00", 561.9,
16719, "2011-07-14 09:54:00", 308.2,
13012, "2011-07-14 09:58:00", 304.92,
15125, "2011-07-14 10:08:00", 315.12,
15125, "2011-07-14 10:09:00", 49.92,
13577, "2011-07-14 10:11:00", 465.35,
17315, "2011-07-14 10:15:00", 112.81,
14426, "2011-07-14 10:19:00", -500.74,
17396, "2011-07-14 10:20:00", 791.7,
17396, "2011-07-14 10:20:00", 1960.8,
17396, "2011-07-14 10:21:00", 1037.1,
17396, "2011-07-14 10:21:00", 596.7,
17396, "2011-07-14 10:24:00", 18,
16701, "2011-07-14 10:27:00", -119.76
)
This data is coming from MySQL database which is fetched via reactivePoll every 30 minutes as follows:
con <- dbConnect(MySQL(),
user = ('root'),
password = ('ruvimboML55AMG'),
host = ('localhost'),
dbname = ('healthcare_mining'))
``` r
get_rfmData <- function(con) {
RFM_data_orders <- dbGetQuery(con, "SELECT CustomerID, Date, sum(Amount) as Revenue FROM onlineRetail WHERE CustomerID is not null Group by CustomerID, Date;")
}
# Reactive poll for rfm data every 30 min
RFM_data_orders <- reactivePoll(1800000, session,
checkFunc = function(){
print("Entered Check")
Sys.time()
print(Sys.time())
# get max date from database table to determine if data has been updated
max_date <- dbGetQuery(con, "SELECT MAX(date_updated) FROM onlineRetail as updated_last;")
return(max_date)
},
valueFunc = function(){
print("Entered Value")
Sys.time()
print(Sys.time())
get_rfmData(con)
}
)
Next i need to create a data table which gives the rfm data from the reactive poll data with the following code:
``` r
# Customer Recency, Frequency and Monetary Analytics
output$rfmAnalytics <- DT::renderDataTable({
req(credentials()$user_auth)
withProgress(message = 'Calculation in progress',
detail = 'This may take a while...', value = 0, {
for (i in 1:15) {
incProgress(1/15)
Sys.sleep(20)
}
})
analysis_date <- lubridate::as_date(today(),tz = 'CAT')
RFM_data_orders() %>%
dplyr::mutate(Date = as.Date(Date, '%m/%d/%Y %H:%M')) %>%
rfm_result <- rfm_table_order(RFM_data_orders(), CustomerID, Date, Revenue, analysis_date)
rfm_result
DT::datatable(rfm_result$rfm)
})
I am getting the following error message:
Warning: Error in -: non-numeric argument to binary operator
This should be something to do with the 'Date' field which i think is not being converted to the 'date' format and remains in the 'char' as imported from MySQL.
Please assist
Regards,
Chris