spotify

weekly revenue 
weekly streams
delta  for rev
delta for streams

===============

.us()
setScience("ConversionRate", loadImage=FALSE, create.if.not.exist=TRUE, subl=FALSE)

# tbls.spot <- qShowTables("spotify", justnames=TRUE)
# heads.DT <- headDB(tbls.spot, where="download_datetime > '2014-01-01' AND download_datetime < '2014-02-01'", 5000)


minDate <- '2013-12-01'
qry <- sprintf("SELECT download_datetime, customerid, product, zip, country, gender, birthyear, mobile, trackid FROM production.staging_raw_spotify WHERE download_datetime > '%s'", minDate)
DB.Spotify <- runQry(qry, verbose.max.width=200)
jesusForData(DB.Spotify, info="raw pull for product conversion")

if (FALSE) {
  loadFromJesus("SAMP.DB.raw", srcDir="SpotifyAdds")
  setnames(SAMP.DB.raw, "userid", "customerid")
  DB.RawSpotify <- copy(SAMP.DB.raw[, 1:15, with=FALSE])
}


## The raw spotify data
print(DB.RawSpotify)

cls(5)
colsNotUsing <- c("albumname", "trackname", "artistname", "download_date", "isrc", "upc", "original_upc")

DT <- copy(DB.RawSpotify[, !intersect(colsNotUsing, names(DB.RawSpotify)), with=FALSE] )[1:1.2e5]
cleanSpotify_ (DT)

suppressWarnings(DT[, c(colsNotUsing) := NULL])

# if (FALSE)  
# {
#     set.seed(1)
#     DT <- copy(heads[["spotify"]])
#     DT[, c("albumname", "trackname", "artistname", "download_date", "isrc", "upc", "original_upc", "trackid") := NULL]
#     DT <- rbind(DT, DT, DT, DT, DT, DT)
#     DT[, product := sample(product)]
#     DT[, download_datetime := sample(download_datetime)]
#     cleanSpotify_ (DT)
#     DT[, customerid := paste0(LETTERS, LETTERS)[as.numeric(customerid)]]
# }

keyCols <- c("customerid", "download_datetime", "product")
setkeyIfNot(DT, keyCols)

DT[, D := diffNA(product, padTop=TRUE), by="customerid"]
setcolorderpt(DT, endcols=c("customerid", "product", "D"))
# DT[.(c("AA","BB", "CC")), c("download_datetime", "customerid", "product", "D"), with=FALSE]
DT[D != 0 | is.na(D)]

DT.cid_and_prod <- unique(DT, by=)


  - measure: paid_units_percent_change
    type: percent_of_previous
    sql: ${total_paid_units}


      - measure: total_royalty_dollars
    type: sum
    decimals: 2
    sql: ${royaltydollar}