## 11 Merge Counts with Accounting Data.r ### ... need to run file in src/Lee/ NEED COUNTRY!!! GOAL is to merge ## Revenue data (Accounting) DT.Acc (Accounting Data) ## With User count data (Analytics) DT.Spotify.Counts.plusMAX_MIN_DATE.byCnDPMb ON: date, country, product ## THERE IS DOUBLE COUNTING, IF A USER CONVERTED THIS MONTH, THEY ARE COUNTED UNDER BOTH PRODUCTS Percent_of_Monthly_Streams === "Attributed to a product type: The number of streams this month as percentage of all streams this month" Percent_of_Monthly_Streams === "Attributed to a product type: The number of uusers this month as percentage of all uusers this month" ### DT.Spotify.Counts.plusMAX_MIN_DATE.byCnDPMb. Instead of changing the DT (because dont know where else needed), copying then modifying DT.Spot_Prep <- copy(DT.Spotify.Counts.plusMAX_MIN_DATE.byCnDPMb) DT.Acc_Prep <- copy(DT.Acc) setnames(DT.Spot_Prep, "month", "Month") setnames(DT.Spot_Prep, "product", "Product") setnames(DT.Spot_Prep, "country", "Country") keyCols.Merge <- c("Month", "Product", "Country") nms.merge.An <- setdiff(names(DT.Spot_Prep), keyCols.Merge) setnames(DT.Spot_Prep, nms.merge.An, paste0("Anlytcs.", nms.merge.An)) nms.merge.Ac <- setdiff(names(DT.Acc_Prep), keyCols.Merge) setnames(DT.Acc_Prep, nms.merge.Ac, paste0("Acc.", nms.merge.Ac)) setkeyIfNot(DT.Spot_Prep, keyCols.Merge) setkeyIfNot(DT.Acc_Prep, keyCols.Merge) key(DT.Spot_Merged) DT.Spot_Merged <- merge(DT.Spot_Prep, DT.Acc_Prep, all=TRUE) # -------------------------------------------------------------------- fieldTypes <- desc(DT.Spot_Merged, quiet=TRUE) measure.vars <- fieldTypes[c("integer", "numeric")]$Columns ## Manually coerce to numeric, to avoid the warning in melt() DT.Spot_Merged[, c(measure.vars) := lapply(.SD, as.numeric), .SDcols=measure.vars] # id.vars <- unique(c("Month", fieldTypes["character"]$Columns)) DT.Spot_Melted <- reshape2::melt(DT.Spot_Merged, measure.vars=measure.vars) ## Source DT.Spot_Melted[, Source := ifelse (grepl("^Acc\\.", variable), "Accounting", ifelse(grepl("^Anlytcs\\.", variable), "Analytics", NA))] DT.Spot_Melted[, variable := gsub("^(Anlytcs|Acc)\\.", "", variable)] # -------------------------------------------------------------------- character : Product, Country, Acc.Partner, Acc.Currency, Acc.Payable_type factor : Acc.Product_Descr integer : Anlytcs.uniqueUsers_perDCnP, Anlytcs.uniqueUsers_perDCn, Anlytcs.uniqueUsers_perDP, Acc.Total_tracks, Acc.Rightholders_tracks, Acc.Revenue_share, Acc.New_product_users numeric : Anlytcs.total_streams_perCnDP_Mobile, Anlytcs.total_streams_perCnD_Product, Anlytcs.median_monthly_streams_perDCnP_Mobile, Anlytcs.mean_monthly_streams_perDCnP_Mobile, Anlytcs.median_monthly_streams_perDCnP, Anlytcs.mean_monthly_streams_perDCnP, Anlytcs.perc_mobile_perDCn_Product, Anlytcs.perc_mobile_perD_Country, Anlytcs.mean_streams_per_user_perDCnP, Anlytcs.mean_of__perc_mobile_perDCnP_User__perDCnP, Acc.Pro_rata_share, Acc.Months_committed_but_not_activated, Acc.Fee_per_month, Acc.Payable, Acc.Payable_EUR, Acc.Total_bundles, Acc.Net_revenue, Acc.Registered_users, Acc.Active_users, Acc.Gross_revenue, Acc.Payable_USD, Acc.Orchard_Track_Share, Acc.Orchard_User_Share Date : Date logical : Anlytcs.mobile (accounting) Revo> print(head(DT.Spotify.Counts.by.DPMbCn.plusMAXDATE)) year month product Total_Streams Avg_Streams Median_Streams Unique_Users Total_Monthly_Users Total_Monthly_Streams Percent_of_Monthly_Streams Percent_of_Monthly_Users Streams_per_User Date 1: 2013 3 Open 85349 5123 13.2850 4 6424193 13061655 232572101 0.3669620 0.4918361 13.2850 2013-03-01 2: 2013 3 Free 11669586 12.4607 4 936513 13061655 232572101 0.0501762 0.0716994 12.4607 2013-03-01 3: 2013 3 DesktopBasic 15438113 39.2310 13 393518 13061655 232572101 0.0663799 0.0301277 39.2310 2013-03-01 4: 2013 3 Premium 120119279 22.6323 8 5307431 13061655 232572101 0.5164819 0.4063368 22.6323 2013-03-01 5: 2013 4 Open 90593382 13.1012 4 6914910 13929255 244972813 0.3698099 0.4964307 13.1012 2013-04-01 6: 2013 4 Free 11260273 12.1417 4 927407 13929255 244972813 0.0459654 0.0665798 12.1417 2013-04-01 (accounting) Revo> DT.Acc[.("BE", as.Date("2013-10-01")), ] Country Date Partner Product Currency Pro_rata_share Months_committed_but_not_activated Fee_per_month Payable_type Payable Payable_EUR Total_tracks Rightholders_tracks Revenue_share Total_bundles Net_revenue Registered_users Active_users 1: BE 2013-10-01 NA U EUR 9.21 NA NA NA 1494.5416 1494.5416 4120126 379482 60 NA 27044.32 6503.65 6503.65 2: BE 2013-10-01 NA A EUR 5.57 NA NA NA 2933.6076 2933.6076 25869204 1441633 60 NA 87736.26 1838511.68 1838511.68 3: BE 2013-10-01 NA KHB EUR 7.35 NA NA NA 49.4526 49.4526 58333 4285 0 NA 1142.45 177.16 177.16 4: BE 2013-10-01 NA KSB EUR 6.67 NA NA NA 4101.9607 4101.9607 4704377 313889 0 NA 106289.32 18351.55 18351.55 5: BE 2013-10-01 NA KSA EUR 6.99 NA NA NA 161.9602 161.9602 124524 8708 60 NA 3860.04 359.55 359.55 6: BE 2013-10-01 NA P EUR 7.63 NA NA NA 17026.3887 17026.3887 25004130 1907321 60 NA 367637.79 45056.19 45056.19 New_product_users Gross_revenue Payable_USD Product_Descr 1: 0 27044.32 2030.1853 Unlimited 2: 0 98766.00 3985.0125 Ad funded 3: 0 1142.45 67.1764 KPN (Dutch) Hard Bundle 4: 0 106289.32 5572.1035 KPN (Dutch) Soft Bundle 5: 0 3860.04 220.0068 KSA 6: 0 367637.79 23128.6464 Premium