Lesson 5.6 · 55 minDashboard

What you will have

The student has a dashboard as the admin home page at /admin, showing for a chosen period: new leads, conversion between stages, win rate, response time and two charts. Every figure has a written definition, and the student has checked each one against a manual count.

Video

The video for this lesson is not recorded yet.

Prompts used in this lesson

Part 3 — Building the dashboard

Build the dashboard as the admin home page at /admin, replacing the
placeholder.

Purpose: the owner sees the state of the business at a glance, and can
check every figure by hand. A figure nobody can verify is worse than none.

Context: docs/crm-spec.md, the leads and lead_status_history tables.

First, add a "Dashboard definitions" section to docs/crm-spec.md with the
definitions below, word for word in meaning, and build from that section.

Period: a start and end date, inclusive, in the business time zone. Presets
for the last 7, 30 and 90 days including today, plus custom dates; default
30 days; kept in the page address. "Leads in the period" means leads whose
created time falls inside it. Every figure follows those leads.

1. New leads: the count of leads in the period.
2. Reached: a lead reached contacted if its history has a row with to
   status contacted, qualified or won; reached qualified if it has a row to
   qualified or won; reached won if it has a row to won.
   - New to contacted: reached contacted / leads in the period.
   - Contacted to qualified: reached qualified / reached contacted.
   - Qualified to won: reached won / reached qualified.
   Show each as a percentage with both counts, e.g. "62% (8 of 13)".
3. Win rate: among leads in the period whose current status is won or
   lost: won / (won + lost). Show both counts and the number still open.
4. Response time: for leads in the period with created_via = form that
   have at least one history row with from status new: the time from the
   lead's created time to the earliest such row. Show the median (for an
   even count, the mean of the two middle values) in hours and minutes,
   and how many leads it is based on. Clock time, no business-hours logic.
5. Waiting now: leads in the period with created_via = form and status new.
   Links to the lead list with that filter.
6. Chart "Leads over time": leads created per day in the period, zero days
   included.
7. Chart "Leads by stage": leads in the period by current status, five
   bars in pipeline order.

When a denominator is zero, show a dash, never 0% or an error. Under each
figure, a "How is this calculated?" line that reveals its definition. Chart
values are also available as text. Leave room for one more chart, "Leads by
source", which a later lesson adds.

Also add a "Check the numbers" link that opens a table of the leads in the
period, one row per lead: name, created time, created_via, current status,
and each history row in order (from, to, time). I will count by hand.

Constraints: calculations run on the server for the signed-in owner only.
Prefer simple charts; if you want a chart library, check that it is
currently maintained and tell me before installing.

Before you report, verify in a real browser using the browser connection;
when the login page appears, wait while I sign in myself. For the 7-day and
30-day periods, compute each item a second, independent way directly from
the database and report it beside what the page shows. Check that a period
in the future shows dashes without errors. Check phone width. Run the
build. Say what you could not verify.

Do along

1. Pause after Part 2. Read the seven definitions. If you want one changed — for example the default period — change it in the prompt before running.
2. Pause at the prompt in Part 3. Run the prompt.
3. After Part 5, choose a period that contains five to nine leads. Open "Check the numbers".
4. Fill in the worksheet from Materials by hand. A phone calculator is enough.
5. Compare each result with the dashboard. Resolve every difference as in Part 5.
6. Do the "Check your work" steps, then run the commit and tag commands that end the lesson.

Check your work

Use the short period you chose.

1. Count the rows in "Check the numbers". Expected: equals "New leads".
2. Count leads with any history change to contacted, qualified or won. Expected: equals the first count in "New to contacted".
3. Count leads with any change to qualified or won, then those with a change to won. Expected: they match the counts in the other two conversion figures.
4. Count current status won and lost. Expected: won ÷ (won + lost) equals the win rate; the rest equals "still open".
5. For each form lead with a change from new, subtract created time from that change's time; take the middle value. Expected: equals the response time.
6. Count form leads still new. Expected: equals "Waiting now".
7. Add the bars of "Leads by stage", and the bars of "Leads over time". Expected: both equal "New leads".
8. Set a period in the future. Expected: zeros and dashes, no error.

Common problems

• A count is off by one. → A lead near midnight falls on a different day in UTC. → "Period boundaries and daily grouping must use the business time zone from the spec. This lead, created at <time>, is on the wrong day."
• Conversion is above 100% or the counts look impossible. → Leads were counted once per history row, not once per lead. → "A lead with several history rows must count once. Recount per lead."
• Win rate shows 0% for a period with no closed leads. → Division by zero handled as zero. → "Show a dash when won + lost is zero."
• Response time looks far too long. → The last change is used instead of the first. → Quote the definition to Claude and name one lead where it differs.

Homework

About 30 minutes, on your own, after the lesson. No later lesson depends on it.

1. Predict, then move. Pick one lead in your period and write down which figures will change, and to what, if you move it to another stage. Move it, reload the dashboard and compare. Deliverable: your prediction with the result beside it. Done when: every prediction matched, or you can explain the difference from the definitions.
2. A second count. Choose a different period with five to nine leads and count two figures by hand: win rate and median response time. Deliverable: a second worksheet. Done when: both figures match the dashboard.
3. One action per figure. For each of the seven figures write one line: "If this gets worse, I will …". For response time, add the target you want to hold. Mark a figure with no action as a candidate to drop, but remove nothing now: later lessons add to this page. Deliverable: a section "Dashboard actions" in docs/crm-habits.md (your own notes; create it if missing). Done when: there are seven lines.

If a task changed files in the project, commit and push without a tag.

Save your work

git add -A
git commit -m "Lesson 5.6: dashboard with defined, verified figures"
git tag lesson-5.6
git push
git push --tags