Core Overview

The core data model is the heart of the SoundCommerce Retail Data platform. This data model unifies and transforms order data to reveal trends and KPIs for sales, growth, customer engagement, and more. The power of this data model stems from its ability to trace the end-to-end lifecycle of each order. This article describes the basic data modeling process.

How we model order data

Order data gets pulled, or ingested, into the SoundCommerce platform, from your e-commerce platform, retail store, mobile app, or third-party retail platform. Some examples of e-commerce platforms include Shopify, BigCommerce, and Demandware. Many of these platforms incorporate third-party integrations for shipping and fulfillment. Integrations are treated as separate data sources with their own considerations for ingestion and mapping.

Standard retail order data (not including fulfillment) can use 60 fields or more to handle all aspects of an order. Depending on how a company structures their business, additional order fields may be present. All fields are incorporated into the data model and transformed as needed, using the appropriate business calculations or formulas. (You can find each field, including fields that are unique to your business, and related formula in the Retail Platform Data Dictionary.)

Upon ingestion, each order (identified by order_id) is assigned a unique ID (SCID). The SCID serves as the internal key that references every field related to the order.

Diagram of data lineage

How complete is my order data?

If you have transactions through brick-and-mortar stores in addition to online orders, your order data can be incomplete. Cash sales generally can't be attributed to your marketing efforts unless you enroll cash-paying customers in a loyalty or membership program. Without such a program, cash sales may be impossible to attribute to a particular customer, product, or campaign.

Point-of-sale (POS) data is ingested and mapped the same way as online orders. The only difference is that POS data includes a location ID, which identifies the retail store location.

Fulfillment data

Fulfillment data can give you valuable insights on the post-purchase customer experience. If orders are being delayed in transit or aren't shipping due to latency, you can quickly determine what's blocking timely deliveries. A shipment is considered delivered when the customer takes possession of the item, either by picking it up at a store (BOPIS) or receiving it at their shipping address.

E-commerce systems that include order fulfillment and shipments as part of their order data require no additional integrations to complete this final phase of the order lifecycle. Shopify is one example of an e-commerce platform that stores fulfillment data with order data.

E-commerce systems that store fulfillment on separate systems require additional API calls and mapping. A few systems that do this include Amazon, and Salesforce Commerce Cloud, which uses Demandware and other partner systems. These integrations are included in the SoundCommerce Campaign module.

The Customer data model

Customer data is extrapolated from your order data and modeled separately. Because a customer is likely to have a presence on multiple retail platforms, we aggregate data from all the different platforms, removing duplicates and outdated details. This ensures that your numbers aren't inflated and your customer contact information is current.

By aggregating different source systems into a single stream of data, we are able to calculate such things as cohorts and historical sales per customer. These pre-calculated metrics are particularly helpful when you're trying to assess customer engagement over time or segment customers by lifetime value. Whether you're visually inclined or more comfortable using SQL, these metrics are readily available in both the dashboards and Direct Access, and are integral to the basic data model.

Customer privacy

In the Retail Dashboards and Direct Access data, customers are represented using a universally unique ID number (CUID). The CUID is the result of deterministic matching, most typically done using email addresses, which allows us to accurately count customers through a de-duping process.

The CUID allows us to anonymize and restrict a customer's personal and private details from the SoundCommerce dashboards and primary instance of Google BigQuery, while leveraging customer transactional and demographic details for the data model. All personal identifying information (PII), including CUID to PII mapping and views are stored in a separate, secure data set in your BigQuery or Snowflake project called Priv that is available with administrator privileges.

In summary, the Order data gives you all the tools you need to address to answer the following questions, both in the Executive dashboards and Direct Access data: