music_and_video_bucketcleanup_ <- function(DT, zUncategorized = "zUncategorized Store", storeidCol = "storeid", cluster=NULL) { browser(expr = inDebugMode(c("music_and_video_bucketcleanup_", "music_and_video", "cleanup", "bucket")), text="in music_and_video_bucketcleanup_() at the top") ## Error Check if (!is.null(storeidCol) && !(DT %cont% storeidCol)) stop ("storeidCol ('", storeidCol, "') is not a column of DT") key.bak <- key(DT) if (DT %cont% "release_product_type") { ## Proper term is "Video Etc", not "Video" DT[release_product_type=="Video", release_product_type := "Video Etc"] ## If music_vs_video is uncategorized, determine based on product type DT[music_vs_video %ni% c("Music", "Video"), music_vs_video := ifelse(release_product_type == "Music", yes="Music", no="Video") ] ## Note: here we are assining music_vs_video according to release_product_type ## RECALL: music_vs_video is FIRST done by transaction. Thus, there will be several instances where there are many rows for: ## DT [release_product_type != "Music" & music_vs_video == "Music"] ## TODO: Check the specific stores / releases thare acting like this. } if (!is.null(storeidCol)) { ## ------------ START: FIX 355 ------------ ## ## storeid 355 is fucked. All "12" transactions should be "4" ## identify which index are 355 for speed inds.355 <- DT[[storeidCol]] == 355 if (any(inds.355)) { ## make sure we have DT.transacs -- othwerise query for it if (!exists("DT.transacs")) { DT.transacs <- runQry("SELECT * from bi.transactiontype_view", cluster=cluster) assign("DT.transacs", DT.transacs, envir=globalenv()) } setkeyIfNot(DT.transacs, transac_typeid, superset.ok=TRUE, verbose=FALSE) trans.colsToFix <- intersect(names(DT.transacs), names(DT)) ## There might be a storeid == 355, without any transaction info if (length(trans.colsToFix)) { ## identify correct values either by transac_typeid or by transac_type_abbr if (DT %cont% "transac_typeid") trans.correctVals <- unique(DT[inds.355 & transac_typeid == 4, trans.colsToFix, with=FALSE], by=NULL) else if (DT %cont% "transac_type_abbr") trans.correctVals <- unique(DT[inds.355 & transac_type_abbr == "DR", trans.colsToFix, with=FALSE], by=NULL) else { warn ("do not know how to fix storeid == 355, even though there is SOME transac info") trans.correctVals <- data.table() } ## There should be exactly one row of "correct" values for 355 if (nrow(trans.correctVals) > 1) warning ("There is more than one row of \"correct values\" for storeid == 355") if (nrow(trans.correctVals) >= 1) { trans.correctVals <- trans.correctVals[1] for (tcol in names(trans.correctVals)) DT[inds.355, (tcol) := trans.correctVals[[tcol]]] } } } ## ------------ END: FIX 355 ------------ ## ## ------------ START: store-dependent bucket cleaning ------------ ## if (all(DT %cont% c("store_musicbucket", "store_videobucket"))) { ### iTunes ###------------------------- ## iTunes, Allegro has only 'Movie', SelectO has only 'Web Videos' DT[get(storeidCol) == 1 & music_vs_video == "Video" & label_sc_group == "Allegro", release_product_type := "Movie"] DT[get(storeidCol) == 1 & music_vs_video == "Video" & label_sc_group == "SelectO", release_product_type := "Web Videos"] ### MUVE ###------------------------- ## Muve, always Stream / Streaming DT[get(storeidCol) == 497, store_musicbucket := "Streaming"] ## Not all DT's will have the column transac_type_group if (DT %cont% "transac_type_group") DT[get(storeidCol) == 497, transac_type_group := "Stream"] ### YouTube / Vevo ###------------------------- ## VIDEO BUCKET - SIMPLE RULE: ## Video Services IF AND ONLY IF (YouTube or Vevo) ## YouTube: product is always "Video" and video bucket is always "Video Services" YouTubeIDs <- c(312, 453, 463, 569, 592, 45301, 45302, 45303) VevoIDs <- c(446) ## Dont modify '-1' stores that are already clasified ## SEE: DT[ !(get(storeidCol) == -1 & store_videobucket != zUncategorized) ] DT[ !(get(storeidCol) == -1 & store_videobucket != zUncategorized) | is.na(store_videobucket) | is.na(storeid)] DT[ !(get(storeidCol) == -1 & store_videobucket != zUncategorized) | is.na(store_videobucket) | is.na(storeid) , store_videobucket := ifelse(get(storeidCol) %in% c(YouTubeIDs, VevoIDs), "Video Services", "Retail Video") ] ## Clean up the music bucket, mostly for uniformity across different sources DT[ get(storeidCol) %in% c(YouTubeIDs, VevoIDs), store_musicbucket := "Streaming"] } ## ------------ END: store-dependent bucket cleaning ------------ ## } ## If buckest are not in DT, nothing else to clean if (!all(DT %cont% c("store_musicbucket", "store_videobucket"))) { setkeyIfNot(DT, key.bak, verbose=FALSE) return (invisible(DT)) } if (DT %cont% "ancillaryline") DT[!is.na(ancillaryline), store_musicbucket := ifelse(grepl("Physical", ancillaryline, ignore.case=TRUE), "Download", "Streaming")] ## Cleanup NA's in store_musicbucket/store_videobucket # DT[is.na(store_musicbucket) | store_musicbucket == "zOTHER STORESz", store_musicbucket := zUncategorized] # DT[is.na(store_videobucket) | store_videobucket == "zOTHER STORESz", store_videobucket := zUncategorized] DT[is.na(store_musicbucket) | grepl("^z(Uncategorized|OTHER STORE)", store_musicbucket, ignore.case=TRUE), store_musicbucket := zUncategorized] DT[is.na(store_videobucket) | grepl("^z(Uncategorized|OTHER STORE)", store_videobucket, ignore.case=TRUE), store_videobucket := zUncategorized] ## Unvategorized music bucket, with video bucket is "Retail Video" ==> music bucket is "Download" DT[store_musicbucket == zUncategorized, store_musicbucket := ifelse(store_videobucket=="Video Serivces", "Streaming", "Download")] # music bucket = "Other Services" ==> video bucket = "Video Services" # DT[store_videobucket == zUncategorized & store_musicbucket == "Other Services", store_videobucket := "Video Services"] # WHY? ## For Budget, etc, release_product_type is same as music_vs_video # WHY? DT[store_videobucket=="Retail Video" & music_vs_video == "Video" & label_sc_group %in% c("Allegro", "SelectO", "OSC"), release_product_type := "Movie"] # WHY? DT[store_videobucket=="Retail Video" & music_vs_video == "Video" & label_sc_group == "SelectO", release_product_type := "Web Videos"] setkeyIfNot(DT, key.bak, verbose=FALSE) return(invisible(DT)) } ## TODO: ---------------------------------------- ## TODO: UPDATE bi.aggregated_analytics ## TODO: SET stream_vs_download = 'Stream', ## TODO: transac_type_group = 'Stream' ## TODO: -- storeid 497 is Muve / Cricket ## TODO: WHERE storeid = 497 ## TODO: ; ## TODO: ------------------------------ ## TODO: ## TODO: UPDATE bi.aggregated_analytics ## TODO: SET music_vs_video = 'Video' ## TODO: -- , music_vs_video_by_transac = 'Video' ## TODO: -- , music_vs_video_by_tracktype = 'Video' ## TODO: -- , music_vs_video_by_product = 'Video' ## TODO: WHERE store_group = 'YouTube' ## TODO: ; ## TODO: ---------------------------------------- ## TODO: ## TODO: FROM Joanna: ## TODO: -- "the tethered download transactions for Muve prior to March should be listed as streams" ## TODO: -- "the download ringtones and ringback for muve should be under downloads of course" ## TODO: ## TODO: Music on storeid == 427 (Myspace Video) - how should that be considered ## TODO: Investigate this $0.90 line in DT.acc -- DT.acc[storeid == 29 & date > "2014-01-01"]