We assign some threads to groups, and then the groups (theoretically) assign the threads to individuals for handling.
We’ve created a report to show time (duration) to reassignment
report to show time to reassignment
-- goal: find time from assignment to group
-- to topic closure / unassignment / re-assignment to sub-team or individual
-- For posts that haven't been reassigned, the clock is still running,
-- so use current timestamp in the date diff
--
-- NOTE that this report omits still-pending assignments made before the start of the date range
-- [params]
-- date :start_date = 2023-01-01
WITH
-- find group assignment posts in the date range
group_assignment AS (
SELECT p.topic_id
, p.created_at as assign_date
, pcf.value as group_name
, post_number
FROM posts p
JOIN post_custom_fields pcf on pcf.post_id = p.id
WHERE p.action_code in ('assigned_group', 'reassigned_group')
AND p.created_at >= :start_date AND p.created_at < :start_date::date + INTERVAL '90 day'
ORDER BY topic_id
),
-- find post number of lowest "assignment change" post after group assignment
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- pass forward for use in later join
FROM posts p
JOIN group_assignment ga ON ga.topic_id = p.topic_id AND ga.post_number < p.post_number
WHERE p.action_code in ('reassigned_group', 'reassigned', 'unassigned', 'unassigned_group'
, 'assigned' -- probably this one won't show up, here, but to be safe...
, 'closed.enabled','autoclosed.enabled') -- probably redundant with unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- look up change date for post number
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- pass forward for use in later join
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- do date math between assignment & assignment change
date_math AS (
SELECT ga.group_name
-- for posts still assigned to group, use current timestamp as "reassignment date"
, extract(epoch from (coalesce(acd.change_date, NOW()) - ga.assign_date)/86400) as days
FROM group_assignment ga
LEFT JOIN assignment_change_date acd on acd.topic_id = ga.topic_id
AND ga.post_number=acd.initial_assignment
)
SELECT group_name as "Team"
, count(*) as "Assignments"
, round(avg(days)::numeric,2) as "Avg days to reassignment"
, round(max(days)::numeric,2) as "Max"
, round(min(days)::numeric,5) as "Min"
FROM date_math
GROUP BY group_name
ORDER BY "Avg days to reassignment" desc
This worked well until groups started renaming themselves (for … reasons.)
Because thread assignment records store group names - at the time of the assignment - rather than group IDs, we now have multiple rows in the report for some groups (depending on whether the report timespan includes records from both before and after the rename).
I would like to both consolidate the multiples and future-proof this report (so just hard-coding the synonym sets won’t do it).
Is there a record somewhere of old group names / renamings? Or… something else?
@Falco IIRC the assignment table holds current state. If I want historical data (and I do) I need to look at post_custom_fields to get the assignment value of previous assignment states
SELECT p.topic_id
, p.created_at as assign_date
, pcf.value as group_name
, post_number
FROM posts p
JOIN post_custom_fields pcf on pcf.post_id = p.id
WHERE p.action_code in ('assigned_group', 'reassigned_group')
AND p.created_at >= :start_date AND p.created_at < :start_date::date + INTERVAL '90 day'
ORDER BY topic_id
Ohhh I see now, you need to track the all the historical assignment status changes! This is indeed a hard problem, and we had a customer commission a way to tackle this problem properly a year ago, so I wrote an entire spec for it:
This is almost a two part job.
First we need to store assignment state transitions into a table (new assign, assign changed (assignee or status change), assign destroy).
Then we need to use that information to power a new view that is a dashboard with graphs and tables.
New Settings
enable assign reports
type: bool
default: false
New UI
This will be a new page, either under /admin/dashboard/assignments if that is extensible or under /admin/plugins/assign.
There users can view graphs and export data about assignments, filtering by time period, user/groups and status.
Persisting state transition of assignments
The assignments table do store old assignments already, but we will need to also track Assignment Status, so I’m not sure if we should extend that or just come up with a simpler table dedicated to historical assignment states. I’d be tempted to go the later route so we don’t bloat the table that is actually joined on our hot serializers.
Effort estimation
2.5 to 3 weeks for full work with UI
1 week for just the new table
Unfortunately the customer did de-prioritize this work before we could get to it, but this would solve your needs.
Updated report for anyone interested. The modification is the addition of the group_aliases query (and its use):
-- goal: find time from assignment to group
-- to topic closure / unassignment / re-assignment to sub-team or individual
-- For posts that haven't been reassigned, the clock is still running,
-- so use current timestamp in the date diff
--
-- NOTE that this report omits still-pending assignments made before the start of the date range
-- [params]
-- date :start_date = 2023-01-01
WITH
group_aliases AS (
SELECT group_id,
-- turn columns into rows
UNNEST (array[prev_value, new_value]) AS "alias"
FROM group_histories
WHERE action = 1 AND subject='name'
UNION
SELECT id as group_id
, name
FROM groups
),
-- find group assignment posts in the date range
group_assignment AS (
SELECT p.topic_id
, p.created_at as assign_date
, ga.group_id as group_id
, post_number
FROM posts p
JOIN post_custom_fields pcf on pcf.post_id = p.id
JOIN group_aliases ga on pcf.value = ga.alias
WHERE p.action_code in ('assigned_group', 'reassigned_group')
AND p.created_at >= :start_date AND p.created_at < :start_date::date + INTERVAL '90 day'
ORDER BY topic_id
),
-- find post number of lowest "assignment change" post after group assignment
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- pass forward for use in later join
FROM posts p
JOIN group_assignment ga ON ga.topic_id = p.topic_id AND ga.post_number < p.post_number
WHERE p.action_code in ('reassigned_group', 'reassigned', 'unassigned', 'unassigned_group'
, 'assigned' -- probably this one won't show up, here, but to be safe...
, 'closed.enabled','autoclosed.enabled') -- probably redundant with unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- look up change date for post number
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- pass forward for use in later join
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- do date math between assignment & assignment change
date_math AS (
SELECT ga.group_id as group_id
-- for posts still assigned to group, use current timestamp as "reassignment date"
, extract(epoch from (coalesce(acd.change_date, NOW()) - ga.assign_date)/86400) as days
FROM group_assignment ga
LEFT JOIN assignment_change_date acd on acd.topic_id = ga.topic_id
AND ga.post_number=acd.initial_assignment
)
SELECT date_math.group_id
, count(*) as "Assignments"
, round(avg(days)::numeric,2) as "Avg days to reassignment"
, round(max(days)::numeric,2) as "Max"
, round(min(days)::numeric,5) as "Min"
FROM date_math
GROUP BY group_id
ORDER BY "Avg days to reassignment" desc
