Our core data model

​

Ingestion & sources

​

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

• Ecommerce 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

• 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.

​

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.”

​

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

​

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, warehouseId
  • 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:

"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.

​

Data availability & latency

• 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.