# Set up your enquiry log

Copy this template into the business's private working files. Fill in the rules before calculating results. These are configurable fields, not approved definitions for any particular business.

## Rule record

| Field | Your rule |
| --- | --- |
| Business / record owner | |
| Rules version and effective date | |
| Review time zone | |
| Private customer system used for underlying evidence | |
| Accepted enquiry definition | |
| Excluded contact types: spam, vendors, recruitment, support and others | |
| Returning customers: support versus new work | |
| Offered services / service-area requirements | |
| Qualification criteria and who decides | |
| Job acceptance milestone and required evidence | |
| Handling cancellation after acceptance | |
| Payment verification record / reviewer | |
| Amount convention, currency and tax basis | |
| Refund and partial-payment treatment | |
| Source evidence categories and acceptable joining method | |
| Duplicate / follow-up rule | |
| Review frequency and person responsible | |
| Access and retention rules for private records | |

## Workbook setup

Import the blank CSVs into separate sheets: Opportunities, Payments and Visits. Save as a workbook if you want formatting or additional sheets; CSV itself stores plain rows, not formulas, formatting or multiple tabs. The files supplied here contain no macros or formulas.

Use the worked example in a separate practice sheet. Delete example rows before counting real business results. Never merge the example into your actual enquiries. Leave a genuinely unavailable metric blank and explain the gap; a blank is not a measured zero.

## Opportunity fields

| Field group | How to use it |
| --- | --- |
| rules_version | Version that governed this opportunity's decisions. Preserve any later reclassification notes in the private record. |
| opportunity_id | Neutral ID for one potential job. Do not use a name, email or telephone number as the ID. |
| first_enquiry_date / contact_route | First contact for that job; form, phone, direct email or another agreed route. Use YYYY-MM-DD and keep exact times in the private record if needed. |
| requested_service / service_area_check | Plain service label and outcome of coverage check; keep the customer's exact address in the private system. |
| private_record_ref | Reference to the underlying restricted customer record. Do not paste message text or contact details. |
| customer_reported_source | Customer's stated source; use “not collected” when it was not obtained. Do not silently interpret “Google” as organic search. |
| observed_source / source_evidence_ref | Source supported by an actual record and its private reference. Leave blank if unavailable. |
| evidence_type | linked_record, self_report, conflicting, or unknown. A self-report can coexist with a linked record; retain both fields and explain differences. |
| join_method | Documented link between an enquiry and a visit, if established. A close timestamp alone is insufficient. Use “none” when there is no join. |
| accepted_enquiry_state / date | yes, no or unreviewed after inspecting the request. A notification alone is not a reviewed enquiry. |
| qualification_state / date / rule_ref | yes, no or unknown; date and reference to criteria used. Record the reason in the private record. |
| job_accepted_state / date / evidence_ref | yes, no, not_yet or unknown, supported by the chosen acceptance milestone. Preserve historical acceptance if later cancelled and record cancellation in current_status. |
| current_status | Latest business status, such as unreviewed, quote pending, accepted, lost, cancelled or excluded. This does not replace the separate stage decisions. |
| last_reviewed_date / next_action / owner / due | Review trail and the next practical follow-up. |
| duplicate_of | Original opportunity ID for a duplicate. Exclude duplicate rows from opportunity totals. |

## Payment fields

Use one unique transaction_id per verified transaction and an existing opportunity_id. transaction_type is payment or refund. The starting convention is positive amounts with that separate type; subtract refunds only when producing a clearly labelled net-cash summary. Keep currency and tax_basis explicit and do not combine incompatible amounts.

payment_stage can be deposit, progress, balance, full or refund. Verify the evidence reference and date; do not populate payment rows from invoice amounts or accepted quotes. Link refunds to related_transaction_id where possible. A paid-opportunity count uses distinct opportunity IDs with verified receipt evidence; report later refunds and outstanding balances separately. It is not a fully-paid-job count or accounting revenue.

## Visit summary fields

Use one row per chosen source dimension and period from a named report. Session source / medium describes a different scope from First user source / medium. Record the exact dimension and do not mix them in one total.

tracking_coverage should state known continuity, incomplete or unknown. data_status should identify preliminary or processed data and any report limitations. Keep event name and count separate from sessions. Neither is a reviewed lead count. The visit sheet has no individual customer join.

## Counting rules

- Count distinct, non-duplicate opportunity IDs with accepted_enquiry_state=yes for accepted enquiries.
- Qualification and accepted-job counts require their respective stage evidence under the recorded rules. Report unknown and pending outcomes separately.
- Count paid opportunities once even if a deposit and balance create two payment rows. Show partial payment and refunds separately.
- An enquiry cohort uses a fixed first-enquiry date window, the same IDs and an explicit review cutoff. A monthly activity report groups each milestone by its own date. Do not divide different groups and label the result an enquiry conversion rate.
- When using a cohort denominator, show the entire accepted-enquiry group and qualification/outcome coverage. Label any eligible-subset calculation separately.
- Retain source unknowns and conflicts. Do not guess sources from mailbox routing, timing or unexplained direct traffic.

## Worked arithmetic example

The practice sheet uses six accepted enquiries first received 3–9 August 2026, reviewed at 24 August 2026. Three qualified, two accepted jobs and one with verified deposit evidence give 3/6=50%, 2/6=33.3% and 1/6=16.7%. Stage counts overlap. The pending quote may change later; the deposit does not mean paid in full. All sources are deliberately unknown, so this example supports no channel attribution or visit-to-enquiry rate.

Keep these working files private. Do not send customer details or the private record references to Analytics, tracking URLs or public reports.