Reopened Defects by Month shows a percentage of re-opened defects in comparison to all created defects in that month.
How metric helps
Reopened Defects by Month helps to reveal a lack of resources to maintain a required deliverable quality, e.g. insufficient unit testing. The higher this value - the worse a quality of defect fixes is.
The metric is often a leading indicator (as opposed to a lagging indicator). This means it can be used to foresee problems before they become overwhelming and undermine confidence in both the product and project team.
Metric:
shows a percentage of re-opened defects in comparison to all created defects in the same period
shows how many defects have been re-opened at least once
shows how many defects were not accepted by Testers and thus returned back to work to Developers
shows quality of development work, i.e. first time rightÂ
shows whether engineering quality practices (EngX) are in place (e.g. quality gates in CI/CD, unit testing)
How metric works
Chart overview
Chart displays a percentage of reopened defects (Y axis) over months (Y axis).
Legend within a chart shows the Reopened Defects for the last month as well as a difference with the previous month.  Â
By hover over a column a hint appears showing a percentage of reopened defects in a particular month.          Â
By click on a column a pop-up appears with the following information from the defect tracking system:
Issues with build creation, i.e. fixes are not included into the latest version
Not reproducible issues on development side (locally), so blind fixes are appliedÂ
Limits in physical devices or/and OS configuration in development team, so no way to check on issue resolution locallyÂ
Not resolved merge conflicts when pushing to the main branch used for testing, i.e. fixes become overwrittenÂ
Bug reports are of poor quality or targeted on the wrong thingsÂ
CalculationÂ
RD = Nrdef/(Ndef + Nrdef)* 100%,
where
Nrdef - number of reopened defects in a considered month. Reopened defect is a defect that leaves the ‘In Progress’/'Done' bucket at least once after it gets into it. Repeatedly reopened defects are always counted as one reopened defect.
Ndef - number of defects in a ‘In Progress’/'Done' status for a considered month. ‘In Progress’/'Done' statuses are the statuses from the ‘In Progress’/'Done' column in Project settings>Task Tracking system>Workflows.
RAG thresholds: no.
Calculation notes
1Â Any backward transition of an issue among buckets is considered as it is reopened:Â
from Done to In Progress
from In Progress to To Do
from Done to To Do
2 If an item is completed and then reopened in the same month it is included only into reopened defects.
PerfQL
WITH
month_series AS (
SELECT date_trunc('month', generate_series) AS month
FROM generate_series(
date_trunc('month', current_date) - interval '5 months',
date_trunc('month', current_date),
'1 month'::interval
)
),
tickets_to_process AS (
SELECT key FROM ticket
WHERE is_defect(id)
),
snapshots_temp AS (
SELECT
lag(is_done(s), 1) over (partition by key order by snapshot_created) as prev_is_done,
lag(is_in_progress(s), 1) over (partition by key order by snapshot_created) as prev_is_in_progress,
lag(status, 1) over (partition by key order by snapshot_created) as prev_status,
is_to_do(s) as is_todo,
is_in_progress(s) as is_in_progress,
is_done(s) as is_done,
snapshot_created,
status,
key
FROM ticket_snapshots((SELECT array_agg(key) FROM tickets_to_process )) s
),
snapshots AS (
SELECT * FROM snapshots_temp
WHERE status != prev_status
),
reopened_defects AS (
SELECT distinct s.key, date_trunc('month', snapshot_created) as month
FROM snapshots s
WHERE (prev_is_done = true and is_todo = true)
OR (prev_is_done = true and is_in_progress = true)
OR (prev_is_in_progress = true and is_todo = true)
),
done_defects AS (
SELECT distinct s.key, date_trunc('month', snapshot_created) as month
FROM snapshots s
WHERE is_done = true
AND not exists (
SELECT 1 FROM reopened_defects d
WHERE s.key = d.key and d.month = date_trunc('month', s.snapshot_created)
)
),
done_defects_counted AS (
SELECT month, count(distinct key) as done_defects_count
FROM done_defects
GROUP BY month
ORDER BY month
),
reopened_defects_counted AS (
SELECT month, count(distinct key) as reopened_defects_count
FROM reopened_defects
GROUP BY month
)
SELECT
to_char(ms.month, 'yyyy mon') as month,
CASE
WHEN dc.done_defects_count + rdc.reopened_defects_count = 0 THEN 0
ELSE round(((rdc.reopened_defects_count::float /( dc.done_defects_count + rdc.reopened_defects_count)::float) * 100)::numeric, 0)
END as "reopened defects ratio"
FROM month_series ms
LEFT JOIN done_defects_counted dc ON ms.month = dc.month
LEFT JOIN reopened_defects_counted rdc ON ms.month = rdc.month
ORDER BY ms.month;
--------DRILL DOWN---------
select
'[' || t.key || '](' || t.url || ')' as "issue id",
t.type as "type",
t.priority as "priority",
t.summary as "summary"
from
reopened_defects rd
inner join
ticket t on t.key = rd.key
where
to_char(month, 'yyyy mon') = clicked_x_value
order by
(regexp_matches(t.key,'\d+'))[1]::bigint;
Data Source
Data for the metric can be collected from a task tracking system (Jira, TFS, Rally, etc.).
