## NOTE TO SELF -- DT.revshare was loaded in part 03
qShowTables("bi")


{
  DT.users <- DT.revshare[month >= "2013-10-01"]
  
  DT.users[, calculated_net_euros   := pro_rata_exact * (revenue_share / 100) * payable_eur]
  DT.users[, calculated_net_dollars := pro_rata_exact * (revenue_share / 100) * payable_usd]

  DT.users[currency == "USD", calculated_payable_usd := pro_rata_exact * revenue_share / 100 * net_revenue]
}

if (!exists("DT.users"))
{

  DT.users <- DT.revshare[, list(total_streams = total_tracks, orchard_streams = rightholders_tracks, registered_users, active_users, spotify_net_revenue_usd = net_revenue_usd, orchard_osc_usd = payable_usd, orchard_osc_revenue = payable), keyby=list(date = month, country=country_code, currency, product)]
  DT.users <- DT.users[date >= "2013-10-01"]


  DT.users <- DT.users[, lapply(.SD, "/", 1e6), keyby=key(DT.users)]
  DT.users <- rbind(
                 aggregateDT(DT.users, exclude=c("country", "product"))[, country := "global"]
               , aggregateDT(DT.users, exclude=c("product"))
               , use.names=TRUE
              )
}


DT.monthly_cast <- dcast.data.table(DT.monthly, month + country ~ topx, value.var = "avg_monthly_streams")
setnames(DT.monthly_cast, "month", "date")
matchKey(DT.users, DT.monthly_cast)
addColsFrom_ (DT.monthly_cast, DT.users)
## Drop any columns for which the Spotify data has not been sent to us yet
DT.monthly_cast <- DT.monthly_cast[!is.na(orchard_streams)]

cols.monthly_cast <- setdiff(names(DT.monthly_cast), key(DT.monthly_cast))

metrics_using <- c("top05", "top50", "total_streams", "orchard_streams", "registered_users")
country_using <- c("US", "global", "SE")
country_using <- "global"

--------------

DT.monthly_cast.perc_incr <- copy(DT.monthly_cast)
DT.monthly_cast.perc_incr[, (cols.monthly_cast) := lapply(.SD, percentIncrease), keyby=c(setdiff(key(DT.monthly_cast.perc_incr), "date")), .SDcols=cols.monthly_cast]

## Melt the motnhly data
DT.monthly_molt.perc_incr <- melt(DT.monthly_cast.perc_incr, id.vars=kCols, value.name="percent_increase", variable.name="metric") [!is.na(percent_increase)]
setkeyIfNot(DT.monthly_molt.perc_incr, kCols, verbose=FALSE)


(P <- ggLinegraph(DT.monthly_molt.perc_incr[country %in% country_using & metric %in% metrics_using], x="date", y="percent_increase", color="metric", smooth = TRUE, dots=0, yscale ="percent", facet_formula=". ~ country"))

--------------

--------------

DT.monthly_cast.perc_of_total <- copy(DT.monthly_cast)
DT.monthly_cast.perc_of_total[, (cols.monthly_cast) := lapply(.SD, function(x) x / total_streams), keyby=c(setdiff(key(DT.monthly_cast.perc_of_total), "date")), .SDcols=cols.monthly_cast]

## Melt the motnhly data
DT.monthly_molt.perc_incr <- melt(DT.monthly_cast.perc_of_total, id.vars=kCols, value.name="percent_increase", variable.name="metric") [!is.na(percent_increase)]
setkeyIfNot(DT.monthly_molt.perc_incr, kCols, verbose=FALSE)

(P <- ggLinegraph(DT.monthly_molt.perc_incr[country %in% country_using & metric %in% metrics_using], x="date", y="percent_increase", color="metric", smooth = TRUE, dots=0) + facet_grid(. ~ country))

--------------


PR * (60/100 * TOTAL_DOLLARS) = PAYABLE_DOLLARS

PR * (60/100) = PAYABLE / TOTAL
(PR * (60 / 100))  / PAYABLE =  1 / TOTAL