# Client tracker schema

One row per client or contact, past or current, in one file. The weekly BD block, the monthly dormant-client review, and the monthly concentration check all read from these same columns, so keep one tracker, not a copy per purpose.

## Columns

| Column | Values | What goes here |
|---|---|---|
| client_name | free text | The name you would recognize in six months, not a project code. |
| engagement_type | project, retainer | A one-off job is "project." Anything recurring (monthly retainer, ongoing support) is "retainer." |
| ttm_revenue | number, no currency symbol | Trailing 12 month revenue from this client. Update it whenever it changes rather than once a year. Leave it blank rather than guessing: the concentration math skips a blank value and never treats a guess as real data. |
| last_contact_date | YYYY-MM-DD | The date of the last real exchange: a call, a reply, a meeting. Not the date you sent something into silence. |
| status | active, dormant, departed, dormant-closed | See status definitions below. |
| departure_reason | free text, see categories below | Filled in only once status is departed. A plain reason, not a guess dressed up as fact. |
| stakeholder_count | integer | How many people at that account you have a direct relationship with, not just a name copied on a thread. |
| relationship_tier | current client, strong relationship, connector, cold contact | Drives the weekly BD time split (skillMd Phase 2, step 6). |

## Status definitions

- **active**: an engagement is running right now, project or retainer.
- **dormant**: last_contact_date is older than the dormancy threshold (set once in the config line below) and nothing is currently running. scripts/flag_dormant_and_concentration.py sets this status by date, so it is not something to pick by hand week to week.
- **dormant-closed**: the dormancy touch sequence (references/dormancy-cadence.md) ran its full course with no reply, the closing-the-loop message went out, and the cadence stopped for that client. This is a deliberate decision made at the end of the sequence, never something the clock sets on its own.
- **departed**: the engagement ended and nothing is running. Pair with a departure_reason the first time this status is set.

A client moves from active to dormant on the clock, automatically, once the threshold passes. It moves from dormant to dormant-closed when the full touch sequence closes with no reply, and from dormant to departed when the user reports that the relationship has ended; both of those moves happen on a decision, never on the clock by itself. A client can move back to active at any point a new engagement starts, from any prior status.

## Departure reason categories

Pick the closest fit instead of writing a new label each time:

- budget cut (their side lost the budget line, not a comment on the work)
- project ended on schedule (the scoped work finished; nothing open-ended was ever promised)
- went in-house (they hired someone internally to do the work)
- went to a competitor (the work moved to someone else)
- went quiet, no stated reason (they stopped responding and never said why)
- restructure or acquisition (their company changed shape and the relationship did not survive it)
- scope or fit mismatch (the work stopped matching what either side needed)

## Config line

The dormancy threshold and the two concentration ceilings are set once in Phase 1 and read by every later phase, including scripts/flag_dormant_and_concentration.py. Store them as plain comment lines at the very top of the tracker CSV, before the header row, so the whole tracker stays one file with one set of numbers:

```
# dormancy_threshold_days=75
# solo_client_ceiling_pct=22
# top3_ceiling_pct=50
```

Change these numbers only in this one place. A worked example with this config already in place is in examples/tracker-template.csv.

# dormancy_threshold_days=75
# solo_client_ceiling_pct=22
# top3_ceiling_pct=50
client_name,engagement_type,ttm_revenue,last_contact_date,status,departure_reason,stakeholder_count,relationship_tier
Acme Fabrication,retainer,48000,2026-09-29,active,,2,current client
Northgate Wholesale,project,12000,2026-08-15,active,,1,current client
Vantage Point Legal,retainer,6000,2026-04-02,dormant,,1,strong relationship
Boone and Co,project,0,2026-09-10,active,,1,connector
Driftwood Cafe,project,0,2026-08-20,active,,1,cold contact
Halsey Metalworks,project,0,2026-03-01,departed,project ended on schedule,1,strong relationship
Yarrow Property Group,retainer,0,2025-12-05,dormant,,1,strong relationship
Finch and Rowe Import,project,0,2025-11-20,departed,went quiet no stated reason,1,cold contact

# Dormancy definition and touch-sequence timing

## What counts as dormant

A client is dormant when the last real contact (a call, a reply, a meeting, not a message sent into silence) is older than the threshold and nothing is currently running. A common rule of thumb, not a hard rule, puts this between 60 and 90 days; 75 is a reasonable middle default if the user has no number of their own. Ask the user to confirm or override this number once, in Phase 1, then write it into the tracker's config line (references/tracker-schema.md) so every later phase uses the same value. A faster-cycle business (weekly or biweekly client touchpoints) may want 45 to 60 days; a slow-cycle one (quarterly reviews, annual contracts) may want 90 to 120. Let the number fit the business instead of defaulting blind.

