# itunes_red_usa_check.r


BackUpOrRestore("DT.acc.itunes_red_usa_check", clear=TRUE)
DT.acc.itunes_red_usa_check <- runQry(makeQry(tbl="accounting", schema="bi", colsToPull=c("transac_type_abbr", "activity_month", "accounting_month"), colsToAgg=c("units", revenue="gross")
                                            , where=list(country_code="US", storeid=1, label_sc_group="RED"), key="colsToPull", limit=NULL))
BackUpOrRestore("DT.acc.itunes_red_usa_check")

DT.acc.itunes_red_usa_check[, months_apart := round(as.integer(accounting_month - activity_month) / 30)]

## Calculate percent of totals for revenue and units, by ACTIVITY
DT.acc.itunes_red_usa_check[, units.percOfActM := percOfTotal(units), by=list(transac_type_abbr, activity_month)]
DT.acc.itunes_red_usa_check[, revenue.percOfActM := percOfTotal(revenue), by=list(transac_type_abbr, activity_month)]

setcolorder_suffix(DT.acc.itunes_red_usa_check, startCols=c("transac_type_abbr", "activity_month", "accounting_month", "months_apart"))
setcolorderpt(DT.acc.itunes_red_usa_check, c("transac_type_abbr", "activity_month", "accounting_month", "months_apart", "revenue.percOfActM", "units.percOfActM", "revenue", "units"))

## Calculate percent of totals for revenue, by ACCOUNTING MONTH
## This does not have much buisness significance
DT.acc.itunes_red_usa_check[, revenue.percOfAccounting := percOfTotal(revenue), by=list(transac_type_abbr, accounting_month)]



## Load in DT.bi_anal_totalpaidunits
loadIfNotExists("DT.bi_anal_totalpaidunits")


## < SEE > 
{
  cat("\n     *ACCOUNTING*   :: Total itunes RED USA for Transac 'DV', by activity_month\n")
  print(DT.acc.itunes_red_usa_check[transac_type_abbr == "DV" & activity_month >= "2014-08-01" ])
  cat("\n     *ANALYTICS*    :: Total itunes RED USA for Transac 'DV', by activity_month\n")
  print(DT.bi_anal_totalpaidunits[storeid == 1 & transac_type_abbr == "DV" & label_sc_group == "RED" & country_code == 'US'][activity_month544 >= "2014-08-01" & activity_month544 <= max(DT.acc.itunes_red_usa_check$accounting_month)][, revenue, keyby=activity_month544])
}

## MERGE
DT.DV <- {
  merge(
  DT.acc.itunes_red_usa_check[transac_type_abbr == "DV"][, list(reported_revenue=sum(revenue)), keyby=list(date=activity_month)]
  , 
  DT.bi_anal_totalpaidunits[storeid == 1 & transac_type_abbr == "DV" & label_sc_group == "RED" & country_code == 'US'][, list(anal_revenue=revenue), keyby=list(date=activity_month544)]
  , all=TRUE)
}

## Only keep those dates strictly less than most current date. Why? Most current date is only partially reported, and it will skew the results
DT.DV <- DT.DV[date < max(DT.acc.itunes_red_usa_check$accounting_month)]

N.ma <- 4
DT.DV[, rev_diff := reported_revenue - anal_revenue]
DT.DV[, ma.rev_diff := forecast::ma(rev_diff, N.ma)]
DT.DV[, ma.rev_repr := forecast::ma(reported_revenue, N.ma)]
DT.DV[, diff_to_rev.via_ma := ma.rev_diff / ma.rev_repr]
DT.DV[!is.na(rev_diff), mean(rev_diff) / mean(reported_revenue)]

DT.DV[date >= "2013-10-01"][!is.na(rev_diff), mean(rev_diff) / mean(reported_revenue)]
DT.DV[date >= "2014-01-01"][!is.na(rev_diff), mean(rev_diff) / mean(reported_revenue)]

DT.acc.itunes_red_usa_check[xor(revenue.percOfActM > .15 , units.percOfActM > .15)]

DT.acc.itunes_red_usa_check[transac_type_abbr == "DV"]