> DB.iradio.customer customer_id postal_code user_type zip plus4 zip3 1: 49604383 NA Not a Match Subsc NA NA NA 2: 191990863 NA Not a Match Subsc NA NA NA 3: 456682143 NA Not a Match Subsc NA NA NA 4: 456682167 NA Not a Match Subsc NA NA NA 5: 474366267 NA Not a Match Subsc NA NA NA --- 14487502: 719526907 99705-7705 Match Subscriber 99705 7705 997 14487503: 62749937 99709-3314 Match Subscriber 99709 3314 997 14487504: 233545682 99801-7744 Match Subscriber 99801 7744 998 14487505: 1418764637 99801-9005 Match Subscriber 99801 9005 998 14487506: 5001582292 99901-9341 Match Subscriber 99901 9341 999 DB.iradio.event event_type end_reason event_start_time event_end_time customer_id apple_id device_type track_ownership station_id storefront_name duration station_type 1: Played Skipped 2013-11-19 05:29:45 2013-11-19 05:29:46 903773088 658889425 Mobile Not Owned 100008 US 1.036 secs User Created 2: Played Natural 2013-11-19 16:56:59 2013-11-19 17:01:21 1139363507 132824606 Desktop Not Owned 1000227 US 261.920 secs User Created 3: Played Natural 2013-11-15 13:04:51 2013-11-15 13:07:29 5460181523 144134030 Mobile Not Owned 1000227 US 157.467 secs User Created 4: Played Natural 2013-11-15 16:12:09 2013-11-15 16:14:47 1138883713 144134030 Mobile Not Owned 1000227 US 157.467 secs User Created 5: Played Other 2013-11-15 16:29:55 2013-11-15 16:30:12 463971063 144134030 Mobile Not Owned 1000227 US 16.972 secs User Created --- 22179568: Played Natural 2013-11-19 06:33:03 2013-11-19 06:36:30 2754271563 42263716 Mobile Not Owned 9e+05 US 206.958 secs User Created 22179569: Played Natural 2013-11-23 00:49:04 2013-11-23 00:52:31 479057658 42263716 Mobile Not Owned 9e+05 US 206.958 secs User Created 22179570: Played Skipped 2013-11-17 02:49:00 2013-11-17 02:49:01 1128221423 524101839 Mobile Not Owned 9e+05 US 0.945 secs User Created 22179571: Played Natural 2013-11-19 06:28:11 2013-11-19 06:30:24 2754271563 524101839 Mobile Not Owned 9e+05 US 133.587 secs User Created 22179572: Played Natural 2013-11-19 06:10:59 2013-11-19 06:13:18 7378334508 524109246 Desktop Not Owned 9e+05 US 138.333 secs User Created # duration should be rounded DB.iradio.event[, duration := round(duration)] durs.90 <- DB.iradio.event[event_type == "Played" & duration < 90 & Track.Seconds > 100, round(duration), by=list(end_reason)] durs.90[, dur10 := ceiling(V1/10) * 10] table(durs.90[, !"V1", with=FALSE]) DB.iradio.event[, levels(event_type)] DB.iradio.event[event_type==("Wished")] I want to see if each "non-skipped" duration is identical, per apple id durs <- DB.iradio.event[.("Natural"), .N, by=list(apple_id, duration)][N>1] ## CALCULATE HOW EARLY TRACK WAS SKIPPED setkey(DB.iradio.event, end_reason, device_type) DB.iradio.event[.("Skipped"), SkippedBefore10 := duration < 10] DB.iradio.event[.("Skipped"), SkippedAfter30 := duration > 30] DB.iradio.event[.("Skipped"), SkippedBefore30 := !SkippedAfter30 & !SkippedBefore10] agg.skipps <- DB.iradio.event[.("Skipped"), list(.N, Secs_10=sum(SkippedBefore10), Secs_30=sum(SkippedBefore30), After_30=.N-sum(SkippedBefore30)), keyby=list(device_type, track_ownership, station_type)] countcols <- c("N", "Secs_10", "Secs_30", "After_30") agg.skipps[, paste0(countcols, ".perc") := lapply(.SD, "/", N), .SDcols=countcols] agg.end_reason.device_type <- DB.iradio.event[, .N, keyby=list(device_type, end_reason)] agg.end_reason.device_type[, c("Total_by_device_type", "Perc_of_Total.string", "Perc_of_Total") := {T <- sum(N); list(T, fwp(N/T), N/T)}, by=device_type] ## But, how many new tracks per each agg.tracks.per.device_type <- unique(DB.iradio.event[, `:=`(uniqTracks=lunique(apple_id), totalTracks=.N), keyby=list(device_type, end_reason, customer_id)][, list(device_type, end_reason, customer_id, uniqTracks, totalTracks)]) agg.tracks.per.device_type How many customers listen to both mobile and desktop; and how does that divide agg.tracks.per.device_type[, customer_id] P.trks <- ggplot() + geom_boxplot(aes(y=totalTracks, x=device_type, color=end_reason), notch=TRUE, data=agg.tracks.per.device_type) + ggtitle("Total Tracks by Device Type & End-Reason") P.trks + scale_y_log10(labels=comma) jesusForData(agg.tracks.per.device_type) saveImageTo() library(ggplot2) library(scales) ggplot() + geom_density(aes(x=DB.iradio.event[, list("Users Music"=uniq.tracks.per.user)]), color="blue") + geom_density(aes(x=DB.iradio.event[, list("Tracks Users"=uniq.users.per.track)]), color="green") rep(c("A", "C"), each=c(2, 3)) rep(c("A", "C"), times=c(2, 3)) DB.plot <- rbind( unique(DB.iradio.event, by="apple_id")[, list(value=uniq.users.per.track, group="Track's Users")] , unique(DB.iradio.event, by="customer_id")[, list(value=uniq.tracks.per.user, group="User's Music")] ) logx <- scale_x_log10(labels=comma) logy <- scale_y_log10(labels=comma) dim(DB.plot) { P <- ggplot() + geom_density(aes(x=value, fill=group), data=DB.plot, alpha=0.5, color=FALSE) Plog <- P + scale_x_log10(labels=comma) } { P <- ggplot() + geom_density(aes(x=value, fill=group), data=DB.plot[value >= 10], alpha=0.5, color=FALSE) Pfac <- P + scale_x_continuous(labels=comma) + facet_grid(group ~ .) Pfac } geom_density(aes(x="Users Music"), data=DB.iradio.event[, list("Users Music"=uniq.tracks.per.user)], fill="blue", alpha=0.5, color=FALSE) + geom_density(aes(x="Tracks Users"), data=DB.iradio.event[, list("Tracks Users"=uniq.users.per.track)], fill="green", alpha=0.5, color=FALSE) # ~~~~~ print("hi") DB.iradio.event[, table(end_reason, station_type)] station_type end_reason Program User Created Natural 5245069 6496349 Skipped 1625940 3061052 Other 2063680 3687482 # ~~~~~ DB.iradio.event[, table(event_type, station_type)] station_type event_type Program User Created Played 8839396 12772236 2 (undefined) 0 0 Liked 42606 293259 Banned 17381 116385 Wished 35255 62954 Unwished 51 49 DB.iradio.customer[, table(user_type)] user_type Match Subscriber Not a Match Subsc 1005484 13482022 DB.iradio.customer[, table(user_type, zip3)] D