Analyse de la durée des étapes d'opportunité Salesforce

Salesforce track les transitions d’étapes dans la table OpportunityFieldHistory. Avec le tracking d’historique de champ activé sur StageName, cette table fournit un enregistrement chronologique de quand chaque opportunité a changé d’étape. Calculer le temps par étape à partir de ces données requiert un pattern de modélisation spécifique utilisant des fonctions de fenêtre.

La source : OpportunityFieldHistory

Salesforce track les changements au niveau du champ quand le tracking d’historique de champ est activé. Pour Opportunity, vous devez vous assurer que StageName a le tracking d’historique de champ activé (c’est un paramètre d’admin Salesforce, pas un problème d’entrepôt — mais vérifiez-le avant de construire le modèle, ou vous aurez une table vide).

Quand activé, OpportunityFieldHistory stocke une ligne par changement de champ :

OpportunityId — quelle opportunité a changé
Field (ou field_name) — quel champ a changé (on veut StageName)
OldValue — la valeur précédente
NewValue — la nouvelle valeur
CreatedDate — quand le changement s’est produit

Cela vous donne la matière première : une séquence chronologique de transitions d’étapes pour chaque opportunité.

Le modèle de durée par étape

Le pattern utilise la fonction de fenêtre LEAD pour associer chaque entrée d’étape avec l’entrée d’étape suivante, puis calcule la différence :

-- int__opportunity_stage_duration.sql
WITH

stage_changes AS (
    SELECT
        opportunity_id  AS opportunity__id,
        new_value       AS opportunity__stage,
        created_date    AS stage__entered_at,
        LEAD(created_date) OVER (
            PARTITION BY opportunity_id
            ORDER BY created_date
        ) AS stage__exited_at
    FROM {{ ref('base__salesforce__opportunity_field_history') }}
    WHERE field_name = 'StageName'
)

SELECT
    opportunity__id,
    opportunity__stage,
    stage__entered_at,
    stage__exited_at,
    DATE_DIFF(
        COALESCE(stage__exited_at, CURRENT_DATE()),
        stage__entered_at,
        DAY
    ) AS stage__days
FROM stage_changes

La fonction LEAD regarde en avant vers la ligne suivante au sein de la même opportunité (partitionnée par opportunity_id, ordonnée par created_date). Pour l’étape courante (la dernière dans la séquence), LEAD retourne NULL — le COALESCE avec CURRENT_DATE() gère cela en calculant combien de jours le deal est resté dans son étape actuelle.

La sortie est une ligne par combinaison opportunité-étape, avec le nombre de jours passés dans cette étape.

Pourquoi cela diffère de la durée basée sur les snapshots

Les snapshots dbt peuvent également tracker les changements d’étapes, mais avec une différence importante : les snapshots capturent l’état au moment de l’exécution (typiquement quotidiennement). Si une opportunité passe de « Discovery » à « Proposal » puis à « Negotiation » dans la même journée, un snapshot quotidien ne capture que l’état final. Vous verriez le deal passer de « Discovery » à « Negotiation » sans enregistrement de l’étape « Proposal ».

OpportunityFieldHistory capture chaque changement individuel au moment où il se produit, quel que soit le moment où votre snapshot tourne. Cela vous donne un timing précis par étape même pour les deals qui progressent rapidement. Le compromis est que OpportunityFieldHistory n’existe que si le tracking d’historique de champ est activé, et Salesforce limite chaque objet au tracking de l’historique sur 20 champs.

Utilisez OpportunityFieldHistory quand vous avez besoin d’un timing précis par étape. Utilisez les snapshots quand vous avez besoin d’un état à un moment donné pour des champs qui ne sont pas trackés dans l’historique de champ.

Patterns d’analyse en aval

Benchmarks par étape

Joignez le modèle de durée par étape à votre mart d’opportunités pour une analyse par étape et segment :

SELECT
    opportunity__stage,
    account__industry,
    AVG(stage__days) AS avg_days_in_stage,
    PERCENTILE_CONT(stage__days, 0.5) OVER (
        PARTITION BY opportunity__stage, account__industry
    ) AS median_days_in_stage
FROM {{ ref('int__opportunity_stage_duration') }}
INNER JOIN {{ ref('mrt__sales__opportunity_enhanced') }}
    USING (opportunity__id)
GROUP BY 1, 2

Cela produit des moyennes par étape et segment — par exemple, les jours moyens dans une étape donnée par secteur d’activité.

Détection des goulots d’étranglement

Signalez les deals significativement au-dessus de la moyenne pour leur étape actuelle :

WITH

stage_benchmarks AS (
    SELECT
        opportunity__stage,
        AVG(stage__days) AS avg_days,
        STDDEV(stage__days) AS stddev_days
    FROM {{ ref('int__opportunity_stage_duration') }}
    WHERE stage__exited_at IS NOT NULL  -- uniquement les étapes terminées
    GROUP BY 1
)

SELECT
    sd.opportunity__id,
    sd.opportunity__stage,
    sd.stage__days,
    sb.avg_days,
    CASE
        WHEN sd.stage__days > sb.avg_days + 2 * sb.stddev_days THEN 'stalled'
        WHEN sd.stage__days > sb.avg_days + sb.stddev_days THEN 'at_risk'
        ELSE 'on_track'
    END AS stage__health
FROM {{ ref('int__opportunity_stage_duration') }} AS sd
INNER JOIN stage_benchmarks AS sb
    ON sd.opportunity__stage = sb.opportunity__stage
WHERE sd.stage__exited_at IS NULL  -- étape actuelle uniquement

Cette sortie peut alimenter une alerte Slack ou un dashboard CRM signalant les deals par état de santé de l’étape.

Taux de remportés : la métrique complémentaire

L’analyse de la durée par étape se couple avec le taux de remportés pour une vue complète de la performance commerciale.

SAFE_DIVIDE de BigQuery gère proprement le cas limite de la division par zéro :

SAFE_DIVIDE(
    COUNTIF(opportunity__is_won),
    COUNTIF(opportunity__is_closed)
) AS owner__win_rate

Partitionnez par opportunity__owner_id pour les taux de remportés par commercial, ou par account__industry pour l’analyse par segment. Combinez avec AVG(opportunity__amount) et le modèle de durée par étape pour une vue unifiée de la vélocité, du taux de remportés et de la taille des deals.

Considérations sur les types d’enregistrement

Si votre org a plusieurs types d’enregistrement d’opportunités (Nouvelle affaire, Renouvellement, Upsell), l’analyse de la durée par étape doit être segmentée par type d’enregistrement. Les cycles de renouvellement ont des séquences d’étapes et des benchmarks de timing complètement différents des deals de nouvelle affaire. Les mélanger dans une seule moyenne produit un nombre qui ne décrit précisément aucun des deux pipelines.

Filtrez votre modèle de durée par étape par type d’enregistrement, ou incluez record_type_id comme dimension pour que l’analyse en aval puisse segmenter par ce critère.

Notes pratiques

Rétention de l’historique de champ. Salesforce conserve les données d’historique de champ pendant 18 à 24 mois par défaut (varie selon l’édition). Si vous avez besoin d’un historique plus long, extrayez et stockez-le en entrepôt avant que Salesforce ne le purge. Une fois dans BigQuery, vous le gardez indéfiniment.

Plusieurs changements le même jour. OpportunityFieldHistory.CreatedDate inclut l’heure, donc deux changements d’étape le même jour sont distinguables. Utilisez TIMESTAMP_DIFF à la place de DATE_DIFF si la précision infraquotidienne compte pour votre analyse.

Étapes sautées. Les deals ne suivent pas toujours un chemin linéaire par étapes. Un deal peut passer directement de « Prospecting » à « Negotiation », en sautant « Qualification » et « Proposal ». Le modèle gère cela correctement — il enregistre les transitions réelles, pas le chemin attendu. Mais vos benchmarks devraient en tenir compte lors du calcul des moyennes : un deal qui a sauté trois étapes a moins de lignes de durée par étape, pas des entrées à zéro jour pour les étapes sautées.