Admiral Stats 1周年のユーザデータ解析

active_user.rb

# 一時的にログ出力を無効にする
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

exporter.rb

# 一時的にログ出力を無効にする
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

import.rb

# 一時的にログ出力を無効にする
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

mau.rb

# 一時的にログ出力を無効にする
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

persistance.rb

# 一時的にログ出力を無効にする
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