Purpose

Sprint Plan Change shows a percentage of issues added or removed during an iteration.

How metric helps

Sprint Plan Change:

helps to analyze reasons for a dramatic scope change
shows how mature sprint planning process is
helps to understand how robust priorities are
shows how efficient the team is in refining sprint backlog ahead to eliminate blockers and depedencies during its execution
shows how the correct sprint goal is being set
shows whether sprint goal corresponds to product goal
shows whether team's sprint capacity is being managed properly with corresponding workload assigned
shows how effectively "ready for development" criteria is followed to have all stories prepared for sprint work ahead (reqs, tech notes, designs, etc)
shows whether additional uncontrolled changes are being added without approval
shows whether the team has worked out its stable velocity
shows whether there is change management process in place
shows how stable sprint scope is

If we imagine ideal planning on a project, the target for the metric here should be 0%. On the other hand, deviations can be justified, for example, if a story is rejected due to not enough details and replaced with another story. So, such deviations are subjects for discussion.

How metric works

Chart overview

Chart shows a sprint plan change in % - Axis Y by sprint - Axis X.

Chart may be viewed in:

Items;
Story Points;
Hours.

On hover over a column a hint appears with the following information:

Sprint name as it is in a tracking system;
Iteration time frame ;
Metric value;
Added or deleted scope value.

Chart legend shows the latest calculated Sprint Plan Change for Added and Removed items in percentage.

By click on a column a pop up appears with the following information got from a task tracking system:

Issue ID
Type
Priority
Summary

What metric means

Parallel development of the same product by several teams at the same time → integration issues or blockers, merge conflicts
Lack of any formal scope or requirements management, there is no change management process
Change management process is in place, but it's unknown for a client or/and team participants
Absence of roadmap, poor vision on product goal, priorities, and milestones → in fact, the backlog cannot be formed, refined, and prepared for development
The team is rather new with fluctuating velocity
No clear statement of scope or no sign off for scope from the client's side
Sprint planning and sprint goal is not aligned/shared with other stakeholders
Not mature communication management process
Direct [unmanaged] contact between the client and team participants
There is no proper pre-planning preparation on the team level in place
Changing priorities during Sprint execution can result in not actual Sprint goal
The sprint goal is not accurate enough, i.e. not aligned with product goal and overall business strategy
SDLC is longer than 2 weeks, applied process framework is not applicable to the environment
Weak demand intake flow, the process when an idea gets into product backlog is not properly defined
SM low engagement or authority

Calculation

Scope added = ScA/ScP*100%,

Scope deleted = -ScR/ScP*100%,

where

ScP – amount of initially planned scope.
ScA – amount of added scope.
ScR – amount of removed scope.

RAG thresholds: Green – metric value > 0% and <9%, amber – metric value >10% and <30%, red – metric value > 31%.

Calculation notes

Sub-items are not included in the calculation because they usually do not represent delivery value at a business level.
Re-estimation of initially planning scope is not included in this metric. If it is an often case for a project, a double-bug false interpretation is possible. In order to avoid it, it is recommended to use Sprint Plan Change metric together with Estimation Accuracy one.

PerfQL

with sprints as (
    select id, name, coalesce(activated_date, start_date) + (sprint_starting_grace_period())::INTERVAL as start_date,
        coalesce(complete_date, finish_date) as finish_date
    from sprint
    where state != 'FUTURE' 
        and start_date is not null 
        and start_date < now()
        and (finish_date is not null or complete_date is not null)
    order by finish_date desc 
    limit 6 
),

scope as ( 
    select *
    from ticket 
    where parent_task is null 
),

sprint_field_update_history as (
    select t.id as workitem_id, 
        coalesce(h.start,t.created) as updated_date, 
        case when h.start is null then t.sprints else string_to_array(h.sprint, ',')::bigint[] end as sprint
    from scope t 
    left join tickethistory h on t.id = h.workitem_id and h.field = 1 
),

start_sprint_scope as ( 
    select distinct s.id as sprint_id, 
        array_agg(workitem_id) as tickets 
    from sprints s 
    inner join sprint_update_history_on_sprint_start h on h.id = s.id and s.id = any(h.sprint) 
    group by sprint_id 
),

