> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dema.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Our core data model

> Discover how Dema collects, processes, and enriches your e-commerce data—from real-time events and orders to marketing costs, shipping rules, and returns. See how each data point comes together to power near real-time profitability insights, including Gross Profit 2 (GP2) costs like toll, pick & pack, and transaction fees.

## Ingestion & sources

<Note>
  Dema uses a flexible approach to ingest data from multiple sources: a client-side tracking script for user events, plus third-party integrations for orders, returns, inventory, and marketing costs.
</Note>

### Tracking Script Behavior

* **Default events:**\
  Dema tracks **page views**, **product views**, and **orders** on the client side to capture essential user activity.

* **Session construction:**
  * We build sessions similarly to Google Analytics Universal.
  * A `dv_track` cookie is set at first visit. Events stick to that session until 30 minutes of inactivity passes.
  * Each new session gets its own ID, while user ID derives from the `dv_track` cookie.

### Third-party integrations

* **E-commerce platforms:**\
  Dema connects with **Shopify**, **Centra**, **Commercetools**, **sFTP uploads**, and **Snowflake** to fetch orders, incoming deliveries, returns, and inventory. We grab orders through webhooks (real-time) and nightly batch fetches to keep data fresh.

* **Marketing costs:**\
  We integrate with **Meta**, **Google Ads**, **TikTok**, and **Google Sheets** to gather your spend, clicks, impressions, and conversions—broken down by campaign, ad set, and country.
  * One-click setups manage API authentication for a seamless connection.
  * We store these details in two tables:
    * **Marketing costs** – For campaign-level spend (e.g., daily totals)
    * **Product marketing costs** – For feed-based ads (e.g., shopping), capturing spend per product.

### Data orchestration

<Tip>
  <Icon icon="check" iconType="solid" /> **Scheduling Best Practice**\
  Most batch processes (order updates, returns, marketing costs, inventory snapshots) run between <strong>4–6 AM CET</strong>. Webhooks and the tracking script feed data continuously in near real-time.
</Tip>

* **Real-time vs. scheduled:**
  * Events and session data appear almost instantly, typically within \~3 minutes.
  * Batch jobs refine shipping info, order statuses, or inventory levels on a daily schedule.

```mermaid theme={null}
flowchart LR
    subgraph External Integrations
        A[Tracking Script] --> B[DEMA Pipeline]
        X[E-commerce Platforms] --> B
        Y[Marketing APIs] --> B
        Z[Data Warehouses] --> B
    end
    B --> C[Orders Table]
    B --> D[Events Table]
    B --> G[Inventory Table]
    B --> E[Marketing Cost Table]
    B --> F[Product Marketing Cost Table]
```

## Event & session construction

### Sessionization logic

* **GA Universal–style approach:**\
  Dema uses a cookie-based strategy that assigns a session on a user’s first visit, resetting it after 30 minutes of no activity.

* **Tracking cookies:**
  * `dv_track` identifies each user and keeps the session ID.
  * **Anonymous vs. authenticated:** Users who opt out or aren’t recognized get “backfilled” later with final order data, labeled under the “Backfilled” channel if no session details are available.

### Attribution data

* **UTM parameters:**
  * We record `utm_source`, `utm_medium`, `utm_campaign`, etc., as soon as the user lands.
  * Within Dema, map these parameters to relevant channels (for example, `utm_source=meta` => “Meta – Paid Social”).

* **Marketing source storage:**
  * UTM data is stored alongside sessions or users, linking each order to the right marketing channel.
  * If no tracking data is present, orders default to “Backfilled.”

<Accordion title="Why 'Backfilled' Orders?">
  In cases where a user doesn't allow or triggers the tracking script (ad blockers, cookie opt-outs, etc.), we fetch the order data later via webhooks or batch processes.\
  Since there's no session-level data, the channel is "Backfilled" by default. No need to worry; these orders still appear in Dema and contribute to your overall analytics.
</Accordion>

***

## Data processing & real-time calculations

> **3-minute window:** Dema’s pipeline processes events, orders, and related data in near real-time—so you see what’s happening in minutes, not hours.

* **Real-time session updates:**
  * Page views, product views, and orders flow instantly into Dema.
  * If shipping details or additional costs (like toll or pick & pack) arrive later, we update the existing record accordingly.

* **Order updates:**
  * We merge data from your tracking script, webhooks, and nightly batch fetches.
  * The final, most recent data is always the source of truth—ensuring no detail is missed if new info arrives after initial order creation.

## Data model: Tables & entities

This section covers each core table in Dema. They might differ slightly in your actual implementation, but the fundamental logic remains consistent.

### Events table

* **Purpose:**\
  Captures site events like page views, product views, and purchase actions.

