reconcileReturns <- function(DT, oid="order_id", pid="vendor_identifier" , cid="customer_identifier", units="units", aid="artist" , dontRename=FALSE) { # expects units to be positive / negative based on sale / return. # oid - the order # pid - the product # cid - the customer ## THESE ARE USED FOR VIEWING maincols <- c("download_date", ".hasr", "upc", "oid", "cid", "pid" , "artist", "title", "product", "customer_price", "wasFree", "badDataRow") # DT <- copy(DT) ## Set Col Names for easier handling setnames(DT, c(oid, pid, cid, units, aid), c("oid", "pid", "cid", "units", "aid")) # key by order oc <- c("oid", "cid") op <- c("oid", "pid") cp <- c("cid", "pid") ocp <- c("oid", "cid", "pid") setkeyv(DT, ocp) ## Additional rows that will be populated DT[, badDataRow := FALSE] DT[, NOTES := ""] # column for ease of filtering # .hasr := DOES THE **ORDER** CONTAIN A RETURN (not the line item) DT[, .hasr := FALSE] DT[DT[units < 0, unique(oid)], .hasr := TRUE] stopifnot(isUniqueByGroup(DT, ".hasr")) # Great Example # oid download_date .hasr upc cid pid artist title product customer_price wasFree sale_return units releaseDate startDate endDate N_dates.per.oc # 1: 100000579707063 2013-06-18 FALSE 7891430260125 7596411052 480948 Jesuton Encontros Album 6.30 FALSE Sale 1 2012-11-13 2013-01-26 2013-02-02 1 # 2: 100000579707063 2013-06-18 TRUE 7891430260125 7596411052 5546679 Jesuton Redemption Song Track 0.00 TRUE Return -1 2012-11-13 2013-01-26 2013-02-02 1 # 3: 100000579707063 2013-06-18 TRUE 7891430260125 7596411052 5546679 Jesuton Redemption Song Track 0.69 FALSE Sale 1 2012-11-13 2013-01-26 2013-02-02 1 # Column orders for viewing setcolorderpt(DT, maincols, endcols=c("fullName", "badDataRow", "NOTES"), showWarnings=FALSE) badDataNote <- function(index, NewNote) { invisible(DT[index, c("badDataRow", "NOTES") := list(TRUE, pastendl(NOTES, NewNote, .nostart=TRUE))]) } # ------------------------------------------------------------------- # ## Check for any customers with more negative units than positive units ## If any, these should probably be near `dateMin` from the SQL pull ## (ie, the order was prior to the ) # ------------------------------------------------------------------- # DT[, TotUnits.by.op := sum(units), by=op] DT[, TotUnits.by.cp := sum(units), by=cp] DT[, TotUnits.by.ocp := sum(units), by=ocp] TotalCols <- c("TotUnits.by.op", "TotUnits.by.cp", "TotUnits.by.ocp") hasNegTotUnits <- which(DT[, .SD < 0, .SDcols=TotalCols], arr.ind=TRUE)[,1] badDataNote(hasNegTotUnits, "Negative Total Units by (o|c|p)") ## This catches all the bad data rows in this context, along with distance from minDate DT[, c(.SD, list(minDate=min(download_date))), by=pid] [TotUnits.by.ocp<=0 & !Canceled, list(pid, oid, cid, upc, aid, title, download_date, distFromMinDate=download_date-minDate)] # ------------------------------------------------------------------- # # ------------------------------------------------------------------- # ## Check for any order that pertains to more than one customer # ------------------------------------------------------------------- # if (!isUniqueByGroup(DT, "cid", "oid")) { dups <- setkeyv(whereAreTheMultipleRowsPerGroup(DT, "cid", "oid", verbose=FALSE), key(DT)) file.bad.orders <<- as.path(outDir, "iTunes_MultipleCustomersPerOrder", ext="tsv", ts=TRUE) write.table(file=file.bad.orders, x=dups, row.names=FALSE, col.names=TRUE, sep="\t", fileEncoding="UTF-8") badDataNote(dups, "Multiple customers per order id") warning("Some orders have more than one customer. Bad data values saved to file.bad.orders =\n ", file.bad.orders, "\n") } # ------------------------------------------------------------------- # # Its a return if, # for a given customers order (oc) # a given product id has two line items: # * /- one "Sale" # \- one "Return" # * identical units per each # Count the Sale / Retruns per group DT[, luniq.S_R := lunique(sale_return), by=ocp] # Create a canceled Column DT[, Canceled := FALSE] # If there is a Sale & Return per ocp AND tot units is zero, cancel it DT[luniq.S_R==2 & TotUnits.by.ocp==0, Canceled := TRUE] << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> I was looking for orders that spread across more than one day, customers that "complete my order" customers that purchased after downloading free track, etc DT[, totalSpend.by.UPCcid := sum(customer_price), by=list(upc, cid)] DT[, list(sumBy.ocp=sum(order)) totalUPCspend.bycid] DT[, list(totalBy.ocp=sum(customer_price)), by=ocp][totalBy.ocp==9][order(pid)] DT[, c(.SD, list(minDate=min(download_date))), by=pid] [TotUnits.by.ocp<=0 & !Canceled, list(pid, oid, cid, upc, aid, title, download_date, distFromMinDate=download_date-minDate)] ## EXAMPLE: THIS WAS A COMPLETE-MY-ORDER DT[DT[(.hasr)][product=="Album" & units > 0, oid]][cid=="6832334963", sum(customer_price)] ## Check for line items that SHOULD have been canceled but were not. .D(DT[(.hasr & !Canceled)]) << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> << LEFT OFF -- DID I GET IT ALL, DID I MISS ANY SALES TO CANCEL >> ## Put DT back the way we found it if (dontRename) setnames(DT, c("oid", "pid", "cid" ,"units", "aid"), c(oid, pid, cid ,units, aid)) return(invisible(DT)) } # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # DT <- copy(DT.agg) reconcileReturns(DT) # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # # CALC THE ORDER TOTAL DT[, orderTotal := sum(customer_price), by=oid] DT[, orderHasAnAlbum := "Album" %in% product, by=oid] DT[, customersFirstPurch.by.aid := ] DT[orderTotal!=0 & (orderTotal - .99) %% 1.0 != 0] prices <- DT[, list(Count=.N), by=list(Price=orderTotal)] prices[, Price := factor(fw3(Price, 2))] setkey(prices, Price) ggplot(prices[!.(" 0.00")], aes(x=Price, y=Count)) + geom_bar(stat="identity") + scale_y_log10() + theme( title=element_text(hjust=0.5, size=rel(.7)) , axis.text.x = element_text(angle=65, vjust=0.85, size=rel(.8)) ) qplot() c(.99 %in% ) ## Count the number of products per order DT[, N.pid.by.oc := lunique(pid), by=oc] DT[, L.pid.by.oc := length(pid), by=oc] .D(c("Npid", "Lpid"))[Lpid - Npid > 1][orderch(ocp)] ## -- FIND CANCELED ORDERS WITH MORE ITEMS DT[DT[(Canceled)], ] DT[(Canceled), list(nProds=lunique(pid), by=oc][] # ------------------------------------------------------------------- # ## Check for orders that are split across more than one day # ------------------------------------------------------------------- # DT[, N_days.per.order := lunique(download_date), by=oid] DT[, N_dates.per.oc := lunique(download_date), by=oc]#[luniq_download_date==1, .SD, .SDcols=(oc)] DT[N_dates.per.oc > 1 & !.hasr] DT[(.hasr)] [, list(.N, luniq_download_date=lunique(download_date)), by=oc]#[luniq_download_date==1, .SD, .SDcols=(oc)] [, lunique(download_date), by=oc] [lunique(download_date)==1, .SD, by=oc] mults <- whereAreTheMultipleRowsPerGroup(DT, colsToCheck="download_date", byCols=oc, verbose=verbose) # ------------------------------------------------------------------- # r.d if(missing(to) && length(mc) > 1 && !(is.character(mc2 <- mc[[2]])) && names(mc)[[2]]=="x") { to <- as.character(mc2) if (is.null(file)) x=clipPaste() value <- ifelse(missing(value), FALSE, value) } ## THIS IS GOOD, but Not for reconciling ---------------------------------------- ## Check for one customer purchsing the same track more than once (without returns) DT[, TotalOrders.by.upc.cid := lunique(oid), by=list(upc, cid)] ---------------------------------------- # ~~~~~~~~~~~~~~~ # if (FALSE) { DT <- copy(DT.agg); cls() } .D <- .DataBrowser <- function(x=maincols, appendToMainCols=TRUE) { ## This is a helper function to help me display cols with less typing # eg: # maincols <- c("download_date", ".hasr", "upc", "oid", "cid", "pid" # , "artist", "title", "product", "customer_price", "wasFree", "badDataRow") if (toupper(as.character(substitute(x)))[[1]]=="B") { x <- setdiff(c(maincols, "badDataRow", "NOTES") , "sale_return") DT <- DT[(badDataRow)][order(NOTES, artist, pid)] } if (is.data.table(x)) { DT <- x x <- maincols cmiss <- TRUE } cmiss <- missing(x) if (identical(key(DT)[[1]], "oid")) { if (is.numeric(x) && x > nrow(DT)) { x <- as.character(x) DT <- setcolorderpt(DT[.(x)], "download_date") x <- maincols } } if (is.logical(x) || is.numeric(x)) x <- names(DT)[x] if (isTRUE(as.logical(appendToMainCols))) { x <- c(maincols, x) } cols <- intersect(x, names(DT)) print(DT[, cols, with=FALSE]) cat("\n\n\t\t",pasteR(30), "\n\n") if (cmiss) return(invisible(DT)) return(invisible(DT[, cols, with=FALSE])) }