### Keyby abbreviations ## Abrv - Column - Description ## P - product - product ## D - month - Date ## Mb - mobile - mobile ## Cn - country - country ## U - userid - user ## COUNT -- DONT DOUBLE COUNT (eg, if a user is in more than one country) { ## for shorthand DT <- DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb ## clean up the order for ease on the eyes setcolorderpt(DT, kCols_DCnUPMb) print(DT[1:80][order(userid, lastStream_byUCnDPMb)]) ## Number of users ## The problem with this straightforward count is, when a user converts, they are counted twice. ## Spotify counts the user as the last product of the month ## ## NO GOOD: DT[, user_count_perDCnP := lunique(userid), by=kCols_DCnP] ## ## Therefore, we will only count the last occurance of the user in each month ## ## REMEMBER THE PURPOSE: Each user can only be counted ONCE per MONTH. ## But can be counted more than once across multiple months. ## (why monthly? because that is when they get billed) ## ## notify("101") ## First, set the whole column to FALSE DT[, isLastOccurOfUser_perD := FALSE] ## Then, ordering on lastStream_byUCnDPMb (whose largest value corresponds to the value we'd like to set to TRUE) ## by user and month DT[ order(lastStream_byUCnDPMb) , isLastOccurOfUser_perD := c(isLastOccurOfUser_perD[-1L], TRUE) , by=kCols_UD] # no country! notify("113") ## First, set the whole column to FALSE DT[, isFirstOccurOfUser_perD := FALSE] ## Then, ordering on lastStream_byUCnDPMb (whose largest value corresponds to the value we'd like to set to TRUE) ## by user and month DT[ order(firstStream_byUCnDPMb) , isFirstOccurOfUser_perD := c(TRUE, isFirstOccurOfUser_perD[-1L]) , by=kCols_UD] # no country! notify("123") ## Most of the rest of the counting is by Date-Country-Product-User (or some subset starting with Date-..) ## So set key to help speed up that process setkeyIfNot(DT, kCols_DCnPU) if (!identical(DT, DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb)) { warning ("Not identical at line No.49 -- IT'S SETTING KEY!!\n") DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb <- DT } notify("Done setting key. Counting Unique Users then saving") ## Use of the 'isLastOccurOfUser_perD' column ensures that, in a month, the sum(isLastOccurOfUser_perD) is exactly one per user. ## Thus, if a user belongs to more than one group ('group' as defined by the kCols_..), ## then still that user will only be counted once. That is, the user's vote will be assigned only once. if (exists("dopar")) { notify("BEGINNING PARALLEL") ## Unique by Date-Country-Product DT[, uniqueUsers_perCnDP := { ### PARALLEL ## HOW TO SPLIT foreach(x=.SD[, "isLastOccurOfUser_perD", with=FALSE], .combine="c", .inorder=TRUE, .multicombine=TRUE, .maxcombine=200, .verbose=getVerbose("par")) %dopar% ## HOW TO ACT ON EACH SPLIT sum(isLastOccurOfUser_perD) } , by=kCols_DCnP, .SDcols=c("isLastOccurOfUser_perD")] EmailStatusUpdate("First Parallel Success!!") ## Unique by Date-Country DT[, uniqueUsers_perCnD := { ### PARALLEL ## HOW TO SPLIT foreach(x=.SD[, "isLastOccurOfUser_perD", with=FALSE], .combine="c", .inorder=TRUE, .multicombine=TRUE, .maxcombine=200, .verbose=getVerbose("par")) %dopar% ## HOW TO ACT ON EACH SPLIT sum(isLastOccurOfUser_perD) } , by=kCols_DCn, .SDcols=c("isLastOccurOfUser_perD")] notify("168") ## Unique by Date-Country-Product DT[, uniqueUsers_perDP := { ### PARALLEL ## HOW TO SPLIT foreach(x=.SD[, "isLastOccurOfUser_perD", with=FALSE], .combine="c", .inorder=TRUE, .multicombine=TRUE, .maxcombine=200, .verbose=getVerbose("par")) %dopar% ## HOW TO ACT ON EACH SPLIT sum(isLastOccurOfUser_perD) } , by=kCols_DP, .SDcols=c("isLastOccurOfUser_perD")] } else { ## NON-PARALLEL DT[, uniqueUsers_perCnDP := sum(isLastOccurOfUser_perD), by=kCols_DCnP] DT[, uniqueUsers_perCnD := sum(isLastOccurOfUser_perD), by=kCols_DCn] DT[, uniqueUsers_perDP := sum(isLastOccurOfUser_perD), by=kCols_DP] } notify("188") ## ^^^ This is the main information we want, so we might save here notify("done counting unique users. Saving" ) if (!identical(DT, DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb)) { warning ("Not identical at line No.72\n") DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb <- DT } jesusForData(DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb, info="User Counts added but missing streams count") notify("Line 200: Saving complete. Beginning Streams count") # Last Checkpoint I got at 3/21 17:40 # ---------- TOTAL STREAM COUNT ---------- # # non-parallel : ## Aggregate out the mobile value. Count per user per Product-Country-Month # non-parallel : DT[, total_streams_perCnDP_User := sum(total_streams_perCnDPUMb), by=kCols_DCnPU] # non-parallel : ## Aggregate out unique user info. Count per device per Product-Country-Month # non-parallel : DT[, total_streams_perCnDP_Mobile := sum(total_streams_perCnDPUMb), by=kCols_DCnPMb] # non-parallel : ## Aggregate out user and mobile info. Count per Product-Country-Month # non-parallel : DT[, total_streams_perCnD_Product := sum(total_streams_perCnDPUMb), by=kCols_DCnP] ## Total Number of Streams by Date-Country-Product-User DT[, total_streams_perCnDP_User := { ### PARALLEL ## HOW TO SPLIT foreach(x=.SD[, "total_streams_perCnDPUMb", with=FALSE], .combine="c", .inorder=TRUE, .multicombine=TRUE, .maxcombine=200, .verbose=getVerbose("par")) %dopar% ## HOW TO ACT ON EACH SPLIT sum(total_streams_perCnDPUMb) } , by=kCols_DCnPU, .SDcols=c("total_streams_perCnDPUMb")] notify("222") ## Total Number of Streams by Date-Country-Product-Mobile DT[, total_streams_perCnDP_Mobile := { ### PARALLEL ## HOW TO SPLIT foreach(x=.SD[, "total_streams_perCnDPUMb", with=FALSE], .combine="c", .inorder=TRUE, .multicombine=TRUE, .maxcombine=200, .verbose=getVerbose("par")) %dopar% ## HOW TO ACT ON EACH SPLIT sum(total_streams_perCnDPUMb) } , by=kCols_DCnPMb, .SDcols=c("total_streams_perCnDPUMb")] notify("236") ## Total Number of Streams by Date-Country-Product DT[, total_streams_perCnD_Product := { ### PARALLEL ## HOW TO SPLIT foreach(x=.SD[, "total_streams_perCnDPUMb", with=FALSE], .combine="c", .inorder=TRUE, .multicombine=TRUE, .maxcombine=200, .verbose=getVerbose("par")) %dopar% ## HOW TO ACT ON EACH SPLIT sum(total_streams_perCnDPUMb) } , by=kCols_DCnP, .SDcols=c("total_streams_perCnDPUMb")] # ---------- END - TOTAL STREAM COUNT ---------- # notify("251") ## Avg Streams per DCnP and device. (No User info) ## We are essentially calculating averages across all users in the group DT[, `:=`( median_monthly_streams_perDCnP_Mobile = median(total_streams_perCnDPUMb) , mean_monthly_streams_perDCnP_Mobile = mean(total_streams_perCnDPUMb) ) , by=kCols_DCnPMb ] ## Avg per month, per country, per product (No User info, no device info) ## We are essentially calculating averages across all users in the group. DT[, `:=`( median_monthly_streams_perDCnP = median(total_streams_perCnDP_User[ !duplicated(userid) ]) , mean_monthly_streams_perDCnP = mean(total_streams_perCnDP_User[ !duplicated(userid) ]) ) , by=kCols_DCnP ] ## Note to self: When tallying up total_streams_perCnDPUMb, we allow for duplicate userid's, ## because the total_streams_perCnDPUMb values are unique. ## Correction! We can allow duplicates, but there shold not be any, since we have already ## aggregated by DCnPUMb and, VERY IMPORTANTLY, only kept unique rows ## Whereas now for total_streams_perCnDP_User, we've aggregated, but we retained non-unique rows ## Specifically, the column we aggregated out (userid) can now contain duplicates relative to the new group notify("Beginning Percentag counts`") ## Avg is the total number of streams divided by the number of users DT[ , mean_streams_per_user_perDCnP := sum(total_streams_perCnDPUMb) / sum(isLastOccurOfUser_perD) , by=kCols_DCnP] notify("278") # ======================= # ## Percentage Mobile DT[, perc_mobile_perDCnP_User := sum(mobile*total_streams_perCnDPUMb)/total_streams_perCnDP_User[[1L]] # the denominator is simply sum(total_streams_perCnDPUMb), already computed previously , by=kCols_DCnPU] notify("284") DT[, perc_mobile_perDCn_Product := sum(mobile*total_streams_perCnDPUMb)/sum(total_streams_perCnDPUMb) , by=kCols_DCnP] notify("287") DT[, perc_mobile_perD_Country := sum(mobile*total_streams_perCnDPUMb)/sum(total_streams_perCnDPUMb) , by=kCols_DCn] # ======================= # notify("294") ## Take the average of the user-level percentages, per DCnP DT[, mean_of__perc_mobile_perDCnP_User__perDCnP := mean(perc_mobile_perDCnP_User), by=kCols_DCnP] notify("Done calculations. Final Save") if (!identical(DT, DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb)) { warning ("Not identical at line No.125\n") ## TODO: Here, not identical? ******* DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb <- DT } # ------------------------------------ # ## Beginning to calculate conversion rate ## This is all untested try({ kCols_UCnD <- c("userid", "country", "month") kCols_DCnPU_lastDate_Mobile <- c(kCols_DCnU, "lastStream_byUCnDPMb", "firstStream_byUCnDPMb", "mobile") ## NO PRODUCT!! setkeyIfNot(DT, kCols_DCnPU_lastDate_Mobile) ## Specifically, we do NOT want as.num.as.char ... we want the number underlying the factor if ("product_integer" %ni% names(DT)) DT[, product_integer := as.integer(product)] # DT[, `:=`(productDELTA = diffNA(product_integer, padTop=TRUE) DT[, `:=`(productDELTA = {j <- diffNA(product_integer, padTop=TRUE); if (!is.integer(j)) {print(.BY); print(dput(.BY)); stop("not an integer")} else j } , productLASTFIRST=product_integer[which.max(lastStream_byUCnDPMb)] - product_integer[which.min(firstStream_byUCnDPMb)] ) , by=userid] setcolorderpt(DT, c(kCols_DCnUP, "productDELTA", "productLASTFIRST", "firstStream_byUCnDPMb", "lastStream_byUCnDPMb", "mobile")) }) # ------------------------------------ # notify("322") if (!identical(DT, DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb)) { warning ("Not identical at line No.157\n") DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb <- DT } ColsThatDontMakeSenseWOuserInfo <- c("userid", "lastStream_byUCnDPMb", "firstStream_byUCnDPMb", "total_streams_perCnDPUMb", "isLastOccurOfUser_perD" , "total_streams_perCnDP_User", "perc_mobile_perDCnP_User") ColsToKeepForDCnPMb <- setdiff(names(DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb), ColsThatDontMakeSenseWOuserInfo) DT.Spotify.Counts.plusMAX_MIN_DATE.byDCnPMb <- unique(DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb[, ColsToKeepForDCnPMb, with=FALSE], by=kCols_DCnPMb) notify("341") backupDir <- as.path(dataDir, "ToTxr") dir.create(backupDir, showWarnings=FALSE) f.out.DT.DCnPMb <- jesusForData(DT.Spotify.Counts.plusMAX_MIN_DATE.byDCnPMb, info="AggdOut User Info from DCnPUMb", dir=backupDir) notify("347") try({ ## TAKE SAMPLE DT..byDCnPUMb.Sample <- DT[rbind(DT[1, key(DT)[1:3], with=FALSE], DT[5e6, key(DT)[1:3], with=FALSE], DT[nrow(DT), key(DT)[1:3], with=FALSE])] f.out.DT.DCnPUMb.sample <- jesusForData(DT..byDCnPUMb.Sample, info="Sample of Main DT", dir=backupDir) }) notify("356") f.out.DT.DCnPUMb <- jesusForData(DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb, info="END OF 13. Calculations done. Still need Conv Rate calculations", dir=backupDir) notify("Done Saving") cat(f.out.DT.DCnPMb, "\n") cat(f.out.DT.DCnPUMb, "\n") cat(f.out.DT.DCnPUMb.sample, "\n") saveImageTo() } notify("371") # ------------------ SCARP -------------- # multiProduct <- DT[1:10000, list(N=lunique(product)), by=userid] # custids.multiprods <- multiProduct[N>2, userid] # SEE 04_2 for more scratch work, including conversion rate