378 lines
12 KiB
Ruby
378 lines
12 KiB
Ruby
require 'date'
|
|
|
|
class Cohort < ActiveRecord::Base
|
|
|
|
EARLIEST_DATE = Time.parse('2014-03-01')
|
|
TOTAL_COHORT_DATE = Time.at(0)
|
|
|
|
ALLTIME_LABELS = {
|
|
registered_users: 'Registered Users',
|
|
first_downloaded_client_at: 'DL app',
|
|
first_certified_gear_at: 'Certified Gear',
|
|
music_sessions_user_history: 'Played Online',
|
|
jam_tracks_played: 'Played JT',
|
|
jam_track_rights: 'Purchased JT',
|
|
recorded_tracks: 'Made Recording',
|
|
friendships: 'Friended',
|
|
invited_users: 'Invite Others',
|
|
}
|
|
|
|
MONTHLY_LABELS = {
|
|
registered_users: 'Registered Users',
|
|
first_downloaded_client_at: 'DL app',
|
|
first_certified_gear_at: 'Certified Gear',
|
|
music_sessions_user_history_1: 'Played Online 1',
|
|
music_sessions_user_history_2_5: 'Played Online 2-5',
|
|
music_sessions_user_history_6_: 'Played Online 6+',
|
|
jam_tracks_played_1: 'Played JT 1',
|
|
jam_tracks_played_2_5: 'Played JT 2-5',
|
|
jam_tracks_played_6_: 'Played JT 6+',
|
|
jam_track_rights_redeemed: 'Redeemed JT',
|
|
jam_track_rights: 'Purchased JT',
|
|
recorded_tracks: 'Made Recording',
|
|
friendships: 'Friended',
|
|
invited_users: 'Invite Others',
|
|
}
|
|
|
|
attr_accessible :all_time, :monthly_start
|
|
#serialize :data_set, JSON
|
|
|
|
before_create do
|
|
self.data_set ||= {}
|
|
end
|
|
|
|
def self.date_tuples(from,to)
|
|
prec = from.size
|
|
start = Date.new(*from)
|
|
finish = Date.new(*to)
|
|
|
|
filter_on = [:day,:mon].first(3-prec)
|
|
filter = ->(d) { filter_on.all? {|attr| d.send(attr) == 1 } }
|
|
|
|
(start..finish)
|
|
.select(&filter)
|
|
.map { |d| [d.year,d.mon,d.day].first(prec) }
|
|
end
|
|
|
|
def self.earliest_cohort
|
|
user = User.where(admin: false).order(:created_at).first
|
|
starting = user.created_at if user
|
|
starting = EARLIEST_DATE if starting.nil? || starting < EARLIEST_DATE
|
|
starting # this is necessary to always return not null
|
|
end
|
|
|
|
def self.cohort_group_ranges(starting=nil, ending=nil)
|
|
starting ||= self.earliest_cohort
|
|
ending ||= Time.now
|
|
dates = self.date_tuples([starting.year, starting.month], [ending.year, ending.month])
|
|
ranges = []
|
|
dates.each_with_index do |d1, idx|
|
|
d2 = dates[idx+1] || [Time.now.next_month.year,Time.now.next_month.month]
|
|
rr = Time.parse("#{d1[0]}-#{d1[1]}-1")..(Time.parse("#{d2[0]}-#{d2[1]}-1") - 1.second)
|
|
ranges << rr
|
|
end
|
|
ranges
|
|
end
|
|
|
|
def self.generate_monthly_cohorts(monthly_start, monthly_end)
|
|
Cohort.delete_all(['all_time = ?',false])
|
|
self.cohort_group_ranges.collect do |range|
|
|
next if range.first > monthly_end
|
|
cc = Cohort.new
|
|
cc.group_start = range.first
|
|
cc.group_end = range.last
|
|
cc.monthly_start = monthly_start
|
|
cc.monthly_end = monthly_end
|
|
cc.all_time = false
|
|
cc.save!
|
|
cc
|
|
end
|
|
end
|
|
|
|
def self.generate_all_time_cohorts
|
|
Cohort.delete_all("all_time = 't'")
|
|
self.cohort_group_ranges.collect do |range|
|
|
unless cc = Cohort.where(group_start: range.first).where(all_time: true).limit(1).first
|
|
cc = Cohort.new
|
|
cc.group_start = range.first
|
|
cc.group_end = range.last
|
|
cc.all_time = true
|
|
cc.save!
|
|
end
|
|
cc
|
|
end
|
|
end
|
|
|
|
def _put_data_set(key, count, num_user)
|
|
self.data_set[key.to_s] = count
|
|
xx = (count.to_f / num_user.to_f)
|
|
self.data_set["#{key}%"] = 100.0 * xx.round(2)
|
|
end
|
|
|
|
def self.cohort_users(cohort)
|
|
User.where(created_at: cohort.group_start..cohort.group_end)
|
|
end
|
|
=begin
|
|
SELECT played.user_id FROM
|
|
(SELECT user_id, COUNT(*) cnt FROM music_sessions_user_history msuh1
|
|
WHERE
|
|
msuh1.created_at >= '2024-11-01' AND
|
|
msuh1.created_at <= '202' AND
|
|
EXTRACT(EPOCH FROM (msuh1.session_removed_at - msuh1.created_at)) >= 900 AND
|
|
(SELECT COUNT(*) FROM music_sessions_user_history msuh2
|
|
WHERE msuh1.music_session_id = msuh2.music_session_id
|
|
) > 1
|
|
GROUP BY user_id
|
|
) played
|
|
=end
|
|
|
|
def _played_online_subquery(constraint)
|
|
where = if constraint.is_a?(Range)
|
|
"played.cnt >= #{constraint.first} AND played.cnt <= #{constraint.last}"
|
|
else
|
|
"played.cnt #{constraint}"
|
|
end
|
|
start_date = all_time ? self.group_start : self.monthly_start
|
|
end_date = all_time ? self.group_end : self.monthly_end
|
|
sql =<<SQL
|
|
SELECT played.user_id FROM
|
|
(SELECT user_id, COUNT(*) cnt FROM music_sessions_user_history msuh1
|
|
WHERE
|
|
msuh1.created_at >= '#{start_date}' AND
|
|
msuh1.created_at <= '#{end_date}' AND
|
|
EXTRACT(EPOCH FROM (msuh1.session_removed_at - msuh1.created_at)) >= 900 AND
|
|
(SELECT COUNT(*) FROM music_sessions_user_history msuh2
|
|
WHERE msuh1.music_session_id = msuh2.music_session_id
|
|
) > 1
|
|
GROUP BY user_id
|
|
) played
|
|
WHERE #{where}
|
|
SQL
|
|
end
|
|
|
|
def _played_jamtrack_subquery(constraint)
|
|
where = if constraint.is_a?(Range)
|
|
"played.cnt >= #{constraint.first} AND played.cnt <= #{constraint.last}"
|
|
else
|
|
"played.cnt #{constraint}"
|
|
end
|
|
start_date = all_time ? self.group_start : self.monthly_start
|
|
end_date = all_time ? self.group_end : self.monthly_end
|
|
sql =<<SQL
|
|
SELECT played.player_id FROM
|
|
(SELECT player_id, COUNT(*) cnt FROM playable_plays pp
|
|
WHERE
|
|
pp.created_at >= '#{start_date}' AND
|
|
pp.created_at <= '#{end_date}' AND
|
|
pp.playable_type = 'JamRuby::JamTrack' /* VRFS-2916 jam_tracks.id is varchar: ADD */
|
|
GROUP BY player_id
|
|
) played
|
|
WHERE #{where}
|
|
SQL
|
|
end
|
|
|
|
def _subquery(assoc_key, num_user)
|
|
assoc = User.reflections[assoc_key]
|
|
return 0 unless assoc
|
|
start_date = all_time ? self.group_start : self.monthly_start
|
|
end_date = all_time ? self.group_end : self.monthly_end
|
|
sql =<<SQL
|
|
SELECT #{assoc.foreign_key} FROM #{assoc.class_name.constantize.table_name} tt
|
|
WHERE
|
|
tt.created_at >= '#{start_date}' AND
|
|
tt.created_at <= '#{end_date}'
|
|
SQL
|
|
if block_given?
|
|
yield_sql = yield(sql)
|
|
sql = yield_sql unless yield_sql.blank?
|
|
end
|
|
self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
end
|
|
|
|
def _monthly!
|
|
unless 0 < num_user = self.class.cohort_users(self).count
|
|
self.update_attribute(:data_set, {})
|
|
return
|
|
end
|
|
|
|
self.data_set['registered_users'] = num_user
|
|
num_user = num_user.to_f
|
|
|
|
qq = self.class.cohort_users(self)
|
|
.where(first_downloaded_client_at: self.monthly_start..self.monthly_end)
|
|
_put_data_set(:first_downloaded_client_at, qq.count, num_user)
|
|
|
|
qq = self.class.cohort_users(self)
|
|
.where(first_certified_gear_at: self.monthly_start..self.monthly_end)
|
|
_put_data_set(:first_certified_gear_at, qq.count, num_user)
|
|
|
|
count = _subquery(assoc_key = :invited_users, num_user)
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :recorded_tracks, num_user)
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :jam_track_rights, num_user) do |subsql|
|
|
subsql += " AND tt.is_test_purchase = 'f' AND tt.redeemed = 'f' "
|
|
end
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :jam_track_rights, num_user) do |subsql|
|
|
subsql += " AND tt.is_test_purchase = 'f' AND tt.redeemed = 't' "
|
|
end
|
|
_put_data_set(:jam_track_rights_redeemed, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :friendships, num_user)
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
sql = _played_online_subquery(' = 1 ')
|
|
count = self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
_put_data_set(:music_sessions_user_history_1, count, num_user)
|
|
|
|
sql = _played_online_subquery(2..5)
|
|
count = self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
_put_data_set(:music_sessions_user_history_2_5, count, num_user)
|
|
|
|
sql = _played_online_subquery(' >= 6')
|
|
count = self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
_put_data_set(:music_sessions_user_history_6_, count, num_user)
|
|
|
|
sql = _played_jamtrack_subquery(' = 1 ')
|
|
count = self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
_put_data_set(:jam_tracks_played_1, count, num_user)
|
|
|
|
sql = _played_jamtrack_subquery(2..5)
|
|
count = self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
_put_data_set(:jam_tracks_played_2_5, count, num_user)
|
|
|
|
sql = _played_jamtrack_subquery(' >= 6')
|
|
count = self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
_put_data_set(:jam_tracks_played_6_, count, num_user)
|
|
|
|
self.save!
|
|
end
|
|
|
|
def _all_time!
|
|
unless 0 < num_user = self.class.cohort_users(self).count
|
|
self.update_attribute(:data_set, {})
|
|
return
|
|
end
|
|
|
|
self.data_set['registered_users'] = num_user
|
|
num_user = num_user.to_f
|
|
|
|
count = self.class.cohort_users(self)
|
|
.where(['first_downloaded_client_at IS NOT NULL'])
|
|
.count
|
|
_put_data_set('first_downloaded_client_at', count, num_user)
|
|
|
|
count = self.class.cohort_users(self)
|
|
.where(['first_certified_gear_at IS NOT NULL'])
|
|
.count
|
|
_put_data_set('first_certified_gear_at', count, num_user)
|
|
|
|
count = _subquery(assoc_key = :invited_users, num_user)
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :recorded_tracks, num_user)
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :friendships, num_user)
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :jam_track_rights, num_user) do |subsql|
|
|
subsql += " AND tt.is_test_purchase = 'f'"
|
|
end
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
count = _subquery(assoc_key = :jam_tracks_played, num_user) do |subsql|
|
|
# VRFS-2916 jam_tracks.id is varchar: REMOVE
|
|
# subsql += " AND tt.jam_track_id IS NOT NULL "
|
|
# VRFS-2916 jam_tracks.id is varchar: ADD
|
|
subsql += " AND tt.playable_type = 'JamRuby::JamTrack' "
|
|
end
|
|
_put_data_set(assoc_key, count, num_user)
|
|
|
|
sql = _played_online_subquery(' >= 1')
|
|
count = self.class.cohort_users(self).where("users.id IN (#{sql})").count
|
|
_put_data_set(:music_sessions_user_history, count, num_user)
|
|
|
|
self.save!
|
|
end
|
|
|
|
def populate!
|
|
self.all_time ? _all_time! : _monthly!
|
|
end
|
|
|
|
def calculate_totals(cohorts)
|
|
self.group_start = self.group_end = TOTAL_COHORT_DATE
|
|
labels = all_time ? Cohort::ALLTIME_LABELS : Cohort::MONTHLY_LABELS
|
|
|
|
self.data_set = labels.inject({}) do |hh, (kk,vv)|
|
|
hh[kk.to_s] = hh["#{kk}%"] = 0
|
|
hh
|
|
end
|
|
|
|
labels = labels.keys.map(&:to_s)
|
|
cohorts.each do |cc|
|
|
labels.each do |key|
|
|
self.data_set[key] += cc.data_set[key].to_i
|
|
end
|
|
end
|
|
|
|
user_total = self.data_set['registered_users'].to_f
|
|
labels.delete('registered_users')
|
|
labels.each do |key|
|
|
xx = (self.data_set[key].to_f / user_total)
|
|
self.data_set["#{key}%"] = 100.0 * xx.round(4)
|
|
end
|
|
|
|
self.save!
|
|
cohorts << self
|
|
end
|
|
|
|
def self.alltime_cohorts!
|
|
cohorts = Cohort.generate_all_time_cohorts.each do |cc|
|
|
cc._all_time!
|
|
end
|
|
Cohort.new(all_time: true).calculate_totals(cohorts)
|
|
end
|
|
|
|
def self.monthly_cohorts!(monthly_start, monthly_end=nil)
|
|
monthly_end ||= monthly_start + 1.month - 1.second
|
|
cohorts = self.generate_monthly_cohorts(monthly_start, monthly_end).compact.each do |cc|
|
|
cc._monthly!
|
|
end
|
|
Cohort.new(all_time: false, monthly_start: monthly_start).calculate_totals(cohorts)
|
|
end
|
|
|
|
def group_start_str
|
|
is_total_cohort? ? 'Total' : self.group_start.strftime('%Y-%m')
|
|
end
|
|
|
|
def group_end_str
|
|
self.group_end.strftime('%Y-%m-%d')
|
|
end
|
|
|
|
def data_val(col, percent=false)
|
|
if percent
|
|
val = self.data_set["#{col}%"]
|
|
val ? "#{'%0.f' % val}%" : ''
|
|
else
|
|
self.data_set[col.to_s]
|
|
end
|
|
end
|
|
|
|
def self.monthly_starts
|
|
self.cohort_group_ranges.collect do |rr|
|
|
rr.first.to_s
|
|
end
|
|
end
|
|
|
|
def is_total_cohort?
|
|
self.group_start == TOTAL_COHORT_DATE
|
|
end
|
|
|
|
end
|
|
|