need to add a payable_fixed column calculate the "shares" manually, to avoid rounding errors DT.Acc Questions: ## Why is Registered_users == Active_users for all rows? nrow(DT.Acc[!is.na(Registered_users)][(Registered_users == Active_users)]) == nrow(DT.Acc[!is.na(Registered_users)]) ## Why is User info NA for many users and other fields DT.Acc[!is.na(Registered_users)][(Registered_users == Active_users)] ## What is the New_product_users column? Why is it all 0 ? nrow(DT.Acc[New_product_users == 0]) == nrow(DT.Acc[!is.na(New_product_users)]) ## Why so many rows with no 'Payable' Value, even if different Currency DT.Acc[is.na(Payable_USD), table(Currency)] ## What is payable_type ??? DT.Acc[!is.na(Payable_type)] as.data.table(DT.Acc[, table(Payable_type, Product)])[count != 0] DT.Acc[, sum(Payable_Using[!is.na(Payable_type)], na.rm=TRUE) / sum(Payable_Using, na.rm=TRUE)] [1] 0.00226088 ## How is Net_revenue calculated DT.Acc[Net_revenue != Gross_revenue] ## What are the Partner columns? Partner === ?? Months_committed_but_not_activated === ?? Total_bundles === ?? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ## GROUPINGS, Affecting rates Month === Month ** Country == Country ** ## Product Product_code === Spotifys Shorthand Product === Descriptive name Product_Tier === We get paid based on thses ** ## User Info Registered_users === Number of Users who have accounts this categ Active_users === Number of Users who are using the produdct this categ New_product_users === ?? Conversion ?? New Sign up ## Track Info Total_tracks === Total Streams ALL of Spotify this categ Rightholders_tracks === Streams of Orchard Music this categ Pro_rata_share === Rightholders_tracks / Total_tracks * 100 ** ## Payable Payable === Measured in 'Currency'. What they owe us Currency === Applies to 'Payable'. Can bary Territory AND product Payable_EUR === Payable Ammount in Euros Payable_USD === Payable Ammount in Dollars Payable_type === ???, 18 ROWS have "per_user_payable" as a value %??% Fee_per_month === ??? 18 none-blank values unclear, but I think ** ## Revenue Gross_revenue === Spotifys Revenue this categ Net_revenue === Spotifys Net Revenue. Im not sure how this is calculated, but this is what we get paid off of. Not Gross. ** Revenue_share === This is the "A%". 60% until EOY 2013. 55% Thereafter. ** ## Partner Partner === ?? Months_committed_but_not_activated === ?? Total_bundles === ?? *** OUR_CUT = Payable = (Net_revenue * Pro_rata_share/100) * Revenue_share *** Payable_Fixed .... Orchard_User_Share := Rightholders_tracks / Registered_users Month Product_code Month Country Product_code Product_Tier Product Fee_per_month 1: 2013-06-01 M 2013-06-01 AR M ThirdPartyMobile Mobile NA 2: 2013-07-01 M 2013-07-01 AR M ThirdPartyMobile Mobile NA 3: 2013-10-01 OSA 2013-10-01 CH OSA VariousPartners OSA NA 4: 2013-10-01 P 2013-10-01 CH P PremiumTier Premium NA 5: 2013-09-01 TSA 2013-09-01 SE TSA VariousPartners TSA NA 6: 2013-10-01 10 2013-10-01 SE 10 VariousPartners 10 NA 7: 2013-07-01 P 2013-07-01 US P PremiumTier Premium NA 8: 2013-08-01 U 2013-08-01 US U LimitedTier Unlimited NA Month Product_code Active_users New_product_users Orchard_User_Share Registered_users Pro_rata_share 1: 2013-06-01 M 0.00 0 Inf 0.00 0.92 2: 2013-07-01 M 0.00 0 Inf 0.00 12.11 3: 2013-10-01 OSA 2942.77 0 30.0577 2942.77 5.97 4: 2013-10-01 P 44663.29 0 35.6432 44663.29 6.34 5: 2013-09-01 TSA 9682.00 0 23.9209 9682.00 4.61 6: 2013-10-01 10 NA NA NA NA 21.21 7: 2013-07-01 P 92288.00 0 28.7712 92288.00 10.01 8: 2013-08-01 U 61655.00 0 62.5346 61655.00 12.28 Month Product_code Total_tracks Rightholders_tracks 1: 2013-06-01 M 1845 17 2: 2013-07-01 M 190 23 3: 2013-10-01 OSA 1482231 88453 4: 2013-10-01 P 25122917 1591944 5: 2013-09-01 TSA 5025114 231602 6: 2013-10-01 10 66 14 7: 2013-07-01 P 26513699 2655233 8: 2013-08-01 U 31403236 3855573 Month Product_code Currency Payable Payable_EUR Payable_type Payable_USD 1: 2013-06-01 M USD 0.0429 NA NA NA 2: 2013-07-01 M USD 0.0230 NA NA NA 3: 2013-10-01 OSA CHF 1137.0159 922.9047 NA 1253.67 4: 2013-10-01 P CHF 20412.5108 16568.6354 NA 22506.83 5: 2013-09-01 TSA SEK 19084.4833 NA NA NA 6: 2013-10-01 10 SEK 143.3384 16.2817 NA NA 7: 2013-07-01 P USD 43395.0354 NA NA NA 8: 2013-08-01 U USD 19382.8034 NA NA NA Month Product_code Revenue_share Net_revenue Gross_revenue 1: 2013-06-01 M 60 7.7687 9.1396 2: 2013-07-01 M 60 0.0000 0.0000 3: 2013-10-01 OSA 60 26777.2598 26777.2598 4: 2013-10-01 P 60 536892.6436 536892.6436 5: 2013-09-01 TSA 60 0.0000 0.0000 6: 2013-10-01 10 65 1008.0000 NA 7: 2013-07-01 P 60 722198.3328 849645.0975 8: 2013-08-01 U 60 263118.1539 309550.7693 Month Product_code Partner Months_committed_but_not_activated Total_bundles 1: 2013-06-01 M NA NA NA 2: 2013-07-01 M NA NA NA 3: 2013-10-01 OSA NA NA NA 4: 2013-10-01 P NA NA NA 5: 2013-09-01 TSA NA NA NA 6: 2013-10-01 10 NA NA 12.6 7: 2013-07-01 P NA NA NA 8: 2013-08-01 U NA NA NA > setkeyv(DT.Acc, keyCols.Merge)[DT.Acc.Using[, .N, by=c("Country", "Month", "Product")][(N>1)][1:4]] Country Month Product_Tier Product_code Product Fee_per_month Active_users New_product_users Registered_users Total_tracks Rightholders_tracks Pro_rata_share Currency 1: BE 2014-01-01 KPN (Dutch) Hard Bundle KHB KPN (Dutch) Hard Bundle 3.80 NA NA NA NA NA 6.36 EUR 2: BE 2014-01-01 KPN (Dutch) Hard Bundle KHB KPN (Dutch) Hard Bundle NA 115.71 0 115.71 41662 2656 6.38 EUR 3: BE 2014-01-01 KPN (Dutch) Soft Bundle KSB KPN (Dutch) Soft Bundle 3.35 NA NA NA NA NA 6.62 EUR 4: BE 2014-01-01 KPN (Dutch) Soft Bundle KSB KPN (Dutch) Soft Bundle NA 16829.32 0 16829.32 4283547 285644 6.67 EUR 5: CH 2014-01-01 OHB NA NA NA NA NA NA NA NA NA NA 6: DE 2013-12-01 DT NA NA NA NA NA NA NA NA NA NA Payable Payable_EUR Payable_type Payable_USD Revenue_share Net_revenue Gross_revenue Partner Months_committed_but_not_activated Total_bundles Pro_rata_share.exact Payable_Using N 1: 3.7475 3.7475 per_user_payable NA NA NA NA kpng-be 15.52 NA NA 5.05462 2 2: 28.0312 28.0312 NA 37.8084 0 733.193 733.193 NA NA NA 0.0637511 37.80840 2 3: 2951.6052 2951.6052 per_user_payable NA NA NA NA kpng-be 13309.06 NA NA 3981.11701 2 4: 3759.5253 3759.5253 NA 5070.8477 0 95777.398 95777.398 NA NA NA 0.0666840 5070.84770 2 5: NA NA NA NA NA NA NA NA NA NA NA NA 2 6: NA NA NA NA NA NA NA NA NA NA NA NA 2 > 37.8084 / 28.0312 * 3.7475 [1] 5.05462 > DT.Acc[, sum(Payable_Using[!is.na(Payable_type)], na.rm=TRUE) / sum(Payable_Using, na.rm=TRUE)] [1] 0.00226088 >