finish_sprint_scope as ( 
    select s.id as sprint_id, 
        array_agg(workitem_id) as tickets 
    from sprints s 
    inner join sprint_update_history_on_sprint_finish h on h.id = s.id and s.id = any(h.sprint) 
    group by sprint_id 
),

sprint_stat as ( 
    select s.name, 
        s.start_date, 
        coalesce(sc.tickets, array[]::bigint[]) as start_items, 
        coalesce(fc.tickets, array[]::bigint[]) as finish_items, 
        array_subtract(coalesce(fc.tickets, array[]::bigint[]), coalesce(sc.tickets, array[]::bigint[])) as added_items, 
        array_subtract(coalesce(sc.tickets, array[]::bigint[]), coalesce(fc.tickets, array[]::bigint[])) as removed_items
    from sprints s 
    left join start_sprint_scope as sc on s.id = sc.sprint_id 
    left join finish_sprint_scope as fc on s.id = fc.sprint_id 
)

select name as "Sprint", 
    case when added_items_count = 0 then 0 when start_items_count = 0 then 100.00 else round(added_items_count::numeric / start_items_count * 100, 2) end as "Added items", 
    case when removed_items_count = 0 then 0 when start_items_count = 0 then 100.00 else round(-removed_items_count::numeric / start_items_count * 100, 2) end as "Removed items"
from sprint_count_stat 
order by start_date;

----DRILL-DOWN-----

with sprint_added_items as (
    select s.name, unnest(s.added_items) as ticket_id
    from sprint_stat s
    select 
    case 
        when t.url is not null then '['||t.key||']('||t.url||')'
        else t.key 
    end as "Issue Id", 
    t.type as "Type", 
    t.priority as "Priority", 
    t.summary as "Summary"
from sprint_added_items s
inner join ticket t on s.ticket_id = t.id
where name = clicked_x_value
order by (regexp_matches(t.key,'\d+'))[1]::bigint;

with sprints as (
  select id, name, coalesce(activated_date, start_date) + (sprint_starting_grace_period())::INTERVAL as start_date, 
         coalesce(complete_date, finish_date) as finish_date
  from sprint
  where state != 'FUTURE' and start_date is not null and start_date < now() and 
        (finish_date is not null or complete_date is not null)
  order by finish_date desc limit 6), 

scope as ( select * from ticket where parent_task is null), 

story_points_field_update_history as ( 
  select t.id as workitem_id, coalesce(h.start, t.created) as updated_date,
         case when h.start is null then t.story_points else h.story_points end as story_points
  from scope t
  left join tickethistory h on t.id = h.workitem_id and h.field = 7
),

story_points_update_history_on_sprint_start as (
  select distinct on (h.workitem_id, s.id) h.workitem_id, s.id as sprint_id, h.story_points
  from story_points_field_update_history h, sprints s
  where h.updated_date < s.start_date
  order by h.workitem_id, s.id, h.updated_date desc
),

story_points_update_history_on_sprint_finish as (
  select distinct on (h.workitem_id, s.id) h.workitem_id, s.id as sprint_id, h.story_points
  from story_points_field_update_history h, sprints s
  where h.updated_date < s.finish_date
  order by h.workitem_id, s.id, h.updated_date desc
), 

sprint_sp_stat as (
    select s.name, s.start_date,
           (select coalesce(sum(story_points), 0) from story_points_update_history_on_sprint_start h
            where h.sprint_id = s.id) as start_sp,

           (select coalesce(sum(story_points), 0) from story_points_update_history_on_sprint_finish h
            where h.sprint_id = s.id) as added_sp,

           (select coalesce(sum(story_points), 0) from story_points_update_history_on_sprint_finish h
            where h.sprint_id = s.id) as removed_sp
    from sprints s
)

select name as "Sprint",
       case when added_sp = 0 then 0 when start_sp = 0 then 100.00 else round(added_sp::numeric / start_sp::numeric * 100, 2) end as "Added Story Points",
       case when removed_sp = 0 then 0 when start_sp = 0 then 100.00 else round(-removed_sp::numeric / start_sp::numeric * 100, 2) end as "Removed Story Points"
from sprint_sp_stat
order by start_date;

----DRILL-DOWN-----

Data Source

Data for the metric can be collected from a task tracking system (Jira, TFS, Rally, etc.).

TelescopeAI User Guide

Sprint Plan Change