Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Admiral Stats 1周年のユーザデータ解析
# 一時的にログ出力を無効にする
ActiveRecord::Base.logger = nil
base_time = Time.parse('2016-09-03 00:00:00 +09:00')
366.times do |i|
t = base_time + i.day
# 初回ログインしたユーザ数
num_register = Admiral.where('created_at <= ?', t.end_of_day).count
# 1回以上インポートしたユーザ数
num_import = AdmiralStatus.where('exported_at <= ?', t.end_of_day).select(:admiral_id).distinct.count
# 過去30日以内に、1回以上インポートしたユーザ数
num_import_in_30days = AdmiralStatus.where('exported_at >= ? AND exported_at <= ?', t - 29.days, t.end_of_day).distinct.count(:admiral_id)
# 過去60日以内に、1回以上インポートしたユーザ数
num_import_in_60days = AdmiralStatus.where('exported_at >= ? AND exported_at <= ?', t - 59.days, t.end_of_day).distinct.count(:admiral_id)
puts sprintf("%s,%d,%d,%d,%d", t.strftime('%Y-%m-%d'), num_register, num_import, num_import_in_30days, num_import_in_60days)
end
# 一時的にログ出力を無効にする
ActiveRecord::Base.logger = nil
base_time = Time.parse('2017-03-01 00:00:00 +09:00')
7.times do |i|
t = base_time + i.month
records = ApiRequestLog.where('created_at >= ? AND created_at <= ?', t.beginning_of_month, t.end_of_month).group(:admiral_id, :user_agent).count
uniq_pairs = records.map do |r|
admiral_id = r[0][0]
user_agent = case r[0][1]
when /^AdmiralStatsExporter-Ruby/, /^Faraday/
'Ruby'
when /^AdmiralStatsExporter-PS/
'PowerShell'
when /^AdmiralStatsExporter-Python/
'Python'
when /Mozilla/
'Bookmarklet'
else
'Other'
end
[admiral_id, user_agent]
end.uniq
uniq_pairs.each do |r|
puts sprintf("%s,%d,%s", t.strftime('%Y-%m'), r[0], r[1])
end
end
# 一時的にログ出力を無効にする
ActiveRecord::Base.logger = nil
base_time = Time.parse('2016-09-01 00:00:00 +09:00')
12.times do |i|
t = base_time + i.month
records = AdmiralStatus.where('exported_at >= ? AND exported_at <= ?', t.beginning_of_month, t.end_of_month).group(:admiral_id).count
# 外れ値(開発者の ID = 1 は除いて判定)
max_cnt = records.select{|k, v| k != 1 }.values.max
records.each do |admiral_id, cnt|
puts sprintf("%s,%d,%d,%s", t.strftime('%Y-%m'), admiral_id, cnt, (cnt == max_cnt))
end
end
# 一時的にログ出力を無効にする
ActiveRecord::Base.logger = nil
base_time = Time.parse('2016-09-01 00:00:00 +09:00')
12.times do |i|
t = base_time + i.month
new_ids = Admiral.where('created_at >= ? AND created_at <= ?', t.beginning_of_month, t.end_of_month).select(:id).map{|a| a.id }
counts = AdmiralStatus.where('exported_at >= ? AND exported_at <= ?', t.beginning_of_month, t.end_of_month).group(:admiral_id).count
num_import = counts.size
num_import_new = counts.select{|k, v| new_ids.include?(k) }.size
puts sprintf("%s,%d,%d", t.strftime('%Y-%m'), num_import, num_import_new)
end
# 一時的にログ出力を無効にする
ActiveRecord::Base.logger = nil
base_time = Time.parse('2016-09-01 00:00:00 +09:00')
last_ids1 = []
last_ids2 = []
12.times do |i|
t = base_time + i.month
counts = AdmiralStatus.where('exported_at >= ? AND exported_at <= ?', t.beginning_of_month, t.end_of_month).group(:admiral_id).count
ids1 = counts.keys.uniq
ids2 = counts.select{|k, v| v >= 2 }.keys.uniq
persist_ids1 = ids1.select{|id| last_ids1.include?(id) }
persist_ids2 = ids2.select{|id| last_ids2.include?(id) }
rate1 = (persist_ids1.size.to_f / last_ids1.size).round(3)
rate2 = (persist_ids2.size.to_f / last_ids2.size).round(3)
puts sprintf("%s,%.3f,%.3f", t.strftime('%Y-%m'), rate1, rate2)
last_ids1, last_ids2 = ids1, ids2
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment