CRM Milestone Tracking - Handoff Summary

Module: bista_menmd_customization Requested by: Leadership - permanent milestone dates for CRM reporting Built/tested by: Charlie (local dev) Status: Local testing complete, ready for UAT

1. What This Does

Adds six new datetime fields to the Opportunity (crm.lead) record that permanently capture the first time an opportunity reached each key pipeline stage, regardless of what stage it's currently in:

• Date Qualified
• Date Appointment Set
• Date Pitched
• Date Won
• Date Lost
• Date Disqualified

2. Files Changed

All changes are in bista_menmd_customization, no other module was touched.

3. How the Automation Works

• Stage-based fields (Qualified, Appointment Set, Pitched, Won, Lost, Disqualified) are all set by matching the opportunity's stage_id name against the actual pipeline stages: New, Qualified, Appointment Set, Pitched, Won, Existing Prospect: Reached Out, Existing Customer: Reached Out, Disqualified, Lost.
• Safety net for Lost/Disqualified: if an opportunity is marked lost via the standard "Mark as Lost" wizard (which sets active=False + a lost reason, without necessarily moving stage_id), the milestone still fires - matched against the lost reason name.
• First-entry only: every write checks that the field is currently empty before setting it. Once populated, nothing in the automation touches it again.
• The mapping lives at the top of crm_lead.py in STAGE_MILESTONE_MAP and LOST_REASON_MILESTONE_MAP - if stage names or lost reasons ever change, update those dictionaries.

4. Access Control

• Milestone fields are visible to everyone, but read-only for normal users (including RSMs) - enforced in crm_lead.py's write() override, which strips any milestone field from a write unless it's the automation itself or a user in the "CRM Milestone Fields Editor" group.
• Members of that group get a new CRM > Milestone Corrections menu item, which opens Opportunities with the milestone fields editable, for manual corrections.
• The group is currently standalone - it does not imply or get implied by sales_team.group_sale_manager or any other group. If Sales Managers should get edit rights automatically, that's a one-line addition to crm_milestone_security.xml (add an implied_ids field) - open decision, not yet made.

5. Reporting - Confirmed Working

All six fields are plain stored fields, so they're automatically available in:

• Opportunities list/pivot: "Add Custom Filter" and "Add Custom Group" (including by month)
• Grouping by Salesperson or Sales Team alongside a milestone date
• Standard XLSX export

Side-by-side milestone comparison (e.g. "compare monthly Pitched and Won totals," from the original request) isn't something the stock CRM pivot can do directly, since it only groups by one field at a time. This was solved instead using the Dynamic Dashboard app's SQL Query chart type (Mixed Chart: Bar + Line), with this query:

SELECT to_char(date_trunc('month', COALESCE(date_pitched, date_won)), 'Mon YYYY') AS label,
       COUNT(date_pitched) AS value,
       COUNT(date_won) AS secondary_value
FROM crm_lead
WHERE date_pitched IS NOT NULL OR date_won IS NOT NULL
GROUP BY 1
ORDER BY 1

This chart will live under Dynamic Dashboard > CRM. The same pattern can be extended to compare any other pair of milestones, or generalized to all six, if leadership wants that later - not part of the original request, so it wasn't built beyond this one proof-of-concept chart.

6. Testing Already Completed (Local)

• [x] Stage progression sets each field correctly (New -> Qualified -> Appointment Set -> Pitched -> Won)
• [x] No-overwrite rule: moved an opportunity backward then forward through Pitched: original date_pitched timestamp held
• [x] Read-only enforcement for normal users (fields greyed out on standard form)
• [x] Admin override via Milestone Corrections menu: edited a field, verified it saved
• [x] Dynamic Dashboard side-by-side Pitched vs. Won chart renders correctly

7. Testing Still Needed on Staging/UAT

Re-run the full checklist in a clean environment, since local dev had test data and manual DB edits (the deactivated view) that won't be present on UAT until Bega replicates them:

1. Install/update the module and confirm it installs cleanly on a database that doesn't already have crm installed as a side effect of something else (this is the actual test of the missing-dependency fix from Section 5).
2. ~~Check whether UAT has the same orphaned campaign_touch_ids view~~ - done, confirmed absent on both UAT/Staging and Production. No action needed here; see Section 5.
3. Full milestone walk-through using the original example: New (Jul 1) -> Qualified (Jul 3) -> Appointment Set (Jul 8) -> Pitched (Jul 12) -> Won (Jul 25). Confirm each field sets once, at the correct time.
4. Lost / Disqualified, both via dragging directly into those kanban columns and via the "Mark as Lost" wizard from a different stage, to confirm both paths are caught.
5. Non-admin read-only check, including via list view inline edit if RSMs have list access to Opportunities (not just the form) - confirms the write() guard holds regardless of entry point.
6. Export test - export a filtered/grouped Opportunities view to XLSX, confirm milestone columns are included and accurate.
7. Confirm the implied_ids decision (Section 4) with leadership/Bega before UAT sign-off, since it affects who can make corrections in production.
8. Backfill decision - the original request asked us to confirm whether milestone dates can be backfilled for existing opportunities. Short answer: partially, and only for Qualified/Appointment Set/Pitched, using Odoo's mail.tracking.value history on stage_id (since that field is tracked by default). Won/Lost/Disqualified are not reliably backfillable, since active and lost_reason_id aren't tracked by default - those should be left blank for pre-existing records unless there's another data source (e.g. an export from Salesforce or wherever the prior data lived) to seed them from. A standalone backfill script was drafted earlier in this project but not yet run against real data - confirm with leadership whether partial backfill (three of six fields) is acceptable before running it, since the request specifically said to leave fields blank rather than assign inaccurate dates.