Add user cohorts table to admin area

This table shows the percentage of users who registered in the last
twelve months, who last signed in during or later than each of those
twelve months, by month.

It is only enabled when the usage ping is enabled, and the page also
shows pretty-printed usage ping data.

The cohorts table is generated in Ruby from some basic SQL queries,
because performing the gap-filling and running sums needed in both MySQL
and Postgres is painful.

12 files changed, 179 insertions, 2 deletions

diff --git a/app/assets/javascripts/dispatcher.js b/app/assets/javascripts/dispatcher.js
index 9d8f965dee0..6c94975d851 100644
--- a/app/assets/javascripts/dispatcher.js
+++ b/app/assets/javascripts/dispatcher.js
@@ -366,6 +366,7 @@ const ShortcutsBlob = require('./shortcuts_blob');
           new Admin();
           switch (path[1]) {
             case 'application_settings':
+            case 'user_cohorts':
               new gl.ApplicationSettings();
               break;
             case 'groups':
diff --git a/app/controllers/admin/application_settings_controller.rb b/app/controllers/admin/application_settings_controller.rb
index 73b03b41594..643993d035e 100644
--- a/app/controllers/admin/application_settings_controller.rb
+++ b/app/controllers/admin/application_settings_controller.rb
@@ -19,7 +19,12 @@ class Admin::ApplicationSettingsController < Admin::ApplicationController
 
   def usage_data
     respond_to do |format|
-      format.html { render html: Gitlab::Highlight.highlight('payload.json', Gitlab::UsageData.to_json) }
+      format.html do
+        usage_data = Gitlab::UsageData.data
+        usage_data_json = params[:pretty] ? JSON.pretty_generate(usage_data) : usage_data.to_json
+
+        render html: Gitlab::Highlight.highlight('payload.json', usage_data_json)
+      end
       format.json { render json: Gitlab::UsageData.to_json }
     end
   end
diff --git a/app/controllers/admin/user_cohorts_controller.rb b/app/controllers/admin/user_cohorts_controller.rb
new file mode 100644
index 00000000000..5dd6eedfb06
--- /dev/null
+++ b/app/controllers/admin/user_cohorts_controller.rb
@@ -0,0 +1,7 @@
+class Admin::UserCohortsController < Admin::ApplicationController
+  def index
+    if ApplicationSetting.current.usage_ping_enabled
+      @cohorts = UserCohortsService.new.execute(12)
+    end
+  end
+end
diff --git a/app/services/user_cohorts_service.rb b/app/services/user_cohorts_service.rb
new file mode 100644
index 00000000000..7f84b6a0634
--- /dev/null
+++ b/app/services/user_cohorts_service.rb
@@ -0,0 +1,49 @@
+class UserCohortsService
+  def initialize
+  end
+
+  def execute(months_included)
+    if Gitlab::Database.postgresql?
+      created_at_month = "CAST(DATE_TRUNC('month', created_at) AS date)"
+      current_sign_in_at_month = "CAST(DATE_TRUNC('month', current_sign_in_at) AS date)"
+    elsif Gitlab::Database.mysql?
+      created_at_month = "STR_TO_DATE(DATE_FORMAT(created_at, '%Y-%m-01'), '%Y-%m-%d')"
+      current_sign_in_at_month = "STR_TO_DATE(DATE_FORMAT(current_sign_in_at, '%Y-%m-01'), '%Y-%m-%d')"
+    end
+
+    counts_by_month =
+      User
+        .where('created_at > ?', months_included.months.ago.end_of_month)
+        .group(created_at_month, current_sign_in_at_month)
+        .reorder("#{created_at_month} ASC", "#{current_sign_in_at_month} DESC")
+        .count
+
+    cohorts = {}
+    months = Array.new(months_included) { |i| i.months.ago.beginning_of_month.to_date }
+
+    months_included.times do
+      month = months.last
+      inactive = counts_by_month[[month, nil]] || 0
+
+      # Calculate a running sum of active users, so users active in later months
+      # count as active in this month, too. Start with the most recent month
+      # first, for calculating the running totals, and then reverse for
+      # displaying in the table.
+      activity_months =
+        months
+          .map { |activity_month| counts_by_month[[month, activity_month]] }
+          .reduce([]) { |result, total| result << result.last.to_i + total.to_i }
+          .reverse
+
+      cohorts[month] = {
+        months: activity_months,
+        total: activity_months.first,
+        inactive: inactive
+      }
+
+      months.pop
+    end
+
+    cohorts
+  end
+end
diff --git a/app/views/admin/application_settings/_form.html.haml b/app/views/admin/application_settings/_form.html.haml
index f4e4bac62d7..13e9faa9642 100644
--- a/app/views/admin/application_settings/_form.html.haml
+++ b/app/views/admin/application_settings/_form.html.haml
@@ -477,7 +477,7 @@
           diagrams in Asciidoc documents using an external PlantUML service.
 
   %fieldset
-    %legend Usage statistics
+    %legend#usage-statistics Usage statistics
     .form-group
       .col-sm-offset-2.col-sm-10
         .checkbox
diff --git a/app/views/admin/dashboard/_head.html.haml b/app/views/admin/dashboard/_head.html.haml
index 7893c1dee97..0c2e5efc052 100644
--- a/app/views/admin/dashboard/_head.html.haml
+++ b/app/views/admin/dashboard/_head.html.haml
@@ -27,3 +27,7 @@
           = link_to admin_runners_path, title: 'Runners' do
             %span
               Runners
+        = nav_link path: 'user_cohorts#index' do
+          = link_to admin_user_cohorts_path, title: 'User cohorts' do
+            %span
+              User cohorts
diff --git a/app/views/admin/user_cohorts/_cohorts_table.html.haml b/app/views/admin/user_cohorts/_cohorts_table.html.haml
new file mode 100644
index 00000000000..a322ea9e5db
--- /dev/null
+++ b/app/views/admin/user_cohorts/_cohorts_table.html.haml
@@ -0,0 +1,37 @@
+.bs-callout.clearfix
+  %p
+    User cohorts are shown for the last twelve months. Only users with
+    activity are counted in the cohort total; inactive users are counted
+    separately.
+
+.table-holder
+  %table.table
+    %thead
+      %tr
+        %th Registration month
+        %th Inactive users
+        %th Cohort total
+        %th Month 0
+        %th Month 1
+        %th Month 2
+        %th Month 3
+        %th Month 4
+        %th Month 5
+        %th Month 6
+        %th Month 7
+        %th Month 8
+        %th Month 9
+        %th Month 10
+        %th Month 11
+    %tbody
+      - @cohorts.each do |registration_month, cohort|
+        %tr
+          %td= registration_month.strftime('%b %Y')
+          %td= number_with_delimiter(cohort[:inactive])
+          %td= number_with_delimiter(cohort[:total])
+          - cohort[:months].each do |running_total|
+            %td
+              - next if cohort[:total].zero?
+              = number_to_percentage(100 * running_total / cohort[:total], precision: 0)
+              %br
+              (#{number_with_delimiter(running_total)})
diff --git a/app/views/admin/user_cohorts/_usage_ping.html.haml b/app/views/admin/user_cohorts/_usage_ping.html.haml
new file mode 100644
index 00000000000..a95f81a7f49
--- /dev/null
+++ b/app/views/admin/user_cohorts/_usage_ping.html.haml
@@ -0,0 +1,10 @@
+%h2 Usage ping
+
+.bs-callout.clearfix
+  %p
+    User cohorts are shown because the usage ping is enabled. The data sent with
+    this is shown below. To disable this, visit
+    = succeed '.' do
+      = link_to 'application settings', admin_application_settings_path(anchor: 'usage-statistics')
+
+%pre.usage-data.js-syntax-highlight.code.highlight{ data: { endpoint: usage_data_admin_application_settings_path(format: :html, pretty: true) } }
diff --git a/app/views/admin/user_cohorts/index.html.haml b/app/views/admin/user_cohorts/index.html.haml
new file mode 100644
index 00000000000..dddcbd834f7
--- /dev/null
+++ b/app/views/admin/user_cohorts/index.html.haml
@@ -0,0 +1,16 @@
+- @no_container = true
+= render "admin/dashboard/head"
+
+%div{ class: container_class }
+  - if @cohorts
+    = render 'cohorts_table'
+    = render 'usage_ping'
+  - else
+    .bs-callout.bs-callout-warning.clearfix
+      %p
+        User cohorts are only shown when the
+        = link_to 'usage ping', help_page_path('user/admin_area/settings/usage_statistics', anchor: 'usage-data')
+        usage ping is enabled. It is currently disabled. To enable it and see
+        user cohorts, visit
+        = succeed '.' do
+          = link_to 'application settings', admin_application_settings_path(anchor: 'usage-statistics')
diff --git a/changelogs/unreleased-ee/user-cohorts.yml b/changelogs/unreleased-ee/user-cohorts.yml
new file mode 100644
index 00000000000..67d64600a4f
--- /dev/null
+++ b/changelogs/unreleased-ee/user-cohorts.yml
@@ -0,0 +1,4 @@
+---
+title: Show user cohorts data when usage ping is enabled
+merge_request:
+author:
diff --git a/config/routes/admin.rb b/config/routes/admin.rb
index 3c1c2ce2582..5b44d449b2b 100644
--- a/config/routes/admin.rb
+++ b/config/routes/admin.rb
@@ -106,6 +106,8 @@ namespace :admin do
     end
   end
 
+  resources :user_cohorts, only: :index
+
   resources :builds, only: :index do
     collection do
       post :cancel_all
diff --git a/spec/services/user_cohorts_service_spec.rb b/spec/services/user_cohorts_service_spec.rb
new file mode 100644
index 00000000000..8d8d0de31cd
--- /dev/null
+++ b/spec/services/user_cohorts_service_spec.rb
@@ -0,0 +1,42 @@
+require 'spec_helper'
+
+describe UserCohortsService do
+  describe '#execute' do
+    def month_start(months_ago)
+      months_ago.months.ago.beginning_of_month.to_date
+    end
+
+    # In the interests of speed and clarity, this example has minimal data.
+    it 'returns a list of user cohorts' do
+      6.times do |months_ago|
+        months_ago_time = (months_ago * 2).months.ago
+
+        create(:user, created_at: months_ago_time, current_sign_in_at: Time.now)
+        create(:user, created_at: months_ago_time, current_sign_in_at: months_ago_time)
+      end
+
+      create(:user) # this user is inactive and belongs to the current month
+
+      expected = {
+        month_start(11) => { months: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(10) => { months: [2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(9) => { months: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(8) => { months: [2, 1, 1, 1, 1, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(7) => { months: [0, 0, 0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(6) => { months: [2, 1, 1, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(5) => { months: [0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(4) => { months: [2, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(3) => { months: [0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(2) => { months: [2, 1, 1], total: 2, inactive: 0 },
+        month_start(1) => { months: [0, 0], total: 0, inactive: 0 },
+        month_start(0) => { months: [2], total: 2, inactive: 1 }
+      }
+
+      result = described_class.new.execute(12)
+
+      expect(result.length).to eq(12)
+      expect(result.keys).to all(be_a(Date))
+      expect(result).to eq(expected)
+    end
+  end
+end