--- 201409_11_16 - TEST - CHECK FOR OLD COMMITS --------------- lib(ggplot) lib(reshape2) setScience("scrubbing", create=TRUE, subl=FALSE) makeQry(tbl="fact_analytics", colsToPull=c(processed_date = "CAST(processeddaytime AS date)", "download_activity_date", "storeid"), colsToAgg=c(rows="*"), Agg="count", minDate="2014-08-01", dateCol="download_activity_date", where=c(storeid=286)) DT.DataCheck <- runQry(makeQry(tbl="fact_analytics", colsToPull=c("processeddaytime", "download_activity_date", "storeid"), colsToAgg=c(rows="*"), Agg="count", minDate="2014-02-01", dateCol="download_activity_date")) addStoreName.storeid_(DT.DataCheck) DT.DataCheck[, processed_date := as.Date(format(processeddaytime, "%Y-%m-%d"))] DT.DataCheck[, days_apart := processed_date - download_activity_date] DT.rows_by_days_apart <- DT.DataCheck[, list(rows = sum(rows)), keyby=list(storeid, store_name, days_apart)] #----- "I can measure the number of days until it was finally proceesed (ie, relative to download_activity_date) Or I can measure what activity we are processing each day -- this will point out spikes in data intake " kCols.S <- c("storeid", "store_name") kCols.P <- c("processed_date") kCols.A <- c("days_apart") kCols.SP <- c(kCols.S, kCols.P) ## distinct_dates_processed is a property of each S-P combination, but not a key col kCols.SPA <- c(kCols.S, kCols.P, kCols.A) ## Measure not only the number of days apart, but how many distinct download_activity_date's are processed each day, by store DT.DataCheck[, distinct_dates_processed := lunique(download_activity_date), by=kCols.SP] ## Count the number of rows by processed date - days apart (per store) DT.rows_by_processed_date <- DT.DataCheck[, list(distinct_dates_processed = unique(distinct_dates_processed), rows = sum(rows)), keyby=kCols.SPA] ## Count the TOTAL number of rows (regardless of activity date) processed each day (by store) DT.rows_by_processed_date[, total_rows_bySP := sum(rows), by=kCols.SP] ## CONFIRM: that days_apart is unique by S-P stopifnot(DT.rows_by_processed_date[, !anyDuplicated(days_apart), keyby=kCols.SP][, V1]) ## Calculate What percentage each rows count represents of the total processed for the store-processed date DT.rows_by_processed_date[, perc_of_SP_by_SPA := rows / total_rows_bySP] ## CONFIRM: percentages sum to 1 by store processed sumsToOne(DT.rows_by_processed_date, "perc_of_SP_by_SPA", by=kCols.SP) ## If we are processing several dates at once, per processed date, ## we would expect an approximately equal portion of rows processed each date (with fluctuations for actual user activity) ## However, even if we are processing several dates at once, we might still have a few 'backlog' dates that have a small percentage ## This will throw off any smooth division. ## EG, if we are processing 7 days worth of data, but we also have a backlog of day -8 and -9, and ## we expect 1/9th of the rows processed each day, we will not find that. ## ## Thus we calculate the scaled distance for each SP (ie, how many rows, relative to the avg rows) DT.rows_by_processed_date[, scaled_rows := scale(rows), by=kCols.SP] DT.rows_by_processed_date[, scaled_rows_man := perc_of_SP_by_SPA - mean(perc_of_SP_by_SPA), by=kCols.SP] tol_from_mean = 10/100 DT.rows_by_processed_date[, possible_correct_daycount := abs(perc_of_SP_by_SPA - mean(perc_of_SP_by_SPA)) < tol_from_mean, by=kCols.SP] ## The "actual days distance" is DT.rows_by_processed_date[, actual_days_distance := if (all(possible_correct_daycount)) as.integer(unique(distinct_dates_processed)) else if (any(perc_of_SP_by_SPA > .75)) as.integer(days_apart[perc_of_SP_by_SPA > .75]) else if (any(possible_correct_daycount)) max(as.integer(days_apart[possible_correct_daycount])) else NA_integer_ , by=kCols.SP] DT.rows_by_processed_date[is.na(actual_days_distance)] DT.rows_by_processed_date[storeid == 286 & processed_date >= '2014-08-30'] DT.rows_by_processed_date[1:25][, !kCols.S, with=FALSE] ## DT.DataCheck[store_name == "deezer" & processed_date >= "2014-08-01"] DT.DataCheck[store_name %chin% c("Spotify", "deezer")] DT.DataCheck[, processed_month := format(processed_date, format="%b") ] addWeekday_(DT.DataCheck) DT.DataCheck jesusForData(DT.DataCheck) # -------- ## Plot Spotify setkeyIfNot(DT.DataCheck, kCols.SPA) DT.plot <- copy(DT.DataCheck) ## Add color column ## Dont need this, since we have color_by_dict() # DT.DataCheck[, colors.weekday := makeFactorUsingDict(processed_weekday, "dict.wdays.colors") ] # DT.DataCheck[, colors.month := makeFactorUsingDict(processed_month, "dict.mnth.colors") ] { P.Spotify_Data_2014_Sept <- ggplot(DT.plot[.(286)][download_activity_date > "2014-08-01"], aes(x=download_activity_date, y=rows)) + geom_line(color="grey80", size=1.5) + # geom_point(aes(color=download_activity_weekday, shape=download_activity_weekday), size=3) + geom_point(size=3) + millions.y() + labs(title="Spotify Rows of Data\nSeptember 2014", y="Millions of Rows of Data") ggsave.out(P.Spotify_Data_2014_Sept) } ## Drop these two error lines for now DT.plot2 <- DT.plot[!(storeid == 286 & (download_activity_date == "2014-09-01" | download_activity_date == "2014-09-02"))] # DT.plot2 <- DT.plot2[download_activity_date > "2014-04-01"] DT.plot2 <- DT.plot2[, list(rows = sum(rows)), keyby=c(kCols.S, "download_activity_date", "download_activity_weekday")] setnames(DT.plot2, "download_activity_weekday", "Day of Week") { P.iTunes_Data_Row_Count_2014 <- ggplot(DT.plot2[storeid==1], aes(x=download_activity_date, y=rows)) + geom_line(color="grey80", size=1.5) + geom_point(aes(color=`Day of Week`, shape=`Day of Week`), size=3) + labs(title="iTunes Daily Rows of Data in fact_analytics\n2014", y="Thousands of Rows of Data") + thousands.y() + scale_shape_manual(values=setNames(nm=getWdays(), obj=c(15, 4, 4, 4, 4, 15, 15))) + color_by_dict("dict.wdays.colors") + facet_grid(store_name ~ ., scales="free_y") ggsave.out(P.iTunes_Data_Row_Count_2014) } { P.Fact_Analytics_Row_Count_2014 <- ggplot(DT.plot2, aes(x=download_activity_date, y=rows)) + geom_line(color="grey80", size=1.5) + geom_point(aes(color=`Day of Week`, shape=`Day of Week`), size=2.5) + labs(title="Rows of Data in fact_analytics\n2014", y="Thousands of Rows of Data") + thousands.y() + scale_shape_manual(values=setNames(nm=getWdays(), obj=c(15, 4, 4, 4, 4, 15, 15))) + color_by_dict("dict.wdays.colors") + facet_grid(store_name ~ ., scales="free_y") + legendtop() ggsave.out(P.Fact_Analytics_Row_Count_2014, height = 2* lunique(DT.plot2$storeid), width=10) } (P.daily <- ggplot(DT.plot, aes(x=download_activity_weekday, y=rows)) + geom_boxplot() + geom_point() + millions.y() + labs(title="Spotify Rows of Data\nSeptember 2014", y="Millions of Rows of Data")) ggsave.out(P.Spotify_Data_2014_Sept) # -------- qShowCols("fact_sales") q.cricket <- makeQry(tbl="fact_sales", colsToPull=c("accountingperiodid", "transactiontypeid"), colsToAgg=c(gross="gross", units="sales"), minDate=dateToperiodid("2013-10-01", FALSE), dateCol="accountingperiodid", where=c(storeid=497)) DT.cricket <- runQry(q.cricket) setkey(DT.cricket, accountingperiodid) addDateCols.periodid_(DT.cricket, drop=FALSE, newCol.nms="accounting_month") ## For plotting purposes, fix 185 / 186 setkeyIfNot(DT.cricket, c("accountingperiodid", "transactiontypeid")) DT.cricket <- rbind(DT.cricket, DT.cricket[CJ(185:186, unique(transactiontypeid))][is.na(gross)]) setkeyIfNot(DT.cricket, c("accountingperiodid", "transactiontypeid")) DT.cricket[, accounting_month := NULL] addDateCols.periodid_(DT.cricket, drop=FALSE, newCol.nms="accounting_month") setkeyIfNot(DT.cricket, "transactiontypeid") setkeyIfNot(DT.transacs, "transac_typeid") DT.cricket[DT.transacs, transac_type_abbr := i.transac_type_abbr] DT.cricket[DT.transacs, transac_type := i.transac_type] setkey(DT.cricket, accountingperiodid) ## Save jesusForData(DT.cricket) DT.cricket[accountingperiodid %in% (185:186), lapply(.SD, mean, na.rm=TRUE), .SDcols=c("gross", "units"), by=transactiontypeid] DT.cricket.molten <- melt(DT.cricket, measure.vars=c("gross", "units")) ggplot(DT.cricket.molten, aes(x=accounting_month, y=value, color=variable) )+ geom_line(size=3) + geom_point(color="black") + facet_grid(variable ~ transac_type_abbr, scales="free_y" ) + thousands.y() ggplot(DT.cricket.molten, aes(x=accounting_month, y=value, color=variable) )+ geom_line(size=3) + geom_point(color="black") + facet_grid( transac_type_abbr ~ variable, scales="free_y" ) + thousands.y() ggplot(DT.cricket.molten[variable=="gross"], aes(x=accounting_month, y=value, color=variable) )+ geom_line(size=3) + geom_point(color="black") + facet_grid( transac_type_abbr ~ variable, scales="free_y" ) + thousands.y() # ------------------ qShowCols("fact_analytics") q.cricket.anal <- makeQry(tbl="fact_analytics", colsToPull=c("download_activity_date", "transactiontypeid", "royaltydollar"), colsToAgg=c("units", "freeunits", "paidunits"), minDate="2013-10-01", dateCol="download_activity_date", where=c(storeid=497)) DT.cricket_anal <- runQry(q.cricket.anal) setkeyIfNot(DT.cricket_anal, "transactiontypeid") setkeyIfNot(DT.transacs, "transac_typeid") DT.cricket_anal[DT.transacs, transac_type_abbr := i.transac_type_abbr] DT.cricket_anal[DT.transacs, transac_type := i.transac_type] setkey(DT.cricket_anal, download_activity_date) if(all(DT.cricket_anal[, royaltydollar == 0])) DT.cricket_anal[, royaltydollar := NULL] DT.cricket_anal.molten <- melt(DT.cricket_anal, measure.vars=c("units", "freeunits", "paidunits")) addWeekday_(DT.cricket_anal.molten) jesusForData(DT.cricket_anal.molten, DT.cricket_anal) { ggplot(DT.cricket_anal.molten, aes(x=download_activity_date, y=value)) + geom_line(aes(color=variable), size=1.5) + geom_point(aes(fill=`download_activity_weekday`, shape=`download_activity_weekday`), size=2.5) + labs(title="Cricket Units from fact_analytics\n2014", y="Thousands of Units") + thousands.y() + scale_shape_manual(values=setNames(nm=getWdays(), obj=c(15, 4, 4, 4, 4, 15, 15))) + fill_by_dict("dict.wdays.colors") + facet_grid(transac_type_abbr ~ ., scales="free_y") + legendtop() } ----------------- qShowCols("fact_analytics") q.itunes.anal <- makeQry(tbl="fact_analytics", colsToPull=c("download_activity_date", "transactiontypeid", "royaltydollar", "labelid", "artistid", "releaseid"), colsToAgg=c("units", "freeunits", "paidunits"), minDate="2013-10-01", dateCol="download_activity_date", where=c(storeid=1)) DT.itunes_anal <- runQry(q.itunes.anal) DT.itunes_anal[, releaseid := factor(releaseid)] setkeyIfNot(DT.itunes_anal, "transactiontypeid") setkeyIfNot(DT.transacs, "transac_typeid") DT.itunes_anal[DT.transacs, transac_type_abbr := i.transac_type_abbr] DT.itunes_anal[DT.transacs, transac_type := i.transac_type] jesusForData(DT.itunes_anal) DT.itunes_anal.JulAugSept <- DT.itunes_anal[download_activity_date >= "2014-07-01"] kCols.itunes <- c("download_activity_date", "labelid", "releaseid", "artistid") kCols.itunes_rd <- c(kCols.itunes, "royaltydollar") mCols.itunes <- c("gross_calcd", "paidunits", "freeunits") setkeyIfNot(DT.itunes_anal.JulAugSept, kCols.itunes_rd) DT.itunes_anal.JulAugSept.agg <- DT.itunes_anal.JulAugSept[, lapply(.SD, sum), keyby=kCols.itunes_rd, .SDcols=c("paidunits", "freeunits")] DT.itunes_anal.JulAugSept.agg[, gross_calcd := royaltydollar * paidunits] DT.itunes_anal.JulAugSept.agg <- DT.itunes_anal.JulAugSept.agg[, lapply(.SD, sum), keyby=kCols.itunes, .SDcols=mCols.itunes] DT.itunes_anal.JulAugSept.agg[, paste0(mCols.itunes, "_daily_rank") := lapply(.SD, function(x) rank(-x)), .SDcols=mCols.itunes, by=download_activity_date] DT.itunes_anal.JulAugSept.agg[.(as.Date(c("2014-09-08", "2014-09-16")))] [freeunits_daily_rank < 15 | gross_calcd_daily_rank < 15][order(download_activity_date, gross_calcd_daily_rank)] runQry("SELECT * from dim_artist where artistid = 660999") runQry("SELECT * FROM dim_release WHERE releaseid = 886444737906") runQry("SELECT * FROM dim_label WHERE labelid = 23547") -------- q.itunes.bianal <- makeQry(tbl="analytics", schema="bi", colsToPull=c("download_activity_date", "transac_type_abbr", "labelid", "label_is_red","releaseid", "release_name", "artistid", "artist_name"), colsToAgg=c("freeunits", "paidunits", "royaltydollar_times_paidunits"), minDate="2013-10-01", dateCol="download_activity_date", where=c(storeid=1)) DT.itunes_bianal <- runQry(q.itunes.bianal) DT.itunes_bianal.JulAugSept <- DT.itunes_bianal[download_activity_date >= "2014-07-01"] kCols.itunes.bi <- c("download_activity_date", "labelid", "release_name", "artist_name") mCols.itunes <- c("gross_calcd", "paidunits", "freeunits") setkeyIfNot(DT.itunes_bianal.JulAugSept, kCols.itunes.bi) setnames(DT.itunes_bianal.JulAugSept, "royaltydollar_times_paidunits", "gross_calcd") DT.itunes_bianal.JulAugSept[, paste0(mCols.itunes, "_daily_rank") := lapply(.SD, function(x) rank(-x)), .SDcols=mCols.itunes, by=download_activity_date] DT.itunes_bianal.JulAugSept[.(as.Date(c("2014-09-08", "2014-09-16")))] [freeunits_daily_rank < 15 | gross_calcd_daily_rank < 15][order(download_activity_date, gross_calcd_daily_rank)] runQry("SELECT * from dim_artist where artistid = 660999") runQry("SELECT * FROM dim_release WHERE releaseid = 886444737906") runQry("SELECT * FROM dim_label WHERE labelid = 23547") jesusForData(lsos(pat="DT.itun")$Name) jesusForData(DT.itunes_anal, DT.itunes_bianal, DT.itunes_bianal.JulAugSept, DT.itunes_anal.JulAugSept, DT.itunes_anal.JulAugSept.agg) -------- DT.itunes_anal.JulAugSept.agg2 <- DT.itunes_anal.JulAugSept.agg[, lapply(.SD, sum), keyby=download_activity_date, .SDcols=c("paidunits", "freeunits", "gross_calcd")] addWeekday_(DT.itunes_anal.JulAugSept.agg2) DT.itunes_anal.JulAugSept.agg2.molten <- melt(DT.itunes_anal.JulAugSept.agg2, measure.vars=c("gross_calcd", "freeunits", "paidunits")) { ggplot(DT.itunes_anal.JulAugSept.agg2.molten[download_activity_date > "2014-09-01"], aes(x=download_activity_date, y=value)) + geom_line(aes(fill=variable), color="grey 80", alpha=.9, size=1.75) + geom_point(aes(color=`download_activity_weekday`, shape=`download_activity_weekday`), size=4) + labs(title="iTunes Units from fact_analytics\n2014", y="Thousands") + thousands.y() + scale_shape_manual(values=setNames(nm=getWdays(), obj=c(15, 4, 4, 4, 4, 15, 15))) + color_by_dict("dict.wdays.colors") + facet_grid(variable ~ ., scales="free_y") + legendtop() } DT.itunes_anal.JulAugSept.agg[, `:=`(freeunits_daily_rank = rank(-freeunits), gross_calcd_daily_rnak = rank(-gross_calcd)) , by="download_activity_date"] DT.itunes_anal.JulAugSept.agg[.(as.Date(c("2014-09-08", "2014-09-16")))] [freeunits_daily_rank < 15 | gross_calcd_daily_rnak < 15] minDate <- "2014-03-01" q.bianal <- makeQry(tbl="analytics", schema="bi", colsToPull=c("download_activity_date", "storeid", "transac_typeid", "labelid", "artistid", "releaseid"), colsToAgg=c("freeunits", "paidunits", "royaltydollar_times_paidunits"), minDate=minDate, dateCol="download_activity_date", where=list(storeid=c(1, 7, 11, 187, 286, 495:500))) DT.bianal <- runQry(q.bianal) q.anal <- makeQry(tbl="fact_analytics", colsToPull=c("download_activity_date", "storeid", transac_typeid="transactiontypeid", "royaltydollar", "labelid", "artistid", "releaseid"), colsToAgg=c("freeunits", "paidunits"), minDate=minDate, dateCol="download_activity_date", where=list(storeid=c(1, 7, 11, 187, 286, 495:500))) DT.anal <- runQry(q.anal) jesusForData(DT.bianal) jesusForData(DT.anal)