Untouched Content.r All UPC-TrackIDs in existance All UPC-TrackIDs by store setScience("Untouched_Content", create=FALSE, subl=FALSE, load=FALSE) minDate <- "2014-01-01" maxDate <- today() %>% prevMonth %>% EndOfMonth wh_using <- "SCIENCE" tbl.relases <- "dim_release" schema.releases <- "production" # sfWarehouseOn(wh_using) qShowCols("dim_release") colsToPull.releases <- c("labelid", "artistid", "releaseid", "releasedate", "sale_start_date", "deletions", "itunes_previewable", "release_status", "sync_only") dateCols <- c("sale_start_date", "releasedate") where <- sprintf("%s <= '%s'", dateCols, maxDate) ## RELEASES q.releases <- makeQry(colsToPull=colsToPull.releases, aggFunc=NULL, tbl=tbl.relases, schema=schema.releases, where=where, conjunction="AND", dateCol="releasedate", limit = NULL) DT.all_releases <- q.releases %>% runQry jesusForData(DT.all_releases) ## TRACKS q.tracks <- makeQry(colsToPull=c("trackid", releaseid="upc"), aggFunc=NULL, tbl="dim_track", schema=schema.releases, limit = NULL) DT.all_tracks <- runQry(q.tracks) addColsFrom_(DT.all_tracks, DT.all_releases, colsToBring=c("releasedate", "deletions"), joinCols="releaseid") DT.all_tracks[is.na(releasedate), releasedate := .origin] DT.all_tracks[is.na(deletions), deletions := 'N'] dateCol.fact="accountingperiodid" colsToPull.fact <- c("releaseid", "storeid", "isrcid", "trackid", dateCol.fact) q.sales <- makeQry(tbl="fact_sales", colsToAgg=NULL, dateCol=dateCol.fact, colsToPull=colsToPull.fact, aggFunc=NULL, distinct=TRUE, maxDate=maxDate, minDate=minDate) DT.releases_with_sales <- runQry(q.sales) jesusForData(DT.releases_with_sales) ## RELEASES setkeyIfNot(DT.all_releases, "deletions", "labelid", "artistid", "releaseid", organize=TRUE) releases_2014 <- DT.all_releases[!.("Y")][releasedate < '2015-01-01', unique(as.character(releaseid))] releases_current <- DT.all_releases[!.("Y")][, unique(as.character(releaseid))] ## totals total_release_2014 <- length(releases_2014) total_release_current <- length(releases_current) ## TRACKS setkeyIfNot(DT.all_tracks, "deletions", "trackid", organize=TRUE) tracks_2014 <- DT.all_tracks[!.("Y")][releasedate < '2015-01-01', unique(as.character(trackid))] tracks_current <- DT.all_tracks[!.("Y")][, unique(as.character(trackid))] ## totals total_track_2014 <- length(tracks_2014) total_track_current <- length(tracks_current) { cat("Total RELEASES == ", formnumb(total_release_current), sep="", fill=TRUE) cat("Total TRACKS == ", formnumb(total_track_current), sep="", fill=TRUE) } DT.releases_with_sales[, releaseid_char := as.character(releaseid)] DT.samp <- {set.seed(1); copy(DT.releases_with_sales[sort(sample(nrow(DT.releases_with_sales), 10000, FALSE))])} # DT.samp <- {DT.releases_with_sales} pid.Jan01_2014 <- 181 pid.Jan01_2015 <- 193 pid.max <- DT.samp[, max(accountingperiodid)] ## BY UPC s.t({ release_count_per_store.2014 <- DT.samp[accountingperiodid < pid.Jan01_2015][accountingperiodid >= pid.Jan01_2014] [, 1L, keyby=list(storeid, releaseid)][, .N, keyby=storeid] release_count_per_store.2015 <- DT.samp[accountingperiodid >= pid.Jan01_2015] [, 1L, keyby=list(storeid, releaseid)][, .N, keyby=storeid] release_count_per_store.by_month <- DT.samp[accountingperiodid >= pid.Jan01_2014] [, 1L, keyby=list(accountingperiodid, storeid, releaseid)][, .N, keyby=list(accountingperiodid, storeid)] addStoreName.storeid_(release_count_per_store.2014, colsToBring=c("store_name", "store_is_in_analytics")) addStoreName.storeid_(release_count_per_store.2015, colsToBring=c("store_name", "store_is_in_analytics")) addStoreName.storeid_(release_count_per_store.by_month, colsToBring=c("store_name", "store_is_in_analytics")) release_count_per_store.2014[, perc_per_store_in_2014 := as.perc(N / total_release_2014)] release_count_per_store.2015[, perc_per_store_in_2015 := as.perc(N / total_release_current)] release_count_per_store.by_month[, perc_per_store_by_month := as.perc(N / total_release_current)] release_count_per_store.2014[, perc_string := fwp(perc_per_store_in_2014)] release_count_per_store.2015[, perc_string := fwp(perc_per_store_in_2015)] release_count_per_store.by_month[, perc_string := fwp(perc_per_store_by_month)] }) ## BY TRACK s.t({ track_count_per_store.2014 <- DT.samp[accountingperiodid < pid.Jan01_2015][accountingperiodid >= pid.Jan01_2014] [, 1L, keyby=list(storeid, trackid)][, .N, keyby=storeid] track_count_per_store.2015 <- DT.samp[accountingperiodid >= pid.Jan01_2015] [, 1L, keyby=list(storeid, trackid)][, .N, keyby=storeid] track_count_per_store.by_month <- DT.samp[accountingperiodid >= pid.Jan01_2014] [, 1L, keyby=list(accountingperiodid, storeid, trackid)][, .N, keyby=list(accountingperiodid, storeid)] addStoreName.storeid_(track_count_per_store.2014, colsToBring=c("store_name", "store_is_in_analytics")) addStoreName.storeid_(track_count_per_store.2015, colsToBring=c("store_name", "store_is_in_analytics")) addStoreName.storeid_(track_count_per_store.by_month, colsToBring=c("store_name", "store_is_in_analytics")) track_count_per_store.2014[, perc_per_store_in_2014 := as.perc(N / total_track_2014)] track_count_per_store.2015[, perc_per_store_in_2015 := as.perc(N / total_track_current)] track_count_per_store.by_month[, perc_per_store_by_month := as.perc(N / total_track_current)] track_count_per_store.2014[, perc_string := fwp(perc_per_store_in_2014)] track_count_per_store.2015[, perc_string := fwp(perc_per_store_in_2015)] track_count_per_store.by_month[, perc_string := fwp(perc_per_store_by_month)] }) ## SHOW ## --------------------------------------------- ## release_count_per_store.2014[, perc_string := fwp(perc_per_store_in_2014)] release_count_per_store.by_month[, perc_string := fwp(perc_per_store_by_month)] release_count_per_store.2014[(store_is_in_analytics)] release_count_per_store.by_month[(storeid == 286)] release_count_per_store.by_month[(storeid == 1)] ## --------------------------------------------- ## track_count_per_store.2014[, perc_string := fwp(perc_per_store_in_2014)] track_count_per_store.by_month[, perc_string := fwp(perc_per_store_by_month)] track_count_per_store.2014[(store_is_in_analytics)] track_count_per_store.by_month[(storeid == 286)] track_count_per_store.by_month[(storeid == 1)] ## --------------------------------------------- ## jesusForData( release_count_per_store.2014 , release_count_per_store.2015 , release_count_per_store.by_month , track_count_per_store.2014 , track_count_per_store.2015 , track_count_per_store.by_month , stampDir=TRUE)