A customer profile becomes difficult to trust when one joined table mixes people, orders, line items and eligibility changes. Repeated rows can inflate revenue, while a single overwritten field can erase the history needed to explain a decision.
A customer 360 data model should keep those relationships explicit. Build a customer-level view from well-defined tables, preserve source identifiers and aggregate each fact at the grain the consuming workflow needs.
Key Takeaways
Define what one row represents in every table before writing joins or profile calculations.
Keep source-system identifiers alongside the resolved customer identifier.
Store transactions, line items and time-dependent records at their natural grain.
Aggregate related facts before joining them to a customer-level profile.
Treat the example schema as a design illustration, not a required Amperity implementation.
Define the grain before naming the tables
Grain means what a single row represents. A profile table might contain one row per resolved person, while an orders table contains one row per order.
Document the identity policy behind that person identifier. A household, account and individual are different entities, even when they share an email address or postal address.
Also define which source history the model covers. A customer who appears to be a first-time buyer within the loaded date range may have older purchases outside it. Our identity resolution techniques guide provides context for the relationships feeding the model.
Build the core tables around distinct relationships
An illustrative logical schema can use these tables:
Customer: one row per resolved person, with a customer key and selected profile attributes.
Customer source map: one row per source-system record mapped to a customer, preserving source name and source record key.
Orders: one row per order, with an order key, customer key, order timestamp and documented amount definition.
Order items: one row per order line, linked by order key and a line key.
Contact eligibility history: one row per relevant status change, with contact point, channel, purpose, status, effective time and source.
The tables connect through these illustrative relationships:
From | To | Relationship | Linking key |
|---|---|---|---|
Customer | Customer source map | One customer to many source records | Customer key |
Customer | Orders | One customer to many orders | Customer key |
Orders | Order items | One order to many order lines | Order key |
Customer | Contact eligibility history | One contact point to many status changes, by channel and purpose | Contact point associated with the customer |
These are suggested logical names, not Amperity standard table names. Add product, membership or household relationships only when the use case requires them and their meaning is defined.
Walk through a fictional customer example
Suppose store record S101 and ecommerce record E205 have been reviewed and associated with fictional customer C001. Preserve both source records in the mapping table rather than replacing their original identifiers.
C001 has two illustrative orders: O100 for $80 and O101 for $120. O100 has two line items, while O101 has one. Under an explicitly chosen gross-order definition, the two orders total $200.
If you join the orders directly to line items and then sum order amount, O100's $80 appears twice. The resulting $280 is a join error, not additional customer value.
Aggregate before joining facts to profiles
Calculate order-level and customer-level measures in a controlled sequence. Verify uniqueness at each step, then join the customer-level result to the profile.
Keep definitions visible: gross versus net revenue, treatment of returns, canceled orders, currency conversion and time zone. A lifetime value field can mean different things unless the calculation and source coverage are documented.
Use small known examples to validate the calculation before relying on totals. The cost of poor customer data quality includes rework caused by plausible but incorrect joins.
Keep history, eligibility and identity changes visible
A current profile serves a different job from a record of what was known at an earlier time. Decide which effective dates, processing dates and historical versions your reporting requires.
Keep contact eligibility tied to the correct contact point, channel and purpose. The example history table is a modeling suggestion, not a substitute for your organization's rules or a claim that one flag grants universal contact permission.
Preserve the evidence needed to investigate identity changes. A newly merged or split grouping can affect counts, calculated attributes and audiences; downstream teams need a controlled way to understand those effects.
Understand how Amperity's documented model differs
Amperity's data-table reference describes a Customer 360 table and related customer and interaction tables. Its model connects customer profiles to transactions and itemized transactions through defined relationships.
The Customer 360 operator guide explains that the table is built and configured, and that the Amperity ID exists alongside source keys. Follow those technical definitions rather than treating the illustrative schema above as an out-of-the-box Amperity schema.
Table availability, fields and calculations depend on your configured data and model. Review the actual tenant configuration before mapping a business requirement to a particular column.
Validate the model against a real decision
Choose a report or audience and trace several customers from original records to the final output. Test duplicate source keys, unmatched transactions, returns and changes to identity relationships.
Our build-versus-buy framework can help assign ownership of those checks. Explore our customer profile capabilities alongside the technical reference, then agree on the model your team can explain and maintain.



