### 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) ## ## # ------ # --------------- NO LONGER NEEDED -------------------- # # REPLACED BY isFirstStream_byUD, firstStream_byUD, etc # ------ # --------------- NO LONGER NEEDED -------------------- # # see 12 # ## First, set the whole column to FALSE # see 12 # DT[, isLastOccurOfUser_perD := FALSE] # see 12 # ## Then, ordering on lastStream_byUCnDPMb (whose largest value corresponds to the value we'd like to set to TRUE) # see 12 # ## by user and month # see 12 # DT[ order(lastStream_byUCnDPMb) # see 12 # , isLastOccurOfUser_perD := c(isLastOccurOfUser_perD[-1L], TRUE) # see 12 # , by=kCols_UD] # no country! # see 12 # # see 12 # ## Reoeat for first stream, but the "TRUE" is the first element, not the last # see 12 # DT[, isFirstOccurOfUser_perD := FALSE] # see 12 # DT[ order(firstStream_byUCnDPMb) # see 12 # , isFirstOccurOfUser_perD := c(TRUE, isFirstOccurOfUser_perD[-1L]) # see 12 # , by=kCols_UD] # no country! # ------ # --------------- NO LONGER NEEDED -------------------- # CLEAN UP... Especially the DCn CnD stuff. Check the current Values ## 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. DT[, uniqueUsers_perCnDP := sum(isLastOccurOfUser_perD), by=kCols_CnDP] DT[, uniqueUsers_perCnD := sum(isLastOccurOfUser_perD), by=kCols_CnD] DT[, uniqueUsers_perDP := sum(isLastOccurOfUser_perD), by=kCols_DP] ## ^^^ This is the main information we want, so we might save here notifyAndEmail("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("Saving complete. Beginning Streams count") # ---------- TOTAL STREAM COUNT ---------- # ## Total Number of Streams by Date-Country-Product-User ## Aggregate out the mobile value. Count per user per Product-Country-Month DT[, total_streams_perCnDP_User := sum(total_streams_perCnDPUMb), by=kCols_DCnPU] ## Total Number of Streams by Date-Country-Product-Mobile ## Aggregate out unique user info. Count per device per Product-Country-Month DT[, total_streams_perCnDP_Mobile := sum(total_streams_perCnDPUMb), by=kCols_DCnPMb] ## Total Number of Streams by Date-Country-Product ## Aggregate out user and mobile info. Count per Product-Country-Month DT[, total_streams_perCnD_Product := sum(total_streams_perCnDPUMb), by=kCols_DCnP] # ---------- END - TOTAL STREAM COUNT ---------- # ## 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] # ======================= # ## 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] DT[, perc_mobile_perDCn_Product := sum(mobile*total_streams_perCnDPUMb)/sum(total_streams_perCnDPUMb) , by=kCols_DCnP] DT[, perc_mobile_perD_Country := sum(mobile*total_streams_perCnDPUMb)/sum(total_streams_perCnDPUMb) , by=kCols_DCn] # ======================= # ## 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") DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb <- DT } # ------------------------------------ # ## Beginning to calculate conversion rate ## This is all untested try({ kCols_DCnPU_lastDate_Mobile <- c(kCols_DCnU, "lastStream_byUCnDPMb", "firstStream_byUCnDPMb", "mobile") ## NO PRODUCT!! setkeyIfNot(DT, kCols_DCnPU_lastDate_Mobile) DT[, product_integer := as.integer(product)] DT[, `:=`(productDELTA = diffNA(product_integer, padTop=TRUE) , 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")) }) # ------------------------------------ # 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.byCnDPMb <- unique(setkeyv(DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb[, ColsToKeepForDCnPMb, with=FALSE], kCols_CnDPMb)) backupDir <- as.path(dataDir, "ToTxr") dir.create(backupDir, showWarnings=FALSE) f.out.DT.DCnPMb <- jesusForData(DT.Spotify.Counts.plusMAX_MIN_DATE.byCnDPMb, info="AggdOut User Info from DCnPUMb", dir=backupDir) 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) }) 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() } # ------------------ 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