# Accounting-to-Activity Time matchup.r # --------------- Qry.analDates <- makeQry("aggregated_analytics" , colsToPull=c("store_name_short", "accounting_month as accounting_month", "DATEADD('month', 1, accounting_month) as New_Acc_Month", "extract('month' from activity_date) as activity_month", "extract('year' from activity_date) as activity_year") , colsToSum=c("units", "paidunits") , whereIn=list(storeid=c(1, 286), "extract('day' from activity_date)"=9:11) , dateCol="activity_date" , gte=">=" , minDate="2014-04-01" ) Qry.accDates <- makeQry("aggregated_accounting" , colsToPull=c("store_name_short", "accounting_month", "activity_month") , colsToSum =c("units", "gross") , whereIn=list(storeid=c(1, 286)) , dateCol="accounting_month" , gte=">=" , minDate="2014-04-01" ) DT.accDates <- runQry(Qry.accDates , dont.setkey=TRUE) DT.analDates <- runQry(Qry.analDates, dont.setkey=TRUE) DT.analDates[, activity_month := as.Date(sprintf("%s-%s-01", activity_year, activity_month))][, activity_year := NULL] DT.accDates [, perc_of_stores_monthly_gross := fwp(percOfTotal(gross))] DT.analDates[, perc_of_stores_monthly_paidunits := fwp(percOfTotal(paidunits))] setkeyIfNot(DT.accDates, store_name_short, accounting_month, activity_month, organize=TRUE) setkeyIfNot(DT.analDates, store_name_short, activity_year, activity_month, organize=TRUE) DT.accDates DT.analDates # --------------- ## Breakdown of an accounting total by activity month. ## The percentage indicates how much did the activity in question contribute to the store's accounting month's total ## (eg, how much did "iTunes Music in March contribute to iTunes May total.") byCols <- c("storeid", "accounting_month") minDate <- as.Date("2014-03-01") dimColsAll <- c("storeid", "store_name_short", "continent_ordered", "label_sc_group", "music_vs_video") dateCols <- c("accounting_month", "activity_month") measureCols  <- c("units", "gross") percCols <- paste0(measureCols, "_perc") percStrCols <- paste0(measureCols, "_percstr") rankCols <- paste0("rank_", measureCols) dimCols <- setdiff(dimColsAll, "music_vs_video") DT.activity_perc <- DT.acc[as.Date(accounting_month) >= minDate, lapply(.SD, sum), keyby=c(dimCols, dateCols), .SDcols=measureCols] DT.activity_perc[, (dateCols) := lapply(.SD, as.Date), .SDcols=dateCols] setkeyIfNot(DT.activity_perc, dimCols) DT.activity_perc[ , (percCols) := lapply(.SD, percOfTotal), .SD=measureCols, by=byCols] DT.activity_perc[ , (percStrCols) := lapply(.SD, fwp), .SDcols=percCols] DT.activity_perc[ , (rankCols) := lapply(-.SD, rank), .SDcols=percCols, by=byCols] DT.activity_perc[ , moreThan1Perc := 0 < rowSums(.SD > 0.01), .SDcols = percCols] setkeyIfNot(DT.activity_perc, c(byCols, "activity_month", rankCols)) DT.activity_perc[.(c(1, 286))][(moreThan1Perc)] storeid store_name_short continent_ordered label_sc_group accounting_month activity_month units gross units_perc gross_perc units_percstr gross_percstr rank_units rank_gross moreThan1Perc 1: 1 iTunes 1 North America Orchard 2014-03-01 2014-01-01 31426702 18175.097 0.54370664 2.03132e-03 54.37 % 0.20 % 1 13 TRUE 2: 1 iTunes 1 North America RED 2014-03-01 2014-01-01 19338648 11028.452 0.33457381 1.23258e-03 33.46 % 0.12 % 2 14 TRUE 3: 1 iTunes 1 North America Allegro 2014-03-01 2014-01-01 884608 504.316 0.01530441 5.63644e-05 1.53 % ~0.00 % 6 22 TRUE 4: 1 iTunes 1 North America Orchard 2014-03-01 2014-02-01 3116873 3984166.287 0.05392435 4.45286e-01 5.39 % 44.53 % 3 1 TRUE 5: 1 iTunes 2 Europe Orchard 2014-03-01 2014-02-01 1241427 1985830.267 0.02147766 2.21944e-01 2.15 % 22.19 % 4 2 TRUE 6: 1 iTunes 6 Oceania Orchard 2014-03-01 2014-02-01 247663 529816.567 0.00428476 5.92144e-02 0.43 % 5.92 % 7 4 TRUE 7: 1 iTunes 3 South America Orchard 2014-03-01 2014-02-01 146557 233874.179 0.00253555 2.61387e-02 0.25 % 2.61 % 8 5 TRUE 8: 1 iTunes 4 Asia Orchard 2014-03-01 2014-02-01 140637 197270.317 0.00243313 2.20477e-02 0.24 % 2.20 % 9 6 TRUE 9: 1 iTunes 1 North America RED 2014-03-01 2014-02-01 87743 131105.795 0.00151802 1.46529e-02 0.15 % 1.47 % 10 7 TRUE 10: 1 iTunes 1 North America Allegro 2014-03-01 2014-02-01 63249 104085.457 0.00109426 1.16330e-02 0.11 % 1.16 % 11 8 TRUE 11: 1 iTunes 1 North America RED 2014-03-01 2014-03-01 1016533 1595714.790 0.01758682 1.78343e-01 1.76 % 17.83 % 5 3 TRUE 12: 1 iTunes 1 North America Orchard 2014-04-01 2014-02-01 26946630 14989.620 0.55108131 1.58790e-03 55.11 % 0.16 % 1 13 TRUE 13: 1 iTunes 1 North America RED 2014-04-01 2014-02-01 14835267 8174.620 0.30339372 8.65967e-04 30.34 % 0.09 % 2 14 TRUE 14: 1 iTunes 1 North America Allegro 2014-04-01 2014-02-01 757218 416.683 0.01548575 4.41407e-05 1.55 % ~0.00 % 6 23 TRUE 15: 1 iTunes 1 North America Orchard 2014-04-01 2014-03-01 3001493 3749689.293 0.06138306 3.97218e-01 6.14 % 39.72 % 3 1 TRUE 16: 1 iTunes 2 Europe Orchard 2014-04-01 2014-03-01 1173238 1831083.802 0.02399371 1.93973e-01 2.40 % 19.40 % 5 3 TRUE 17: 1 iTunes 6 Oceania Orchard 2014-04-01 2014-03-01 249347 592715.475 0.00509936 6.27885e-02 0.51 % 6.28 % 7 4 TRUE 18: 1 iTunes 3 South America Orchard 2014-04-01 2014-03-01 155634 229712.220 0.00318285 2.43342e-02 0.32 % 2.43 % 8 5 TRUE 19: 1 iTunes 4 Asia Orchard 2014-04-01 2014-03-01 134812 184979.340 0.00275702 1.95955e-02 0.28 % 1.96 % 9 6 TRUE 20: 1 iTunes 1 North America RED 2014-04-01 2014-03-01 75575 120018.010 0.00154557 1.27139e-02 0.15 % 1.27 % 10 7 TRUE 21: 1 iTunes 1 North America Allegro 2014-04-01 2014-03-01 64770 119346.491 0.00132460 1.26428e-02 0.13 % 1.26 % 11 8 TRUE 22: 1 iTunes 1 North America RED 2014-04-01 2014-04-01 1412692 2428389.140 0.02889074 2.57248e-01 2.89 % 25.72 % 4 2 TRUE 23: 1 iTunes 1 North America Orchard 2014-05-01 2014-03-01 27564695 14106.001 0.52696095 1.40128e-03 52.70 % 0.14 % 1 13 TRUE 24: 1 iTunes 1 North America RED 2014-05-01 2014-03-01 16871454 8545.453 0.32253567 8.48897e-04 32.25 % 0.08 % 2 15 TRUE 25: 1 iTunes 1 North America Allegro 2014-05-01 2014-03-01 886843 449.162 0.01695399 4.46193e-05 1.70 % ~0.00 % 6 24 TRUE 26: 1 iTunes 1 North America Orchard 2014-05-01 2014-04-01 3481899 4359778.057 0.06656431 4.33096e-01 6.66 % 43.31 % 3 1 TRUE 27: 1 iTunes 2 Europe Orchard 2014-05-01 2014-04-01 1443480 2264274.719 0.02759536 2.24931e-01 2.76 % 22.49 % 4 2 TRUE 28: 1 iTunes 6 Oceania Orchard 2014-05-01 2014-04-01 292293 606320.475 0.00558784 6.02313e-02 0.56 % 6.02 % 7 4 TRUE 29: 1 iTunes 3 South America Orchard 2014-05-01 2014-04-01 183322 280462.891 0.00350461 2.78609e-02 0.35 % 2.79 % 8 5 TRUE 30: 1 iTunes 4 Asia Orchard 2014-05-01 2014-04-01 170800 238464.372 0.00326522 2.36888e-02 0.33 % 2.37 % 9 6 TRUE 31: 1 iTunes 1 North America RED 2014-05-01 2014-04-01 97602 169161.436 0.00186588 1.68043e-02 0.19 % 1.68 % 10 7 TRUE 32: 1 iTunes 1 North America Allegro 2014-05-01 2014-04-01 75034 129787.986 0.00143444 1.28930e-02 0.14 % 1.29 % 11 8 TRUE 33: 1 iTunes 1 North America RED 2014-05-01 2014-05-01 1120872 1769465.230 0.02142798 1.75777e-01 2.14 % 17.58 % 5 3 TRUE 34: 286 Spotify 2 Europe Orchard 2014-03-01 2014-02-01 291195007 2188420.086 0.45471736 5.84721e-01 45.47 % 58.47 % 1 1 TRUE 35: 286 Spotify 1 North America Orchard 2014-03-01 2014-02-01 219184840 932428.201 0.34226944 2.49134e-01 34.23 % 24.91 % 2 2 TRUE 36: 286 Spotify 1 North America RED 2014-03-01 2014-02-01 73272795 332821.926 0.11441959 8.89263e-02 11.44 % 8.89 % 3 3 TRUE 37: 286 Spotify 2 Europe RED 2014-03-01 2014-02-01 12945870 101299.434 0.02021570 2.70661e-02 2.02 % 2.71 % 4 4 TRUE 38: 286 Spotify 6 Oceania Orchard 2014-03-01 2014-02-01 11877491 80541.002 0.01854737 2.15197e-02 1.85 % 2.15 % 5 5 TRUE 39: 286 Spotify 3 South America Orchard 2014-03-01 2014-02-01 11422684 18285.517 0.01783716 4.88569e-03 1.78 % 0.49 % 6 8 TRUE 40: 286 Spotify 2 Europe Orchard 2014-04-01 2014-03-01 331071987 2247587.469 0.45223425 5.74507e-01 45.22 % 57.45 % 1 1 TRUE 41: 286 Spotify 1 North America Orchard 2014-04-01 2014-03-01 248646456 983325.879 0.33964349 2.51348e-01 33.96 % 25.13 % 2 2 TRUE 42: 286 Spotify 1 North America RED 2014-04-01 2014-03-01 83488229 355870.715 0.11404238 9.09643e-02 11.40 % 9.10 % 3 3 TRUE 43: 286 Spotify 3 South America Orchard 2014-04-01 2014-03-01 15391081 27216.710 0.02102375 6.95687e-03 2.10 % 0.70 % 4 6 TRUE 44: 286 Spotify 2 Europe RED 2014-04-01 2014-03-01 14999073 106002.845 0.02048828 2.70954e-02 2.05 % 2.71 % 5 4 TRUE 45: 286 Spotify 6 Oceania Orchard 2014-04-01 2014-03-01 14227989 97272.239 0.01943500 2.48638e-02 1.94 % 2.49 % 6 5 TRUE 46: 286 Spotify 1 North America SelectO 2014-04-01 2014-03-01 7745811 23558.010 0.01058054 6.02167e-03 1.06 % 0.60 % 7 8 TRUE 47: 286 Spotify 2 Europe Orchard 2014-05-01 2014-04-01 320880718 2747992.780 0.43681682 5.68737e-01 43.68 % 56.87 % 1 1 TRUE 48: 286 Spotify 1 North America Orchard 2014-05-01 2014-04-01 248005554 1194751.958 0.33761143 2.47271e-01 33.76 % 24.73 % 2 2 TRUE 49: 286 Spotify 1 North America RED 2014-05-01 2014-04-01 91587903 482543.857 0.12467915 9.98694e-02 12.47 % 9.99 % 3 3 TRUE 50: 286 Spotify 3 South America Orchard 2014-05-01 2014-04-01 18575097 37453.018 0.02528639 7.75144e-03 2.53 % 0.78 % 4 6 TRUE 51: 286 Spotify 2 Europe RED 2014-05-01 2014-04-01 15041762 133793.889 0.02047644 2.76906e-02 2.05 % 2.77 % 5 4 TRUE 52: 286 Spotify 6 Oceania Orchard 2014-05-01 2014-04-01 14076641 114771.838 0.01916261 2.37537e-02 1.92 % 2.38 % 6 5 TRUE 53: 286 Spotify 1 North America SelectO 2014-05-01 2014-04-01 8364058 30066.879 0.01138604 6.22278e-03 1.14 % 0.62 % 7 7 TRUE storeid store_name_short continent_ordered label_sc_group accounting_month activity_month units gross units_perc gross_perc units_percstr gross_percstr rank_units rank_gross moreThan1Perc