Process notes, still sourced from the workbook configuration.

Purpose

This workbook explains BAU ticket flow from Jira, rebuilds the status path from changelogs, calculates the main delivery durations, and summarizes finished BAU work by month.

How the workbook is built

The script loads BP Jira issues that are not EPIC or Story, checks the local cache, refreshes only tickets whose Jira updated date changed, rebuilds each ticket's status history from changelog status moves, then writes the Excel workbook and formats it in Excel when automation is available.

Source scope

The BAU export queries BP issues with type not in (EPIC, Story) and excludes Jira status 10138 (Cancelled).

Exclusion workbook

Before export, the launcher refreshes suivi-bp-exclusions.xlsx with BAU ticket IDs and keeps the existing Exclude ? values by ID. Any BAU row marked YES is removed from all BAU tabs, aggregates, and graphics.

Main status map

Typical BAU path: Backlog -> Ready for qualif -> In Analysis -> Analysis Done -> Priorise -> Dev en cours -> A Valider or A valider TAM -> Valide -> optional En Recette Externe -> Merge -> Publie.

Status history

Status History shows the status date only so the workbook stays readable. Exact timestamps are still kept internally to order events correctly and calculate durations correctly when several status changes happen on the same day.

BAU Tickets tab

One row per ticket with metadata, current status, and one column per main BAU workflow status only. Legacy or side statuses remain visible in Status History, while the BAU Tickets tab stays focused on the operational path. If a ticket entered the same status several times, the dates are shown in the same cell separated by |.

Partner Monitoring tab

One row per ticket with the calculated BAU durations used for operational follow-up: lead time, pre-qualification, qualification, development, internal testing, and external testing.

Monthly Dashboard tab

Groups finished BAU tickets by finish month and shows monthly averages for each duration. This is the tab used to read trends over time.

Finished Tickets tab

Shows only BAU tickets that reached Publie, with their finish month and all calculated durations. This is the base used for monthly BAU averages.

Lead time rule

Lead time is measured in weekdays from the first Backlog date to the last Publie date. If a ticket is published, reopened, then published again, the full period is kept.

Pre-qualification rule

Pre-qualification is measured in weekdays from the first Backlog date to the first Ready for qualif date.

Qualification rule

Qualification is measured in weekdays from the first Ready for qualif date to the first later Priorise date. Intermediate statuses such as In Analysis or Analysis Done can exist between them.

Development rule

Development is measured by adding each weekday span that starts when the ticket enters Dev en cours and ends at the next A Valider or A valider TAM.

Internal testing rule

Internal testing is measured by adding each weekday span from A Valider or A valider TAM to the next Valide. If the ticket leaves and comes back, the calculation follows the latest matching cycle so interrupted loops are counted correctly.

External testing rule

External testing is measured by adding each weekday span from En Recette Externe to the next Merge. If that phase is skipped, the value stays blank.

Weekday logic

All durations are measured in weekdays only. Saturdays and Sundays are excluded. The workbook shows both weekdays and week equivalents using weekdays divided by 5.

Avg Tech rule

Avg Tech for BAU is intentionally calculated as avg Qualification + avg Development + avg Internal Testing, using finished BAU tickets only.

Tech Incomplete

Finished Tickets includes a Tech Incomplete flag. A row is flagged YES and highlighted in red when one of Qualification, Development, or Internal Testing is missing, so incomplete BAU tickets stay visible without changing the Avg Tech formula.

Graphics

If Excel automation is available, data tabs are formatted as tables, key tabs get colors, and a Graphics sheet is added to visualize monthly averages, lead time evolution, development evolution, and product views.

Purpose

This workbook explains the end-to-end flow of integration epics and their child tickets from Jira, rebuilds the status path from changelogs, calculates the main delivery durations, and summarizes finished integrations by month.

How the workbook is built

The script loads BP epics whose summary starts with Int, or whose summary clearly names both a product and a partner, then recursively loads child issues linked to those epics, checks the local cache, refreshes only issues whose Jira updated date changed, rebuilds status history from changelog status moves, then writes the Excel workbook and formats it in Excel when automation is available.

Source scope

The integrations export starts from BP issues of type EPIC whose summary starts with Int or matches the product plus partner integration naming pattern, then walks down the parent-child hierarchy to collect descendant tickets.

Exclusion workbook

Before export, the launcher refreshes suivi-bp-exclusions.xlsx with integration Epic IDs and keeps the existing Exclude ? values by ID. Any Epic row marked YES removes that epic and all of its descendants from all integration tabs, aggregates, and graphics.

Epic status map

Typical epic path: Backlog -> Projet a faire cadrage -> Projet en analyse specs -> Cadrage termine -> Projet en cours dev -> Projet termine.

Child status map

Typical child path: Backlog -> In Analysis -> Analysis Done -> Priorise -> Dev en cours -> A Valider or A valider TAM -> Valide -> optional En Recette Externe -> Merge -> Publie.

Status history

Status History shows the status date only so the workbook stays readable. Exact timestamps are still kept internally to order events correctly and calculate durations correctly when several status changes happen on the same day.

Integration Epics tab

One row per integration epic with metadata, product, current status, and the main epic workflow statuses only. Older side statuses remain available in Status History, while this tab stays focused on the delivery path.

Epic Children tab

One row per child ticket with metadata, current status, and one column per main child workflow status only. Older side statuses remain available in Status History. If a child entered the same status several times, the dates are shown in the same cell separated by |.

Partner Monitoring tab

One row per integration with the calculated delivery durations used for follow-up: lead time, pre-qualification, cadrage, development, internal testing, and external testing.

Monthly Dashboard tab

Groups finished integrations by finish month and shows monthly averages for each duration. This is the tab used to read trend evolution over time.

Finished Integrations tab

Shows only integrations considered finished, with their finish month and all calculated durations. This is the base used for monthly integration averages.

Lead time rule

Lead time is measured in weekdays from the first Backlog found on the epic to the last Publie found on the Step 3 child path. The finish month is based on that final publication point. If no Step 3 child exists but all children are in Publie status, the latest Publie date across all children is used as the finish date.

Pre-qualification rule

Pre-qualification is measured in weekdays from the latest Backlog date on the epic to the latest Projet a faire cadrage date on the epic.

Cadrage rule

Cadrage is measured in weekdays from the latest Projet a faire cadrage date to the latest Cadrage termine date on the epic.

Development rule

Development is measured by adding each weekday span found on child tickets from Dev en cours to the next A Valider or A valider TAM.

Internal testing rule

Internal testing is measured by adding each weekday span found on child tickets from A Valider or A valider TAM to the next Valide. If a child loops back and forth, each matched cycle is counted.

External testing rule

External testing is measured by adding each weekday span found on child tickets from En Recette Externe to the next Publie. If that phase is skipped, the value stays blank.

Weekday logic

All durations are measured in weekdays only. Saturdays and Sundays are excluded. The workbook shows both weekdays and week equivalents using weekdays divided by 5.

Avg Tech rule

Avg Tech for integrations is calculated from each finished integration as Cadrage + Development + Internal Testing, then the monthly average is taken from those per-integration totals.

Product logic

Product is inferred from the summary using keywords such as MRH, MOTO, AUTO, ENERGIE, EMP, and SANTE so finished integrations can also be read by product.

Graphics

If Excel automation is available, data tabs are formatted as tables, key tabs get colors, and a Graphics sheet is added to visualize monthly averages, lead time evolution, development evolution, and product views.