## The touch sequence, once a client goes dormant

This is the cadence the monthly review in Phase 3 runs for each dormant client, stage by stage, until the client replies or the sequence closes. Every stage's clock is anchored to last_contact_date, the same column that trips the dormancy flag in the first place, not to any separate or undefined reference point:

1. **First proactive touch: the monthly review that first flags the client dormant.** Because that review runs on a monthly calendar rather than continuously, this first touch typically lands 3 to 4 weeks after the exact day the threshold trips. The touch needs one real, specific detail about that client's business; a bare "checking in" does not count.
2. **Value-add follow-up, every 2 to 4 weeks after that, for as long as there is no response.** Each one carries a new piece of value (an observation, a relevant result, an update that actually applies to them) and skips anything that just repeats the last message.
3. **Settle into a monthly touch for anyone who stays dormant past the follow-up stage.** Lower frequency, same rule: every message still needs something specific, and a templated nudge does not qualify.
4. **Closing the loop, if there is still no response after the full sequence.** One last message that says plainly the outreach is stopping, with the door left open rather than pressure applied. After this message, mark the client dormant-closed and stop the cadence for that client specifically.

## Stopping the cadence

The moment a dormant client replies, at any stage, stop that client's cadence immediately. A message already drafted for that client does not go out once a reply has landed; check the tracker before sending anything queued from a prior week.

## Decision checklist for Phase 1

- [ ] Confirm the dormancy threshold in days with the user (default 75; range 60 to 90).
- [ ] Write the confirmed number into the tracker's config line.
- [ ] Confirm the user understands the four-stage sequence above and when a client exits it (reply, or closing-the-loop message with no response).
- [ ] Confirm who checks for replies before a queued message goes out (this guardrail is non-negotiable, see Phase 3 step 19 of the main SOP).

# Re-engagement message bank

These are starting drafts. Every one needs a real, specific detail about that client or contact filled in before it goes anywhere, and every one needs the user's explicit approval before it leaves the draft state. Nothing here ships as written.

## Weekly BD touches, by relationship tier

**Current client** (keep the relationship active, spot early signs of a wind-down)
> Hey [name], it's been a few weeks since we talked through [specific project detail]. How did [specific outcome or milestone] land? Anything coming up on your side I should know about for next quarter?

**Strong relationship or decision-maker** (people who already take your calls)
> [name], saw [specific news, update, or result relevant to their business] and thought of you. Worth a quick call sometime in the next couple of weeks?

**Connector** (someone who can refer work)
> [name], quick one: I just wrapped [specific piece of work] for a client in [specific industry]. If you run into anyone dealing with [specific problem], happy to be the person you point them to.

**Cold contact** (fresh or first touch)
> Hi [name], I came across [specific, genuine reason for reaching out, e.g. a post, a mutual contact, a specific piece of their work]. I do [specific, concrete description of the work], and wanted to introduce myself rather than stay a stranger.

## Dormant-client cadence touches

**Stage 1, first proactive touch (roughly 3 to 4 weeks after disengagement)**
> Hi [name], it's been a while since we last worked together on [specific project]. I came across [specific detail: an industry update, a change at their company, a result from similar work] and figured it was worth reaching out. How are things going over there?

**Stage 2, value-add follow-up (every 2 to 4 weeks)**
> [name], following up with something that actually applies to you this time: [specific observation, result, or update]. No pitch, just thought it was relevant given [specific detail about their business].

**Stage 3, monthly touch (settled dormancy)**
> Hi [name], still thinking of you every so often. [specific, current detail: something you noticed, read, or worked on that connects to their world]. Would love to hear how things are going when you have a minute.

**Stage 4, closing the loop (end of sequence, no response)**
> Hi [name], I've reached out a few times and haven't heard back, which is completely fine. I'll stop checking in for now, but the door's open any time if something comes up on your end. Hope things are going well.

## What not to send

- A generic "just checking in" with no specific detail attached. It reads as "I only remember you when I need work" and can close the door for good.
- The same message reused across two different clients without changing the specific detail.
- Anything sent before the user has given an explicit yes in that turn.

