# top_automated_new_version.r # screen -xRR TopX ## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ## ## SETUP ## ## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ## setWidth(277) setScience(proj="Spotify_Accounting_ETL", subProj="BizReviewTopX", subl=FALSE) # source("~/git/orch/src/Spotify_Accounting_ETL/TopXQueries_Manual_Copies/get_query_top_labels.r") # source("~/git/orch/src/Spotify_Accounting_ETL/supportFns/TopX supportFns/get_endCols.r") # source("~/git/orch/src/Spotify_Accounting_ETL/supportFns/TopX supportFns/crop_and_format_DT.r") setGitBranchToSystem(); .g() sourceEntireFolder("~/git/orch/src/Spotify_Accounting_ETL/TopXQueries_Manual_Copies/") sourceEntireFolder("~/git/orch/src/Spotify_Accounting_ETL/supportFns/TopX supportFns/") lib(reshape2) library(bit64) N.top = 250 # X="labelid"; year=2015; country_code=NULL; X.nm=gsub("_?id$", "", tolower(X)); dateCol="activity_month"; tbl="accounting"; schema="bi"; maxMonth="auto" wh <- if ("BEAST" %in% sfShowWarehouses(showWarnings=FALSE)[trim(state) == "On", name]) "BEAST" else "LOOKER_WH_LARGE" dbname <- "prod" snowflake_inuse <- TRUE setSnowflake(wh=wh, dbname=dbname, start=TRUE) tbl <- "accounting" schema <- "bi" dateCol <- "activity_month" storeid = 286 types_using <- c("label"="labelid", "track"="trackid", "album"="releaseid") countries_using <- c("US", "GB", "SE", "ES", "NO", "DE", "NL", "MX", "AU", "BR", "DK", "FR", "IT", "FI", "CH", "AR", "TR") %>% unique %>% selfname_ years_using <- 2013:2015 N.limit_per_type = 100000 ## Sort the years years_using %<>% sort %>% rev ## Find the maxMonth maxDate_in_tbl <- qMaxDate(tbl=tbl, schema=schema, dateCol=dateCol, where=list(storeid=storeid), verbose=TRUE, snowflake_inuse=snowflake_inuse) catn("The max month in the system for the given filters is ", as.character(maxDate_in_tbl)) maxMonth <- month(maxDate_in_tbl) suffix.ytm <- sprintf(" (Jan-%s)", month.abb[maxMonth]) ## Load in high-level revshare data loadIfNotExists("DT.revshare") if ("net_revenue_usd" %ni% names(DT.revshare)) DT.revshare[, net_revenue_usd := payable_usd / payable * net_revenue] ## ---------------------------------------------------------------------------------------------------- ## # COMPUTE ORCHARD & SPOTIFY HIGH LEVEL REVENUE FOR ALL COUNTRIES ## ---------------------------------------------------------------------------------------------------- ## ### OLD: No percentages # old: DT.orchard_spotify_revenue_totals <- DT.revshare[month(month) <= maxMonth & year(month) %in% years_using # old: , list(orchard_rev_usd = sumn(payable_usd), spotify_netrev_usd = sumn(net_revenue_usd)) # old: , by=list(year=year(month), country_code) # old: ] # old: setkeyIfNot(DT.orchard_spotify_revenue_totals, year, country_code) ## NEW: add percentages ## Spotify Percent Premium DT.orchard_spotify_revenue_totals <- DT.revshare[month(month) <= maxMonth , list(orchard_rev_usd = sumn(payable_usd), spotify_netrev_usd = sumn(net_revenue_usd)) , by=list(year(month), country_code, premium=product!="A") ][ , list(orchard_percent_premium=orchard_rev_usd[(premium)]/sumn(orchard_rev_usd), orchard_rev_usd = sumn(orchard_rev_usd) , spotify_percent_premium=spotify_netrev_usd[(premium)]/sumn(spotify_netrev_usd), spotify_netrev_usd = sumn(spotify_netrev_usd)) , by=list(year, country_code)] setkeyIfNot(DT.orchard_spotify_revenue_totals, year, country_code) ## ---------------------------------------------------------------------------------------------------- ## # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SaveTheFirstTime <- TRUE file.outs <- emptylist(countries_using) for (cc in countries_using) { catn(" ---- PROCESSING '", cc, "' ----------") ## US donemanually # if (cc == "US") # next; ## COMPUTE TOP LEVEL AMMOUNTS -- WILL BE ADDED IN LATER ## ---------------------------------------------------------------------------------------------------- ## colsTopLevel_Orch_Spot <- extract(c("percent|_usd"), DT.orchard_spotify_revenue_totals) DT.top_line_percentages_for_country <- lapply(colsTopLevel_Orch_Spot, function(col) DT.orchard_spotify_revenue_totals [country_code %in% c(cc) & year %in% years_using][, country_code := NULL] %>% dcast(. ~ year, value.var=col) %>% {as.data.table(.)[, -1, with=FALSE]} %>% {setnames(., paste(col, names(.)))} %>% {.} ) %>% unname %>% do.call(cbind, .) ## ---------------------------------------------------------------------------------------------------- ## .get_DT <- .%>% get_DT_for_country(type=., cc=cc, maxMonth=maxMonth, N.limit_per_type=N.limit_per_type, years_using=years_using, DT.top_line_percentages_for_country=DT.top_line_percentages_for_country, jesus=FALSE) DT.labels_country <- .get_DT("labelid"); if (SaveTheFirstTime) jesusForData(DT.labels_country) DT.releases_country <- .get_DT("releaseid"); if (SaveTheFirstTime) jesusForData(DT.releases_country) DT.tracks_country <- .get_DT("trackid"); if (SaveTheFirstTime) jesusForData(DT.tracks_country) SaveTheFirstTime <- FALSE # ## ORGANIZE TEMP # startCols <- c("country_code", "labelid", "label_name", "label_priority", "label_owner", "is_d3", "supply_chain_group", "label_country", "artistid", "artist_name", "artist_country", "releaseid", "release_name", "release_date", "release_marketing_priority", "release_version", "is_compilation", "is_deleted", "release_genre", "isrc", "track_name" ) # setcolorderpt(DT.labels_country, startCols=startCols, endCols=get_endCols("label")) # setcolorderpt(DT.releases_country, startCols=startCols, endCols=get_endCols("release")) # setcolorderpt(DT.tracks_country, startCols=startCols, endCols=get_endCols("track")) listDTs <- list(labels=crop_and_format_DT(DT.labels_country, N.top=N.top, type_noid="label") , albums=crop_and_format_DT(DT.releases_country, N.top=N.top, type_noid="album") , tracks=crop_and_format_DT(DT.tracks_country, N.top=N.top, type_noid="track") ) info <- sprintf("Spotify_%s_Top_%i_for_%s_Jan_to_%s_%i_to_%i", cc, N.top, pasteC(names(listDTs), C="_"), month.abb[maxMonth], min(years_using), max(years_using)) f.out <- out.p("TopX", info, ext="xlsx") if (!file.exists(dirname(f.out))) dir.create(dirname(f.out), recursive=TRUE) if (file.exists(f.out)) zArchive(f.out, addTimeStamp=TRUE) # names(listDTs) <- paste(cc, toupper(names(listDTs))) file.outs[[cc]] <- exportXLS.usingXLConnect(f.out=f.out, DTs.list=listDTs) quickEmail(getRS(), f.out, subject=info) try({ j.dts <- lsosdt(pattern=sprintf("_%s_", cc), envir=globalenv())$name jesusForData(objNames=j.dts, envir=globalenv()) }) } ## For when testing, interactively if (FALSE) notifyAndEmail("comeback") jesusForData() print(unlist(file.outs) %>% cbind) # OLD SCRATCH :: # OLD SCRATCH :: # OLD SCRATCH :: # OLD SCRATCH :: # OLD SCRATCH :: DT.country # OLD SCRATCH :: qMinDate("fact_sales", dateCol="activityperiodid", whereIn=list(storeid=286, labelid=24957)) # OLD SCRATCH :: # OLD SCRATCH :: "country_code" # OLD SCRATCH :: "labelid" # OLD SCRATCH :: "label_name" # OLD SCRATCH :: "label_priority" # OLD SCRATCH :: "label_owner" # OLD SCRATCH :: "is_d3" # OLD SCRATCH :: "supply_chain_group" # OLD SCRATCH :: "label_country" # OLD SCRATCH :: # OLD SCRATCH :: # OLD SCRATCH :: setcolorderpt(DT.country, endCols=c(grossCols, rankCols)) # OLD SCRATCH :: cbind(names(DT.country)) # OLD SCRATCH :: # OLD SCRATCH :: # OLD SCRATCH :: DT.confirm <- { # OLD SCRATCH :: sfQry(" # OLD SCRATCH :: SELECT country_code, labelid, year(activity_month) as year, activity_month, sum(gross) AS gross # OLD SCRATCH :: FROM bi.accounting # OLD SCRATCH :: WHERE labelid = 16581 # OLD SCRATCH :: AND year(activity_month) >= 2013 # OLD SCRATCH :: AND country_code = 'SE' # OLD SCRATCH :: AND storeid = 286 # OLD SCRATCH :: GROUP BY 1, 2, 3, 4 # OLD SCRATCH :: ORDER BY 4")} # OLD SCRATCH :: DT.confirm[, list(gross=sum(gross)), keyby=list(country_code, labelid, year, first_five_months=month(activity_month) <= 5)] [(first_five_months)] # OLD SCRATCH :: DT.country[labelid==16581, cbind(sapply(grossCols, function(x) get(x)) %>% {.[order(names(.))]})] # OLD SCRATCH :: sapply(quantile(x, c(10, 20, 50, 80, 90)/100, na.rm=TRUE), . %>% {percTrue(x >= .)}) %>% fwp # OLD SCRATCH :: DT.country[above_quantile(label_total_gross_for_2015, .50) & above_quantile(label_total_gross_for_2014, .30) & above_quantile(label_total_gross_for_2013, .20)][order(`Rank Increase 2015 vs 2014`, decreasing=TRUE)] # OLD SCRATCH :: DT.country[above_quantile(label_total_gross_for_2015, .50) & above_quantile(label_total_gross_for_2014, .30) & above_quantile(label_total_gross_for_2013, .20)][`Rank Increase 2015 vs 2014` > `Rank Increase 2014 vs 2013` & `Rank Increase 2014 vs 2013` > 0][order(`label_rank_for_2015`, decreasing=FALSE)][1:22] # OLD SCRATCH :: DT.country[order(`label_total_gross_for_2015`, decreasing=TRUE)][1:22]