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.

5 files changed, 68 insertions, 1 deletions

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')