#!/usr/bin/env python3
"""
flag_dormant_and_concentration.py

Reads the client pipeline tracker CSV and prints two things:
1. Every client past the dormancy threshold with no active engagement.
2. Any client, or any top-3 group, past the revenue-concentration ceiling.

Standard library only: csv, argparse, datetime, pathlib. No install step.

Usage:
    python3 flag_dormant_and_concentration.py path/to/tracker.csv
    python3 flag_dormant_and_concentration.py tracker.csv --as-of 2026-10-08
    python3 flag_dormant_and_concentration.py tracker.csv --dormancy-days 60

The tracker's first lines may set config as comments, read automatically:
    # dormancy_threshold_days=75
    # solo_client_ceiling_pct=22
    # top3_ceiling_pct=50

A --dormancy-days / --solo-ceiling / --top3-ceiling flag on the command
line overrides that value for a single run. Neither this script nor the
tracker ever invents a revenue figure: a client with a blank or
non-numeric ttm_revenue is left out of the concentration math and listed
separately so the user can fill it in instead of guessing.
"""

import argparse
import csv
import sys
from datetime import date, datetime
from pathlib import Path

REQUIRED_COLUMNS = [
    "client_name",
    "engagement_type",
    "ttm_revenue",
    "last_contact_date",
    "status",
    "departure_reason",
    "stakeholder_count",
    "relationship_tier",
]


def parse_config_line(line, config):
    # Lines look like: "# dormancy_threshold_days=75"
    body = line.lstrip("#").strip()
    if "=" not in body:
        return
    key, _, value = body.partition("=")
    key = key.strip()
    value = value.strip()
    if key == "dormancy_threshold_days":
        config[key] = int(value)
    elif key in ("solo_client_ceiling_pct", "top3_ceiling_pct"):
        config[key] = float(value)


def load_tracker(path):
    config = {}
    data_lines = []
    with open(path, newline="", encoding="utf-8") as handle:
        for raw_line in handle:
            stripped = raw_line.strip()
            if not stripped:
                continue
            if stripped.startswith("#"):
                parse_config_line(stripped, config)
                continue
            data_lines.append(raw_line)

    reader = csv.DictReader(data_lines)
    missing = [c for c in REQUIRED_COLUMNS if c not in (reader.fieldnames or [])]
    if missing:
        sys.exit(
            "Tracker is missing required column(s): "
            + ", ".join(missing)
            + ". See references/tracker-schema.md."
        )
    rows = list(reader)
    return config, rows


def parse_date(value):
    value = (value or "").strip()
    if not value:
        return None
    try:
        return datetime.strptime(value, "%Y-%m-%d").date()
    except ValueError:
        return None


def parse_revenue(value):
    value = (value or "").strip()
    if not value:
        return None
    try:
        return float(value)
    except ValueError:
        return None


def find_dormant(rows, as_of, dormancy_days):
    flagged = []
    for row in rows:
        if row.get("status") == "departed":
            continue
        contact_date = parse_date(row.get("last_contact_date"))
        if contact_date is None:
            flagged.append((row, None, "no last_contact_date on file"))
            continue
        days_since = (as_of - contact_date).days
        if days_since >= dormancy_days:
            was_active = row.get("status") == "active"
            note = "newly dormant" if was_active else "still dormant"
            flagged.append((row, days_since, note))
    return flagged


