Time to first response for a support group

data & reporting
1

In the course of working for @quimgil on improving his use of Discourse groups as a support system, we’ve developed some queries (more to follow). Let us know if you can spot any issues or improvements :slight_smile:

Time to first response for a support group

  • Average number of days for a set period: (2019-04-04 to 2019-04-06 in example)
  • Only counts business days (i.e. excluding Saturday and Sunday)
  • Does not count selected public holidays (2019-04-11 in example)
  • Group name in example is ‘support’
SELECT AVG(t.days)::float AS "Average business days to first response"
FROM (
  SELECT t.id, t.title, t.created_at, MIN(p.created_at) as "first_reply_created_at", (
    SELECT count(*) FILTER (
      WHERE d not in ('2019-04-11')
      AND extract('ISODOW' FROM d) < 6
    )
    FROM generate_series(t.created_at::timestamp::date
                        , MIN(p.created_at)::timestamp::date
                        , interval '1 day') as s(d)
  ) as "days"
  FROM topics t
  INNER JOIN posts p ON p.topic_id = t.id AND (
    CASE WHEN p.post_number = 1
    THEN p.via_email IS TRUE 
    ELSE true 
    END
  )
  WHERE t.archetype = 'private_message'
  AND t.id IN (
    SELECT topic_id FROM topic_allowed_groups
    WHERE group_id IN (SELECT id FROM groups WHERE name ilike 'support')
  )
  AND t.deleted_at IS NULL
  AND t.created_at::timestamp::date >= '2019-04-04'
  AND t.created_at::timestamp::date <= '2019-04-06'
  AND p.deleted_at IS NULL
  AND p.post_number > 1
  GROUP BY t.id
) t

To test the query is working properly, you can change the SELECT from an average to

t.days as "business days", t.title, t.created_at, t.first_reply_created_at

For example, on my Sandbox when I do this, I get

Screenshot%20at%20Apr%2012%2011-35-11
Screenshot%20at%20Apr%2012%2011-35-111431×604 170 KB

Notes:

  • Do you want to count replies on the same day as 0 or 1? (currently it’s 1)
  • How do you want to handle messages with no replies? (i.e. should they be excluded, or how many days should they be ascribed?). Currently messages with no replies are excluded from the count.

Number of messages received in a given group.

  • Group name in example is ‘support’
  • Count for a set period: (2019-04-04 to 2019-04-06 in example)
SELECT count(t) as "Number of messages"
FROM topics t
WHERE t.archetype = 'private_message'
AND t.id IN (
  SELECT topic_id FROM topic_allowed_groups
  WHERE group_id IN (SELECT id FROM groups WHERE name ilike 'support')
)
AND (
  SELECT via_email FROM posts
  WHERE topic_id = t.id AND post_number = 1 
)
AND t.deleted_at IS NULL
AND t.created_at::timestamp >= '2019-04-04'::timestamp
AND t.created_at::timestamp <= '2019-04-06'::timestamp
8 Likes