diff options
Diffstat (limited to 'app/lib/admin/metrics')
20 files changed, 787 insertions, 0 deletions
diff --git a/app/lib/admin/metrics/dimension.rb b/app/lib/admin/metrics/dimension.rb new file mode 100644 index 000000000..d8392ddfc --- /dev/null +++ b/app/lib/admin/metrics/dimension.rb @@ -0,0 +1,20 @@ +# 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, + tag_servers: Admin::Metrics::Dimension::TagServersDimension, + tag_languages: Admin::Metrics::Dimension::TagLanguagesDimension, + }.freeze + + def self.retrieve(dimension_keys, start_at, end_at, limit, params) + Array(dimension_keys).map do |key| + klass = DIMENSIONS[key.to_sym] + klass&.new(start_at, end_at, limit, klass.with_params? ? params.require(key.to_sym) : nil) + end.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..5872c22cb --- /dev/null +++ b/app/lib/admin/metrics/dimension/base_dimension.rb @@ -0,0 +1,40 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::BaseDimension + def self.with_params? + false + end + + def initialize(start_at, end_at, limit, params) + @start_at = start_at&.to_datetime + @end_at = end_at&.to_datetime + @limit = limit&.to_i + @params = params + 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 + + def params + raise NotImplementedError + 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..a6aaf5d21 --- /dev/null +++ b/app/lib/admin/metrics/dimension/languages_dimension.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::LanguagesDimension < Admin::Metrics::Dimension::BaseDimension + include LanguagesHelper + + 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: human_locale(row['locale']), 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/dimension/tag_languages_dimension.rb b/app/lib/admin/metrics/dimension/tag_languages_dimension.rb new file mode 100644 index 000000000..1cfa07478 --- /dev/null +++ b/app/lib/admin/metrics/dimension/tag_languages_dimension.rb @@ -0,0 +1,36 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::TagLanguagesDimension < Admin::Metrics::Dimension::BaseDimension + include LanguagesHelper + + def self.with_params? + true + end + + def key + 'tag_languages' + end + + def data + sql = <<-SQL.squish + SELECT COALESCE(statuses.language, 'und') AS language, count(*) AS value + FROM statuses + INNER JOIN statuses_tags ON statuses_tags.status_id = statuses.id + WHERE statuses_tags.tag_id = $1 + AND statuses.id BETWEEN $2 AND $3 + GROUP BY COALESCE(statuses.language, 'und') + ORDER BY count(*) DESC + LIMIT $4 + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, params[:id]], [nil, Mastodon::Snowflake.id_at(@start_at, with_random: false)], [nil, Mastodon::Snowflake.id_at(@end_at, with_random: false)], [nil, @limit]]) + + rows.map { |row| { key: row['language'], human_key: human_locale(row['language']), value: row['value'].to_s } } + end + + private + + def params + @params.permit(:id) + end +end diff --git a/app/lib/admin/metrics/dimension/tag_servers_dimension.rb b/app/lib/admin/metrics/dimension/tag_servers_dimension.rb new file mode 100644 index 000000000..12c5980d7 --- /dev/null +++ b/app/lib/admin/metrics/dimension/tag_servers_dimension.rb @@ -0,0 +1,35 @@ +# frozen_string_literal: true + +class Admin::Metrics::Dimension::TagServersDimension < Admin::Metrics::Dimension::BaseDimension + def self.with_params? + true + end + + def key + 'tag_servers' + end + + def data + sql = <<-SQL.squish + SELECT accounts.domain, count(*) AS value + FROM statuses + INNER JOIN accounts ON accounts.id = statuses.account_id + INNER JOIN statuses_tags ON statuses_tags.status_id = statuses.id + WHERE statuses_tags.tag_id = $1 + AND statuses.id BETWEEN $2 AND $3 + GROUP BY accounts.domain + ORDER BY count(*) DESC + LIMIT $4 + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, params[:id]], [nil, Mastodon::Snowflake.id_at(@start_at, with_random: false)], [nil, Mastodon::Snowflake.id_at(@end_at, with_random: false)], [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 + + private + + def params + @params.permit(:id) + end +end diff --git a/app/lib/admin/metrics/measure.rb b/app/lib/admin/metrics/measure.rb new file mode 100644 index 000000000..a839498a1 --- /dev/null +++ b/app/lib/admin/metrics/measure.rb @@ -0,0 +1,21 @@ +# 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, + tag_accounts: Admin::Metrics::Measure::TagAccountsMeasure, + tag_uses: Admin::Metrics::Measure::TagUsesMeasure, + tag_servers: Admin::Metrics::Measure::TagServersMeasure, + }.freeze + + def self.retrieve(measure_keys, start_at, end_at, params) + Array(measure_keys).map do |key| + klass = MEASURES[key.to_sym] + klass&.new(start_at, end_at, klass.with_params? ? params.require(key.to_sym) : nil) + end.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..513189780 --- /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..0107ffd9c --- /dev/null +++ b/app/lib/admin/metrics/measure/base_measure.rb @@ -0,0 +1,55 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::BaseMeasure + def self.with_params? + false + end + + def initialize(start_at, end_at, params) + @start_at = start_at&.to_datetime + @end_at = end_at&.to_datetime + @params = params + 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 + + def params + raise NotImplementedError + 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..b928fdb8f --- /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..00cb24f7e --- /dev/null +++ b/app/lib/admin/metrics/measure/resolved_reports_measure.rb @@ -0,0 +1,35 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::ResolvedReportsMeasure < Admin::Metrics::Measure::BaseMeasure + def key + 'resolved_reports' + end + + def total + Report.resolved.where(action_taken_at: time_period).count + end + + def previous_total + Report.resolved.where(action_taken_at: previous_time_period).count + end + + def data + sql = <<-SQL.squish + SELECT axis.*, ( + WITH resolved_reports AS ( + SELECT reports.id + FROM reports + WHERE date_trunc('day', reports.action_taken_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/measure/tag_accounts_measure.rb b/app/lib/admin/metrics/measure/tag_accounts_measure.rb new file mode 100644 index 000000000..ef773081b --- /dev/null +++ b/app/lib/admin/metrics/measure/tag_accounts_measure.rb @@ -0,0 +1,41 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::TagAccountsMeasure < Admin::Metrics::Measure::BaseMeasure + def self.with_params? + true + end + + def key + 'tag_accounts' + end + + def total + tag.history.aggregate(time_period).accounts + end + + def previous_total + tag.history.aggregate(previous_time_period).accounts + end + + def data + time_period.map { |date| { date: date.to_time(:utc).iso8601, value: tag.history.get(date).accounts.to_s } } + end + + protected + + def tag + @tag ||= Tag.find(params[:id]) + 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 + + def params + @params.permit(:id) + end +end diff --git a/app/lib/admin/metrics/measure/tag_servers_measure.rb b/app/lib/admin/metrics/measure/tag_servers_measure.rb new file mode 100644 index 000000000..cc064f63f --- /dev/null +++ b/app/lib/admin/metrics/measure/tag_servers_measure.rb @@ -0,0 +1,50 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::TagServersMeasure < Admin::Metrics::Measure::BaseMeasure + def self.with_params? + true + end + + def key + 'tag_servers' + end + + def total + tag.statuses.where('statuses.id BETWEEN ? AND ?', Mastodon::Snowflake.id_at(@start_at, with_random: false), Mastodon::Snowflake.id_at(@end_at, with_random: false)).joins(:account).count('distinct accounts.domain') + end + + def previous_total + tag.statuses.where('statuses.id BETWEEN ? AND ?', Mastodon::Snowflake.id_at(@start_at - length_of_period, with_random: false), Mastodon::Snowflake.id_at(@end_at - length_of_period, with_random: false)).joins(:account).count('distinct accounts.domain') + end + + def data + sql = <<-SQL.squish + SELECT axis.*, ( + SELECT count(distinct accounts.domain) AS value + FROM statuses + INNER JOIN statuses_tags ON statuses.id = statuses_tags.status_id + INNER JOIN accounts ON statuses.account_id = accounts.id + WHERE statuses_tags.tag_id = $1 + AND statuses.id BETWEEN $2 AND $3 + AND date_trunc('day', statuses.created_at)::date = axis.day + ) + FROM ( + SELECT generate_series(date_trunc('day', $4::timestamp)::date, date_trunc('day', $5::timestamp)::date, ('1 day')::interval) AS day + ) as axis + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, params[:id].to_i], [nil, Mastodon::Snowflake.id_at(@start_at, with_random: false)], [nil, Mastodon::Snowflake.id_at(@end_at, with_random: false)], [nil, @start_at], [nil, @end_at]]) + + rows.map { |row| { date: row['day'], value: row['value'].to_s } } + end + + protected + + def tag + @tag ||= Tag.find(params[:id]) + end + + def params + @params.permit(:id) + end +end diff --git a/app/lib/admin/metrics/measure/tag_uses_measure.rb b/app/lib/admin/metrics/measure/tag_uses_measure.rb new file mode 100644 index 000000000..b7667bc6c --- /dev/null +++ b/app/lib/admin/metrics/measure/tag_uses_measure.rb @@ -0,0 +1,41 @@ +# frozen_string_literal: true + +class Admin::Metrics::Measure::TagUsesMeasure < Admin::Metrics::Measure::BaseMeasure + def self.with_params? + true + end + + def key + 'tag_uses' + end + + def total + tag.history.aggregate(time_period).uses + end + + def previous_total + tag.history.aggregate(previous_time_period).uses + end + + def data + time_period.map { |date| { date: date.to_time(:utc).iso8601, value: tag.history.get(date).uses.to_s } } + end + + protected + + def tag + @tag ||= Tag.find(params[:id]) + 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 + + def params + @params.permit(:id) + end +end diff --git a/app/lib/admin/metrics/retention.rb b/app/lib/admin/metrics/retention.rb new file mode 100644 index 000000000..0179a6e28 --- /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, :rate, :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(*))::float / (SELECT GREATEST(count(*), 1) FROM new_users)] AS retention_value_and_rate + 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, rate = row['retention_value_and_rate'].delete('{}').split(',') + + current_cohort.data << CohortData.new( + date: row['retention_period'], + rate: rate.to_f, + value: value.to_s + ) + end + end +end |