# Acc_vs_Anal_for_Spotify.r First check BI tables, since it incorporates the error table Then check fact tables * Check reported Rightsholders tracks against Accounting * Check accounting vs analytics lastly, comp setScience("Acc_vs_Anal_2015") loadFromJesus("DT.revshare", srcDir= src.p(proj="Spotify_Accounting_ETL") ) create_DT.acc_v_anal() ## Crop out the most recent dates, where accounting has not come in yet DT.acc_v_anal_spotify <- DT.acc_v_anal[storeid == 286][date <= max(date[countryid == 1 & !is.na(paidunits.acc)])] DT.acc_v_anal_spotify <- DT.acc_v_anal_spotify[date >= min(date[!is.na(paidunits.anal)])] setkeyIfNot(DT.acc_v_anal_spotify, c("storeid", "countryid", "transid", "date"), organize=TRUE) ## Drop revenue columns DT.acc_v_anal_spotify[, extract("revenue", DT.acc_v_anal_spotify) := NULL] DT.acc_v_anal_spotify_usa <- DT.acc_v_anal_spotify[countryid == 1] DT.acc_v_anal_spotify_usa[, ] qry.raw <- makeQry(tbl="staging_raw_spotify", schema="production", colsToPull=c("download_date", country_code="country", "product"), colsToAgg=c(paidunits="*"), aggFunc="count", dateCol="download_date", minDate="2013-01-01") DT.spotify_raw <- runQry(qry.raw, cluster=4, firstOfMonth=TRUE) DT.spotify_raw_usa <- DT.spotify_raw[country_code=="US"] runQry("SELECT count(*) FROM production.staging_raw_spotify WHERE download_date >= '2014-03-01' AND download_date <= '2014-08-01'", cluster=4) DT.raw_vs_anal <- merge(DT.spotify_raw_usa[, list(paidunits.raw = sum(paidunits)), keyby=list(date=download_date)], DT.acc_v_anal_spotify_usa[date >= "2013-03-01", list(paidunits.anal=sum(paidunits.anal)), keyby=date]) DT.raw_vs_anal[, raw_vs_anal := paidunits.raw / paidunits.anal] DT.raw_vs_anal qry.spot_simple_fa <- makeQry(tbl="fact_analytics", colsToPull=c(date="download_activity_date"), colsToAgg=c(paidunits_anal="paidunits"), whereIn=c(storeid=286, countryid=1), dateCol="download_activity_date", minDate="2013-01-01", key="colsToPull") qry.spot_simple_fe <- convertQry.fa_to_faerrors(qry.spot_simple_fa) qry.spot_simple <- unionQrys(qry.spot_simple_fa, qry.spot_simple_fe) DT.fact_simple <- runQry(qry.spot_simple, firstOfMonth=TRUE) (Acc..Anal) R|Acc_vs_Anal_2015> qShowCols("staging_raw_spotify", cluster=4) [1] "albumname" "artistname" "birthyear" "country" "customerid" "download_date" "download_datetime" "filename" "filesize" [10] "gender" "ingestion_time" "isrc" "mobile" "original_upc" "product" "trackid" "trackname" "upc" [19] "zip" (Acc..Anal) R|Acc_vs_Anal_2015> qShowCols("fact_sales") [1] "accountingmonth" "accountingperiodid" "accountingquarter" "accountingyear" [5] "activity_fx_rate" "activitymonth" "activityperiodid" "activityquarter" [9] "activityyear" "actual_net" "adjusted_gross" "artistid" [13] "catalogid" "countryid" "datecreated" "distribution_fees" [17] "dpd_publishing" "fx_actual_net" "fx_adjusted_exchange_rate" "fx_adjusted_gross" [21] "fx_distribution_fees" "fx_dpd_publishing" "fx_gross" "fx_net_receipt" [25] "fx_oms_fees" "fx_ringtone_publishing" "fx_spread_fee" "genreid" [29] "gross" "imprintid" "isrcid" "labelid" [33] "net_receipt" "oms_fees" "original_currency_id" "partner_share" [37] "payout_currency_id" "releaseid" "retail_price" "ringtone_publishing" [41] "sales" "statement_detail_id" "storeid" "subaccountid" [45] "trackid" "transactiontypeid" (Acc..Anal) R|Acc_vs_Anal_2015> qShowCols("fact_analytics") [1] "artistid" "catalogid" "countryid" "currencyid" [5] "dayid" "download_activity_date" "freeunits" "genreid" [9] "imprintid" "isrcid" "labelid" "paidunits" [13] "processeddaytime" "releaseid" "royalty" "royaltydollar" [17] "storeid" "storeuserid" "subaccountid" "trackid" [21] "transactiontypeid" "units" "uuid" "zipid" (Acc..Anal) R|Acc_vs_Anal_2015> qShowCols("bi.accounting") [1] "accounting_month" "accounting_month_name" "activity_month" [4] "activity_month_name" "actual_net" "adjusted_gross" [7] "album_vs_track" "artist_country" "artist_name" [10] "artistid" "continent" "country_code" [13] "country_name" "dim_catalogid" "dim_imprintid" [16] "dim_isrcid" "dim_subaccountid" "distribution_fees" [19] "dpd_publishing" "gross" "is_store_itunesusa" [22] "isrc" "label_continent" "label_country" [25] "label_is_dthree" "label_is_ioda" "label_name" [28] "label_owner" "label_ownerid" "label_priority" [31] "label_sc_group" "labelid" "music_vs_video" [34] "music_vs_video_by_product" "music_vs_video_by_tracktype" "music_vs_video_by_transac" [37] "net_receipt" "release_genre" "release_genre_group" [40] "release_genre_group_less" "release_is_compilation" "release_is_deleted" [43] "release_is_digital_only" "release_is_itunes_previewable" "release_name" [46] "release_new_or_catalog" "release_priority" "release_product_type" [49] "release_releasedate" "release_sale_physical_date" "release_sale_preorder_date" [52] "release_sale_start_date" "release_theatrical_release_date" "release_version" [55] "release_video_channel" "release_video_is_vod" "release_vod_sale_start_date" [58] "releaseid" "retail_price" "ringtone_publishing" [61] "store_group" "store_group_less" "store_is_in_analytics" [64] "store_musicbucket" "store_name" "store_name_full" [67] "store_uses_royaltydollar" "store_videobucket" "storeid" [70] "stream_vs_download" "track_name" "trackid" [73] "transac_type" "transac_type_abbr" "transac_type_group" [76] "transac_typeid" "units" (Acc..Anal) R|Acc_vs_Anal_2015> qShowCols("bi.analytics") [1] "accounting_days_in_month" "accounting_days_missing" "accounting_days_present" [4] "accounting_max_date_by_group" "activity_date" "activity_month544" [7] "activity_month544_end" "activity_month544_name" "activity_month544_start" [10] "activity_month_name" "album_vs_track" "artist_country" [13] "artist_name" "artistid" "continent" [16] "country_code" "country_name" "freeunits" [19] "from_errors_table" "gest" "gest_net_of_sc" [22] "gpuest" "is_store_itunesusa" "label_continent" [25] "label_country" "label_is_dthree" "label_is_ioda" [28] "label_name" "label_owner" "label_ownerid" [31] "label_priority" "label_sc_group" "labelid" [34] "music_vs_video" "music_vs_video_by_product" "music_vs_video_by_tracktype" [37] "music_vs_video_by_transac" "paidunits" "paidunits_expected_ratio" [40] "release_genre" "release_genre_group" "release_genre_group_less" [43] "release_is_compilation" "release_is_deleted" "release_is_digital_only" [46] "release_is_itunes_previewable" "release_name" "release_new_or_catalog" [49] "release_priority" "release_product_type" "release_releasedate" [52] "release_sale_physical_date" "release_sale_preorder_date" "release_sale_start_date" [55] "release_theatrical_release_date" "release_version" "release_video_channel" [58] "release_video_is_vod" "release_vod_sale_start_date" "releaseid" [61] "royaltydollar" "store_group" "store_group_less" [64] "store_is_in_analytics" "store_musicbucket" "store_name" [67] "store_name_full" "store_uses_royaltydollar" "store_videobucket" [70] "storeid" "stream_vs_download" "thisrow_last_updated" [73] "thisrow_max_fact_processeddaytime" "transac_type" "transac_type_abbr" [76] "transac_type_group" "transac_typeid" "units"