Building Quote-Level Price and Demand Data

Why Quote-Level Data Construction Matters

A demand record is not simply a premium joined to a purchase flag. It must identify which offer was made, to whom, when, for what coverage, and whether the customer acted within a defined period. If the price comes from one quote revision while the outcome comes from another, the record describes no actual offer. If the data include only issued policies, offers that were declined have disappeared from the denominator.

This tutorial focuses on building and checking the records that will later be used to study demand. It does not estimate a price-response relationship. The construction choices made here determine what the later analysis can reasonably describe.

Choose the Observation Unit and Define the Outcome

The observation unit must match the decision being studied. For a new-business purchase question, a useful unit is commonly a quote-offer episode: the customer may accept or decline that offered contract. A customer is not necessarily the right unit, because one customer may receive several offers or quote revisions. A policy is also not the right unit for a purchase analysis if it exists only after acceptance.

For a renewal analysis, define a renewal-offer episode separately from the original new-business quote. The renewal price, offered terms, and decision period should correspond to that renewal. Do not treat a renewal offer as an independent new customer merely because it has a new transaction identifier.

Before extracting data, specify what counts as the outcome. For example, “purchased” might mean that coverage was bound within 30 days of the offer date, while “renewed” might mean that the offered renewal was accepted by its effective date. The chosen rule should be applied consistently and should distinguish a confirmed decline from an outcome that is not yet observable.

Define the Price and Coverage Being Offered

The primary price field should represent the price the customer actually faced for the stated offer. Commonly this is the quoted premium for the full policy term, before any later changes that were not part of that offer. Store the quote date, policy term, currency, payment plan, and any fees or surcharges needed to interpret the amount. Keep a separately defined amount if the business question concerns a different price measure.

Do not substitute a billed installment, a bound premium, or a renewal premium for the offered quote price. These amounts can differ for valid reasons: payment frequency, fees, endorsements, updated information, or a different coverage election. If an installment amount is all that is available, do not silently label it an annual premium. Retain its frequency and the information needed to interpret it, and mark the full-term quoted amount unavailable if it cannot be recovered reliably.

Price also needs coverage context. Preserve the offered limits, deductible, insured property or exposure, and other material coverage choices that define the contract. A lower premium for a lower limit is not the same offer at a lower price. Keeping coverage characteristics with the record allows analysts to identify materially different offers rather than treating their prices as directly interchangeable.

Set the Time Window and Preserve the Offer Timeline

A usable record needs dates that establish the sequence: when the offer was created or presented, when it was revised, the proposed effective date, and when a decision or bind occurred. The outcome window should start from a defined event, usually the offer date or the presentation date, and end at a stated deadline. A decision outside that window should not be counted as an in-window purchase unless the outcome definition explicitly allows it.

If the observation period ends before the full outcome window has elapsed, some records are right-censored: their final outcome is not yet known under the chosen rule. Keep these records distinguishable from completed nonpurchases. A data extract should also use consistent time zones and date conventions; a bind just after midnight can otherwise be assigned to the wrong day.

Build and Validate the Record

A practical data specification makes the record grain explicit before joining files. A robust key often combines a quote identifier, offer or option identifier, and revision identifier. Customer and policy identifiers are useful for linking and auditing, but they do not by themselves establish that two rows are the same offer. Two different coverage options from one quote can be legitimate separate records; repeated copies of the same option and revision are not.

Join cardinality matters. If one quote row matches three transaction rows, a join can turn one offer into three records. That can inflate counts or give the episode extra weight in a later analysis. Before accepting a joined file, compare row counts before and after each join, inspect keys with multiple matches, and decide whether the multiplicity is valid or an error.

Missingness, Duplication, and Interpretation

Missing records are not all equivalent. A missing quoted premium may indicate a failed quote-system join; a missing outcome may indicate an unfinished window; a missing coverage limit may make the offer difficult to compare; and a missing customer characteristic may reflect a separate data collection issue. Preserve missingness reasons where possible instead of replacing all missing values with zero or a generic “no.”

Likewise, investigate whether missingness is associated with the outcome. For example, if declined quotes are less likely than bound quotes to be retained in a downstream policy system, a file built from that system will omit relevant nonpurchase episodes. Such a file cannot be repaired simply by assigning nonpurchases to observed policies. The source system and inclusion rules must support the intended population of offers.

These records support interpretation only for the population and offers represented by the data. For example, filtering to quotes with complete premium and coverage information may be necessary, but it changes which episodes remain. Report those exclusions and their counts. Also distinguish a descriptive association among observed offers from a causal price effect: the construction of a clean record does not by itself remove differences between customers who received different prices.

Common Data-Construction Traps

• Using a policy file as the quote universe. A policy file generally centers on bound business and may omit declined offers.
• Using the customer as the only key. One customer may have multiple offers, revisions, or renewal episodes.
• Overwriting the offered price. A later bound premium or transaction amount is not automatically the premium the customer saw.
• Deduplicating too broadly. Remove repeated copies of the same offer revision, not distinct coverage options that happen to share a customer or quote identifier.
• Calling unobserved outcomes declines. Require a complete outcome window or a confirmed decision before coding a nonpurchase.