### NOTE:  DB.spotify has the min(date) per each customerid--product--country--gender--mobile combo
##         This will NOT provide a unique-user count

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

lib(ggplot)
lib(data.table)
use.mini <- TRUE
{
.g()
.us()
.us()
setScience("ConversionRate", loadImage=FALSE, create.if.not.exist=TRUE, subl=FALSE)


if (use.mini)
  projName <- paste0(projName, "_mini")

setScience(projName, loadImage=FALSE, create.if.not.exist=TRUE, subl=FALSE)

verboseMsg(verbose, "STARTING")

## DATA PULL
minDate <- '2014-01-01'

if (use.mini)
  minDate <- '2014-02-12'

## This might be too big of a query
qry <- sprintf("SELECT download_datetime, customerid, product, zip, country, gender, birthyear, birthyear, mobile, trackid FROM production.staging_raw_spotify WHERE download_datetime > '%s'", minDate)
## Trying this paired down query instead
qry <- sprintf("SELECT download_datetime, customerid, product, country, gender, birthyear, mobile FROM production.staging_raw_spotify WHERE download_datetime > '%s'", minDate)

## Trying a different query
qry.distinct <- sprintf("SELECT min(download_datetime) as download_datetime,
                                customerid, product, country, gender, birthyear
                        FROM   production.staging_raw_spotify
                        WHERE  download_datetime > '%s'
                        GROUP BY customerid, product, country, gender, birthyear, mobile", '2014-01-01')
qry <- qry.distinct

DB.Spotify <- runQry(qry, verbose.max.width=140, verbose=TRUE, check.table.perms=TRUE)

if (!nrow(DB.Spotify))
  stop ("No rows in DB.Spotify")

notify("Query done running")
message("Done Pulling Data From SQL")

## BACKUP
jesusForData(DB.Spotify, info="raw pull for product conversion")
saveImageTo()

## CLEAN
## drop some columns
colsNotUsing <- c("albumname", "trackname", "artistname", "download_date", "isrc", "upc", "original_upc")
suppressWarnings(DB.Spotify[, c(colsNotUsing) := NULL])
message("BEGINNING CLEANING")
## clean up the DT
cleanSpotify_ (DB.Spotify)
notify("Done clenaing")

## KEY
keyCols <- c("customerid", "download_datetime", "product")
setkeyIfNot(DB.Spotify, keyCols)

## CALCULATE
DB.Spotify[, product_tier := makeFactorUsingDict(vec=product, dict=getDict.SpotifyTier(), missing_to_NA=FALSE)]
DB.Spotify[, product_tier_num := as.numeric(product_tier) ]
## Shift by a small decimal, subtracting from Max so that the smaller numbers are more greatly affected
DB.Spotify[ , product_tier_num := product_tier_num - ((max(product_tier_num)+1-product_tier_num)/10)]

## Take a diff from product_tiers to find conversions
DB.Spotify[, D := diffNA(product_tier_num, padTop=TRUE), by="customerid"]

## DATE FIELDS
DB.Spotify[, `:=`(year_month = year(download_datetime ) + month(download_datetime) / 100
                , year_week  = year(download_datetime ) + week(download_datetime)  / 100
                )]

## CLEAN
cleanGenderColumn_(DB.Spotify, by="customerid")

setcolorderpt(DB.Spotify, endcols=c("customerid", "product", "product_tier", "product_tier_num", "D"))

## COPY
#  DT.conversions <- copy(DB.Spotify[isPaying & ((D) != 0 | is.na(D))])  # this is WRONG. We are interested in conversions TO payments, which would shift the parens to the left. HOwever, for now, grab them all
DT.conversions <- copy(DB.Spotify[(D != 0 | is.na(D))])

## BACKUP
f.spot    <- jesusForData(DB.Spotify, info="AGE + GENDER + Scrubbed & Prod Changes")
f.dt.conv <- jesusForData(DT.conversions, info="Filtered off of DB.Spotify")
saveImageTo()
notify("ALL DONE!!!")

message("files saved: \n")
message(f.spot)
message("\n")
message(f.dt.conv)
}

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


DT.FreeToPaid <- unique(DB.Spotify[D == 3.9-1.7], by="customerid")
jesusForData(DT.FreeToPaid)


NoOfUsersByAge <- unique(DB.Spotify[product_tier=="FreeTier"], by="customerid")[, list(NoOfUsers=.N), keyby="Age"]

Counts <- DT.FreeToPaid[, list(Conversions=.N), keyby="Age"]

## Merge in NoOfUsersByAge
Counts <- merge(Counts, NoOfUsersByAge, all=TRUE)

## Any NA's in Conversions should be considered Zero
Counts[is.na(Conversions), Conversions := 0]

Counts[, ConvPerc := Conversions / NoOfUsers]
Counts[, NoOfUsers := as.numeric(NoOfUsers)]

## Moving Averages
Counts[, ConvPerc5MA  := forecast::ma(ConvPerc,  5)]
Counts[, NoOfUsers5MA := forecast::ma(NoOfUsers, 5)]


jesusForData(Counts, info="Just Free to Paid")


minAge <- 12
maxAge <- 110

Counts <- Counts[.(minAge:maxAge)]

makeGraph <- function(vars, title, maxX=maxAge, Dat=Counts) {
  vars$y.line <- paste0(vars$y, "5MA")
  ggplot(data=Dat, aes_string(x=vars$x, color=vars$color)) + geom_line(aes_string(  y=vars$y.line)) + geom_point(aes_string(y=vars$y)) + 
    {if (vars$y %cont% "perc") percent.y() else thousands.y()} + 
    {if (vars$color %cont% "perc") 
        scale_color_gradient(low="light blue", high="red", na.value="white", labels=percent) 
     else 
         scale_color_gradient(low="light blue", high="red", na.value="white", labels=formnumb)
    } + 
    scale_x_continuous(breaks=seq(10, maxX+10, by=10), limits=c(1, maxX+1)) + 
    ggtitle(title) + 
    {if ("gender" %in% names(Dat))
      facet_grid(gender~.)} + 
    NULL
}


YlabConv <- "Approximate Conversion rate by Age Group\nAveraged over 5-year age range"
YlabNoOfUsers <- "Number of Users (in 1,000's)\nAveraged over 5-year age range"

titleConv <- "Conversion Rate by Age (values are approx)\nSpotify Data from Jan 2014"
titleNoOfUsers <- "Number of Users by Age (values are approx)\nSpotify Data from Jan 2014"

Counts <- Counts[!.(12:13)]
P.ConvPerc  <- makeGraph(list(x="Age", y="ConvPerc", color="NoOfUsers"), title=titleConv) + ylab(YlabConv)
P.NoOfUsers <- makeGraph(list(x="Age", y="NoOfUsers", color="ConvPerc"), title=titleNoOfUsers) + ylab(YlabNoOfUsers)


ggsave.out(nm="Spot_ConversByAge",  plot=P.ConvPerc,  open=TRUE)
ggsave.out(nm="Spot_UserCountByAge",  plot=P.NoOfUsers, open=TRUE)


## ---------  SAME, but BY GENDER -------------- ##
cleanGenderColumn_(DB.Spotify, by="customerid")
NoOfUsersByAgeGender <- unique(DB.Spotify[product_tier=="FreeTier"], by="customerid")[, list(NoOfUsers=.N), keyby="gender,Age"]

CountsByAgeGender <- DT.FreeToPaid[, list(Conversions=.N), keyby="gender,Age"]

## Merge in NoOfUsersByAgeGender
CountsByAgeGender <- merge(CountsByAgeGender, NoOfUsersByAgeGender, all=TRUE)


## Any NA's in Conversions should be considered Zero
CountsByAgeGender[is.na(Conversions), Conversions := 0]

CountsByAgeGender[, ConvPerc := Conversions / NoOfUsers]
CountsByAgeGender[, NoOfUsers := as.numeric(NoOfUsers)]

## Moving Averages
CountsByAgeGender[, ConvPerc5MA  := forecast::ma(ConvPerc,  5), by=gender]
CountsByAgeGender[, NoOfUsers5MA := forecast::ma(NoOfUsers, 5), by=gender]

CountsByAgeGender <- CountsByAgeGender
jesusForData(CountsByAgeGender, info="Just Free to Paid")

## Crop low Ages
CountsByAgeGender <- CountsByAgeGender[!(Age %in% -10:13)][!is.na(Age)]
## Crop the max conv rate
CountsByAgeGender[ConvPerc > .06, ConvPerc := .0599999]

P.ConvPercGender  <- makeGraph(list(x="Age", y="ConvPerc", color="NoOfUsers"), title=titleConv, Dat=CountsByAgeGender) + ylab(YlabConv)
P.NoOfUsersGender <- makeGraph(list(x="Age", y="NoOfUsers", color="ConvPerc"), title=titleNoOfUsers, Dat=CountsByAgeGender) + ylab(YlabNoOfUsers)

ggsave.out(nm="Spot_ConversByAge_and_Gender",  plot=P.ConvPercGender,  open=TRUE)
ggsave.out(nm="Spot_UserCountByAge_and_Gender",  plot=P.NoOfUsersGender, open=TRUE)
## ---------  SAME, but BY GENDER -------------- ##