Business Data & Point of Sale (POS) Requirements
To accurately measure marketing performance and connect it to real business outcomes, Bonsai requires access to your Business data such as Point of Sale (POS), Online Orders, and/or In-store orders.
This document outlines what data is needed, why it matters, and how to prepare it — whether your data lives in Azure SQL, Snowflake, BigQuery, Redshift, or another data warehouse.
Overview
Bonsai uses your business data to connect customer actions (seen in your analytics platforms like GA4) with business outcomes (recorded in your POS or order management systems).
This enables:
Multi-Touch Attribution (MTA): attributing revenue to the marketing interactions that led to it.
Incrementality Measurement: quantifying the lift caused by marketing campaigns.
Customer Insights: tracking lifetime value (LTV), retention, and cross-channel behavior.
To make this work, Bonsai needs transaction-level data that can be matched to your marketing analytics data using a shared unique identifier (such as a transaction_id or order_id).
How Marketing & Business Data Connect
When a customer completes a purchase — online or in-store — the transaction ID should appear in both:
Your business data (e.g., POS, CRM, or OMS systems), and
Your analytics data (e.g., GA4, Adobe Analytics, etc.) via a “purchase” or “business outcome” event.
Bonsai uses this shared ID (sometimes referred to as a cookie match dimension) to tie web and ad interactions to real sales.
Without this shared key, marketing touchpoints and sales data remain disconnected — making true ROI analysis impossible.
Bonsai uses analytics data as a linking layer between advertising platforms and business or POS systems. Ad identifiers such as click IDs are first matched to analytics events, and a separate unique identifier is then used to connect analytics data to downstream revenue or POS records. Bonsai does not join advertising data directly to point-of-sale systems — analytics serves as the intermediary that enables accurate attribution and measurement.
Data Domains & Requirements
POS and order data is typically organized into several domains. The schemas below are platform-agnostic examples of the fields Bonsai uses to support measurement, modeling, and attribution.
Order Level Sales is required for Bonsai products and represents the minimum dataset necessary to enable core attribution and modeling workflows. The required fields may be delivered as a single table or distributed across multiple tables, provided keys and relationships are maintained.
All additional domains (e.g., Sales Detail / Line Items, Customer, Retail Store) are optional and enable enhanced functionality and reporting, including:
Product-level attribution and SKU/service performance (requires Sales Detail)
Audience Analytics and customer segmentation (requires Customer)
Store/location-level reporting and geographic cuts (enhanced by Retail Store)
Privacy / PII Handling
Bonsai does not require raw personally identifiable information (PII) values. If preferred, PII fields (e.g., email address, phone number, street address) may be anonymized prior to delivery. Bonsai supports hashed identifiers (SHA-256 or SHA-512 preferred) for privacy-preserving identity matching. Contact your Bonsai Customer Engineer to align on hashing standards and expected formats.
1. Order Level Sales
Represents each unique transaction or order.
transaction_id*
Y
Unique ID for the transaction/order
2025-05-22-ATL-000982
store_zip
N
Store location: can be zip, dma, city, state
06831
store_id
N
Unique store or location id for the business
store123
customer_id
Y
Links to customer table (nullable)
CUST-9981
customer_zip
Y
Customer location: can be zip, full address, city, state
123 Main Street, Chicago, IL
customer_phone
Y**
Customer phone number
800-300-9089
customer_phone2
N
Alternative customer phone number
800-400-2356
transaction_datetime
Y
Timestamp of sale (UTC)
2025-05-22T19:45:00Z
updated_at
Y
Timestamp of when the sale or transaction status or customer data changed
2025-06-22T18:45:00Z
subtotal_amount
Y
Total before tax/discount
350.00
discount_amount
Y
Total discounts applied
-10.00
tax_amount
Y
Tax collected
21.00
total_amount
Y
Final transaction total
361.00
payment_type
N
Tender method (cash, credit, gift card, etc.)
Credit Card
status
Y
Transaction status
Completed / Voided / Returned
*Commonly used as the connection between your analytics (GA4) data and point of sales/transactions. This may be called something different depending on the business.
**Phone numbers are only required for businesses that rely on call tracking or phone-based lead attribution (e.g., service or appointment-driven businesses). For retail or e-commerce businesses where purchases occur directly online or in-store, phone fields are optional.
2. Sales Detail (Line Item Level)
Breaks down each order into its component products or services.
transaction_id
Y
Foreign key linking to Sales Header
2025-05-22-ATL-000982
line_id
N
Unique line within the transaction
1
sku*
Y
Product SKU
TIRE-A123
quantity
Y
Units sold
4
unit_price
Y
Price per unit
80.00
line_discount
Y
Discount at item level
5.00
extended_amount
Y
Quantity × unit price
320.00
*This can be a traditional product SKU (retail inventory item) or a service identifier for non-inventory line items (e.g., labor, installation, membership, diagnostic fee).
3. Customer
Captures the buyer’s identity and key attributes.
customer_id
Y
Unique ID per customer
CUST-8821
customer_name
N
Customer name
John Doe
Y
Customer email
9b74c9897b...
phone_number
Y
Customer phone number
800-330-9089
phone_number2
N
address
Y
Customer address
123 Main St, Chicago, IL
postal_code
Y
Customer ZIP or postal code
63110
created_at
N
Customer record creation time
2023-02-05T00:00:00Z
Privacy Note: If hashed or anonymized identifiers are preferred for compliance and security (SHA-256 or SHA-512 is preferred), please reach out to your Bonsai Customer Engineer to share which hash function you use.
4. Retail Store
Details for each retail location.
store_id
Y
Unique store key
STL01
store_name
Y
Store name
Bonsai - Chicago
store_zip
Y
Zip code
60007
address_city
Y
City
Chicago
address_state
Y
State
IL
Data Delivery Guidelines
Bonsai’s preferred method for onboarding business data is through our prebuilt Connect Cards, which provide secure, out-of-the-box integrations for most major cloud data warehouses and business systems. Please review the Business Point of Sale Connect Cards to begin onboarding your data.
If you have a preferred method of data sharing, Bonsai can share Google Cloud Storage Bucket or Google Big Query table destination information for data transfers.
Preferred cadence: Daily incremental loads Required history: At least 24 months Minimum viable dataset: Order Level Sales
Data Quality & Validation
Bonsai requests raw, untransformed data to ensure full transparency and control over joins, transformations, and validations. Clients are encouraged to review the validation checklist and provide a data dictionary or lookup table with key field definitions and business logic notes. This enables accurate validation during the proof-of-concept build. Once the initial connection is complete, Bonsai automates all data ingestion and quality checks, requiring no ongoing client effort.
Bonsai validates all incoming data against a core checklist:
Incremental Logic
Incremental Key Present
Identify columns used for delta loads (updated_at, modified_date, etc.)
Key consistently populated and updated
Incremental Key Behavior
Identify column updates on inserts/updates (no nulls or static values)
Timestamp updates on all modified rows
Data History
History Coverage
Earliest and latest transaction dates
≥ 24 months available
Archive Data
Check if history tables exist beyond live data
Archive accessible if needed
Volume Profiling
Average Daily Volume
Count of daily transactions
Consistent daily volume; note peaks
Volume Consistency
Identify outliers (batch imports, missing days)
No unexplained gaps/spikes
Reconciliation
Reconciliation Method Known
Client’s internal totals process (register, ERP, etc.)
Reconciliation source identified
Daily Totals Match
Bonsai-calculated totals vs client control totals
Within ±0.1% variance
Timezone Standardization
Timestamp Source
Determine if timestamps are UTC or local
Timezone clearly defined
Normalization Plan
Plan for UTC normalization
UTC normalization strategy confirmed
PII Handling
Sensitive Columns
Inspect Customer data for names, emails, phones
Hash Verification
Validate hash pattern and determinism, if applicable
SHA-256 or equivalent confirmed
Status & Transaction Flags
Status Field Present
Check for Completed, Voided, Returned flags
Status column exists and consistent
Returns Handling
Identify how returns are logged (negative, linked, or separate)
Return logic documented
Financial Metrics
Margin %
Verify cost or margin fields (margin_pct, cost_amount)
Present and logical (0–90%)
Mechanical Margin %
For service clients, check labor/service margin
Present and logical if applicable
Tax & Discount Logic
Verify tax_amount, discount_amount formulas
Totals reconcile to invoice header
Data Relationships
Foreign Keys Valid
Confirm joins: SalesDetail → SalesHeader → Customer/Store
≥99.9% join coverage
Null Keys
Identify orphaned or missing FK values
Minimal or no nulls in key fields
Data Freshness
Latest Transaction Date
Check most recent transaction_datetime
Data ≤ 24h old
Update Frequency
Confirm daily/hourly ingestion feasible
Agreed frequency documented
Schema & Field Standards
Data Types Consistent
Verify type alignment (e.g., decimal vs int)
Matches expected schema
Naming Conventions
Confirm lowercase + underscores naming pattern
Consistent and documented
Discounts & Promotions
Discount Source
Identify where discounts are stored (header, detail, promo table)
Confirm structure and logic
Promo Codes
Check for promo_id or promo_code linkage
Fields present and valid
Store Metadata
Store Info Complete
Confirm store_id, store_name, region, timezone
All stores covered with metadata
Region Consistency
Verify region and timezone alignment
Stores grouped correctly
Completeness & Quality
Null / Blank Checks
Identify columns with excessive nulls
<5% null rate on required fields
Duplicate Keys
Detect duplicates in transaction_id, line_id, customer_id
No duplicates in key fields
Documentation
Data Dictionary Provided
Confirm client provided schema reference
Available and up to date
Business Rules Confirmed
Document how returns, voids, discounts work
Notes captured for modeling
Need Help? Reach out to your Bonsai Customer Engineer for a quick data readiness review.
Last updated