## 11 Merge Counts with Accounting Data.r

### ... need to run file in src/Lee/

GOAL is to merge 
   DT.Spotify.Counts.agg
   DT.Acc
   ON:  year, month, 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.agg needs year+month concat'd.  Instead of changing the DT (because dont know where else needed), copying then modifyin
DT.Spot_Merged <- copy(DT.Spotify.Counts.agg)
DT.Spot_Merged[, Date := as.Date(sprintf("%4i-%02i-01", year, month))]
DT.Spot_Merged[, c("year", "month") := NULL]
setnames(DT.Spot_Merged, "product", "Product")

keyCols.Merge <- c("Product", "Date")
setcolorderpt(DT.Spot_Merged, keyCols.Merge)

(accounting) Revo> print(head(DT.Spotify.Counts.agg))
   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      85345123     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