tws-talk-2018-02-23.org

#+BEGIN_SRC emacs-lisp :results output silent :exports none
  (setq
   org-latex-minted-options '(("breaklines" "true"))
   org-latex-listings 'minted
   org-latex-packages-alist '(("" "minted" "parskip" "hypersetup"))
   org-latex-to-pdf-process
   '("pdflatex -shell-escape -interaction nonstopmode -output-directory %o %f"
     "pdflatex -shell-escape -interaction nonstopmode -output-directory %o %f"
     "pdflatex -shell-escape -interaction nonstopmode -output-directory %o %f")
   org-latex-pdf-process org-latex-to-pdf-process)
#+END_SRC
#+OPTIONS: toc:nil title:nil
#+LATEX_HEADER: \usepackage[margin=0.75in]{geometry}
#+LATEX_HEADER: \hypersetup{colorlinks=true, linkcolor=black,  urlcolor=blue,}
#+LATEX_HEADER: \setlength{\parindent}{0em}
#+LATEX_HEADER: \setlength{\parskip}{0.5em}
#+LaTeX_HEADER: \usepackage{minted}
#+LATEX_HEADER: \usemintedstyle{trac}
#+LATEX_HEADER: \pagenumbering{gobble}

* Introduction
  :PROPERTIES:
  :header-args:sql: :session tws :eval no-export :exports results :engine postgresql :database (let ((str (shell-command-to-string "heroku pg:credentials:url -a tws-prod | sed -n 3p | sed -e s/\\\"//g | sed -e 's/^[[:space:]]*//'"))) (replace-regexp-in-string "\n$" "" str))
  :END:

  Ankit and I spoke on 2018-02-01 and 2018-02-23 about the future of TWS. Both
  of us have been hands-off for a while, and only a few cities are still active.
  Here's the rundown on how various parts of the Tea With Strangers Org are performing.

** New User & Host Acquisition
   Total number of users since the beginning of 2018 and the cities where more
   than five people have signed up, and the total number of users in that city
   since TWS' inception:
  #+BEGIN_SRC sql
    SELECT c.name AS "City", count(*) AS "Signups", c.users_count AS "Total Users" FROM users as u
    JOIN cities AS c ON (c.id = u.home_city_id)
    WHERE u.created_at >= date('2018-01-01')
    GROUP BY c.name, c.users_count
    HAVING count(*) > 5
  #+END_SRC

  #+RESULTS:
  | City          | Signups | Total Users |
  |---------------+---------+-------------|
  | Boston        |       7 |        2196 |
  | DC            |      40 |        1461 |
  | London        |      37 |        2427 |
  | New York City |      39 |        4240 |
  | SF / Bay Area |      46 |        7200 |


  New users interested in hosting since the beginning of the year:

  #+BEGIN_SRC sql
    SELECT c.name AS "City", c.users_count AS "Total Users", count(*) AS "New Users Interested in Hosting" FROM users AS u
    JOIN cities AS c ON (c.id = u.home_city_id)
    WHERE (tws_interests ->> 'hosting')::boolean AND u.created_at >= date('2018-01-01')
    GROUP BY c.name, c.users_count
    ORDER BY "Total Users" DESC
    --HAVING count(*) > 1
  #+END_SRC

  #+RESULTS:
  | City                    | Total Users | Users Interested in Hosting |
  |-------------------------+-------------+---------------------------------|
  | SF / Bay Area           |        7200 |                               6 |
  | New York City           |        4240 |                               6 |
  | London                  |        2427 |                               2 |
  | DC                      |        1461 |                               2 |
  | Seattle                 |         502 |                               1 |
  | Austin                  |         181 |                               1 |
  | Mumbai                  |          38 |                               1 |
  | Barcelona               |           2 |                               1 |
  | Columbus                |           2 |                               1 |
  | copenhagen              |           2 |                               1 |
  | Gurgaon                 |           2 |                               1 |
  | Hong Kong               |           2 |                               1 |
  | Jerusalem               |           2 |                               1 |
  | Ukiah                   |           2 |                               1 |
  | Wellington, New Zealand |           2 |                               1 |

  In short, it appears focusing on pre-existing cities is prudent.

** Tea Times In Last Ninety Days

   Next up was examining how active each existing city was; we want to see how
   many tea times occurred where people attended, and how many people were
   waitlisted for those tea times:

   #+BEGIN_SRC sql
     SELECT c.name,
     count(*) AS "Attended Tea Times",
     AVG(a.attended)::int AS "Avg. Attendees",
     AVG(a.waitlisted)::int AS "Avg. Waitlisted Attendees"
     FROM tea_times
     JOIN cities AS c ON (c.id = tea_times.city_id)
     RIGHT JOIN (
      SELECT tea_time_id,
      SUM(case when status in (0,1,3) then 1 else 0 end) as attended,
      SUM(case when status = 4 then 1 else 0 end) AS waitlisted
      FROM attendances
      group by tea_time_id
     ) as a ON (tea_times.id = a.tea_time_id)
     WHERE start_time >= (current_date - 90) AND followup_status != 3
     GROUP BY c.name
     ORDER BY "Attended Tea Times" DESC
     -- SELECT c.name,
     -- CASE
     --   WHEN a.status IN (0,1,3) THEN 'Attended'
     --   WHEN a.status = 4 THEN 'Waitlisted'
     --   ELSE 'Other'
     -- END AS "Attendance Status",
     -- COUNT(*) FROM tea_times
     -- JOIN cities AS c ON (c.id = tea_times.city_id)
     -- JOIN attendances AS a ON (a.tea_time_id = tea_times.id)
     -- WHERE start_time >= (current_date - 90)
     -- GROUP BY ROLLUP(c.name, "Attendance Status")
   #+END_SRC

   #+RESULTS:
   | name          | Attended Tea Times | Avg. Attendees | Avg. Waitlisted Attendees |
   |---------------+--------------------+----------------+---------------------------|
   | London        |                 17 |              3 |                         0 |
   | SF / Bay Area |                  7 |              5 |                         2 |
   | Boston        |                  1 |              2 |                         0 |
   | DC            |                  1 |              6 |                         4 |