Contacts, Traffic & the Sales Pipeline

Smith Douglas’s CRM lives in the Contact table — 429,727 rows in the test database. Every person who interacts with the company in any capacity goes here: prospects, buyers, agents, brokers, attorneys, lenders, vendor contacts, even employees.

At a glance

429,727 contacts in the test database. Every person in any role is one row here.
A Contact’s role is contextual — the same row can be a buyer on one Contract and a buyer’s agent on another.
The sales pipeline: Contact → ContactTraffic (visit) → Quote (qte Contract) → Lot Reservation (lr) → Accept (acc) → Ratify (rat).
vwContactRequiredFields enforces minimum fields before a Contact can go on a Contract.

How a sale starts: Traffic

A walk‑in or visit to a community is a Traffic event. The chain looks like:

1Contact created (or matched).

2ContactTraffic records the visit, source, agent, and rank.

3Notes & follow-ups via Journal, BPMActionItem.

4Quote a Plan + Lot — creates a Contract row at status qte.

5Lot Reservation Contract status lr; lot is held.

6Accepted & Ratified see Contracts.

The CRM model

The Contact row is the person. Phone/email/address rows fan out below it. Traffic, journal entries, and action items are tied to the contact for the sales pipeline view.

Full Contact / Traffic / Journal table reference

Table	What it holds
`Contact`	The person. Name, mailing address, ranks, referral source.
`vwContacts`	One‑row‑per‑contact view used by lookups.
`ContactPhone`, `ContactEmail`, `ContactAddress`	Contact methods.
`ContactRank`	Hot/warm/cold ranking inside the pipeline.
`ContactTraffic`	Each visit by a Contact. Drives traffic-source reports.
`TrafficSource`	”How did you hear about us?” (Zillow, drive-by, agent, etc.).
`Journal`	Notes / activities tied to a Contact.
`BPMActionItem`	Action items on a contact (follow up, send email).

A contact’s role depends on what they’re doing: the same row can be a BuyerAgentID on one Contract and a ListingBrokerID on another.

Required fields and validation

vwContactRequiredFields enforces that a buyer Contact carries the minimum required to put them on a Contract — name, phone, email, mailing address.

Where in the apps

App	Where
SMART	`Sales` → Contact, Traffic, Quote
SMART 2.0	`sales/`, `web-reports/`
Trade Portal	`contact-other-trades.aspx` (only trade-related contacts)

Common queries

Contacts with at least one open contract in the last 90 days

SELECT TOP 100 c.ContactSysID, c.LastName, c.FirstName, ct.ContractStatusID
FROM   dbo.Contact c
JOIN   dbo.Contract ct  ON ct.ContactSysID = c.ContactSysID
WHERE  ct.AcceptedDate >= DATEADD(day, -90, GETDATE())
ORDER  BY ct.AcceptedDate DESC;