{"cells":[{"metadata":{"_uuid":"8c88dbf9a04d2b1dde1849a52f5f207802216002"},"cell_type":"markdown","source":"**0. Introduction**\n\nWe analyse the transaction data with a focus on the type of historic data that can be used to make a forecast. The question is: given a user that is present in the test set, what is the most likely tranasction Revenue in a future period of 2 months? F.i., take user \"4759981878863963838\". She appeared once with a single tranasction value of  3026.100 . What can we assume she will do when she returns in that period?\n\nIn this (version 10) script we examine users that appeared at least once per 62 days in recent periods, and use their expected values in a prediciton. Othger entries remain zero. \n\n--------------------------------------------------------------------------------------------------\n\n\n**Load data from previous script**\n\nCombined vs 2 test and train, from the script by Burrito Dan https://www.kaggle.com/burritodan/gstore-1-flatten"},{"metadata":{"_uuid":"f532642386deeef5aa0b4e74470c8d11ed974d1c","_execution_state":"idle","trusted":true},"cell_type":"code","source":"# list.files(path = \"../input/bothdata\")\nload('../input/bothdata/both001.Rdata')\nrequire(data.table); setDT(both)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"376da32c22a6aa6d2e942ab9e2e14fee51c79aca"},"cell_type":"markdown","source":"**Duplicate column totalTransactionRevenue**\n"},{"metadata":{"trusted":true,"_uuid":"2c44b29f02d55809d9c1af90f764eeeff9691495"},"cell_type":"code","source":"# clean up transactionRevenue\nboth[, transactionRevenue := as.numeric(transactionRevenue)]\nboth[is.na(transactionRevenue), transactionRevenue :=0]\nboth[is.na(totalTransactionRevenue), totalTransactionRevenue :=0] # is == above\nif (sum(both$transactionRevenue != both$totalTransactionRevenue) == 0)\n    both[, totalTransactionRevenue := NULL] # duplicate\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"860c64b45643f154d3f57414bba43c8d30972aff"},"cell_type":"markdown","source":"**1. Data quality**\n\nRemember we have almost no data to base our submission on.There are dates, tranasction Revenues,  the order of vists, but that is all. All the other attributes are worthless in the final prediction, simply because they are not there.\n\nHowever there are many inconsistencies in the data.  Sme observatiobs seem to be absent  We look at berious examples\n\n**Column transactions does not correspond to non zero transactions**\n\nThere are 45 observations that have a non-zero tranasactions column but where transactionRevenue is zero.\n"},{"metadata":{"trusted":true,"_uuid":"4e667b4aef2b81a3bffbc928f4e4357917122cb4"},"cell_type":"code","source":"both[is.na(transactions), transactions := 0L] ### <<< but what is that?\ntrain<- copy(both[test == FALSE, ])\ntrain[, is_nonzeroTransaction:= transactionRevenue > 0]\ntrain[, transactions_nonzero:= transactions > 0]\ntable(train$is_nonzeroTransaction, train$transactions_nonzero)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"df259a0611f18bd3685889f4d53d5004b9ddd34e"},"cell_type":"markdown","source":"\n**Missing visit and tranasaction numbers? **\n\nvisitNumber seems to number consecutively, but there are some gaps. But also visitNumber is not always unique. In here whe take  all users in test and combine with their train data. \n\n"},{"metadata":{"trusted":true,"_uuid":"b3207e77456e8a12e64f532b85e995202d139225","scrolled":true},"cell_type":"code","source":"all_users= unique(both$fullVisitorId[both$test == TRUE])\n\nvs_per_user<- both[fullVisitorId %in% all_users\n           ,.( acted= as.integer(sum( transactionRevenue > 0)), \n               count = .N, \n               minV= min(visitNumber), \n               maxV= max(visitNumber), \n               missing = 1+max(visitNumber)-min(visitNumber)- .N, \n               totalTrans = sum(transactionRevenue)/10^6, \n               avgTrans = sum(transactionRevenue)/(.N * 10^6)\n             ), by=fullVisitorId]\n\nvs_per_user[vs_per_user$count>100,]\nvs_per_user[vs_per_user$missing>30, ]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e58863a9004060d217629c85074e076a467e7e90"},"cell_type":"markdown","source":"** Total and Avg tranaction values**\n\nNow a peek at the transaction values: what is high?  \n"},{"metadata":{"trusted":true,"_uuid":"f35b58faeeafef9ebaf176ecc6ca2d2912870a5c"},"cell_type":"code","source":"as.data.frame(vs_per_user[order(avgTrans, decreasing = TRUE), ])[1:10,]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"edf23624d15a088d2705237acfbe3cf3dc82dcd5"},"cell_type":"markdown","source":"**(Not so much) Fun with VisitNumbers**\n\nMaybe tou thought VisitNumbers are consecutive and unique. Quid non. \n\ntwo examples."},{"metadata":{"trusted":true,"_uuid":"8f1e4529545af6fbb7d51f2e2babe67af3fff074"},"cell_type":"code","source":"# 2863616485775419102 acted 5 time but has a large nr of missing\ntmp<- both[fullVisitorId == \"2863616485775419102\", \n           c(\"date\", \"visitNumber\" , \"transactionRevenue\", \n            \"transactions\"), with=TRUE]\nsetDF(tmp)\ntmp<- tmp[order(tmp$visitNumber), ]\ntmp$transactionRevenue <- tmp$transactionRevenue / 10^6\nrow.names(tmp) <- NULL\nhead(tmp)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"986179eb1ef9803747f5e61c2f56180495345693"},"cell_type":"code","source":"# 4578640586284138624 seens to have dates out of order\n\ntmp<- both[fullVisitorId == \"4578640586284138624\", \n           c(\"date\", \"visitNumber\" , \"transactionRevenue\", \n            \"transactions\"), with=TRUE]\nsetDF(tmp)\ntmp<- tmp[order(tmp$date, tmp$visitNumber), ]\ntmp$transactionRevenue <- tmp$transactionRevenue / 10^6\nrow.names(tmp) <- NULL\nhead(tmp); tail(tmp)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4a550b479333e080ba0984e590c816386855f41c"},"cell_type":"markdown","source":"**Intermediate Conclusions**\n\nIn our attempt to reconstruct purchase history from some of the columns we find inconsistencies and conflicting informatio. Date and transactionRevenue must be used, otherwise we do not even have data, but all other information is either useless or suspect. \n\nDates range from 2016-08-01 to 2018-10-15.  Let's compress the data in 2 months periods.  Might be that we can use that as a time series for forecasting. \n\n"},{"metadata":{"trusted":true,"_uuid":"ce2df29a10bd713f8d4a99fe1c5abb3ff88e7f35"},"cell_type":"code","source":"b <- both[, c(\"fullVisitorId\", \"date\", \"transactionRevenue\"), with=TRUE]\nb[ , period:= as.integer(((year(date)-2016)*12 + month(date))/2)]\nskip <- max(b$period)\n## b <- b[period < skip, ]\nb <- b[period < skip, .(spent = sum(transactionRevenue), \n            visits= .N, first_visit= min(date)), \n            key=c(\"fullVisitorId\", \"period\")]\nb[, totalVisits:= sum(visits), by=fullVisitorId]\n# head(b)\nb_period <- b[, .(spent=sum(spent)/10^6), key=period]\nplot(b_period,  lwd=20, type=\"h\", ylim= c(10000, 300000), col=\"#CCCEEE\", \n    main=\"Revenue in periods of 2 months\", ylab=\"Rev.\")\nabline(v=c(6,12), lty=2, col=\"#AAAAAA\") \n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"30be9fd671ab7388529da7b0fabec60e86fc8b2f"},"cell_type":"markdown","source":"Well, that did not work either. Nothing special in this series other than a new suspicion that the data is not complete at all.  No year end effect at all. And what cause the drop in Revenue in 2017-10?"},{"metadata":{"trusted":true,"_uuid":"8f0c40b4224494dade0d410ed6773d9c956b2513"},"cell_type":"code","source":"b_period <- b[, .(visits=sum(visits)), key=period]\nplot(b_period,  lwd=20, type=\"h\", col=\"#EEECCC\", ylim=c(0, 220000)\n    , main=\"Visits in periods of 2 months\", ylab=\"NrVisits\")\nabline(v=c(6,12), lty=2, col=\"#AAAAAA\") \n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c01310fa34aa58518d190f5de3455a3e8c818bc5"},"cell_type":"markdown","source":"**2. Predicting from previous data. ** \n\nThere are 46 days between test1 and test3. Test2 has 62 days. Test1 has 168 days we take a couple of random perods wih these lenghts and various simple predictors. It is very diffivult to find a predictor that is better than all zero's.... But now (22-11) there is a nice educated guess after correcting several smaller programming errors..."},{"metadata":{"trusted":true,"_uuid":"e8586b49346f25adb721e0dd885913405e141c47"},"cell_type":"code","source":"DELTA <-10 # too much history might make it worse??\nend_te2 <- as.Date(\"2018-10-15\")\nstart_te2 <- end_te2 - 61\nend_te1 <- end_te2 -108\nstart_te1 <- end_te1 - 167\nstart_rem <- start_te1 - DELTA\ncut_off <- 1/62\n\nb_all <- both[date <= format(end_te2),]\nb_te2 <- b_all[date >= format(start_te2),\n               .(fullVisitorId, date, transactionRevenue)]\nb_rem <-  b_all[date <= format(end_te1)  & date >= format(start_rem), \n                .(fullVisitorId, date, transactionRevenue)]\nuser_base <- unique(both$fullVisitorId[both$date >= format(start_te1)\n                                       & both$date <= format(end_te1)])\nb_rem <- b_rem[fullVisitorId %in% user_base, ]\nb_rem_uq <- b_rem[,.( nrVisits= .N, \n                      totalRev= sum(transactionRevenue),\n                      acts = as.integer(sum(transactionRevenue> 0)),\n                      first=min(date), \n                      last= max(date)\n                      ) , key=fullVisitorId]\nb_rem_uq[, periods := pmax(62, (1+as.integer(max(b_rem$date) - b_rem_uq$first)/(60*60*24)))]\nb_rem_uq[, actfreq:= acts/periods]\nper_length <- 21 + as.integer( max(b_rem$date)- b_rem_uq$first)/(60*60*24)\nb_rem_uq[, pred := pmax(1, 62* nrVisits/ per_length) * totalRev/nrVisits]\np1<- b_rem_uq$actfreq > cut_off\nb_rem_uq$pred[! (p1)] <- 0\n\n#-----------------------------------------------------------\n# compare\nb_te2_uq <- b_te2[fullVisitorId %in% user_base, \n                  .(newtotalRev= sum(transactionRevenue)),\n                  by = fullVisitorId]\nb_te2_uq <- b_te2_uq [order(fullVisitorId)]\njustnow <- merge(b_te2_uq, b_rem_uq, all.x=TRUE, by=\"fullVisitorId\")\ncat(\"\\nEducated Guess:\",\n    Metrics::rmsle(justnow$pred, justnow$newtotalRev),\n    \"; allzero pred.:\", \n    Metrics::rmsle(0*justnow$pred, justnow$newtotalRev),\n    \"\\n\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7320375593f7066c5028f6c1fc30f91da25e616a"},"cell_type":"markdown","source":""},{"metadata":{"trusted":true,"_uuid":"ce65004005c82fec0f892c294f56c56ee4dbf222"},"cell_type":"code","source":"rec_user_log1p <- function(end_te2){\n  # end_te2 is last date in test_2\n  if(class(end_te2)!= \"Date\") end_te2 <- as.Date(end_te2)\n  start_te2 <- end_te2 - 61\n  end_te1 <- end_te2 -108\n  start_te1 <- end_te1 - 167\n  test1 <- both$date>= format(start_te1) & both$date <= format(end_te1)\n  test2 <- both$date>= format(start_te2) & both$date <= format(end_te2)\n  rec_users<- intersect(both$fullVisitorId[test1], both$fullVisitorId[test1])\n  b_uq<- both[fullVisitorId %in% rec_users & test2, \n              .(tr= log1p(sum(transactionRevenue))), by= fullVisitorId]\n  return(mean(b_uq$tr))\n}\n\nrange <- seq(as.Date(\"2017-03-20\"), as.Date(\"2018-10-15\"), \n             length.out = 150)\nresult <- sapply(range, rec_user_log1p )\nplot(x=range, y=result, type=\"l\"\n    , main=\"log1p value/2 months for recurrent customers\")\n\n cat(mean(result), \"avg value from this graph\\n\")\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f343b67555203f397bc190834d6db27d03ecab46"},"cell_type":"code","source":"test <- copy(both[test==TRUE,])\ntest<- test[, .( nrVisits= .N, \n                 totalRev= sum(transactionRevenue),\n                 acts = as.integer(sum(transactionRevenue> 0)),\n                 first=min(date), \n                 last= max(date)\n) , key=fullVisitorId]\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a589b0bccc870b01b3fef2cdce068b66d7601096"},"cell_type":"code","source":"END <- max(both$date)\ntest[, periods := pmax(62, (1 + as.integer( END - test$first)/(60*60*24)))]\ntest[, actfreq:= acts/periods]\n\nper_length <-  21L+ as.integer((1+ END- test$first)/(60*60*24))\ntest[, pred := log1p(pmax(1, 62* nrVisits/ per_length) * totalRev/nrVisits)]\n\np1<- test$actfreq > 1/62\ntest$pred[! (p1)] <-  mean(result)\n# mean(test$pred) # only 0.01\nsubm <- test[, .(fullVisitorId, pred)]\nsetDF(subm)\nnames(subm)[2] <- \"PredictedLogRevenue\"\nwrite.csv(subm, file=\"submitnow_001.csv\", row.names= FALSE)\n","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"R","language":"R","name":"ir"},"language_info":{"mimetype":"text/x-r-source","name":"R","pygments_lexer":"r","version":"3.4.2","file_extension":".r","codemirror_mode":"r"}},"nbformat":4,"nbformat_minor":1}