Skip to main content
SourceMedium takes the raw data from your connected platforms and turns it into one set of tables. Your dashboards, the AI Analyst, and your warehouse all read those same tables. This page explains what happens in between. Cleaning is about quality, enrichment is about completeness, and modeling is about making the result easy to query.

​
Cleaning

Every platform sends data in its own shape. Cleaning makes those shapes comparable.
  • One schema across sources. Equivalent fields from different platforms get the same name and type, so a Shopify order and an Amazon order share the same columns in obt_orders. Names follow the schema standards.
  • Consistent identifiers. IDs and keys use the same data type across sources, so joins do not fail silently.
  • Deductions stored as negatives. Discounts and refunds are stored as negative numbers, so revenue math adds up without special cases. See Revenue fields.
  • Timestamps in UTC and local time. Timestamps are delivered in UTC plus a _local_datetime version in your reporting timezone, for example order_processed_at and order_processed_at_local_datetime.
  • Lowercased categorical values. Machine-generated text such as UTM values is lowercased and trimmed, so grouping is not case sensitive. See UTM normalization.
  • Automated checks. Tests on the delivered tables confirm, for example, that keys are unique and that sm_channel only takes expected values.

​
Enrichment

Once the data is clean, we add context that no single platform has.
  • Product costs. SKU-level costs from your cost configuration are joined to order lines, so profitability sits next to revenue.
  • Sales channel. Every order gets an sm_channel and sm_sub_channel, from platform signals and your own channel mapping rules. See Sales channel.
  • Order type. Every order and order line is classified as subscription or one-time. See Order type.
  • Order attribution. Last-click UTM evidence from Shopify, website analytics, referrers, and post-purchase surveys is collected into one table, fct_order_attribution_signals. The winning source becomes the order’s sm_utm_* fields. The attribution source hierarchy explains which source wins.
  • Customer demographics. Age and gender are not in platform exports. See Demographic data for what is available and how to add your own.
  • Media mix measurement. When a measurement vendor is connected, its modelled attribution lands in rpt_media_mix_channel_daily next to last-click and platform-reported figures.

​
Table shapes

Every table name starts with a prefix that tells you what one row is. Start with obt_ tables. Use fct_ and dim_ tables when you need a grain the OBT does not have, and rpt_ tables when the aggregation you want already exists. Modeling shows how to build on them.

Table docs

Column-level schemas for every sm_transformed_v2 table.

Data definitions

Valid orders, revenue fields, refunds, and the other rules behind the numbers.

Data architecture

How data flows from your platforms to the tools you use.

Data freshness

When each source is fresh to the previous day.