Quick answer: A small installer can start with six linked lists: Customers, Leads, Quotes, Jobs, Follow-ups and Service agreements. Give each record a unique ID, an owner, a clear status and an agreed next action. Keep sensitive system details and passwords in appropriately restricted records, not a broadly shared CRM. Use the sample dates/formulas below as a starter to test on fictional data, then review the real permissions and workflow before customer use.
On this page
Why do installers abandon CRMs?
A CRM is easy to abandon when it adds typing without helping the next action or service call. Diagnose your own bottleneck rather than assume every small installer has the same experience. Two useful problems to solve first are:
- Nobody defined what “follow-up” meant, so the task list filled with reminders that were snoozed forever.
- It knew the customer’s name but not which recorder, firmware and switch were on the wall, so every service call started with “what do you have there?”
A spreadsheet can be a practical starter when the team can maintain it safely and consistently. It is not automatically simpler or safer than a dedicated system. Decide how quotes, handovers and agreement renewals reach a named person, and how errors and access are reviewed. Use the camera schedule and BOM guide alongside the controlled asset record.
What are the six tabs and their columns?
Use one row per record, unique IDs and real date values displayed as yyyy-mm-dd. Agree the workbook locale/time zone, freeze the header row and validate status values. Avoid merged cells in data tables. The column positions below are deliberate: Jobs E is Handover date; Follow-ups D is Due date and F is Status.
| Tab | Columns A onward | Example status values |
|---|---|---|
| Customers | A Customer ID;B Company;C Authorized contact/reference;D Approved contact channel;E Contact preference/permission;F Owner;G Review/retention date;H Status | Active;Dormant;Do not contact |
| Leads | A Lead ID;B Received date;C Customer ID;D Site reference;E Request;F Owner;G Next action;H Due date;I Status;J Outcome | New;Contacted;Survey booked;Quoting;Converted;Lost |
| Quotes | A Quote ID;B Lead ID;C Customer ID;D Sent date;E Valid until;F Restricted internal quote reference;G Owner;H Next action date;I Status;J Outcome | Draft;Sent;Follow-up due;Won;Lost;Expired |
| Jobs | A Job ID;B Quote ID;C Customer ID;D Site reference;E Handover date;F Restricted asset-register reference;G Authorized support/custodian reference;H Status | Scheduled;In progress;Installed;Handed over;Closed |
| Follow-ups | A Job ID;B Customer ID;C Type;D Due date;E Owner;F Status;G Outcome;H Next step | Due;Done;Skipped |
| Service agreements | A Agreement ID;B Customer ID;C Site reference;D Start date;E End date;F Visit/scope reference;G Next visit;H Owner;I Status | Active;Renewal due;Expired;Canceled |
Keep private account pricing and your internal quote documents restricted to authorized staff. Do not hide a sensitive price tab and assume it is confidential from other workbook users. CASE public-display rules include an above-announced-MSRP exception; that is not permission to publish your private account prices. Follow actual channel and customer-contract terms.
Status rules that keep the sheet honest
- Use documented transitions that match your sales process: a converted lead may link to a quote/job or another recorded outcome; do not force a false status to satisfy a formula.
- Mark handover only after the agreed acceptance and records are complete, not simply because one app-account field is filled.
- Record a factual lost/no-decision reason and next permission-appropriate action; do not invent a reason to clear the list.
- Check duplicate IDs and broken customer/job links. A lookup returns a matching row, not proof that the ID is unique or correct.
How do you log every system you install (SKU, firmware, date)?
Jobs F points to a controlled asset/service record with the information the authorized team actually needs. It can help identify a CS-N220 P16H-AI and its installed firmware before a service visit, but does not guarantee remote diagnosis, warranty approval or avoiding a truck roll. Keep detailed network/remote-access information and credentials in an appropriately restricted system, not the general lead list.
Write SKUs in full as printed on the spec sheet, because one letter changes the series and the specs. A typical small-office row:
- Record full actual device SKUs/revisions, serial references where needed, firmware, installed camera quantities and actual port/channel mapping.
- A recorder’s20-channel/16-PoE row does not prove spare power, input/decode/AI or storage capacity just because the job has14 cameras. Reassess the actual design before expansion.
- Record exact installed switch and drive identities, approved configurations, purchase/install references and current service history.
- For each asset, retain the actual written warranty start, term, exceptions and evidence. Do not automatically assign six years to every camera, unlimited to every powered accessory or a term to an unconfirmed project item.
- Reference the authorized account custodian without storing passwords, secret QR codes or recovery secrets in the general CRM.
Warranty calculations belong in the separately controlled asset register. For this example only, E is the confirmed warranty start and F the confirmed positive whole-month term; G calculates an end date with EDATE: =IFERROR(IF(AND(ISNUMBER(E2),E2>0,ISNUMBER(F2),F2>0,MOD(F2,1)=0),EDATE(E2,F2),"CONFIRM TERMS"),"CONFIRM TERMS")
Set a month value only from the applicable written terms, including their start/end convention. This is a date calculation, not proof of eligibility or claim approval. Unlimited, project-pending or other non-month terms need their own explicit record rather than a fabricated date. Jobs E remains the handover date and must not be reused as warranty start by assumption.
How does the 7/30/90-day follow-up schedule work?
When the agreed handover is recorded, create the required Follow-ups rows deliberately or through a separately tested authorized automation. A formula calculates a date; it does not create rows, send emails or obtain contact permission. With Follow-ups A holding a unique Job ID and Jobs E the actual handover date, this exact-match VLOOKUP example in D2 adds seven days: =IF($A2="","",IFNA(LET(d,VLOOKUP($A2,Jobs!$A$2:$E,5,FALSE),IF(AND(ISNUMBER(d),d>0),d+7,"CHECK HANDOVER DATE")),"CHECK JOB ID"))
For separately labelled30/90/365-day rows, change +7 to the chosen day count. Blank IDs remain blank; missing IDs or invalid/blank dates require correction. A365-day reminder is a day offset, not always a calendar-year anniversary; use a tested month-based rule if the contract calls for an anniversary. Format results as dates and first test fictional IDs, missing dates, duplicates and leap-year boundaries.
| Example follow-up | Agree the scope | Record the actual result |
|---|---|---|
| 7 days | Permitted early recording/access/snags check; neutral review invitation if appropriate | Verified result, issue, owner and next action |
| 30 days | Actual recording/retention, user access and changed requirements | Evidence, correction or authorized new quote |
| 90 days | Agreed service/health review and maintenance scope | Completed tests, limits and any service proposal |
| 365 days / agreed renewal date | Contract-specific inspection/renewal and record review | Confirmed outcome; do not assume a signed renewal or universal interval |
Conditional formatting that does the nagging
Apply these custom-format rules to the intended Follow-ups data range, with row2 as the first data row. Red marks overdue numeric dates with Status Due: =AND(ISNUMBER($D2),$D2<TODAY(),$F2="Due")
Amber marks today through seven days ahead, so it does not also mark the overdue rows: =AND(ISNUMBER($D2),$D2>=TODAY(),$D2<=TODAY()+7,$F2="Due")
Use separate rules against Quotes H or Service agreements G when those are the actual next-action fields. Do not color a last-contact date as though it were the next due date. Test blanks, text errors, today and completed rows. Save a filter view for Due items in the agreed window; someone still has to review and act on it.
What is the weekly routine?
Agree a review slot and an owner; twenty minutes is an example planning block, not a performance guarantee. At each review: Use the maintenance checklist to record the agreed service findings.
- Check overdue and upcoming actions, assign an accountable owner and record a realistic next step.
- Review sent/expiring quotes under the actual contact and validity policy; a quote does not become lost just because it is a week old.
- Confirm active leads have a justified next action and contact preference, or an accurate closed outcome.
- Review agreement visits/renewals against the actual scope and dates; do not assume renewal consent.
- Check recent handovers, asset references, formula errors and duplicate IDs. Track useful workload/outcome measures and investigate overdue work rather than declare the process broken whenever a count is nonzero.
Treat customer and security-system records according to their actual sensitivity, purpose, retention and applicable privacy/contact rules. Share only with authorized named accounts, review access and revoke it when no longer needed. Google Sheets hidden/protected tabs are not a confidentiality boundary: workbook users may copy or reveal content. Use separate appropriately restricted files or a suitable role-based system if people must not see certain data. Service follow-up does not automatically authorize promotional contact.
When should you graduate to a dedicated CRM?
Review whether the spreadsheet still fits when the team experiences problems such as:
- Uncontrolled edits, duplicate IDs or lost history that the workflow cannot reliably resolve.
- Permission or data-separation needs the workbook cannot safely meet.
- Actual volume, reporting or field-service requirements that consume excessive review time.
- Authorized automation, audit, quoting or integration requirements better met by a supported system.
Map records, relationships, statuses and permissions to the selected system, then test a controlled export/import and reconciliation before relying on it. CSV does not preserve all formulas, validations, sharing, history or relationships. Keep the prior record under the approved retention/recovery plan until the migration is accepted. Recheck destination permissions; Google’s protection and hiding guidance describes the Sheets baseline.
Build and test the starter workbook
- Create the six tabs under an approved company-managed account, using the column positions above. Set locale/time zone and actual date formats.
- Add the relevant validation lists and owner fields. Keep asset/secret/pricing information in the appropriate restricted records.
- Use the sample formulas only in their stated fields. Test fictional valid, missing, blank and duplicate IDs/dates and the conditional-format boundaries before copying them further.
- Review file-level access. Range protection can restrict edits, but cannot make Jobs editable while all other workbook data is secret from the same user.
- Load only necessary authorized records, check relationships and schedule a realistic review. Mobile access uses the same actual file permissions; do not share one broad Editor login with every technician.
What to tell your customer
We keep the service information and confirmed product terms needed for authorized support, with access appropriate to the data. We will agree the follow-up and service schedule and record actual actions and issues. A workbook helps the handover; it does not guarantee diagnosis or contact you have not authorized.
FAQ
Why six tabs instead of one big sheet?
One sheet forces every row to carry every column, so technicians skip fields and filters stop working. Six tabs let each row describe one thing — a customer, a lead, a quote, a job, a follow-up, an agreement — joined by short IDs, so the weekly review is a filter and the Jobs tab is a clean asset register.
What should the Jobs tab record for each installed system?
Record job/customer/site references, handover date, the controlled asset-register reference and authorized support custodian. In the restricted asset record, keep full actual device identities, the firmware maintenance record, configuration references and applicable written warranty terms. Do not put passwords or secret remote-access details in the general Jobs tab or generate warranty dates solely from category labels.
How are the 7/30/90-day follow-ups created?
Create the required rows when the agreed handover is recorded, then calculate the chosen offsets from Jobs E using the guarded lookup example. The formula does not generate rows or messages. Check errors, duplicate IDs, actual contact permissions and whether a day offset or calendar-anniversary rule is intended.
Can we store CASE pricing in the workbook?
Keep private pricing only in appropriately restricted internal records under actual channel policy. A hidden tab is not confidential from workbook users, and a broadly shared CRM is not automatically the right place. The public-display exception above announced MSRP does not authorize private account prices in a website, social post or public document. Review the security camera installer program for the business-account context.
References
- Anabon: exact product/revision documents
- Anabon Technical Support
- Google Sheets: exact-match VLOOKUP
- Google Sheets: LET function
- Google Sheets: EDATE date calculation
- Google Sheets: protection/hiding is not a security measure
