THIS IS NOW OLD
            USE: "~rsaporta/git/orch/src/Spotify_Accounting_ETL/market_share_by_release_month.R"










screen -xRR MarketShare
























setScience(proj="Spotify_Accounting_ETL", subProj="marketShare")
loadIfNotExists("DT.revshare")

## ------------------------------------------------------------------------------------ ##
## THIS LITTLE QUERY CONFIRMS THAT THE 'SPOTIFY APRIL' ISSUE IS PROPERLY ADJUSTED IN THE GROSS REVENUE
    DT.April <- runQry("
    SELECT
      activity_month,
      SUM(acc.gross_revenue_usd) AS gross_revenue_usd,
      SUM(acc.gross_revenue_usd_spotify_unreversed) AS gross_revenue_usd_spotify_unreversed
    FROM BI.ACCOUNTING AS acc
    WHERE 
    acc.activity_month >= '2014-02-01'
    AND acc.activity_month <= '2014-07-01'
    AND acc.storeid = '286'
    GROUP BY 1")

    DT.April_melt <- reshape2::melt(DT.April, "activity_month")
    jesusForData(DT.April_melt)

    ggLinegraph(DT.April_melt, color="variable", x="activity_month", y="value")
## ------------------------------------------------------------------------------------ ##

QRY <- 
"
SELECT 
    acc.activity_month
  , r.month as spotify_activity_month
  , acc.country_code
  , r.country_code as spotify_country_code
  , acc.adsupported_vs_premium
  , r.adsupported_vs_premium as spotify_adsupported_vs_premium
  , acc.release_front_mid_or_cat
  , acc.gross_revenue_usd
  , r.payable_to_orchard_usd
  , r.spotify_gross_revenue_usd
  , streams_fact_sales
  , orchard_streams
  , spotify_total_streams
FROM 
(
  SELECT
    activity_month::date AS activity_month,
    country_code,
    release_front_mid_or_cat AS release_front_mid_or_cat,
    CASE WHEN transac_type_abbr = 'AS' THEN 'AdSupported'
         WHEN transac_type_abbr = 'S'  THEN 'Premium'
         ELSE 'Other'
     END AS adsupported_vs_premium,
    SUM(units) AS streams_fact_sales,
    SUM(gross_revenue_usd) AS gross_revenue_usd
  FROM BI.ACCOUNTING
  WHERE activity_month >= '2013-01-01'
    AND storeid = '286'
  GROUP BY 1, 2, 3, 4
) AS acc
FULL JOIN
(
  SELECT
   month::date as month,
   country_code,
   adsupported_vs_premium, 
   sum(gross_revenue) as spotify_gross_revenue_usd,
   sum(payable_usd) as payable_to_orchard_usd,
   sum(rightholders_tracks) as orchard_streams,
   sum(total_tracks) as spotify_total_streams
  FROM SPOTIFYMGMT.REVSHARE
  WHERE month >= '2013-01-01'
  GROUP BY 1, 2, 3
) r
ON    acc.activity_month = r.month 
  AND acc.adsupported_vs_premium = r.adsupported_vs_premium
  AND acc.country_code = r.country_code
"
DT.spot_review <- sfQry(QRY, wh="LOOKER_WH_LARGE")
# setnames(DT.spot_review, "adsupported_vs_premium.1", "spotify_adsupported_vs_premium")
kCols <- names(DT.spot_review) %>% head(7)
setkeyIfNot(DT.spot_review, kCols)

DT.spot_market_share <- DT.spot_review[spotify_activity_month < "2015-06-01"]

## SEE HOW MANY $ THE NA'S REPRESENT
DT.spot_market_share[, lapply(.SD, sumn), keyby=list(nas=is.na(activity_month) | is.na(payable_to_orchard_usd)), .SDcols=c("spotify_gross_revenue_usd", "payable_to_orchard_usd", "gross_revenue_usd")] %>% formnumb(round=0)

## THE NA'S ARE NEGLIGIBLE;  DROP THEM
DT.spot_market_share <- DT.spot_market_share[!is.na(activity_month) & !is.na(payable_to_orchard_usd)]

if (!nrow(DT.spot_market_share[(country_code != spotify_country_code)]))
  DT.spot_market_share[, spotify_country_code := NULL]
if (!nrow(DT.spot_market_share[(adsupported_vs_premium != spotify_adsupported_vs_premium)]))
  DT.spot_market_share[, spotify_adsupported_vs_premium := NULL]
if (!nrow(DT.spot_market_share[(activity_month != spotify_activity_month)]))
  DT.spot_market_share[, spotify_activity_month := NULL]

if (FALSE) {
 jesusForData(DT.spot_market_share)
#  ~~~~~~~~~
 loadFromJesus("~rsaporta/git/orch/data/Spotify_Accounting_ETL/DT.spot_market_share-20150827_0551-9605x10.RDS", over=TRUE)
}

measureCols <- nwhich(canBeNumeric(DT.spot_market_share))
dimCols <- c("activity_month", "adsupported_vs_premium", "release_front_mid_or_cat")
dimCols_no_fmc <- setdiff(dimCols, "release_front_mid_or_cat")
DT.spot_market_share.aggd <- DT.spot_market_share[, lapply(.SD, sumn), .SDcols=measureCols, keyby=dimCols]

## CALCULATE THE TOTAL FOR FACT_SALES
DT.spot_market_share.aggd[, monthly_fact_sales_total_revenue := sum(gross_revenue_usd), by=dimCols_no_fmc]
DT.spot_market_share.aggd[, monthly_fact_sales_total_streams := sum(orchard_streams),   by=dimCols_no_fmc]

## CALCULATE THE PERCENTAGE ASSOCIATED TO EACH OF Frontline/Midline/Catalog
DT.spot_market_share.aggd[, perc_of_orchard_payable_by_fmc := percOfTotal(gross_revenue_usd, as.perc=FALSE), by=dimCols_no_fmc]
DT.spot_market_share.aggd[, perc_of_orchard_streams_by_fmc := percOfTotal(streams_fact_sales, as.perc=FALSE), by=dimCols_no_fmc]

## CALCULATE orchard_streams_per_fmc / orchard_payable_per_fmc
DT.spot_market_share.aggd[, orchard_payable_per_fmc := payable_to_orchard_usd * perc_of_orchard_payable_by_fmc]
DT.spot_market_share.aggd[, orchard_streams_per_fmc := orchard_streams        * perc_of_orchard_streams_by_fmc]

## rbind in the TOTALs to a new table
tmp_DT.subtotals <- DT.spot_market_share.aggd[, c(dimCols, "orchard_streams_per_fmc", "spotify_total_streams"), with=FALSE]
tmp_DT.totals    <- DT.spot_market_share.aggd[, list(release_front_mid_or_cat="TOTAL", orchard_streams_per_fmc=sum(orchard_streams_per_fmc), spotify_total_streams=spotify_total_streams[[1]]), by=dimCols_no_fmc]
# DT.spot_market_share.aggd[activity_month == "2015-05-01"]
# DT.overall_market_share[month == "2015-05-01"]
# DT.spot_market_share.aggd[activity_month == "2015-05-01", list(release_front_mid_or_cat="TOTAL", orchard_payable_per_fmc=sum(orchard_payable_per_fmc), spotify_gross_revenue_usd=spotify_gross_revenue_usd[[1]]), by=dimCols_no_fmc]
DT.spot_market_share_with_totals <- rbind(tmp_DT.subtotals, tmp_DT.totals, use.names=TRUE)
DT.spot_market_share_with_totals[, perc_of_reported_streams_by_fmc := orchard_streams_per_fmc / spotify_total_streams]
setkeyIfNot(DT.spot_market_share_with_totals, dimCols, verbose=FALSE, organize=FALSE)


## --------------------------------------------------------- ##
##                         OVER ALL                          ##
## --------------------------------------------------------- ##
  if ("adsupported_vs_premium" %ni% names(DT.revshare))
    DT.revshare[, adsupported_vs_premium := ifelse (product == "A", "AdSupported", "Premium")]

  ## Aggregate DT.revshare to get total marketshare
  DT.overall_market_share <- (aggregateDT(DT.revshare[month >= "2013-01-01"], by=c("month", "adsupported_vs_premium"), colsToAgg=c(orchard_streams="rightholders_tracks", spotify_total_streams="total_tracks")))
  ## add in the "TOTAL" rows
  DT.overall_market_share <- rbind(DT.overall_market_share, (aggregateDT(DT.revshare[month >= "2013-01-01"], by="month", colsToAgg=c(orchard_streams="rightholders_tracks", spotify_total_streams="total_tracks")))[, adsupported_vs_premium := "TOTAL"], use.names=TRUE )
  DT.overall_market_share[, is_total := adsupported_vs_premium == "TOTAL"]

  ## Calculate actual marketshare
  DT.overall_market_share[, market_share_by_streams := orchard_streams / spotify_total_streams]

  ## Add in text
  DT.overall_market_share[(month(month)==month(max(month))), text := fwp(market_share_by_streams)]

  DT.overall_market_share[, Orchard_vs_Spotify := "Orchard"]
  ## PLOT
  P.orchard_overall_marketshare_in_spotify <- {
    ggLinegraph(DT.overall_market_share[month >= "2013-06-01"], x="month", y="market_share_by_streams", dotsize=1, ylims=c(0, .08), xlab=NULL, title="Orchard Overall Marketshare in Spotify Worldwide", color="adsupported_vs_premium", label="text", labelcolor="adsupported_vs_premium", labelface="bold", label_y_up=0.2, legend="top",legend_title_on=FALSE, margin_right=2, margin_scale=7, shape="is_total", 
      , legend.size_on = FALSE , legend.linetype_on  = FALSE , legend.shape_on = FALSE , legend.alpha_on = FALSE
      , labeljitter_x=18, linetype="is_total", thickness="is_total", linealpha="is_total") + scale_linetype_manual(values=c(5, 1)) + scale_size_manual(values=c(0.5, 1.)) + scale_alpha_manual(values=c(0.5, 1)) + 
    scale_color_manual(values=c("#6AA79D", "#BB9596", "#111111"))
  }

  ggsave.out(P.orchard_overall_marketshare_in_spotify)
## --------------------------------------------------------- ##


## --------------------------------------------------------- ##
##                       BY FMC                              ##
## --------------------------------------------------------- ##
## Text for the top month
DT.spot_market_share_with_totals[month(activity_month)==month(max(activity_month)), text := fwp(perc_of_reported_streams_by_fmc)]

P.marketshare_by_fmc_orchard_def <- 
  ggLinegraph(DT.spot_market_share_with_totals[activity_month >= '2013-08-01'], x="activity_month", y="perc_of_reported_streams_by_fmc", color="release_front_mid_or_cat", facet_scale="free", facet_y= "adsupported_vs_premium", dotsize=1, title="Orchard marketshare by Frontline/Catalog\n(Using Orchard's Definition)", label="text", legend="top", legend_title_on=FALSE)

ggsave.out(P.marketshare_by_fmc_orchard_def)
ggsave.out(P.orchard_overall_marketshare_in_spotify)