# Accounting_vs_Analytics_2015.r

## This file is SIMILAR to what is in Acc_vs_Anal_2015 but speicific for Forecasting

REMEMBER THE GOAL: 
  To predict NEXT ACCOUNTING MONTH REVENUE

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NEXT STEP -- 

Pull
    accounting vs Analytics  from "bi"
Compare to 
    accounting vs Analytics  from "fact"

If within tolerance, use just  "bi"

Adjust forecast accordingly 


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


.us()
setScience("Acc_vs_Anal_2015", load=TRUE)
subProj <- "Forecasting"
cluster.using <- getOption("db.defaultcluster.out")

## Cols to pull from both tables
aggCols.acc   <- c(revenue="gross"        ,  paidunits="units")
aggCols.anal  <- c(revenue="royaltydollar",  paidunits="paidunits")
addlCols <- c("storeid", "transac_type_abbr", "label_sc_group", "country_code") ## c("transac_type_abbr", setdiff(kCols.splitgroup, "date"))
minDate <- as.Date("2013-01-01")
kCols.main <- c("date", addlCols)


loadIfNotExists("DT.bi_anal_totalpaidunits")
loadIfNotExists("DT.bi_acc_totalpaidunits")
loadIfNotExists("DT.bi_acc_with_act_date")
loadIfNotExists("DT.bi_acc_with_act_date.agg")

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

## There are some blank Transaction types in bi.analytics. These should all stem from the error table and should be in only analytics
## ROWS FOR ACCOUNTING SHOULD BE 0, whereas Analytics will have some rows, but they should all be from_errors_table
verboseMsg(verbose, "Confirming that all missing_transacs are as expected")
whereList <- "transac_type_abbr = '' OR transac_type_abbr is NULL or transac_typeid IS NULL"
DT.missing_transacs.acc <- runQry(makeQry("accounting", schema="bi", aggFunc="count",                                 , where=whereList, limit=NULL), cluster=getOption("db.defaultcluster.out"))
DT.missing_transacs.ana <- runQry(makeQry("analytics",  schema="bi", aggFunc="count", colsToPull="from_errors_table"  , where=whereList, limit=NULL), cluster=getOption("db.defaultcluster.out"))
stopifnot(DT.missing_transacs.acc[ , rows == 0], DT.missing_transacs.ana[, !any(from_errors_table==FALSE)])



## -------------------------------------------------------------- ##
##   Running Three Tables.                                        ##
## -------------------------------------------------------------- ##
# DT.bi_anal_totalpaidunits
# DT.bi_acc_totalpaidunits
# DT.bi_acc_with_act_date
{
    ## Analytics
    ## Analytics
                        BackUpOrRestore("DT.bi_anal_totalpaidunits", clear=TRUE)
    dateCol.anal <- "activity_month544" ## old as of 2015-01-20 c(activity_month544 = "accounting_month")
    DT.bi_anal_totalpaidunits <- runQry(makeQry(tbl="analytics", schema="bi", colsToPull=c(dateCol.anal, addlCols, "store_uses_royaltydollar"), colsToAgg=aggCols.anal, minDate=minDate, dateCol=dateCol.anal), cluster=cluster.using)
                        BackUpOrRestore("DT.bi_anal_totalpaidunits")

    ## Remove revenue value if not using royaltydollar
    DT.bi_anal_totalpaidunits[(!store_uses_royaltydollar), revenue := NA]
    ## Blank Transactions stem from the errors table. Change those to XXXX 
    DT.bi_anal_totalpaidunits[transac_type_abbr == "", transac_type_abbr := "XXXX"]

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

    ## Accounting
    ## Accounting
                        BackUpOrRestore("DT.bi_acc_totalpaidunits", clear=TRUE)
    dateCol.acc <- "accounting_month"
    DT.bi_acc_totalpaidunits <- runQry(makeQry(tbl="accounting", schema="bi", colsToPull=c(dateCol.acc, addlCols), colsToAgg=c(revenue="gross", paidunits = "units"), minDate=minDate, dateCol=dateCol.acc, where=list("store_is_in_analytics" = TRUE)), cluster=cluster.using)
    DT.bi_acc_totalpaidunits.activity_month <- runQry(makeQry(tbl="accounting", schema="bi", colsToPull=c("activity_month", addlCols), colsToAgg=c(revenue="gross", paidunits = "units"), minDate=minDate, dateCol="activity_month", where=list("store_is_in_analytics" = TRUE)), cluster=cluster.using)
                        BackUpOrRestore("DT.bi_acc_totalpaidunits")

    ## Date is simply the accounting month, across the board.  Remember, this is the target date. 
    DT.bi_acc_totalpaidunits[, date := accounting_month]

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

    ## Accounting with Analytics Date
    ## Accounting with Analytics Date
                        BackUpOrRestore("DT.bi_acc_with_act_date", clear=TRUE)
    DT.bi_acc_with_act_date <- runQry(makeQry(tbl="accounting", schema="bi", colsToPull=c("accounting_month", "activity_month", addlCols), colsToAgg=aggCols.acc, minDate=minDate, dateCol="activity_month", where=list("store_is_in_analytics" = TRUE)), cluster=cluster.using)
                        BackUpOrRestore("DT.bi_acc_with_act_date")

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

    jesusForData(DT.bi_acc_totalpaidunits.activity_month)
    jesusForData(DT.bi_anal_totalpaidunits)
    jesusForData(DT.bi_anal_totalpaidunits, DT.bi_acc_totalpaidunits, DT.bi_acc_with_act_date)
    jesusForData(DT.bi_anal_totalpaidunits.bak, DT.bi_acc_totalpaidunits.bak, DT.bi_acc_with_act_date.bak, info="rawpull")
}
## -------------------------------------------------------------- ##


## NOTE TO SELF: Make sure to run DT.bi_anal_totalpaidunits has been ran
DT.bi_anal2 <- DT.bi_anal_totalpaidunits
## Expected date is the date after activity_date, except for itunesred
DT.bi_anal2[, date := as.Date(ifelse(storeid ==1 & label_sc_group=="RED" & country_code == 'US', activity_month544, nextMonth(activity_month544)), origin=.origin) ][, activity_month544 := NULL]

DT.bi_acc_with_act_date.agg <- DT.bi_acc_with_act_date[, lapply(.SD, sumn), keyby=c("activity_month", addlCols), .SDcols=colNamesFromVector(aggCols.acc)]
setnames(DT.bi_acc_with_act_date.agg, "activity_month", "date")

# setnames(DT.bi_anal2[, date := NULL], "activity_month544", "date")
matchKey(DT.bi_acc_with_act_date.agg, DT.bi_anal2, key=kCols.main, organize=TRUE)
DT.activity_date_matchup <- merge(DT.bi_acc_with_act_date.agg, DT.bi_anal2, all=TRUE)#[date >= '2014-01-01']
DT.activity_date_matchup
DT.activity_date_matchup[!is.na(paidunits.y) & !is.na(paidunits.x)]

DT.bi_acc_with_act_date.agg   [date == '2014-01-01' & storeid == 1]
DT.bi_anal2 [date == '2014-01-01' & storeid == 1]

DT.activity_date_matchup  [date == '2014-08-01' & storeid == 1 & country_code == "US"]

{ 
  formnumb(
      DT.activity_date_matchup[storeid == 1, lapply(.SD, sumn), keyby=c(setdiff(kCols.main, "country_code")), .SDcols=c("revenue.x", "revenue.y")] [, rev_ratio := revenue.x / revenue.y] [rev_ratio != 0 & is.finite(rev_ratio)]   [date == "2014-09-01"]
  , round=1)
} [order(label_sc_group, transac_type_abbr)]
--------


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

## Match, Aggregate, and Merge
matchKey(DT.bi_acc_totalpaidunits, DT.bi_anal_totalpaidunits, key=kCols.main, organize=TRUE)
DT.bi_anal_totalpaidunits <- DT.bi_anal_totalpaidunits[, lapply(.SD, sumn), by=key(DT.bi_anal_totalpaidunits)]
DT.bi_acc_v_anal <- merge(DT.bi_acc_totalpaidunits, DT.bi_anal_totalpaidunits, suffix=c(".acc", ".anal"))

DT.bi_acc_totalpaidunits[storeid ==1  & date == d][!DT.bi_anal_totalpaidunits]
DT.bi_acc_totalpaidunits[storeid ==1  & date == d]
DT.bi_anal_totalpaidunits[storeid ==1  & date == d]

## Organize the col order
setcolorderpt(DT.bi_acc_v_anal, sort(names(DT.bi_acc_v_anal)))
setcolorderpt(DT.bi_acc_v_anal, c(key(DT.bi_acc_v_anal), "accountingdate"), showWarnings=FALSE)

## Compare
DT.bi_acc_v_anal[, paidunits.anal_to_acc := paidunits.anal / paidunits.acc]
DT.bi_acc_v_anal[, revenue.anal_to_acc   := revenue.anal   / revenue.acc]

DT.bi_acc_v_anal [transac_type_abbr == "DV"]


### PLOTTING STUFF ... 
if (FALSE)
{
  minDateShow <- '2013-07-01'
  formnumb(suppressWarnings(DT.bi_acc_v_anal[storeid == 1 & date >= minDateShow][, storeid := NULL][ , c("accountingdate", "activitydate") := NULL][, paidunits.anal_to_acc := fwp(paidunits.anal_to_acc)][, revenue.anal_to_acc := fwp(revenue.anal_to_acc) ]))

  DT.bi_acc_v_anal.molt <- melt(DT.bi_acc_v_anal, id.var=key(DT.bi_acc_v_anal), variable.name = "metric")
  addTransacInfo_(DT.bi_acc_v_anal.molt, description=TRUE)
  DT.bi_acc_v_anal.molt


  DT.plot.itunes <- DT.bi_acc_v_anal.molt[storeid == 1 & grepl("anal_to_acc", metric) & date >= minDateShow & transactiontypeid %ni% c(30, 7)]
  DT.plot.itunes[, metric := topropper(gsub("\\.anal_to_acc", "", metric))]
  ggLinegraph(DT.plot.itunes, x="date", y="value", color="metric", yscale = "percent", alpha=1, facet="transac_type~.", xlab="", ylab="Analytics as percentage of accounting\n(matched by activity date)", thickness=.8, dot_alpha=.5) + legendbottom(notitle=TRUE) + geom_hline(y=1, alpha=.25)


}