## APPROVED TITLES { cuts.paidunits <- (1/7) * c(-Inf,-250, -1, 0, 1, 3.5, 7, 21, 50, 100, 250, 400, 800, 1500, 5000, 10000, 60000, Inf) cuts.for_perc_of_weeks <- c(-Inf, -1, -1e-16, 0, 0.7, 0.9, 1, 5, Inf) tranascs_not_using <- c("US", "DV") byCols <- c("storeid", "transac_type_abbr", "category", "releaseid") byCols.no_r <- setdiff(byCols, "releaseid") byCols.no_r.no_c <- setdiff(byCols, c("releaseid", "category")) } { ## The threshold for how many units to have been sold in each week, on an daily-average basis thresh.daily_avg_paidunits <- c(DA=1.5, DT=4, TD=20, S=75, AS=400, AEA=900) # in units per day ## Filter to just iTunes--"DA" DT.weekly_percs <- rbind( copy(DT.weekly_avg.apprvd_titles)[transac_type_abbr %ni% tranascs_not_using][, source := "APVD"] , copy(DT.weekly_avg.owned_and_tvt)[transac_type_abbr %ni% tranascs_not_using][, source := "OTVT"] ) ## The number of weeks in the explored period DT.weekly_percs[, totalWeeks_byStoreTrans := lunique(week_number), by=byCols.no_r.no_c] ## However, for newer releases, this will not be accurate addColsFrom_(DT.weekly_percs, rbind(DT.rel_dates.apprvd_titles, DT.rel_dates.owned_and_tvt), colsToBring=c("weeks_released_by20150206", "category"), joinCols.rec="releaseid", joinCols.giv="UPC") ## TEMPORARY: There is data missing for Spotify if (!nrow(DT.counts.apprvd_titles[storeid == 286][date > "2015-01-05" & date < "2015-01-14"])) { DT.weekly_percs[storeid == 286, weeks_released_by20150206 := max(weeks_released_by20150206 - 1, 0), by=weeks_released_by20150206] } DT.weekly_percs[, expectedWeeks := min(totalWeeks_byStoreTrans, weeks_released_by20150206), by=byCols] ## CONFRIM all transac types are accounted for stopifnot(unique(DT.weekly_percs$transac_type_abbr) %in% names(thresh.daily_avg_paidunits)) ## Convert week_number to factor DT.weekly_percs[, week_number := factor(week_number)] ## Determine if the average daily paidunits is above the threshold DT.weekly_percs[, paidunits_above_limit := paidunits > thresh.daily_avg_paidunits[transac_type_abbr]] ## Determine what percent of the total weeks is above the threshold ## NOTE: percent_of_weeks_above_thresh > 1.00 are possible and signify a pre-release DT.weekly_percs2 <- copy(DT.weekly_percs[!is.na(category), list(percent_of_weeks_above_thresh = sum(paidunits_above_limit) / unique(expectedWeeks)), keyby=byCols]) ## find any releases that are NOT represented in a store-transac group, and add a 0 for them ## This also captures any releases that are on the original CSV list but did not make it through the query ## ------ Three Steps ------- ## ## First get a list of all stores-transactions-releases tmp_DT.all_releases_by_all_stores_and_transacs <- setkey(rbind( DT.counts.apprvd_titles[transac_type_abbr %ni% tranascs_not_using, DT.rel_dates.apprvd_titles[, list(category, UPC)], keyby=byCols.no_r.no_c] , DT.counts.owned_and_tvt[transac_type_abbr %ni% tranascs_not_using, DT.rel_dates.owned_and_tvt[, list(category, UPC)], keyby=byCols.no_r.no_c] )) ## Second, join to the weekly percents table DT.weekly_percs2 <- DT.weekly_percs2[tmp_DT.all_releases_by_all_stores_and_transacs, allow=TRUE] ## Third, change the NAs to 0 DT.weekly_percs2[is.na(percent_of_weeks_above_thresh), percent_of_weeks_above_thresh := 0] ## Cleanup rm(tmp_DT.all_releases_by_all_stores_and_transacs) ## -------------------------- ## DT.weekly_percs2[, table(round(percent_of_weeks_above_thresh, 1))] } { ## Cut the percentages according to grouping DT.weekly_percs2[, cut.perc_of_wks := cut(percent_of_weeks_above_thresh, breaks=cuts.for_perc_of_weeks)] ## identify Victory Records DT.weekly_percs2[, victory := releaseid %in% UPCs.victory] ## For each releaseid, I want to know the groups it participates in DT.weekly_percs2[, group_info := sprintf("%s%s-%s-%s:%s", ifelse(victory, "VIC-", ""), substr(category, 1, 4), storeid, transac_type_abbr, cut.perc_of_wks), by=list(storeid, transac_type_abbr)] ## I believe all of the "pre-releases" are a relatively small bunch. Put those all in one group DT.weekly_percs2[percent_of_weeks_above_thresh > 1, group_info := "Pre-Release"] ## Count how many in each group. Anything less than 9 needs to be reworked groups_to_rework <- DT.weekly_percs2[, lunique(releaseid), by=group_info][(V1 < 9), unique(group_info)] DT.weekly_percs2[group_info %in% groups_to_rework, group_info := "zOTHERz"] ## CREATE GROUPS ## intialize DT.weekly_percs2[, grouping := NA_character_] DT.weekly_percs2[, special_treatment := FALSE] ## NA categroies do not get changed DT.weekly_percs2[is.na(category), grouping := "CATEGROY IS NA -- DONT CHANGE"] DT.weekly_percs2[is.na(category), special_treatment := TRUE] ## ---- VICTORY ------- ## ## For victory, just use DA from iTunes DT.weekly_percs2[(victory)] [storeid == 1 & transac_type_abbr == "DA", .N, by=cut.perc_of_wks] ## < SEE > DT.weekly_percs2[(victory) & storeid == 1 & transac_type_abbr == "DA" , grouping := ifelse(grepl(",0]", cut.perc_of_wks, fixed=TRUE), "Victory Mostly Zero", "Victory Some Sales" )] ## Distribute to the whold release DT.weekly_percs2[(victory), grouping := grouping[!is.na(grouping)], by=releaseid] ## If there are still NAs (There shouldnt be), use the cut, without filtering to itunes DT.weekly_percs2[(victory) & is.na( grouping), grouping := if (any(grepl(",0]", cut.perc_of_wks, fixed=TRUE))) "Victory Mostly Zero" else "Victory Some Sales", by=releaseid] ## Mark it as special treatment DT.weekly_percs2[(victory), special_treatment := TRUE] ## ---- VICTORY ------- ## ## Use all groups DT.weekly_percs2[, tmp.group_string := pasteC(sort(unique(group_info)), C="_x_"), by=releaseid] groups_ok <- DT.weekly_percs2[is.na(grouping), list(N=lunique(releaseid)), by=tmp.group_string][N >= 9, unique(tmp.group_string)] DT.weekly_percs2[tmp.group_string %in% groups_ok, grouping := tmp.group_string] DT.weekly_percs2[is.na(grouping)][, list(releasese=lunique(releaseid), rows=.N), by=tmp.group_string] ## Use only iTunes and Spotify DT.weekly_percs2[, tmp.group_string.1_286 := pasteC(sort(unique(group_info[storeid %in% c(1, 286)])), C="_x_"), by=releaseid] groups_ok <- DT.weekly_percs2[is.na(grouping), list(N=lunique(releaseid)), by=tmp.group_string.1_286][N >= 9, unique(tmp.group_string.1_286)] DT.weekly_percs2[tmp.group_string.1_286 %in% groups_ok & is.na(grouping), grouping := tmp.group_string.1_286] DT.weekly_percs2[is.na(grouping)][, list(releasese=lunique(releaseid), rows=.N), by=tmp.group_string.1_286] ## Use only iTunes DT.weekly_percs2[, tmp.group_string.1 := pasteC(sort(unique(group_info[storeid %in% c(1)])), C="_x_"), by=releaseid] groups_ok <- DT.weekly_percs2[is.na(grouping), list(N=lunique(releaseid)), by=tmp.group_string.1][N >= 9, unique(tmp.group_string.1)] DT.weekly_percs2[tmp.group_string.1 %in% groups_ok & is.na(grouping), grouping := tmp.group_string.1] DT.weekly_percs2[is.na(grouping)][, list(releasese=lunique(releaseid), rows=.N), by=tmp.group_string.1] ## Use only iTunes DA or DT DT.weekly_percs2[, tmp.group_string.1DADT := pasteC(sort(unique(group_info[storeid %in% c(1) & transac_type_abbr %in% c("DA", "DT")])), C="_x_"), by=releaseid] groups_ok <- DT.weekly_percs2[is.na(grouping), list(N=lunique(releaseid)), by=tmp.group_string.1DADT][N >= 9, unique(tmp.group_string.1DADT)] DT.weekly_percs2[tmp.group_string.1DADT %in% groups_ok & is.na(grouping), grouping := tmp.group_string.1DADT] DT.weekly_percs2[is.na(grouping)][, list(releasese=lunique(releaseid), rows=.N), by=tmp.group_string.1DADT] ## Use only iTunes DA, ALLOWING FOR 4 release per group DT.weekly_percs2[, tmp.group_string.1DA := pasteC(sort(unique(group_info[storeid %in% c(1) & transac_type_abbr %in% c("DA")])), C="_x_"), by=releaseid] groups_ok <- DT.weekly_percs2[is.na(grouping), list(N=lunique(releaseid)), by=tmp.group_string.1DA][N >= 4, unique(tmp.group_string.1DA)] DT.weekly_percs2[tmp.group_string.1DA %in% groups_ok & is.na(grouping), grouping := tmp.group_string.1DA] DT.weekly_percs2[is.na(grouping)][, list(releasese=lunique(releaseid), rows=.N), by=tmp.group_string.1DA] ## Whatever is left gets lumped into a single group DT.weekly_percs2[is.na(grouping), grouping := "zOTHERz"] ## Cleanup all tmp cols tmp.tmpCols <- grep("^tmp\\.", names(DT.weekly_percs2), value=TRUE) DT.weekly_percs2[, (tmp.tmpCols) := NULL] rm(tmp.tmpCols) ## Clean up grouping name to simple letters DT.weekly_percs2[!(special_treatment), grouping := factor(grouping, labels=getLLL(3)[seq(unique(grouping))] ) ] ## DONE DT.weekly_percs2 }