# create_DT.iTunes.acc_v_anal.r fileSelfDescription( "creates DT.iTunes.acc_v_anal by first calling create_DT.acc_v_anal() then filtering the results to DT.acc_v_anal[storeid == 1] DT.acc_v_anal is itself created from: * DT.totalpaidunits.acc (raw pull from fact_sales) * DT.totalpaidunits.anal (raw pull from fact_analytics) * DT.totalpaidunits.aErr (raw pull from fact_analytics_error) (the latter two are rbind then merged with the first) " ) if (exists("DT.iTunes.acc_v_anal")) { ## Make sure that each group has the full range of dates setkeyIfNot(DT.iTunes.acc_v_anal, "date", superset.ok=TRUE) DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[CJ_allDatesByCols(DT.iTunes.acc_v_anal, dateCol="date", universal.range=TRUE)] } create_DT.iTunes.acc_v_anal <- function( assign.to="DT.iTunes.acc_v_anal" , envir.to.assign=globalenv() ## rows to ignore, based on meta , transid_to_ignore = c(33:36) , countryid_to_ignore = c() , storeid_to_ignore = c(Hulu=392, YouTube=453, "YouTube Movies"=463) ## min and max date , minDate = as.Date("2012-11-01") , maxDate = monthFloor(today() - 63) ## whether to include each of these meta columns , include_trans = TRUE , include_country = FALSE , include_label = FALSE , include_sc_group = FALSE , add_asr = TRUE , expand_all_dates = TRUE , dont_crop_at_maxDate = FALSE # make TRUE for predicting ## Cluster Using , cluster.using = 09 , jesus = TRUE , jesus.itunes = jesus , jesus.main_DT = jesus.itunes ## Whether to repull data. allow for these to already have been assigned in environment , envir.working = environment() , re_pull_data = if (exists("re_pull_data", envir=parent.frame())) get("re_pull_data", envir=parent.frame()) else TRUE , confirm = if (exists("confirm", envir=parent.frame())) get("confirm", envir=parent.frame()) else !dont_crop_at_maxDate ) { force(re_pull_data) force(confirm) setScienceIfNot("Acc_vs_Anal_2015", load=FALSE) if (dont_crop_at_maxDate && confirm) { message("When dont_crop_at_maxDate is TRUE, the confirmation steps will fail, hence setting that to FALSE") confirm <- FALSE } browser(expr=inDebugMode("create_DT.iTunes.acc_v_anal"), text="top of create_DT.iTunes.acc_v_anal") DT.acc_v_anal <- create_DT.acc_v_anal( assign.to="DT.acc_v_anal" , envir.to.assign=envir.to.assign , re_pull_data = re_pull_data , include_country = include_country , include_trans = include_trans , include_label = include_label , include_sc_group = include_sc_group , minDate = minDate , maxDate = maxDate , storeid_to_ignore = storeid_to_ignore , transid_to_ignore = countryid_to_ignore , countryid_to_ignore = countryid_to_ignore , stores_only = 1 , jesus = jesus.main_DT , confirm = confirm , dont_crop_at_maxDate = dont_crop_at_maxDate , cluster.using = 09 ) kCols <- key(DT.acc_v_anal) cat("kCols (key of DT.acc_v_anal) is " , pasteQand(kCols, q=" "), fill=TRUE) ## suffixes and the main columns suffixes <- c(".acc", ".anal") revCols <- paste0("revenue", suffixes) unitsCols <- paste0("paidunits", suffixes) revAndUnitsCols <- c(revCols, unitsCols) ## --------------------------------------------------------- #`# DT.iTunes.acc_v_anal <- DT.acc_v_anal[storeid == 1] ## Drop specific rows (by country or trans value, as designated) if ("transid" %in% names(DT.iTunes.acc_v_anal)) DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[transid %ni% transid_to_ignore] if ("countryid" %in% names(DT.iTunes.acc_v_anal)) DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[countryid %ni% countryid_to_ignore] ## Drop this row: There is a single month of (-1 paidunits) for transid==30, which occurs two months before any other transid ==30. DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[!(transid == 30 & date == "2014-02-01" & paidunits.anal == -1)] ## Drop this row: It is NA for accounting DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[!(transid == 4 & date == "2012-11-01" & is.na(paidunits.acc))] ## UT(7) & UA(^) represent almost negligible revenue. (UT == (- $75) in all of 2014; UA == $0 in 2014) ## Drop these and drop any other transid that has not had a transaction in the last 8 months transid.keeping <- DT.iTunes.acc_v_anal[!is.naor0(paidunits.acc) & date >= today() - 32*8, unique(transid)] transid.dropping <- DT.iTunes.acc_v_anal[, setdiff(transid, transid.keeping)] if (length(transid.dropping)) warning("In addition to transid %in% c(6, 7, 27), we are also dropping transid %in% c", pasteQ(transid.dropping, q="")) DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[transid %ni% c(transid.dropping, 6, 7, 27)] message("!!! Dont forget about iTunes RADIO (27) !!! ") if (!dont_crop_at_maxDate) { ## Drop any tailing NAs date_crop_candidates <- DT.iTunes.acc_v_anal[, list(date=date[locateLastNonNA(paidunits.anal, showWarnings=FALSE)]), keyby=transid][, table(date)] max_date_to_crop_to <- max(names(date_crop_candidates)) ## Before cropping, confirm that the last date is in fact the most prevelant. (ie, there might be some transids that have NAs sooner or even all throughout) if (max_date_to_crop_to != names(which.max(date_crop_candidates))) { stop ("The max date in date_crop_candidates (", max_date_to_crop_to ,") is not the most prevelant. ie, not sure where to crop. Please investigate manually") } else { ## min between max_date_to_crop and month before maxDate ## Also, not that due to 544-cleanup, we might get some rows just below the minDate. Crop those out ## UPDATE: Changing from `<=` to strictly `<` # DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[date <= min(as.Date(max_date_to_crop_to), prevMonth(maxDate)) & date >= minDate] DT.iTunes.acc_v_anal <- DT.iTunes.acc_v_anal[date < min(as.Date(max_date_to_crop_to), prevMonth(maxDate)) & date >= minDate] } } ## Add column counting how many months-back DT.iTunes.acc_v_anal[, months_back_from_max_date := monthsDiff(max(date), date)] DT.iTunes.acc_v_anal[, more_than_six_months_ago := months_back_from_max_date > 6] DT.iTunes.acc_v_anal[, months_back_TRANSFORMED := transform_months_back(months_back_from_max_date)] ## Add transac_type_abbr columns, cleaning up DR, DV and DT/DA addTransacInfo_(DT.iTunes.acc_v_anal, short.description=FALSE, music=TRUE) ## Designate these as simply Music or Video DT.iTunes.acc_v_anal[transac_type_abbr == "DR", music_vs_video_by_transac := "Music"] DT.iTunes.acc_v_anal[transac_type_abbr == "DV", music_vs_video_by_transac := "Video"] ## Download track and download Album are an order of magnitude more revenue compared to all other transac types DT.iTunes.acc_v_anal[, is_DTorDA := transac_type_abbr %in% c("DT", "DA")] ## There should be no negative values stopifnot(!any( DT.iTunes.acc_v_anal[, sapply(.SD, function(x) x < 0), .SDcols=revAndUnitsCols], na.rm=TRUE )) # cbind(DT.iTunes.acc_v_anal[, as.character(date)], DT.iTunes.acc_v_anal[, sapply(.SD, function(x) x < 0), .SDcols=revAndUnitsCols]) ## Cleanup columns DT.iTunes.acc_v_anal[, transac_type_abbr := factor(transac_type_abbr)] convertLogicalToYN_(DT.iTunes.acc_v_anal) ## Add log-columns for paidunits and revenue DT.iTunes.acc_v_anal[, paste0("log.", revAndUnitsCols) := lapply(.SD, log10), .SDcols=revAndUnitsCols] setcolorderpt(DT.iTunes.acc_v_anal, startCols=unique(c(kCols, extract("trans", DT.iTunes.acc_v_anal), "is_DTorDA", "more_than_six_months_ago", "months_back_from_max_date"))) ## Optionally add as-reported revenue column from mbworkbook if (isTRUE(add_asr)) addRevenueFromWorksheet_(DT.iTunes.acc_v_anal) ## Optionally save the DT. (Note that the flag to save is incorporated into jesusForData() ) jesusForData(DT.iTunes.acc_v_anal, envir=envir.working, doNothing=!isTRUE(jesus.itunes)) ## RM PREVIOUS VERSION, JUST IN CASE if (exists(assign.to, envir=envir.to.assign, inherit=FALSE)) { message("removing previous version of ", assign.to, " in environment ", cleanEnvirString(envir.to.assign)) rm(list=assign.to, envir=envir.to.assign) } if (!is.null(assign.to)) assign( assign.to , value = DT.iTunes.acc_v_anal , envir = envir.to.assign ) # assignWithInfo( # name = assign.to # , value = DT.iTunes.acc_v_anal # , info = "??" # , envir = envir.to.assign, warnOnEnvir=FALSE # ) return(invisible(DT.iTunes.acc_v_anal)) }