# 01 iTunes Forecast.r

cluster.using = 9

setScience("itunes_forecast", create=TRUE, subl=FALSE, load=FALSE)

setDBall(cluster=cluster.using)
qShowTables(schema="bi", row=FALSE)
qShowTables(schema="ds_scratch", row=FALSE)


##  Pull iTunes Statements from Mike B's workbook
DT.iTunesStatements <- runQry(.m("mbworkbook", schema="ds_scratch", where=c(storeid = 1), limit=NULL))

colsNotUsing <- c("storeid", "store_name", "last_modified", "music_vs_video_by_dmv", "store_first_date_in_gl")

DT.iTunesStatements[, (colsNotUsing) := NULL]
setkeyIfNot(DT.iTunesStatements, download_accounting_month,organize=TRUE)

# ---------------

## Pull iTunes Total from the Mgmt Report
DT.iTunesAnalytics <- runQry(.m(colsToPull=c("download_accounting_month", "store_name", "accounting_days_present"), colsToAgg=c("units", "royaltydollar"), tbl="analytics", schema="bi", where=c(storeid = 1), minDate="2014-01-01", dateCol="download_accounting_month"), verbose=TRUE)

## Clean NAs
DT.iTunesAnalytics[, accounting_days_present := unique(removeNA(accounting_days_present)), by=list(download_accounting_month, store_name)]
DT.iTunesAnalytics <- DT.iTunesAnalytics[, list(royaltydollar = sum(royaltydollar)), keyby=list(download_accounting_month, accounting_days_present)]

# ------------------------------


matchKey(DT.iTunesStatements, DT.iTunesAnalytics, "download_accounting_month")

DT.iTunesAnalytics[DT.iTunesStatements, statement_gross := statement_thismonth]

DT.iTunesAnalytics[, statement_difference := statement_gross - royaltydollar]

DT.iTunesAnalytics[, analytics_avg_per_day := royaltydollar   / accounting_days_present]
DT.iTunesAnalytics[, statement_avg_per_day := statement_gross / accounting_days_present]

DT.iTunesAnalytics[, analytics_avg_OVER_state_avg := analytics_avg_per_day / statement_avg_per_day]


Forecast <- DT.iTunesAnalytics[!is.na(statement_avg_per_day), forecast::forecast(statement_avg_per_day, 1)]

Percentage <- as.numeric(DT.iTunesAnalytics[!is.na(statement_avg_per_day), forecast::forecast(analytics_avg_OVER_state_avg, 1)$mean])
daysInMonth <- 35

lowerBound    <- daysInMonth * Percentage * as.integer(Forecast$lower[, 1])
upperBound    <- daysInMonth * Percentage * as.integer(Forecast$upper[, 1])
pointEstimate <- daysInMonth * Percentage * as.integer(Forecast$mean)


stopifnot(equals(percOf(lowerBound, pointEstimate),  -percOf(upperBound, pointEstimate)))


Estimate_String <- sprintf("For the month of October 2014, we can estimate that iTunes top-line, \"gross-gross\" revenue will be\n    %s ±%s\n\nIn other words, with an 80%% confidence level we estimate itunes revenue will be in the range of\n    %s and %s\n", asCurr(pointEstimate, decim=0), fwp(percOf(lowerBound, pointEstimate)), asCurr(lowerBound, decim=0), asCurr(upperBound, decim=0))

cat(Estimate_String)

email(to=c("Rick"="rsaporta@theorchard.com", Jeff="jeff@theorchard.com", Lee="lee@theorchard.com", Pras="prashant@theorchard.com", Josh="josh@theorchard.com"), subj="iTunes October forecast", body=Estimate_String)

formnumb(DT.iTunesAnalytics)

DT.missing_date <- runQry(makeQry(colsToPull=c("download_accounting_month", "store_name", "accounting_days_present", "transac_typeid"), colsToAgg=c("units", "royaltydollar"), tbl="analytics", schema="bi", where=list(accounting_days_present = "is NULL"), minDate="2014-01-01", dateCol="download_accounting_month"), verbose=TRUE)
.a(makeQry)