def find_concentration(rows, solo_ceiling_pct, top3_ceiling_pct):
    usable = []
    missing_revenue = []
    for row in rows:
        if row.get("status") == "departed":
            continue
        revenue = parse_revenue(row.get("ttm_revenue"))
        if revenue is None:
            missing_revenue.append(row)
            continue
        if revenue <= 0:
            continue
        usable.append((row, revenue))

    total = sum(revenue for _, revenue in usable)
    solo_flags = []
    top3_flag = None

    if total > 0:
        for row, revenue in usable:
            share_pct = (revenue / total) * 100
            if share_pct >= solo_ceiling_pct:
                solo_flags.append((row, revenue, share_pct))

        ranked = sorted(usable, key=lambda item: item[1], reverse=True)
        top3 = ranked[:3]
        top3_total = sum(revenue for _, revenue in top3)
        top3_share_pct = (top3_total / total) * 100
        if top3_share_pct >= top3_ceiling_pct:
            top3_flag = (top3, top3_share_pct)

    return solo_flags, top3_flag, missing_revenue, total


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("tracker_csv", help="Path to the tracker CSV file")
    parser.add_argument(
        "--as-of",
        help="Date to measure from, YYYY-MM-DD (default: today)",
        default=None,
    )
    parser.add_argument("--dormancy-days", type=int, default=None)
    parser.add_argument("--solo-ceiling", type=float, default=None)
    parser.add_argument("--top3-ceiling", type=float, default=None)
    args = parser.parse_args()

    path = Path(args.tracker_csv)
    if not path.exists():
        sys.exit("No such file: {}".format(path))

    config, rows = load_tracker(path)

    dormancy_days = args.dormancy_days or config.get("dormancy_threshold_days")
    solo_ceiling = args.solo_ceiling or config.get("solo_client_ceiling_pct")
    top3_ceiling = args.top3_ceiling or config.get("top3_ceiling_pct")

    if dormancy_days is None:
        sys.exit(
            "No dormancy threshold set. Add '# dormancy_threshold_days=NN' to "
            "the top of the tracker, or pass --dormancy-days."
        )
    if solo_ceiling is None or top3_ceiling is None:
        sys.exit(
            "No concentration ceilings set. Add '# solo_client_ceiling_pct=NN' "
            "and '# top3_ceiling_pct=NN' to the top of the tracker, or pass "
            "--solo-ceiling and --top3-ceiling."
        )

    as_of = parse_date(args.as_of) if args.as_of else date.today()
    if args.as_of and as_of is None:
        sys.exit("--as-of must be YYYY-MM-DD")

    print("Pipeline check as of {}".format(as_of.isoformat()))
    print("Dormancy threshold: {} days".format(dormancy_days))
    print(
        "Concentration ceilings: {:.0f}% solo / {:.0f}% top three".format(
            solo_ceiling, top3_ceiling
        )
    )
    print()

    dormant = find_dormant(rows, as_of, dormancy_days)
    print("DORMANT CLIENTS ({})".format(len(dormant)))
    if not dormant:
        print("  None past the threshold.")
    for row, days_since, note in dormant:
        name = row.get("client_name", "(unnamed)")
        if days_since is None:
            print("  - {}: {}".format(name, note))
        else:
            print(
                "  - {}: last contact {} days ago ({})".format(
                    name, days_since, note
                )
            )
    print()

    solo_flags, top3_flag, missing_revenue, total = find_concentration(
        rows, solo_ceiling, top3_ceiling
    )
    print("REVENUE CONCENTRATION (trailing 12 months, total {:.0f})".format(total))
    if not solo_flags:
        print("  No single client past the solo ceiling.")
    for row, revenue, share_pct in solo_flags:
        print(
            "  - {}: {:.0f} revenue, {:.1f}% of total (ceiling {:.0f}%)".format(
                row.get("client_name", "(unnamed)"), revenue, share_pct, solo_ceiling
            )
        )
    if top3_flag:
        top3_rows, top3_share_pct = top3_flag
        names = ", ".join(r.get("client_name", "(unnamed)") for r, _ in top3_rows)
        print(
            "  - Top three combined: {:.1f}% of total (ceiling {:.0f}%): {}".format(
                top3_share_pct, top3_ceiling, names
            )
        )
    else:
        print("  Top three combined are under the ceiling.")
    if missing_revenue:
        print()
        print(
            "  Missing ttm_revenue, left out of this calculation (fill in, "
            "do not estimate):"
        )
        for row in missing_revenue:
            print("    - {}".format(row.get("client_name", "(unnamed)")))


if __name__ == "__main__":
    main()

Open the tracker and mark that client departed with a reason: budget cut, project ended on schedule, went in-house, or whatever actually happened. That immediately starts the dormant cadence at its first touch stage for a possible win-back, and recalculates your revenue concentration numbers now that one client's share just changed, so nothing waits for next month's scheduled review.

Set up the weekly BD block now: a fixed number of hours, on specific days, sized to your current workload. It runs every week regardless of workload instead of starting only after a client leaves, so outreach is always in motion rather than restarting from zero the next time someone goes quiet.

Run the monthly concentration check against your own ceiling. A common rule of thumb puts that ceiling around 20 to 25 percent from one client, but it is not a hard rule, so set the number that fits your business. If you're over it, the fix is another client or two at a normal size. It is also worth checking whether that account has more than one stakeholder you actually know, since losing your one contact there is a common way these relationships end.

No. The dormancy cadence has exactly this stage: a specific, value-add touch instead of a generic check-in, built around one real detail about their business, like a relevant update or a result from similar work. The goal is a message that reads as genuine interest rather than a sales pitch in a friendly tone.

Size it to your current workload, for example two hours a week for a solo operator with a full plate. In a delivery-heavy week, scale the hours down rather than to zero, and let the tracker log the reduced number so the habit survives instead of quietly disappearing for a month.

The weekly BD block flags that as a single point of failure and suggests picking one deliberate outbound channel to build alongside it, whether that is cold outreach, content, or a specific partner network, so a slow referral month does not leave the pipeline empty.