From 07341e7aa60fe7c7d4f298136af99276820940e7 Mon Sep 17 00:00:00 2001 From: Eugen Rochko Date: Thu, 14 Oct 2021 20:44:59 +0200 Subject: Add graphs and retention metrics to admin dashboard (#16829) --- app/lib/activity_tracker.rb | 70 ++++++++++++++++++---- app/lib/admin/metrics/dimension.rb | 15 +++++ app/lib/admin/metrics/dimension/base_dimension.rb | 31 ++++++++++ .../admin/metrics/dimension/languages_dimension.rb | 23 +++++++ .../admin/metrics/dimension/servers_dimension.rb | 23 +++++++ .../dimension/software_versions_dimension.rb | 69 +++++++++++++++++++++ .../admin/metrics/dimension/sources_dimension.rb | 23 +++++++ .../metrics/dimension/space_usage_dimension.rb | 70 ++++++++++++++++++++++ app/lib/admin/metrics/measure.rb | 15 +++++ .../admin/metrics/measure/active_users_measure.rb | 33 ++++++++++ app/lib/admin/metrics/measure/base_measure.rb | 46 ++++++++++++++ .../admin/metrics/measure/interactions_measure.rb | 33 ++++++++++ app/lib/admin/metrics/measure/new_users_measure.rb | 35 +++++++++++ .../metrics/measure/opened_reports_measure.rb | 35 +++++++++++ .../metrics/measure/resolved_reports_measure.rb | 36 +++++++++++ app/lib/admin/metrics/retention.rb | 67 +++++++++++++++++++++ 16 files changed, 611 insertions(+), 13 deletions(-) create mode 100644 app/lib/admin/metrics/dimension.rb create mode 100644 app/lib/admin/metrics/dimension/base_dimension.rb create mode 100644 app/lib/admin/metrics/dimension/languages_dimension.rb create mode 100644 app/lib/admin/metrics/dimension/servers_dimension.rb create mode 100644 app/lib/admin/metrics/dimension/software_versions_dimension.rb create mode 100644 app/lib/admin/metrics/dimension/sources_dimension.rb create mode 100644 app/lib/admin/metrics/dimension/space_usage_dimension.rb create mode 100644 app/lib/admin/metrics/measure.rb create mode 100644 app/lib/admin/metrics/measure/active_users_measure.rb create mode 100644 app/lib/admin/metrics/measure/base_measure.rb create mode 100644 app/lib/admin/metrics/measure/interactions_measure.rb create mode 100644 app/lib/admin/metrics/measure/new_users_measure.rb create mode 100644 app/lib/admin/metrics/measure/opened_reports_measure.rb create mode 100644 app/lib/admin/metrics/measure/resolved_reports_measure.rb create mode 100644 app/lib/admin/metrics/retention.rb (limited to 'app/lib') diff --git a/app/lib/activity_tracker.rb b/app/lib/activity_tracker.rb index 81303b715..6d3401b37 100644 --- a/app/lib/activity_tracker.rb +++ b/app/lib/activity_tracker.rb @@ -1,29 +1,73 @@ # frozen_string_literal: true class ActivityTracker + include Redisable + EXPIRE_AFTER = 6.months.seconds - class << self - include Redisable + def initialize(prefix, type) + @prefix = prefix + @type = type + end - def increment(prefix) - key = [prefix, current_week].join(':') + def add(value = 1, at_time = Time.now.utc) + key = key_at(at_time) - redis.incrby(key, 1) - redis.expire(key, EXPIRE_AFTER) + case @type + when :basic + redis.incrby(key, value) + when :unique + redis.pfadd(key, value) end - def record(prefix, value) - key = [prefix, current_week].join(':') + redis.expire(key, EXPIRE_AFTER) + end - redis.pfadd(key, value) - redis.expire(key, EXPIRE_AFTER) + def get(start_at, end_at = Time.now.utc) + (start_at.to_date...end_at.to_date).map do |date| + key = key_at(date.to_time(:utc)) + + value = begin + case @type + when :basic + redis.get(key).to_i + when :unique + redis.pfcount(key) + end + end + + [date, value] + end + end + + def sum(start_at, end_at = Time.now.utc) + keys = (start_at.to_date...end_at.to_date).flat_map { |date| [key_at(date.to_time(:utc)), legacy_key_at(date)] }.uniq + + case @type + when :basic + redis.mget(*keys).map(&:to_i).sum + when :unique + redis.pfcount(*keys) end + end - private + class << self + def increment(prefix) + new(prefix, :basic).add + end - def current_week - Time.zone.today.cweek + def record(prefix, value) + new(prefix, :unique).add(value) end end + + private + + def key_at(at_time) + "#{@prefix}:#{at_time.beginning_of_day.to_i}" + end + + def legacy_key_at(at_time) + "#{@prefix}:#{at_time.to_date.cweek}" + end end diff --git a/app/lib/admin/metrics/dimension.rb b/app/lib/admin/metrics/dimension.rb new file mode 100644 index 000000000..279539f68 --- /dev/null +++ b/app/lib/admin/metrics/dimension.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension + DIMENSIONS = { + languages: Admin::Metrics::Dimension::LanguagesDimension, + sources: Admin::Metrics::Dimension::SourcesDimension, + servers: Admin::Metrics::Dimension::ServersDimension, + space_usage: Admin::Metrics::Dimension::SpaceUsageDimension, + software_versions: Admin::Metrics::Dimension::SoftwareVersionsDimension, + }.freeze + + def self.retrieve(dimension_keys, start_at, end_at, limit) + Array(dimension_keys).map { |key| DIMENSIONS[key.to_sym]&.new(start_at, end_at, limit) }.compact + end +end diff --git a/app/lib/admin/metrics/dimension/base_dimension.rb b/app/lib/admin/metrics/dimension/base_dimension.rb new file mode 100644 index 000000000..8ed8d7683 --- /dev/null +++ b/app/lib/admin/metrics/dimension/base_dimension.rb @@ -0,0 +1,31 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::BaseDimension + def initialize(start_at, end_at, limit) + @start_at = start_at&.to_datetime + @end_at = end_at&.to_datetime + @limit = limit&.to_i + end + + def key + raise NotImplementedError + end + + def data + raise NotImplementedError + end + + def self.model_name + self.class.name + end + + def read_attribute_for_serialization(key) + send(key) if respond_to?(key) + end + + protected + + def time_period + (@start_at...@end_at) + end +end diff --git a/app/lib/admin/metrics/dimension/languages_dimension.rb b/app/lib/admin/metrics/dimension/languages_dimension.rb new file mode 100644 index 000000000..2d0ac124e --- /dev/null +++ b/app/lib/admin/metrics/dimension/languages_dimension.rb @@ -0,0 +1,23 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::LanguagesDimension < Admin::Metrics::Dimension::BaseDimension + def key + 'languages' + end + + def data + sql = <<-SQL.squish + SELECT locale, count(*) AS value + FROM users + WHERE current_sign_in_at BETWEEN $1 AND $2 + AND locale IS NOT NULL + GROUP BY locale + ORDER BY count(*) DESC + LIMIT $3 + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at], [nil, @limit]]) + + rows.map { |row| { key: row['locale'], human_key: SettingsHelper::HUMAN_LOCALES[row['locale'].to_sym], value: row['value'].to_s } } + end +end diff --git a/app/lib/admin/metrics/dimension/servers_dimension.rb b/app/lib/admin/metrics/dimension/servers_dimension.rb new file mode 100644 index 000000000..3e80b6625 --- /dev/null +++ b/app/lib/admin/metrics/dimension/servers_dimension.rb @@ -0,0 +1,23 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::ServersDimension < Admin::Metrics::Dimension::BaseDimension + def key + 'servers' + end + + def data + sql = <<-SQL.squish + SELECT accounts.domain, count(*) AS value + FROM statuses + INNER JOIN accounts ON accounts.id = statuses.account_id + WHERE statuses.id BETWEEN $1 AND $2 + GROUP BY accounts.domain + ORDER BY count(*) DESC + LIMIT $3 + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, Mastodon::Snowflake.id_at(@start_at)], [nil, Mastodon::Snowflake.id_at(@end_at)], [nil, @limit]]) + + rows.map { |row| { key: row['domain'] || Rails.configuration.x.local_domain, human_key: row['domain'] || Rails.configuration.x.local_domain, value: row['value'].to_s } } + end +end diff --git a/app/lib/admin/metrics/dimension/software_versions_dimension.rb b/app/lib/admin/metrics/dimension/software_versions_dimension.rb new file mode 100644 index 000000000..34917404d --- /dev/null +++ b/app/lib/admin/metrics/dimension/software_versions_dimension.rb @@ -0,0 +1,69 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::SoftwareVersionsDimension < Admin::Metrics::Dimension::BaseDimension + include Redisable + + def key + 'software_versions' + end + + def data + [mastodon_version, ruby_version, postgresql_version, redis_version] + end + + private + + def mastodon_version + value = Mastodon::Version.to_s + + { + key: 'mastodon', + human_key: 'Mastodon', + value: value, + human_value: value, + } + end + + def ruby_version + value = "#{RUBY_VERSION}p#{RUBY_PATCHLEVEL}" + + { + key: 'ruby', + human_key: 'Ruby', + value: value, + human_value: value, + } + end + + def postgresql_version + value = ActiveRecord::Base.connection.execute('SELECT VERSION()').first['version'].match(/\A(?:PostgreSQL |)([^\s]+).*\z/)[1] + + { + key: 'postgresql', + human_key: 'PostgreSQL', + value: value, + human_value: value, + } + end + + def redis_version + value = redis_info['redis_version'] + + { + key: 'redis', + human_key: 'Redis', + value: value, + human_value: value, + } + end + + def redis_info + @redis_info ||= begin + if redis.is_a?(Redis::Namespace) + redis.redis.info + else + redis.info + end + end + end +end diff --git a/app/lib/admin/metrics/dimension/sources_dimension.rb b/app/lib/admin/metrics/dimension/sources_dimension.rb new file mode 100644 index 000000000..a9f061809 --- /dev/null +++ b/app/lib/admin/metrics/dimension/sources_dimension.rb @@ -0,0 +1,23 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::SourcesDimension < Admin::Metrics::Dimension::BaseDimension + def key + 'sources' + end + + def data + sql = <<-SQL.squish + SELECT oauth_applications.name, count(*) AS value + FROM users + LEFT JOIN oauth_applications ON oauth_applications.id = users.created_by_application_id + WHERE users.created_at BETWEEN $1 AND $2 + GROUP BY oauth_applications.name + ORDER BY count(*) DESC + LIMIT $3 + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at], [nil, @limit]]) + + rows.map { |row| { key: row['name'] || 'web', human_key: row['name'] || I18n.t('admin.dashboard.website'), value: row['value'].to_s } } + end +end diff --git a/app/lib/admin/metrics/dimension/space_usage_dimension.rb b/app/lib/admin/metrics/dimension/space_usage_dimension.rb new file mode 100644 index 000000000..aa00a2e18 --- /dev/null +++ b/app/lib/admin/metrics/dimension/space_usage_dimension.rb @@ -0,0 +1,70 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::SpaceUsageDimension < Admin::Metrics::Dimension::BaseDimension + include Redisable + include ActionView::Helpers::NumberHelper + + def key + 'space_usage' + end + + def data + [postgresql_size, redis_size, media_size] + end + + private + + def postgresql_size + value = ActiveRecord::Base.connection.execute('SELECT pg_database_size(current_database())').first['pg_database_size'] + + { + key: 'postgresql', + human_key: 'PostgreSQL', + value: value.to_s, + unit: 'bytes', + human_value: number_to_human_size(value), + } + end + + def redis_size + value = redis_info['used_memory'] + + { + key: 'redis', + human_key: 'Redis', + value: value.to_s, + unit: 'bytes', + human_value: number_to_human_size(value), + } + end + + def media_size + value = [ + MediaAttachment.sum(Arel.sql('COALESCE(file_file_size, 0) + COALESCE(thumbnail_file_size, 0)')), + CustomEmoji.sum(:image_file_size), + PreviewCard.sum(:image_file_size), + Account.sum(Arel.sql('COALESCE(avatar_file_size, 0) + COALESCE(header_file_size, 0)')), + Backup.sum(:dump_file_size), + Import.sum(:data_file_size), + SiteUpload.sum(:file_file_size), + ].sum + + { + key: 'media', + human_key: I18n.t('admin.dashboard.media_storage'), + value: value.to_s, + unit: 'bytes', + human_value: number_to_human_size(value), + } + end + + def redis_info + @redis_info ||= begin + if redis.is_a?(Redis::Namespace) + redis.redis.info + else + redis.info + end + end + end +end diff --git a/app/lib/admin/metrics/measure.rb b/app/lib/admin/metrics/measure.rb new file mode 100644 index 000000000..5cebf0331 --- /dev/null +++ b/app/lib/admin/metrics/measure.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure + MEASURES = { + active_users: Admin::Metrics::Measure::ActiveUsersMeasure, + new_users: Admin::Metrics::Measure::NewUsersMeasure, + interactions: Admin::Metrics::Measure::InteractionsMeasure, + opened_reports: Admin::Metrics::Measure::OpenedReportsMeasure, + resolved_reports: Admin::Metrics::Measure::ResolvedReportsMeasure, + }.freeze + + def self.retrieve(measure_keys, start_at, end_at) + Array(measure_keys).map { |key| MEASURES[key.to_sym]&.new(start_at, end_at) }.compact + end +end diff --git a/app/lib/admin/metrics/measure/active_users_measure.rb b/app/lib/admin/metrics/measure/active_users_measure.rb new file mode 100644 index 000000000..ac022eb9d --- /dev/null +++ b/app/lib/admin/metrics/measure/active_users_measure.rb @@ -0,0 +1,33 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::ActiveUsersMeasure < Admin::Metrics::Measure::BaseMeasure + def key + 'active_users' + end + + def total + activity_tracker.sum(time_period.first, time_period.last) + end + + def previous_total + activity_tracker.sum(previous_time_period.first, previous_time_period.last) + end + + def data + activity_tracker.get(time_period.first, time_period.last).map { |date, value| { date: date.to_time(:utc).iso8601, value: value.to_s } } + end + + protected + + def activity_tracker + @activity_tracker ||= ActivityTracker.new('activity:logins', :unique) + end + + def time_period + (@start_at.to_date...@end_at.to_date) + end + + def previous_time_period + ((@start_at.to_date - length_of_period)...(@end_at.to_date - length_of_period)) + end +end diff --git a/app/lib/admin/metrics/measure/base_measure.rb b/app/lib/admin/metrics/measure/base_measure.rb new file mode 100644 index 000000000..4c336a69e --- /dev/null +++ b/app/lib/admin/metrics/measure/base_measure.rb @@ -0,0 +1,46 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::BaseMeasure + def initialize(start_at, end_at) + @start_at = start_at&.to_datetime + @end_at = end_at&.to_datetime + end + + def key + raise NotImplementedError + end + + def total + raise NotImplementedError + end + + def previous_total + raise NotImplementedError + end + + def data + raise NotImplementedError + end + + def self.model_name + self.class.name + end + + def read_attribute_for_serialization(key) + send(key) if respond_to?(key) + end + + protected + + def time_period + (@start_at...@end_at) + end + + def previous_time_period + ((@start_at - length_of_period)...(@end_at - length_of_period)) + end + + def length_of_period + @length_of_period ||= @end_at - @start_at + end +end diff --git a/app/lib/admin/metrics/measure/interactions_measure.rb b/app/lib/admin/metrics/measure/interactions_measure.rb new file mode 100644 index 000000000..9a4ef6d63 --- /dev/null +++ b/app/lib/admin/metrics/measure/interactions_measure.rb @@ -0,0 +1,33 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::InteractionsMeasure < Admin::Metrics::Measure::BaseMeasure + def key + 'interactions' + end + + def total + activity_tracker.sum(time_period.first, time_period.last) + end + + def previous_total + activity_tracker.sum(previous_time_period.first, previous_time_period.last) + end + + def data + activity_tracker.get(time_period.first, time_period.last).map { |date, value| { date: date.to_time(:utc).iso8601, value: value.to_s } } + end + + protected + + def activity_tracker + @activity_tracker ||= ActivityTracker.new('activity:interactions', :basic) + end + + def time_period + (@start_at.to_date...@end_at.to_date) + end + + def previous_time_period + ((@start_at.to_date - length_of_period)...(@end_at.to_date - length_of_period)) + end +end diff --git a/app/lib/admin/metrics/measure/new_users_measure.rb b/app/lib/admin/metrics/measure/new_users_measure.rb new file mode 100644 index 000000000..b31679ad3 --- /dev/null +++ b/app/lib/admin/metrics/measure/new_users_measure.rb @@ -0,0 +1,35 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::NewUsersMeasure < Admin::Metrics::Measure::BaseMeasure + def key + 'new_users' + end + + def total + User.where(created_at: time_period).count + end + + def previous_total + User.where(created_at: previous_time_period).count + end + + def data + sql = <<-SQL.squish + SELECT axis.*, ( + WITH new_users AS ( + SELECT users.id + FROM users + WHERE date_trunc('day', users.created_at)::date = axis.period + ) + SELECT count(*) FROM new_users + ) AS value + FROM ( + SELECT generate_series(date_trunc('day', $1::timestamp)::date, date_trunc('day', $2::timestamp)::date, interval '1 day') AS period + ) AS axis + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at]]) + + rows.map { |row| { date: row['period'], value: row['value'].to_s } } + end +end diff --git a/app/lib/admin/metrics/measure/opened_reports_measure.rb b/app/lib/admin/metrics/measure/opened_reports_measure.rb new file mode 100644 index 000000000..9acc2c33d --- /dev/null +++ b/app/lib/admin/metrics/measure/opened_reports_measure.rb @@ -0,0 +1,35 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::OpenedReportsMeasure < Admin::Metrics::Measure::BaseMeasure + def key + 'opened_reports' + end + + def total + Report.where(created_at: time_period).count + end + + def previous_total + Report.where(created_at: previous_time_period).count + end + + def data + sql = <<-SQL.squish + SELECT axis.*, ( + WITH new_reports AS ( + SELECT reports.id + FROM reports + WHERE date_trunc('day', reports.created_at)::date = axis.period + ) + SELECT count(*) FROM new_reports + ) AS value + FROM ( + SELECT generate_series(date_trunc('day', $1::timestamp)::date, date_trunc('day', $2::timestamp)::date, interval '1 day') AS period + ) AS axis + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at]]) + + rows.map { |row| { date: row['period'], value: row['value'].to_s } } + end +end diff --git a/app/lib/admin/metrics/measure/resolved_reports_measure.rb b/app/lib/admin/metrics/measure/resolved_reports_measure.rb new file mode 100644 index 000000000..0dcecbbad --- /dev/null +++ b/app/lib/admin/metrics/measure/resolved_reports_measure.rb @@ -0,0 +1,36 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::ResolvedReportsMeasure < Admin::Metrics::Measure::BaseMeasure + def key + 'resolved_reports' + end + + def total + Report.resolved.where(updated_at: time_period).count + end + + def previous_total + Report.resolved.where(updated_at: previous_time_period).count + end + + def data + sql = <<-SQL.squish + SELECT axis.*, ( + WITH resolved_reports AS ( + SELECT reports.id + FROM reports + WHERE action_taken + AND date_trunc('day', reports.updated_at)::date = axis.period + ) + SELECT count(*) FROM resolved_reports + ) AS value + FROM ( + SELECT generate_series(date_trunc('day', $1::timestamp)::date, date_trunc('day', $2::timestamp)::date, interval '1 day') AS period + ) AS axis + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at]]) + + rows.map { |row| { date: row['period'], value: row['value'].to_s } } + end +end diff --git a/app/lib/admin/metrics/retention.rb b/app/lib/admin/metrics/retention.rb new file mode 100644 index 000000000..49ab89129 --- /dev/null +++ b/app/lib/admin/metrics/retention.rb @@ -0,0 +1,67 @@ +# frozen_string_literal: true + +class Admin::Metrics::Retention + class Cohort < ActiveModelSerializers::Model + attributes :period, :frequency, :data + end + + class CohortData < ActiveModelSerializers::Model + attributes :date, :percent, :value + end + + def initialize(start_at, end_at, frequency) + @start_at = start_at&.to_date + @end_at = end_at&.to_date + @frequency = %w(day month).include?(frequency) ? frequency : 'day' + end + + def cohorts + sql = <<-SQL.squish + SELECT axis.*, ( + WITH new_users AS ( + SELECT users.id + FROM users + WHERE date_trunc($3, users.created_at)::date = axis.cohort_period + ), + retained_users AS ( + SELECT users.id + FROM users + INNER JOIN new_users on new_users.id = users.id + WHERE date_trunc($3, users.current_sign_in_at) >= axis.retention_period + ) + SELECT ARRAY[count(*), (count(*) + 1)::float / (SELECT count(*) + 1 FROM new_users)] AS retention_value_and_percent + FROM retained_users + ) + FROM ( + WITH cohort_periods AS ( + SELECT generate_series(date_trunc($3, $1::timestamp)::date, date_trunc($3, $2::timestamp)::date, ('1 ' || $3)::interval) AS cohort_period + ), + retention_periods AS ( + SELECT cohort_period AS retention_period FROM cohort_periods + ) + SELECT * + FROM cohort_periods, retention_periods + WHERE retention_period >= cohort_period + ) as axis + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at], [nil, @frequency]]) + + rows.each_with_object([]) do |row, arr| + current_cohort = arr.last + + if current_cohort.nil? || current_cohort.period != row['cohort_period'] + current_cohort = Cohort.new(period: row['cohort_period'], frequency: @frequency, data: []) + arr << current_cohort + end + + value, percent = row['retention_value_and_percent'].delete('{}').split(',') + + current_cohort.data << CohortData.new( + date: row['retention_period'], + percent: percent.to_f, + value: value.to_s + ) + end + end +end -- cgit