* **Key fields (camel case):**
  * `sessionId` — Ties the event to a session
  * `userId` — Drawn from `dv_track`
  * `eventType` — For instance, `pageview` or `order`
  * `timestamp` — Logs when the event happened
  * `utm_source`, `utm_medium`, `utm_campaign` — Stored at session start

* **Notes:**\
  Useful for reconstructing user journeys and attributing orders to specific channels.

### Order table

* **Purpose:**\
  Main repository for every order, including partial updates from batch or webhook flows. **Returns/refunds** data is also stored here, so there’s no separate returns table.

* **Key fields:**
  * `orderId` — Unique order identifier
  * `sessionId` — If the order ties to a known session
  * `userId` — If recognized via `dv_track`
  * `items` — e.g., `productId`, `variantNo`, `quantity`, `price`, `cogs`, `shippingCost`, `toll`, `pickPackCost`, `transactionFee`
  * `shippingProvider`, `shippingCountry`
  * `returnStatus`, `returnQuantity`, `returnReason`
  * `createdAt`

<Info>
  Note that this table evolves over time. If returns or shipping details arrive later, we update the existing record.
</Info>

### Marketing cost table

* **Purpose:**\
  Tracks campaign-level spend from Meta, Google, TikTok, or manual entries.

* **Key fields:**
  * `platform`, `campaignId`, `adsetId`, `country`
  * `dailySpend`, `clicks`, `impressions`, `conversions`
  * `timestamp` (e.g., daily or per reporting window)

* **Notes:**\
  Dema attributes these costs to orders or sessions using your UTM mapping rules.

### Product marketing cost table

* **Purpose:**\
  Captures feed-based ads (like Google Shopping) where spend is recorded per product.

* **Key fields:**
  * `productId`, `variantNo` (if applicable)
  * `spend`, `clicks`, `impressions`, `conversions`
  * `campaignId`, `adsetId`, `adId`, `country`
  * `timestamp`

* **Notes:**\
  Great for granular analysis on product-level advertising effectiveness.

### Inventory table

* **Purpose:**\
  Logs each SKU (variant), warehouse, stock level, and COGS data.

* **Key fields (camel case):**
  * `productId`, `variantNo`, `warehouse`
  * `stockQuantity`, `incomingDeliveries`
  * `cogs` (fixed or pulled from external data)

* **Notes:**\
  Snapshot runs daily (4–6 AM CET) to track historical levels and forecast demand.

***

## Profitability enrichment

### Rules-based fees & cost structure

Merchants can define **rules** to automatically apply shipping, toll, transaction, and pick & pack fees to each order, for example:

```plaintext theme={null}
"If `shippingProvider` = UPS and `shippingCountry` = US, apply X cost."
```

Costs can be a flat rate, a percentage of order value, or per-item.\
If no rule is found, a fallback logic applies.

These same rules apply to shipping, toll, transaction fees, and more. You can also import real shipping costs via CSV from providers like UPS or DHL.

### Cost of Goods Sold (COGS)

* **Source:**\
  Typically pulled from your **inventory** data. If missing, Dema references manually entered COGS.

### Shipping & logistics costs

* **Rules-based or actual:**
  * Provider and country details help match each order to the right cost profile.
  * Merchants can define actual shipping charges or import real costs via CSV.

### Toll, pick & pack, and transaction fees (GP2 costs)

* **Logistics & operational fees:**
  * **Toll**: import duties/customs
  * **Pick & Pack**: handling fees
  * **Transaction Fees**: payment processing

* **Rule setup:**
  * Similar to shipping, these fees can be configured with your unique rules or CSV imports.

### Calculating gross profit metrics

1. **Gross Profit 1 (GP1)**\
   `GP1 = Sales - COGS`

2. **Gross Profit 2 (GP2)**\
   `GP2 = GP1 - (Shipping + Toll + Pick & Pack + Transaction Fees)`

3. **Gross Profit 3 (GP3)**\
   `GP3 = GP2 - Marketing Spend`

**Net Gross Profit variants:**\
We also calculate **Net GP1**, **Net GP2**, and **Net GP3**, which factor in returns/refunds. Initially, we may use estimated returns until a set threshold (e.g., 30 days) passes, then switch to actual return data.

<Check>
  All these profitability metrics update automatically whenever new cost or return data arrives. No manual recalculations needed!
</Check>

***

## Data availability & latency

<Note>
  **Within 3 minutes:**\
  Your Dema dashboard shows new sessions and orders as soon as they’re processed by our real-time pipeline—no lengthy delays.
</Note>

* **Nightly refinements:**\
  Between **4–6 AM CET**, we finalize details like order statuses, returns, marketing spend, and updated inventory snapshots.

* **Merging & finalizing:**\
  The most recent data always wins. If fresh shipping or returns info arrives later, Dema updates your orders automatically, preserving correct session data and attribution.