# Accounting_vs_Analytics_2015.r

cluster.using <- 07

.us()


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

## Analytics
BackUpOrRestore("DT.anal_totalpaidunits", clear=TRUE)
DT.anal_totalpaidunits <- runQry(.m(tbl="fact_analytics", schema="production", colsToPull=c("download_activity_date", "storeid", "transactiontypeid"), colsToAgg=c(revenue="royaltydollar", "paidunits"), minDate="2012-01-01", dateCol="download_activity_date"), cluster=cluster.using)
BackUpOrRestore("DT.anal_totalpaidunits")

DT.anal_totalpaidunits[, date := lubridate::floor_date(download_activity_date, "month")]
DT.anal_totalpaidunits[, download_activity_date := NULL]
DT.anal_totalpaidunits[, store_uses_rd := storeid %in% DT.anal_totalpaidunits[, all(revenue == 0 | is.na(revenue)), by=storeid][(!V1), storeid]]
DT.anal_totalpaidunits[!(store_uses_rd), revenue := NA]
DT.anal_totalpaidunits[, store_uses_rd := NULL]

## Accounting
BackUpOrRestore("DT.acc_totalpaidunits", clear=TRUE)
dateCol.acc <- "activityperiodid"
DT.acc_totalpaidunits <- runQry(.m(tbl="fact_sales", schema="production", colsToPull=c(dateCol.acc, "storeid", "transactiontypeid"), colsToAgg=c(revenue="gross", paidunits = "sales"), minDate="2012-01-01", dateCol=dateCol.acc), cluster=cluster.using)
BackUpOrRestore("DT.acc_totalpaidunits")
{
  if ("accountingperiodid" %in% names(DT.acc_totalpaidunits))
    DT.acc_totalpaidunits[, periodid := accountingperiodid - 1]
  else if ("activityperiodid" %in% names(DT.acc_totalpaidunits))
    setnames(DT.acc_totalpaidunits, "activityperiodid", "periodid")
  addDateCols.periodid_(DT.acc_totalpaidunits, drop=TRUE)
}

## Match, Aggregate, and Merge
matchKey(DT.acc_totalpaidunits, DT.anal_totalpaidunits, key=c("date", "storeid", "transactiontypeid"), organize=TRUE)
DT.anal_totalpaidunits <- DT.anal_totalpaidunits[, lapply(.SD, sumn), by=key(DT.anal_totalpaidunits)]
DT.acc_v_anal <- merge(DT.acc_totalpaidunits, DT.anal_totalpaidunits, suffix=c(".acc", ".anal"))

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

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


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

minDateShow <- '2013-07-01'
formnumb(suppressWarnings(DT.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.acc_v_anal.molt <- melt(DT.acc_v_anal, id.var=key(DT.acc_v_anal), variable.name = "metric")
addTransacInfo_(DT.acc_v_anal.molt, description=TRUE)
DT.acc_v_anal.molt


DT.plot.itunes <- DT.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)





==============  07  vs  09   out of date ===================
seven <- 
runQry(.m(tbl="fact_analytics", where=list(storeid =1, download_activity_date="2014-11-09"), colsToPull=c("download_activity_date", "storeid", "transactiontypeid"), colsToAgg=c("paidunits", "royaltydollar"), limit=NULL), cluster=4)

nine <- 
runQry(.m(tbl="fact_analytics", where=list(storeid =1, download_activity_date="2014-11-09"), colsToPull=c("download_activity_date", "storeid", "transactiontypeid"), colsToAgg=c("paidunits", "royaltydollar"), limit=NULL), cluster=9)

seven
nine