Query tool (DSL reference)

query is Kreel’s escape hatch. The other MCP tools (see Data tools) are built for specific questions and return a shape an agent can reason about without naming tables or columns. query is different: it’s a small, allowlisted query language against Kreel’s normalized data tables, for the questions those tools don’t cover.

You don’t usually write this by hand. Your connected agent decides when a question needs query and constructs the request itself. This page exists so you can see exactly what’s queryable, understand a result your agent shows you, or write the DSL yourself against the REST API or CLI.

v1 is single-table only

A query request reads from exactly one table. Cross-table joins aren’t supported - questions that need more than one table go through the canonical tools on Data tools and Brand context tools instead.

Where you can run a query

| Surface | How | |---|---| | MCP tool | query(dsl, brand), from an agent connected over MCP. | | REST | GET /api/v1/query?dsl=<json-encoded-object>, using an API key. | | CLI | kreel query '<dsl-json>' - see CLI. |

All three surfaces run the exact same query engine and return the same shape.

The brand argument (MCP) or client_id query parameter (REST) picks which brand’s data you’re querying. If your connection can only reach one brand, it’s applied automatically. If it can reach more than one, you must specify it.

Machine-readable schema

The MCP resource kreel://schema exposes this same table and column list as JSON - see Prompts and resources. An agent typically reads that resource before writing a query, rather than relying on training data about the DSL.

Request shape

A query is a JSON object:

{
  "from": "campaigns",
  "select": ["platform", "campaign_name", "spend", "roas"],
  "where": {"platform": {"eq": "meta"}, "spend": {"gt": 100}},
  "group_by": ["platform"],
  "order_by": [{"field": "spend", "dir": "desc"}],
  "account": ["EU Store", "EU Ads"],
  "limit": 50,
  "cursor": null
}

| Field | Required | Type | Behavior | |---|---|---|---| | from | Yes | string | The table name. Must be one of the tables listed under Table reference. | | select | No | list of strings | Columns and computed metrics to return. Defaults to the table’s default selection if it has one, otherwise every allowed column on the table, in alphabetical order. | | where | No | object | A map of column name to condition. See Filtering with where. | | group_by | No | list of strings | Column names to group by. Any entries turn on aggregation - see Grouping and aggregation. | | order_by | No | list of objects | Each entry needs a field; dir is asc or desc and defaults to asc. field must be one of the table’s columns. | | account | No | string or list of strings | Connected account name/id or saved account-group name. Several values are combined; omit for all accounts. | | limit | No | integer | Row cap. See Limits for defaults and caps per table. | | cursor | No | string or null | A continuation token from a previous response’s meta.next_cursor. See Pagination. |

Filtering with where

where is a map of column name to condition. A condition is either a bare value, shorthand for “equals,” or an object naming one operator.

| Operator | Meaning | |---|---| | eq | Equals. | | ne | Not equal. | | gt | Greater than. | | gte | Greater than or equal. | | lt | Less than. | | lte | Less than or equal. | | in | Value is in a list. | | nin | Value is not in a list. |

in and nin require a JSON list as their value. A bare value, such as 100, is shorthand for the eq operator.

Filtering on a computed column (like roas or open_rate) filters on the same row-level expression the column displays, so what you filter on matches what you see in the results. Filtering on a difference column (like daily_product_sales.net_revenue) filters on the row-level subtraction, before any aggregation.

You can’t filter on client_id. If you include it in where, it’s dropped rather than rejected - see Tenant scoping and brand isolation for why.

Special filters for email tables

email_campaigns and email_flow_daily accept two extra filter keys that aren’t real columns. They can be used in where but never in select, group_by, or order_by.

| Filter key | Matches | Supported operators | |---|---|---| | name_contains | Case-insensitive substring match against the campaign or flow name. A missing name is treated as an empty string. | eq, ne, in, nin | | tag | Exact, case-insensitive match against one of the entity’s tag names. An empty or whitespace value matches untagged rows. | eq, ne, in, nin |

Combine a tag or name_contains filter with group_by on source_type to get one tag group’s summed measures and weighted rates across a window.

Grouping and aggregation

Adding any entries to group_by turns on aggregation. Every column you select that isn’t also in group_by is recomputed rather than returned as-is:

• A column that’s also a group key is returned as-is.
• A registered ratio metric (roas, cpa, ctr, cpm, cpc, conversion_rate, and similar) is recomputed from the summed underlying measures - never summed as a ratio directly, since summing a per-row ratio produces the wrong number.
• Any other ratio-shaped column without a registered formula (for example retention_rate or video_avg_watch_time_sec) falls back to an unweighted average across the grouped rows.
• A plain numeric measure (spend, revenue, clicks, and so on) is summed.
• A non-numeric column that isn’t grouped on collapses to an arbitrary representative value from the group - only put dimensions you actually group on in select.
• A computed column (like campaigns.roas) is always derived from its summed base measures under aggregation.
• A difference column (like daily_product_sales.net_revenue) sums each side, then subtracts.
• Dividing by zero produces null, never 0 and never an error.

Two things can’t be grouped on, and each produces an explicit error rather than a bad result:

• A derived difference column, such as daily_product_sales.net_revenue - Cannot group_by derived column '<name>'. These are output measures, not dimensions.
• A computed-only ratio with no underlying stored column, such as campaigns.revenue or email_campaigns.open_rate - Cannot group_by computed column '<name>'. A computed name that’s also a real stored column (like daily_channel_summary.roas) is fine to group by.

Rounding

Any selected column recognized as a ratio metric is rounded to a fixed number of decimal places on the way out, so the DSL agrees digit-for-digit with the other agent-facing tools:

| Metric | Decimal places | |---|---| | roas | 2 | | cpa | 2 | | cpc | 2 | | cpm | 2 | | ctr | 4 | | conversion_rate | 4 | | open_rate | 4 | | click_rate | 4 | | revenue_per_recipient | 4 |

A null ratio (from dividing by zero) stays null - it’s never rounded or coerced to 0. Every other numeric column passes through unrounded, with one exception: store_revenue_net’s money columns (gross_revenue, cross_period_refunds, net_revenue, average_order_value) are rounded to 2 decimal places so SUM(net_revenue) reconciles digit-for-digit with get_performance’s rounded account revenue instead of exposing float subtraction artifacts.

Limits

| Table | Default limit | Maximum limit | |---|---|---| | products | 50 | 100 | | Every other table | 1000 | 10,000 |

A limit above the table’s maximum is clamped, not rejected. The response’s meta block reports requested_limit, applied_limit, and limit_clamped so you can tell whether your limit was reduced.

products also has a smaller default selection: when select is omitted, it returns only shopify_id, title, vendor, product_type, and status, instead of every column, to keep the default response compact for a wide catalog table. Pass an explicit select to get more.

Pagination

1. Run the query.
2. Check the response’s meta.has_more.
3. If it’s true, run the identical query again - same from, where, select, group_by, order_by, account, and limit - with cursor set to the previous response’s meta.next_cursor.
4. Repeat until has_more is false.

Cursors are signed and bound to the exact shape of the query that produced them. Changing any of the fields above, tampering with the cursor, or passing one longer than 512 characters is rejected with the same message: the cursor doesn’t match the query it’s being reused with.

Ordering is always made stable so that equal sort values can’t shuffle between pages: your order_by is applied first, then Kreel appends a tiebreaker automatically (the table’s row id for a non-aggregate query, or any group_by columns not already ordered on for an aggregate one).

Response shape

{
  "meta": {
    "table": "campaigns",
    "selected_fields": ["platform", "campaign_name", "spend", "roas"],
    "requested_limit": 50,
    "applied_limit": 50,
    "limit_clamped": false,
    "row_count": 12,
    "has_more": false,
    "next_cursor": null,
    "date_filter": {"field": "date", "gte": "2026-06-01", "lte": "2026-06-30"}
  },
  "notices": [],
  "rows": [
    {"platform": "meta", "campaign_name": "...", "spend": 123.45, "roas": 3.21}
  ]
}

meta.date_filter echoes the effective date window the query ran under. When your where targets the table’s declared date column, it’s {"field": "<date column>", ...your condition}. When it doesn’t, it’s null, and meta.note reads "no date filter - full history". Tables with no declared date column at all (entity tables like products or customers) never get the full-history claim: meta.date_filter is null and meta.note just states the table has no canonical date column, even if your where filters some other date-like column on it (for example customers.customer_created_at) - the DSL cannot confirm that filter bounds row coverage the way a declared date field does. No default time window is ever applied; this only reports what your own where already contains.

rows is a flat list of objects keyed by the selected field names. Decimal values come back as regular numbers, dates and datetimes as ISO 8601 strings, and enum-style columns as their plain string value.

notices is an advisory list of caveat strings about that specific result - empty when nothing applies. Check it before you interpret the numbers. Current triggers:

• Selecting daily_channel_summary.revenue adds a caveat that it’s gross Shopify revenue, before cross-period refunds.
• Aggregating daily_channel_summary.revenue (a group_by) without channel in group_by, and without a channel filter that isolates a single basis, adds a caveat that store revenue (shopify_organic) and platform-attributed revenue (meta_ads/google_ads/klaviyo_email) are non-additive and get summed together - group by channel, filter it, or use get_performance/compare for MER math instead.
• Aggregating platform-reported claims (conversions, conversion value, revenue, roas, cpa, or orders on daily_channel_summary) across more than one platform - a group_by on daily_channel_summary, campaigns or adset_performance without channel, platform or connected_account_id in it, and without a filter pinning one platform - adds a caveat that platform claims overlap across platforms and are summed. Grouping by a native campaign or ad set id alone does not suppress it, because the same id can exist on more than one platform. Group by or filter to one platform for per-platform figures, or use get_performance’s mer for blended efficiency. It never doubles the store/attributed caveat above, and it is advisory only: it never blocks a saved dashboard tile.
• Aggregating revenue or conversions on email_campaigns, email_flow_daily or email_flow_messages (a group_by without send_channel, and no send_channel filter that isolates one channel) adds a caveat that the figures sum email and SMS sends. Add group_by: ["send_channel"] or where.send_channel eq "email" for email revenue, the figure the dashboard calls Email revenue.
• Selecting daily_product_sales.revenue adds a caveat that it’s gross of refunds, and points to net_revenue for the true net.
• Querying google_products or google_asset_groups while the brand’s Google Shopping/Performance Max sync is turned off adds a caveat explaining the empty result, rather than letting it read as “no PMax activity.”

Errors

Both the MCP tool and the REST endpoint catch query errors and return a normal response body of the shape shown below, rather than an HTTP error status. Always check for an error key rather than relying on the status code.

{"error": "Unknown table 'campagins'. Known: [...]"}

| When it happens | Error message | |---|---| | Table name isn’t in the allowlist | Unknown table '<name>'. Known: [...] | | Column in select isn’t on that table | Unknown column '<col>' on '<table>' | | Column in where isn’t real and isn’t a special filter | Unknown column in where: '<col>' | | Operator name isn’t recognized | Unsupported operator '<op>' | | in/nin value isn’t a JSON list | Operator '<op>' requires a list operand | | group_by on a derived difference column | Cannot group_by derived column '<name>' | | group_by on a computed-only ratio | Cannot group_by computed column '<name>' | | order_by entry missing field | order_by entries need 'field' | | order_by field isn’t on the table | Unknown order_by field '<field>' | | order_by direction isn’t asc/desc | order_by dir must be 'asc' or 'desc' | | limit isn’t an integer | `limit` must be an integer | | cursor isn’t a string | `cursor` must be a string | | account isn’t a string or list of strings | `account` must be a string or a list of strings | | Account name/id doesn’t belong to the brand | unknown_account, with available_accounts and any available_groups | | Cursor reused with a different query shape, tampered with, or too long | Invalid cursor for this query; reuse the same fields, filters, ordering, and limit that produced it | | Request body isn’t a JSON object at all | Query must be a JSON object | | from is missing or not a string | Missing or invalid \from` field` |

On the REST and CLI surfaces, malformed JSON that never reaches the query engine at all (not valid JSON, or not a JSON object) returns an actual HTTP 422 instead of the JSON error body above.

Tenant scoping and brand isolation

Every query is automatically restricted to the resolved brand. This happens server-side on every request, regardless of what’s in where - there’s no way to request another brand’s rows through this tool.

The optional top-level account selector is resolved inside the already-authorized brand. An unknown name or id returns a structured unknown_account error listing the valid accounts and saved groups; it never falls back to all accounts. See Multiple connected accounts for how the same scope works across the dashboard and other agent tools.

Semantics to know before you query

A handful of facts apply across many tables in this DSL and are easy to get wrong:

• There’s no default time window. A query spans all synced history for the table unless you add a where filter on its date (or equivalent) column yourself. See Dates and comparisons.
• Rates are always 0-1 fractions in this DSL, never 0-100 percentages: open_rate, click_rate, ctr, conversion_rate, and so on.
• Conversions are fractional on every ad-platform table (daily_channel_summary, campaigns, ads, adset_performance, and every google_* table). Google’s data-driven attribution can credit a low-volume day with something like 0.37 conversions - the DSL never floors or rounds this to an integer.
• “revenue” means something different on almost every table - check which one before you use it. See Revenue accounting and the notes in each table below; the short version:
  • daily_channel_summary.revenue is gross Shopify store revenue, before cross-period refunds. For refund-correct store net - and for any MER math - use the store_revenue_net table instead, which reconciles with get_performance account revenue.
  • campaigns.revenue and ads.revenue are platform-attributed ad conversion value, not Shopify store revenue.
  • daily_product_sales.revenue is net of discounts but gross of refunds - use net_revenue for the true net.
  • country_daily_sales.revenue is already net of refunds and excludes shipping.
  • email_campaigns, email_flow_daily, and email_flow_messages .revenue is Klaviyo-attributed conversion revenue.
• cost_micros on every Google table is currency times 1,000,000 - divide by 1,000,000 for a normal currency amount.
• Dated, FX-enabled monetary measures are returned in the brand’s base currency. The DSL converts only fields whose table declares both a monetary measure and a reporting date, using each row’s historical exchange rate before aggregation. Undated structural amounts remain in the source account’s native currency. In particular, adsets.daily_budget, adsets.lifetime_budget, and adsets.bid_amount are not FX-converted; do not compare or aggregate them across mixed-currency accounts without first scoping or grouping by account.
• Several ads columns (landing_page_views, video_3_sec_views, thruplays, and the diagnostic ranking fields) are nullable on purpose: null means “not captured for this row yet,” never a true zero.
• net_revenue and other difference or computed-only columns can’t be used in group_by - they’re output measures, not dimensions, per the errors above.
• select, where, order_by, and group_by column names must all come from the same table’s list below - there’s no cross-table join support in v1.

Table reference

“Computed” below means the column is derived from other columns in the same row and is never stored on its own - you can still select, filter, and order by it. “Difference” means a computed a - b column, summed on each side before subtracting when you aggregate. A column marked “recomputed under group_by” is a real stored value per row, but gets rebuilt from summed base measures rather than summed directly once you group.

daily_channel_summary

Cross-platform daily rollup. One row per brand, date, channel, and sub-channel - the table behind Kreel’s ~500-token cross-platform summary.

| Column | Type | Notes | |---|---|---| | date | Date | Calendar day in the brand’s reporting timezone. | | channel | Text (enum) | One of meta_ads, google_ads, tiktok_ads, klaviyo_email, klaviyo_sms, shopify_store. Only channels with a live connection appear. | | sub_channel | Text, nullable | For shopify_store: the normalized Shopify sales channel (web, pos, and so on) - empty string means the source name wasn’t set, null for rows materialized before this split existed. For tiktok_ads: 'gmv_max' (spend on GMV Max/Shop campaigns, attributing orders without ad engagement - keep it out of click-based efficiency comparisons) or 'unknown' (a TikTok campaign Kreel couldn’t classify); null still means “not TikTok/not channel-attributable,” distinct from TikTok’s own 'unknown' value. Always null for every other channel. | | spend | Decimal | | | revenue | Decimal | Gross Shopify revenue, before cross-period refunds - a cached figure, not the refund-correct one. Selecting it adds a notice pointing you to get_performance for the refund-correct net; for the same net inside the DSL, use the store_revenue_net table. | | orders | Integer | | | impressions | Integer, nullable | Ad-platform channels only; null for email and organic. | | clicks | Integer, nullable | Same as impressions. | | conversions | Decimal | Fractional - never round this down. | | roas | Computed | revenue / spend. | | cpa | Computed | spend / orders. | | ctr | Computed | clicks / impressions. | | currency | Text | The brand’s base currency, three-letter code. |

ga4_site, ga4_traffic, ga4_landing_pages, ga4_events

Google Analytics 4 website data for the brand’s one selected property and web stream, one row per property-calendar day and breakdown. All four carry date, connected_account_id, property_id, and stream_id.

| Table | Breakdown columns | Measures | |---|---|---| | ga4_site | none | sessions, engaged_sessions, ecommerce_purchases, purchase_sessions, purchase_revenue, currency | | ga4_traffic | channel_group, source_medium, campaign_id, campaign_name, device_category | as ga4_site | | ga4_landing_pages | landing_page, device_category, channel_group, source_medium | sessions, engaged_sessions, ecommerce_purchases, purchase_sessions | | ga4_events | event_name, device_category, channel_group | event_count (only view_item, add_to_cart, begin_checkout, purchase) |

engagement_rate (engaged sessions ÷ sessions) and purchase_session_rate (purchase sessions ÷ sessions) are computed, and are recomputed from summed measures under group_by, never averaged. purchase_sessions is null when the property had no purchase key event, so the rate is null (unavailable), not 0. purchase_revenue stays in the property currency, unconverted, and is never additive with store or ad revenue. Event counts are independent volumes, not a funnel. For coverage, thresholding, and warnings, use get_performance(platform="ga4").

store_revenue_net

The refund-correct daily store revenue series - the same number get_performance(entity_type='account') reports, exposed for ad-hoc DSL slicing. Use this, not sum(daily_channel_summary.revenue), whenever you need store net or MER math: daily_channel_summary.revenue is gross and mixes store revenue with attributed ad/email revenue in one column, so summing it across channels double-counts.

This is a virtual table - it has no stored rows. Each row is computed at query time from raw orders and refunds, one row per reporting-timezone day, so two rules differ from every other table:

• It requires a bounded date filter: pass {"date": {"gte": "...", "lte": "..."}} for a range or {"date": {"eq": "..."}} for a single day, not both together. A missing, one-sided, or mixed (eq combined with gte/lte) date filter is rejected, because the cross-period refund correction is defined relative to the window’s start (see Revenue accounting). It’s also the only table where date is the sole filter - filtering on any other column is rejected.
• It does not support group_by (each row is already one day), and order_by, limit, and cursor all work as usual.

It honors the brand’s revenue basis and sales-channel policy exactly like the account revenue surface.

A day that carries a cross-period refund but no orders of its own still appears, as a negative-net row: gross_revenue and orders are 0 and net_revenue is the negated refund. That way SUM(net_revenue) over the window equals get_performance account net exactly - no refund-only day is silently dropped.

| Column | Type | Notes | |---|---|---| | date | Date | Calendar day in the brand’s reporting timezone. | | gross_revenue | Decimal, rounded to 2dp | State-based order revenue for orders created that day, before the cross-period refund correction. 0 on a refund-only day. | | cross_period_refunds | Decimal, rounded to 2dp | Refunds landing that day on orders created before the window start - the window-relative correction. | | net_revenue | Decimal, rounded to 2dp | The headline figure: gross_revenue - cross_period_refunds. Reconciles digit-for-digit with get_performance account revenue, including refund-only days that net negative. | | orders | Integer | Orders created that day counted under the brand’s channel policy. | | average_order_value | Decimal, rounded to 2dp | net_revenue / orders for the day. |

campaigns

Cross-platform campaign performance. Daily, per platform, per campaign.

| Column | Type | Notes | |---|---|---| | date | Date | | | platform | Text (enum) | meta, google, or tiktok. | | platform_campaign_id | Text | The platform’s own campaign id. | | campaign_name | Text | | | status | Text | Normalized to uppercase, e.g. ACTIVE, PAUSED. | | objective | Text | Meta’s objective (OUTCOME_SALES, OUTCOME_TRAFFIC, and so on) or Google’s advertising channel type (SEARCH, SHOPPING, PERFORMANCE_MAX, and so on). Use it to tell a non-sales campaign apart from a sales one before judging CTR or ROAS. | | spend | Decimal | | | impressions | Integer | | | clicks | Integer | | | conversions | Decimal | Fractional under Google’s attribution. | | conversion_value | Decimal | The raw ad-platform conversion value. | | revenue | Computed | Alias for conversion_value. This is platform-attributed ad conversion value, not Shopify store revenue. | | roas | Computed | conversion_value / spend. | | cpa | Computed | spend / conversions. | | ctr | Computed | clicks / impressions. | | search_impression_share | Decimal, nullable | Google only, 0-1 fraction. Null for campaign types that don’t report it. | | search_budget_lost_is | Decimal, nullable | Google only. | | search_rank_lost_is | Decimal, nullable | Google only. | | search_top_is | Decimal, nullable | Google only. | | search_abs_top_is | Decimal, nullable | Google only. |

email_campaigns

Klaviyo campaign performance, one row per campaign per day. Filtered to email-channel campaigns.

| Column | Type | Notes | |---|---|---| | date | Date | Send date. | | source_type | Text (enum) | Always campaign on this table. | | source_id | Text | Klaviyo campaign id. | | source_name | Text, nullable | Campaign name. | | recipients | Integer | | | delivered | Integer | | | opens | Integer | Total open events, not unique recipients. | | unique_opens | Integer | Unique-recipient count, the numerator for open_rate. | | clicks | Integer | Total click events, not unique recipients. | | unique_clicks | Integer | Unique-recipient count, the numerator for click_rate. | | bounced | Integer | | | unsubscribes | Integer | | | spam_complaints | Integer | | | conversions | Integer | | | revenue | Decimal | Klaviyo-attributed conversion revenue. | | open_rate | Computed | unique_opens / delivered. A 0-1 fraction. | | click_rate | Computed | unique_clicks / delivered. A 0-1 fraction. |

Supports the name_contains and tag special filters.

email_flow_daily

Klaviyo flow performance, one row per flow per day. Same shape as email_campaigns, except it doesn’t expose bounced or spam_complaints - Klaviyo’s flow reporting doesn’t measure them, so they’re left out entirely rather than shown as a measured zero.

Columns: date, source_type (always flow), source_id, source_name, recipients, delivered, opens, unique_opens, clicks, unique_clicks, unsubscribes, conversions, revenue, open_rate (computed, 0-1 fraction), click_rate (computed, 0-1 fraction).

Supports the name_contains and tag special filters.

email_flow_messages

Performance for one individual message or step inside a flow, per day.

| Column | Type | Notes | |---|---|---| | date | Date | | | klaviyo_flow_id | Text | | | klaviyo_flow_message_id | Text | | | flow_message_name | Text | | | recipients | Integer | | | delivered | Integer | | | opens | Integer | Total events. | | unique_opens | Integer | | | clicks | Integer | Total events. | | unique_clicks | Integer | | | unsubscribes | Integer | | | conversions | Integer | | | revenue | Decimal | | | open_rate | Computed | unique_opens / delivered, a 0-1 fraction. | | click_rate | Computed | unique_clicks / delivered, a 0-1 fraction. | | revenue_per_recipient | Computed | revenue / recipients. |

orders

Shopify orders.

| Column | Type | Notes | |---|---|---| | id | Text | Internal row id. | | shopify_id | Text | Shopify’s own order id. | | shopify_order_number | Text | Human-readable order number, e.g. #1001. | | total_price | Decimal | State-based: the order’s current total after any edits or refunds, not Shopify’s event-based “Total Sales” figure. See Revenue accounting. | | subtotal_price | Decimal | | | financial_status | Text | e.g. paid, pending, refunded. | | fulfillment_status | Text | e.g. fulfilled, partial, unfulfilled. | | order_created_at | Datetime | | | updated_at | Datetime | | | currency | Text | | | customer_id | Text, nullable | Internal id, joins to customers.id - not Shopify’s own customer id. |

customers

Shopify customers. Personal fields such as email, name, phone, and address are excluded entirely.

| Column | Type | Notes | |---|---|---| | id | Text | | | shopify_id | Text | | | orders_count | Integer | From Shopify. | | total_spent | Decimal | From Shopify. | | customer_created_at | Datetime | | | updated_at | Datetime | | | state | Text | enabled, disabled, or invited. |

products

Shopify product catalog. A smaller default page than the rest of the DSL - see Limits.

| Column | Type | Notes | |---|---|---| | id | Text | Not returned by default. | | shopify_id | Text | Returned by default. | | title | Text | Returned by default. | | vendor | Text | Returned by default. | | product_type | Text | Returned by default. | | status | Text | active, draft, or archived. Returned by default. | | product_created_at | Datetime | Not returned by default. | | updated_at | Datetime | Not returned by default. |

adsets

Meta ad set metadata - not daily performance. For that, use ads, or get_performance on Data tools.

| Column | Type | Notes | |---|---|---| | id | Text | | | meta_id | Text | Meta’s ad set id. | | campaign_id | Text | Meta’s parent campaign id. | | ad_account_id | Text | | | name | Text | | | status | Text | | | effective_status | Text | | | daily_budget | Decimal | Undated structural value in the Meta account’s native currency; not FX-converted. | | lifetime_budget | Decimal | Undated structural value in the Meta account’s native currency; not FX-converted. | | bid_amount | Decimal | Undated structural value in the Meta account’s native currency; not FX-converted. | | bid_strategy | Text | e.g. LOWEST_COST_WITHOUT_CAP, COST_CAP. | | optimization_goal | Text | e.g. OFFSITE_CONVERSIONS, LINK_CLICKS. | | created_time | Datetime | | | updated_time | Datetime | | | start_time | Datetime | | | end_time | Datetime | |

ads

Meta ad performance, one row per ad per day.

| Column | Type | Notes | |---|---|---| | date | Date | | | ad_account_id | Text | | | campaign_id | Text | | | adset_id | Text | | | ad_id | Text | | | ad_name | Text | | | spend | Decimal | | | impressions | Integer | | | reach | Integer | Unique users reached. | | frequency | Decimal, recomputed under group_by | impressions / reach. | | clicks | Integer | | | link_clicks | Integer | Clicks to the destination specifically. | | purchases | Integer | | | purchase_value | Decimal | | | revenue | Computed | Alias for purchase_value. Platform-attributed purchase value, not Shopify store revenue. | | add_to_cart | Integer | Meta-attributed standard event count. | | add_to_cart_value | Decimal | | | initiate_checkout | Integer | | | initiate_checkout_value | Decimal | | | leads | Integer | | | lead_value | Decimal | | | video_views | Integer | Meta’s 30-second watch count - not a 3-second view count. | | video_views_p25, video_views_p50, video_views_p75, video_views_p95, video_views_p100 | Integer | Percentile video-completion counts. | | landing_page_views | Integer, nullable | Null means the row was synced before this field was captured, not a measured zero. History backfills on the next re-sync. | | video_3_sec_views | Integer, nullable | The 3-second view count used for hook rate. Same null-versus-zero caveat as landing_page_views. | | thruplays | Integer, nullable | Same caveat. | | video_avg_watch_time_sec | Decimal, nullable | A per-day average, not a running total - never sum this. Falls back to an unweighted average of daily averages under group_by. | | quality_ranking | Text, nullable | Meta’s diagnostic ranking, e.g. ABOVE_AVERAGE, AVERAGE, BELOW_AVERAGE_10. Null below roughly 500 impressions. | | engagement_rate_ranking | Text, nullable | Same semantics as quality_ranking. | | conversion_rate_ranking | Text, nullable | Same semantics as quality_ranking. | | attribution_setting | Text, nullable | The attribution window the conversions were measured under, e.g. 7d_click, 1d_view. | | cpm | Decimal, recomputed under group_by | spend / impressions * 1000. | | cpc | Decimal, recomputed under group_by | spend / clicks. | | ctr | Computed | clicks / impressions. | | roas | Computed | purchase_value / spend. | | cpa | Computed | spend / purchases. | | conversion_rate | Decimal, recomputed under group_by | purchases / clicks. |

creatives

Meta and TikTok creative asset metadata, enriched with window-scoped ad-grain performance for whichever platform you name. Not media URLs - use the get_creatives tool on Data tools for those.

Requires where.platform - {"eq": "meta"} or {"eq": "tiktok"}. A query with no platform filter is rejected: Meta and TikTok creative performance are never blended in one result.

| Column | Type | Notes | |---|---|---| | platform | Text (enum), required in where | meta or tiktok. | | creative_id | Text | Meta: the Meta creative id. TikTok: the ad’s video_id, or its first image_id when there’s no video - an asset reused across ads shares one creative_id. | | connected_account_id | Text | | | video_duration_sec | Integer, nullable | | | aspect_ratio | Text, nullable | | | asset_hash | Text, nullable | | | media_type | Text, nullable | image, video, or carousel. | | synced_at | Datetime | | | updated_at | Datetime | | | impressions | Integer | Summed over the date window (see below). | | clicks | Integer | | | purchases | Integer | | | video_3_sec_views | Integer, nullable | Meta only - always NULL (never 0) on platform="tiktok" rows, since TikTok doesn’t report this at ad grain. | | thruplays | Integer, nullable | Meta only - same TikTok caveat as video_3_sec_views. | | spend | Decimal, money | Converted to the brand’s base currency at each contributing day’s rate. | | revenue | Decimal, money | Purchase/conversion value (Meta purchase_value, TikTok’s TikTok-attributed value) - platform-attributed, not Shopify store revenue. | | roas | Computed | revenue / spend. | | ctr | Computed | clicks / impressions. | | hook_rate, thumbstop | Computed | Both video_3_sec_views / impressions (two names for the same Meta-only ratio; null on TikTok rows). | | thruplay_hold_rate | Computed | thruplays / video_3_sec_views (Meta only). |

The default selection (when select is omitted) is metadata only - aspect_ratio, asset_hash, creative_id, media_type, platform, synced_at, updated_at, video_duration_sec - not the performance columns; name them explicitly in select to get them. where.date follows the same rule as ad_naming below: eq alone or both gte and lte, defaulting to the trailing 7 days when omitted. A filter on any performance column (or a date filter) describes each creative’s whole-window total, not a single day or ad.

creative_intelligence

Kreel’s AI-generated creative analysis, one row per creative, shared across Meta and TikTok (one taxonomy, discriminated by platform). Every text field is best-effort and can be null until the tagger has processed that creative.

| Column | Type | Notes | |---|---|---| | platform | Text (enum) | meta or tiktok. | | ad_meta_id | Text | Links to ads.ad_id for Meta rows; the TikTok creative id (video_id/image_id) for TikTok rows. | | description | Text, nullable | Short plain-language summary. | | hook_summary | Text, nullable | The creative’s hook, in one line. | | angle | Text, nullable | The marketing or persuasion angle. | | first_3_seconds | Text, nullable | What happens in the first 0-3 seconds. | | spoken_or_onscreen_text | Text, nullable | Transcript, captions, or on-screen copy. | | pacing | Text, nullable | Cut rhythm, energy, and length feel. | | model | Text, nullable | The model used, e.g. gemini-3.1-flash-lite. | | generated_at | Datetime, nullable | | | tagger_version | Integer, nullable | The tagger prompt version that wrote the row; null means version 1. Rows behind the current version are re-tagged within a daily budget. |

creative_tags

Kreel’s structured creative tags, one row per ad, shared across Meta and TikTok. where.platform (eq) is required, so Meta and TikTok rows never blend. Tags are ad grain, so this table does not join the asset-grain creatives table; to see performance by a tag, use get_creatives(group_by=[...]).

| Column | Type | Notes | |---|---|---| | platform | Text (enum) | meta or tiktok. Required in where. | | ad_meta_id | Text | The platform ad id (Meta ad id or TikTok ad id). | | connected_account_id | Text | The ad account the ad belongs to. | | hook_type, format_type | Text, nullable | The hook and visual format vocabularies. | | angle_category | Text, nullable | pain_point, desire_aspiration, social_proof, authority_expertise, value_price, quality_ingredients, convenience, comparison, novelty_innovation, identity_values, urgency_scarcity, gifting, other. | | offer_type | Text, nullable | none, percent_discount, amount_discount, bundle, free_shipping, free_gift, bogo, subscription_discount, limited_time_sale, free_sample_trial, other. | | persona | Text, nullable | general, women, men, parents, young_adults, professionals, seniors, fitness_enthusiasts, beauty_enthusiasts, gift_buyers, budget_shoppers, premium_shoppers, other. | | talent_type | Text, nullable | none, founder, ugc_creator, influencer, expert, model_actor, customer, voiceover_only, other. | | cta_type | Text, nullable | The call to action the creative itself makes, not the platform button: none, shop_now, buy_now, learn_more, get_offer, sign_up, subscribe, take_quiz, other. | | visual_style | Text, nullable | lo_fi_native, polished_studio, graphic_design, text_overlay_heavy, split_screen, green_screen, animation_motion, meme, other. | | seasonality | Text, nullable | evergreen, black_friday_cyber, christmas_holiday, new_year, valentines, mothers_day, fathers_day, summer, back_to_school, lunar_new_year, singles_day, other_seasonal. | | audience_temperature | Text, nullable | The hand-set audience override only; the effective temperature is on get_creatives. | | manually_edited | Boolean | True when a member set any field by hand. Hand-set fields survive re-tagging. | | ai_tagged_at | Datetime, nullable | When the tagger last wrote the row. |

creative_custom_tags

Values of the Brand’s custom tag dimensions (see set_creative_tag_schema), one row per ad and dimension. where.platform (eq) is required.

| Column | Type | Notes | |---|---|---| | platform | Text (enum) | meta or tiktok. Required in where. | | ad_meta_id | Text | The platform ad id. | | connected_account_id | Text | The ad account the ad belongs to. | | dimension_key | Text | The custom dimension’s key. | | value | Text, nullable | One of the dimension’s allowed values; null means untagged. | | manually_edited | Boolean | True when a member set the value by hand; the tagger never overwrites it. |

daily_product_sales

Shopify per-product daily sales.

| Column | Type | Notes | |---|---|---| | product_id | Text | | | sales_channel | Text | Normalized Shopify sales channel. Rows are split per channel - sum across channels for a channel-blind total. Rows written before this split existed carry an empty string. | | date | Datetime | Calendar day in the brand’s reporting timezone. | | units_sold | Integer | | | revenue | Decimal | Net of discounts (the amount actually paid) but gross of refunds. Selecting it adds a notice pointing to net_revenue for the true net. | | gross_revenue | Decimal | Pre-discount list total (quantity times original unit price). | | discount_amount | Decimal | gross_revenue - revenue. Free or gift-with-purchase lines show 0 net. | | refunded_revenue | Decimal | All in-window refunds for the product, before tax, bucketed by refund date. | | net_revenue | Difference | revenue - refunded_revenue. Cannot be used in group_by - it’s an output measure, not a stored dimension. | | tax_amount | Decimal | Post-discount line tax. Zero for orders synced before per-line tax capture. | | refunded_tax_amount | Decimal | The tax portion of refunds. | | included_tax_amount | Decimal | The portion of tax_amount already embedded in revenue, for tax-included orders. net_revenue isn’t tax-adjusted either way. | | materialized_at | Datetime | |

“revenue” is gross of refunds on this table

daily_product_sales.revenue is net of discounts but still includes refunded amounts. Order or rank on net_revenue, not revenue, whenever refunds matter to the result.

country_daily_sales

Shopify per-country daily sales. Built differently from daily_product_sales - its refund handling is the opposite.

| Column | Type | Notes | |---|---|---| | country | Text | Two-letter ISO country code. ZZ means no usable shipping or billing address was on the order - kept so rows still sum to store totals. | | sales_channel | Text | Same semantics as daily_product_sales.sales_channel. | | date | Datetime | Calendar day in the brand’s reporting timezone. | | orders | Integer | | | revenue | Decimal | State-based order total minus shipping, already net of refunds on orders created in the window. Unlike daily_product_sales.revenue, this figure is already refund-corrected. | | refunded_revenue | Decimal | Always 0 on this table. Cross-period refund netting is handled elsewhere at read time, not materialized here. | | shipping | Decimal | What revenue already subtracted. revenue + shipping is the order-total share for that row. | | tax_amount | Decimal | Supports a tax-excluded basis. | | shipping_tax_amount | Decimal | Tax embedded in both shipping and tax_amount. Zero until orders carry shipping-tax detail. | | materialized_at | Datetime | |

customers_rfm

An RFM (recency, frequency, monetary) projection of Shopify customers. Personal fields are excluded, same as customers.

| Column | Type | Notes | |---|---|---| | shopify_id | Text | | | orders_count | Integer | | | total_spent | Decimal | | | average_order_value | Decimal | | | first_order_date | Datetime | | | last_order_date | Datetime | | | rfm_recency_score | Integer | 1 to 5. | | rfm_frequency_score | Integer | 1 to 5. | | rfm_monetary_score | Integer | 1 to 5. | | rfm_segment | Text | e.g. champion, loyal, at_risk. | | rfm_calculated_at | Datetime | |

rfm_segment_summary

One row per RFM segment (the 11 fixed segments) from the latest weekly RFM run. Aggregates only: no customer, email or identity column exists. Each person is counted once across the Brand’s stores (email hash, else store-local id). No group_by and no account selection; filter and order on any column.

| Column | Type | Notes | |---|---|---| | segment | Text | e.g. champions, at_risk, cant_lose. | | customers | Integer | Scored identities in the segment. Sums to all scored identities. | | share | Decimal, ratio | customers over all scored identities (0-1). | | net_revenue_365d | Decimal | Trailing 365 Brand-calendar days ending yesterday, net of refunds on those orders, Base currency. Null when it cannot be converted, never 0. | | revenue_share | Decimal, ratio | The segment’s share of all scored identities’ net_revenue_365d. | | orders_365d | Integer | Orders in the same window. | | aov | Decimal | net_revenue_365d / orders_365d. | | median_days_since_last_order | Decimal | Median whole days since each identity’s latest order. | | calculated_at | Datetime | The latest RFM run. | | week_start | Date | That run’s Brand-calendar ISO week (Monday). |

rfm_migration

Stored week-over-week segment migration: counts of identities that moved between segments. Aggregates only. No group_by and no account selection.

| Column | Type | Notes | |---|---|---| | week_start | Date | The current snapshot’s week. | | horizon_weeks | Integer | 1, 4 or 13: compared with the snapshot exactly this many weeks earlier. | | from_segment | Text | Prior segment, or new for identities absent from the prior snapshot. | | to_segment | Text | Current segment, or not_scored for identities absent now. | | customers | Integer | Rows grouped by from_segment (excluding new) sum to the prior segment size. |

A week with no snapshot exactly horizon_weeks earlier has no rows for that horizon; another week is never substituted.

at_risk_summary

Overdue customers of the latest weekly RFM run, flattened into aggregate rows. A customer is overdue when the whole Brand-calendar days since their last order exceed the p75 of time between orders for their first product, or the store’s when that product has fewer than 30 gaps; one-time buyers use the store’s first-to-second interval. Past the p90, or past the Brand lapse threshold, the customer is lapsed; RFM new_customers are never overdue. Aggregates only: no customer, email or identity column exists, and the per-customer list is not available through MCP. No group_by and no account selection; filter and order on any column.

| Column | Type | Notes | |---|---|---| | dimension | Text | store_interval, status, status_repeat, status_one_time, first_product or channel. | | key | Text | repeat / first_second for store intervals, <account>:<product> for a first product, the ADR-0027 channel for a channel. | | label | Text | The first product’s title. | | n, p75_days, p90_days | Integer, Decimal | Gaps between orders in whole Brand-calendar days. Percentiles are null below two gaps. | | falls_back | Boolean | A first product with n under 30: its customers are judged on the store’s repeat gaps. | | status | Text | not_due, overdue, lapsed or insufficient_history. | | customers | Integer | Identities in the status. The status rows sum to the RFM population. | | net_revenue_365d | Decimal | Overdue rows only: trailing 365 Brand-calendar days ending yesterday, net of refunds, Base currency. Null when it cannot be converted, never 0. | | revenue_at_risk | Decimal | Overdue rows only: an estimate from trailing history, not a forecast. Lifetime net revenue ÷ max(1, tenure in years). | | week_start | Date | The RFM run’s Brand-calendar ISO week (Monday). |

customer_cohorts

Monthly acquisition-cohort retention. A read-only aggregate with no personal data.

| Column | Type | Notes | |---|---|---| | cohort_month | Date | First day of the acquisition month. | | snapshot_month | Date | First day of the snapshot month. | | customer_count | Integer | | | orders | Integer | | | revenue | Decimal | | | retention_rate | Decimal, ratio-like | The fraction of the cohort that ordered again in snapshot_month. Don’t sum this across snapshots - it falls back to an average under group_by. |

email_flows

Klaviyo flow metadata only, no performance metrics. This table intentionally excludes Klaviyo’s own rolling 30-day metrics, so a query never reads a rolling snapshot as if it matched the requested window. Use email_flow_daily for period-accurate performance.

| Column | Type | Notes | |---|---|---| | klaviyo_id | Text | | | name | Text | | | status | Text | draft, live, or manual. | | archived | Boolean | | | trigger_type | Text | metric, list, segment, date, or price_drop. | | action_count | Integer | Number of actions in the flow. | | email_count | Integer | Number of email actions. | | sms_count | Integer | Number of SMS actions. |

email_lists

Klaviyo lists.

| Column | Type | Notes | |---|---|---| | klaviyo_id | Text | | | name | Text | | | list_type | Text, nullable | static or dynamic. | | opt_in_process | Text, nullable | single_opt_in or double_opt_in. | | profile_count | Integer | | | opted_in_count | Integer, nullable | | | subscribed_count | Integer, nullable | | | created_time | Datetime | |

email_segments

Klaviyo segments.

| Column | Type | Notes | |---|---|---| | klaviyo_id | Text | | | name | Text | | | profile_count | Integer | | | is_starred | Boolean | | | is_active | Boolean | | | created_time | Datetime | |

email_campaign_details

Klaviyo campaign metadata only, no performance metrics. Use email_campaigns for daily performance.

| Column | Type | Notes | |---|---|---| | klaviyo_id | Text | | | name | Text | | | campaign_type | Text | email or sms. | | status | Text | draft, scheduled, sending, sent, or cancelled. | | subject | Text, nullable | | | preview_text | Text, nullable | | | from_email | Text, nullable | | | from_name | Text, nullable | | | send_strategy | Text, nullable | immediate or smart_send_time. | | send_time | Datetime, nullable | | | created_time | Datetime | | | updated_time | Datetime | |

inventory_levels

Shopify inventory snapshots, one row per variant per location per day. Kept for 90 days.

| Column | Type | Notes | |---|---|---| | variant_id | Text | | | location_id | Text | | | available | Integer | | | snapshot_date | Datetime | |

google_search_terms

Google Ads search terms. Google-account scoped and fail-closed - see Tenant scoping and brand isolation.

| Column | Type | Notes | |---|---|---| | campaign_id | Text | | | ad_group_id | Text | | | search_term | Text | | | date | Date | First day of the month when grain is month. | | grain | Text | day (kept 30 days) or month (rolled up, kept 24 months). | | impressions | Integer | | | clicks | Integer | | | cost_micros | Integer | Currency times 1,000,000. Divide by 1,000,000 for a normal currency amount. | | conversions | Decimal | Fractional. | | ctr | Computed | clicks / impressions. |

google_keywords

Google Ads keyword performance. Google-account scoped and fail-closed.

| Column | Type | Notes | |---|---|---| | campaign_id | Text | | | ad_group_id | Text | | | criterion_id | Text | | | keyword_text | Text, nullable | | | match_type | Text, nullable | | | status | Text, nullable | | | quality_score | Integer, nullable | 1 to 10. Often absent. | | date | Date | | | grain | Text | day or month, same rollup lifecycle as search terms. | | impressions | Integer | | | clicks | Integer | | | cost_micros | Integer | Currency times 1,000,000. | | conversions | Decimal | Fractional. | | conversion_value | Decimal | | | ctr | Computed | clicks / impressions. |

google_ads

Google Ads ad-level performance. No grain column - ad rows are never rolled up to month. Google-account scoped and fail-closed.

| Column | Type | Notes | |---|---|---| | campaign_id | Text, nullable | | | ad_group_id | Text | | | ad_id | Text | | | ad_type | Text, nullable | | | ad_strength | Text, nullable | | | status | Text, nullable | | | date | Date | | | impressions | Integer | | | clicks | Integer | | | cost_micros | Integer | Currency times 1,000,000. | | conversions | Decimal | Fractional. | | conversion_value | Decimal | | | ctr | Computed | clicks / impressions. |

google_products

Google Shopping and Performance Max product performance. Google-account scoped and fail-closed, and only populated while the brand’s Google Shopping/Performance Max sync is enabled.

| Column | Type | Notes | |---|---|---| | campaign_id | Text, nullable | Null when more than one campaign served the item that day - Kreel sums the metrics across campaigns and keeps a single campaign_id only when exactly one campaign served it. | | item_id | Text | | | title | Text, nullable | | | brand | Text, nullable | | | date | Date | | | grain | Text | day or month. | | impressions | Integer | | | clicks | Integer | | | cost_micros | Integer | Currency times 1,000,000. | | conversions | Decimal | Fractional. | | conversion_value | Decimal | | | ctr | Computed | clicks / impressions. |

google_asset_groups

Google Performance Max asset-group performance. Same Google-account fail-closed scoping and sync gating as google_products.

| Column | Type | Notes | |---|---|---| | campaign_id | Text | | | asset_group_id | Text | | | asset_group_name | Text, nullable | | | status | Text, nullable | | | date | Date | | | grain | Text | day or month. | | impressions | Integer | | | clicks | Integer | | | cost_micros | Integer | Currency times 1,000,000. | | conversions | Decimal | Fractional. | | conversion_value | Decimal | | | ctr | Computed | clicks / impressions. |

adset_performance

Cross-platform ad-set and ad-group performance. Google-populated today; Meta ad sets are planned for a future phase, so filter platform to google for reliable results now. TikTok ad groups don’t write to this unified table either - use the tiktok_adgroups (metadata) and tiktok_ads (performance, summed to ad-group grain) tables below instead, or get_performance(platform="tiktok", entity_type="adset").

| Column | Type | Notes | |---|---|---| | date | Date | | | platform | Text (enum) | meta, google, or tiktok. | | platform_campaign_id | Text, nullable | | | platform_adset_id | Text | | | adset_name | Text, nullable | | | status | Text, nullable | | | spend | Decimal | | | impressions | Integer | | | clicks | Integer | | | conversions | Decimal | Fractional. | | conversion_value | Decimal | | | roas | Computed | conversion_value / spend. | | cpa | Computed | spend / conversions. | | ctr | Computed | clicks / impressions. |

tiktok_ads

TikTok ad-grain daily performance, one row per ad per day, with campaign and ad-group names denormalized onto every row.

| Column | Type | Notes | |---|---|---| | date | Date | | | connected_account_id | Text | | | advertiser_id | Text | TikTok’s own advertiser id - one connection can span several advertisers. | | campaign_id | Text | | | campaign_name | Text | | | adgroup_id | Text | | | adgroup_name | Text | | | ad_id | Text | | | ad_name | Text | | | spend | Decimal | Converted from the advertiser’s native currency to the brand’s base currency. | | impressions | Integer | | | clicks | Integer | | | purchases | Decimal | TikTok’s complete_payment (website purchases) - fractional, never int() this. | | purchase_value | Decimal | TikTok’s total_complete_payment_rate - a value despite the name. | | revenue | Computed | Alias for purchase_value. | | cta_conversions | Decimal | Optimization-goal conversions, click-through attribution - not purchases. | | vta_conversions | Decimal | Optimization-goal conversions, view-through attribution - not purchases. | | roas | Computed | purchase_value / spend. | | cpa | Computed | spend / purchases. | | ctr | Computed | clicks / impressions. | | cpc | Computed | spend / clicks. | | conversion_rate | Computed | purchases / clicks. |

A null on any of these means TikTok reported the metric as unavailable ("-") - never a measured 0.

tiktok_campaigns

TikTok campaign metadata only, no performance metrics - use tiktok_ads (summed by campaign_id) for that.

| Column | Type | Notes | |---|---|---| | id, connected_account_id | Text | | | advertiser_id, campaign_id | Text | | | name | Text | | | objective_type | Text | Raw TikTok objective, e.g. WEB_CONVERSIONS, PRODUCT_SALES, SHOP. | | campaign_type | Text | e.g. REGULAR_CAMPAIGN. Combine with objective_type to identify GMV Max campaigns. | | operation_status | Text | The advertiser’s own on/off switch. | | secondary_status | Text | TikTok’s effective delivery state; CAMPAIGN_STATUS_DELETE means deleted (history is retained). | | is_deleted | Boolean | | | budget | Decimal | | | budget_mode | Text | | | created_time, modified_time | Datetime | |

tiktok_adgroups

TikTok ad-group metadata, including the attribution windows TikTok locks per ad group.

| Column | Type | Notes | |---|---|---| | id, connected_account_id | Text | | | advertiser_id, campaign_id, adgroup_id | Text | | | name | Text | | | operation_status, secondary_status | Text | | | is_deleted | Boolean | | | optimization_goal, optimization_event, promotion_type | Text | | | budget | Decimal | | | budget_mode | Text | | | click_attribution_window, view_attribution_window, engaged_view_attribution_window | Text, nullable | e.g. SEVEN_DAYS, ONE_DAY, OFF. Null when TikTok didn’t report a setting - never inferred. | | attribution_event_count | Text, nullable | EVERY or ONCE - whether repeat conversions from the same user count. | | created_time, modified_time | Datetime | |

ad_naming

Meta ad names, parsed against the brand’s configured naming convention (a delimiter and up to 12 ordered field names, set in the dashboard), joined to the same performance measures as creatives. TikTok naming is not covered by this table.

| Column | Type | Notes | |---|---|---| | ad_id, connected_account_id, ad_name, campaign_id, adset_id, status | Text | | | naming_matched | Boolean | false when the ad name didn’t parse against the brand’s schema, or when no schema is configured at all (the query then carries a notice: “No naming schema is configured for this Brand”). | | (configured fields) | Text | Whatever field names the brand’s naming schema defines (for example hook, angle, format) become first-class selectable/filterable/groupable columns dynamically - they aren’t a fixed set. | | impressions, clicks, purchases, video_3_sec_views, thruplays | Integer, nullable | Same semantics as on creatives. | | spend, revenue | Decimal, money | Same semantics as on creatives. | | hook_rate, thumbstop, thruplay_hold_rate, ctr, roas | Computed | Same formulas as on creatives. |

default_select is (ad_id, ad_name, naming_matched, spend, roas). where.date accepts eq alone or both gte and lte; omit it and the query defaults to the trailing 7 days. Reserved names that can never be a brand’s own naming field include ad_id, spend, revenue, roas, ctr, date, platform, brand, account, and id - the dashboard’s schema editor validates this at save time.

Need help?

Email support@kreel.ai if a query returns something you can’t explain.

Repurchase behavior and product paths

Three virtual Shopify tables compute observed behavior from retained orders through MCP query, REST GET /api/v1/query?dsl=..., and the CLI query command. They require no materialization. Use canonical account-qualified product IDs; browse the result without a product filter to discover them. kreel://schema lists every column.

{
  "from": "repurchase_metrics",
  "where": {
    "product_id": {"eq": "gid://shopify/Product/123"},
    "order_n": {"eq": 1},
    "cohort_date": {"gte": "2026-01-01", "lte": "2026-01-31"}
  },
  "select": ["account_id", "product_id", "cohort_customers", "repeat_rate", "median_days", "days_0_7", "days_7_30", "days_30_90", "days_90_plus", "ltv", "downstream_ltv"]
}

repurchase_metrics has one row per first-product cohort and order_n, plus an overall cohort with null account_id and product_id. Filter account_id too when a product ID occurs in several stores. Multi-product first orders belong to each constituent cohort, so cohorts are non-additive. For N=1, repeat_rate is customers placing a second order divided by cohort customers. For later N it is customers_reaching_next / customers_reaching_n. median_days and interval counts use completed N→N+1 intervals only, measured in UTC seconds / 86,400. Buckets are [0,7), [7,30), [30,90), and [90,∞). No completed interval yields null median and zero counts; zero denominators yield null rates. N=1 is always emitted for a nonempty cohort.

{
  "from": "product_journey",
  "where": {"first_product_id": {"eq": "gid://shopify/Product/123"}},
  "select": ["first_account_id", "first_product_id", "second_account_id", "second_product_id", "second_product_title", "transition_customers", "repeat_customers", "share"],
  "order_by": [{"field": "transition_customers", "dir": "desc"}],
  "limit": 10
}

product_journey counts each product in the customer’s actual second order once. share divides by all repeat customers in the first-product cohort, including those whose second basket is empty or excluded. Multi-product second orders can make shares sum above one. An empty second basket never promotes the third order.

{
  "from": "market_basket",
  "where": {"date": {"gte": "2026-01-01", "lte": "2026-01-31"}},
  "select": ["a_account_id", "a_product_id", "b_account_id", "b_product_id", "pair_orders", "a_orders", "b_orders", "eligible_orders", "support", "confidence_a_to_b", "confidence_b_to_a"],
  "limit": 20
}

market_basket returns unordered distinct canonical pairs. Support is pair_orders / eligible_orders; directional confidence divides by a_orders or b_orders. All eligible orders in the window contribute to support’s denominator, including guests and empty baskets. Canonical siblings and repeated variants count once; self-pairs are omitted. Pair tables default to descending customer/order count with canonical account/product IDs breaking ties. Output filters, sorting, limits, and signed cursors apply after complete aggregation. group_by is unsupported.

cohort_date selects customers by their first eligible retained order, while later orders use all retained selected history. Basket date selects order dates. Both are filter-only, inclusive Brand-timezone dates: use eq alone or both gte and lte, or omit the filter for retained history. Accounts and the Brand sales-channel policy apply before sequencing; voided and cancelled orders are excluded. Equal order timestamps sort by connected account ID, then Shopify order ID. Customers use email hash across stores, otherwise store-local customer ID. Unidentified customers are skipped for cohorts and journeys, but their baskets are retained.

Product membership honors canonical merges, isolation, inclusion/exclusion and tag rules. Effective line quantity is current_quantity + refunded_units, clamped to original quantity; legacy lines without current quantity use original quantity. Zero-effective lines are excluded. Refunds preserve purchase events; edits can change membership. Missing/deleted catalog products remain account-qualified and are disclosed as unclustered.

ltv is observed current-state lifetime revenue per cohort customer, including the first order. downstream_ltv excludes first orders. Both honor the Brand’s tax and shipping Revenue Basis and dated Base-currency conversion; refunds are not subtracted again. Missing currency/rates follow reporting errors. This is not a historical point-in-time reconstruction or a prediction of eventual repeat rate. Retained history may be incomplete and cohort ages unequal. Metadata echoes selection filters separately from observation history, accounts, timezone, currency/basis, source freshness, and coverage notices. No customer identities are returned.

Refund-date cohort LTV

Use cohort_ltv for calendar acquisition cohorts, retention, orders and cumulative LTV. Legacy customer_cohorts remains available for its existing retention aggregate; use cohort_ltv for LTV and dimensions.

{
  "from": "cohort_ltv",
  "where": {
    "dimension": {"eq": "first_product"},
    "cohort_month": {"gte": "2026-01-01", "lte": "2026-03-01"}
  },
  "select": ["cohort_month", "dimension_value", "dimension_label", "month_offset", "cohort_customers", "retention_rate", "orders", "cumulative_ltv"]
}

Omitted dimension defaults to all; alternatives are first_product, discount_code and country. dimension requires eq. Cohort dates require eq or both gte and lte; they select cohort month labels without truncating later observations. Other output filters, ordering and signed cursor pagination apply after aggregation. group_by is rejected. Discount values are case-insensitive, uppercase codes joined with +; empty means no code.

Each row is one cohort month × dimension value × month offset, including active_customers, period_net_revenue, cumulative_net_revenue, cumulative_ltv, snapshot_month and partial. Product cohorts overlap and are non-additive. The other dimensions partition customers.

meta.series is refund_date: a €100 Month-0 order fully refunded in Month 3 has cumulative LTV [100,100,100,0]. The series follows current Brand policy, including cancelled/VOIDED orders. meta.coverage reports excluded unidentified and unknown-history populations and refund timing limitations. Refund-month fx_reconciliation_adjustment bridges dated FX to current-order valuation. meta.reconciliation compares the full unique matched population before filters, not summed product rows or independently queried historical store-net windows. Definitions, accounts and limitations are also available in kreel://schema. See Cohorts for eligibility and reconstruction details.

Email attributed products

email_attributed_products answers which products a campaign or flow sold. Rows aggregate by connected_account_id, source_type, source_id, source_name, product_id, and product_name, with orders, units, gross_item_revenue, refunded_units, refunded_revenue, and net_item_revenue in Brand Base currency.

{
  "from": "email_attributed_products",
  "where": {
    "date": {"gte": "2026-08-01", "lte": "2026-08-31"},
    "source_type": "campaign",
    "source_id": "CAMPAIGN_ID"
  },
  "order_by": [{"field": "net_item_revenue", "dir": "desc"}],
  "limit": 10
}

Use source_type: "flow" for flows, product_id to select a product, and the standard top-level account selector for Klaviyo accounts. is_subscription: false excludes subscription and unknown-status orders; true selects detected subscription orders. Detection uses subscription flags, selling-plan indicators, and subscription tags. Rows already aggregate their dimensions; group_by is unsupported. Raw order and profile references remain internal.

Dates are conversion dates on the Brand calendar; both gte and lte (or eq) are required. Klaviyo’s configured attribution window is surfaced as account-configured with an unknown duration. Send-date campaign reports can differ because of the attribution tail, cancellations, deletions, edits and currency. Product refunds include later refunds and cover only exact Shopify order matches. Unmatched items retain gross revenue. Shipping, tax, discounts and missing items can prevent product totals from equalling order-level attributed revenue.

Email attribution coverage

email_attribution_coverage requires the same bounded date. It returns one row per Klaviyo account, or per account and source when source_type/source_id is filtered. Fields: events, attributed_events, matched_orders, unmatched_no_id, unmatched_no_order, ambiguous, match_rate, klaviyo_attributed_value, shopify_net_value, subscription_orders, backfilled_from, and synced_through, plus account/source identifiers.

{
  "from": "email_attribution_coverage",
  "where": {"date": {"gte": "2026-08-01", "lte": "2026-08-31"}}
}

All Placed Order events contribute to coverage, including unattributed events. The two monetary counters compare attributed Klaviyo value with matched attributed Shopify current-state net value. An empty denominator has a null match rate. Notices disclose match rates below 95% and incomplete refund netting. The 95% match target must be measured on your connected Brand; it is not guaranteed. meta.accounts discloses Klaviyo accounts, and meta.shopify_accounts discloses the stores used for reconciliation. Coverage timestamps describe event ingestion, independently of aggregate campaign/flow history. Defaults retain a 365-day backfill; operators can extend the configured window and rerun historical sync. Existing Klaviyo connections require reauthorization to grant events:read.

Contribution margin

Three virtual tables compute the same margin calculation at different grains: product_margin (one row per canonical product), variant_margin (one row per raw variant), and margin_summary (exactly one blended row - its dimension columns are null). All three require a bounded date filter (eq, or both gte and lte) and do not support group_by - margin rates are non-additive.

Margin basis: net merchandise revenue (ex-tax/ex-shipping) minus sold COGS, plus refunded COGS added back, at current unit cost (manual overrides win), minus a flat blended fulfillment cost per order (margin_summary only). See Contribution margin for the full definition.

{
  "from": "product_margin",
  "where": {"date": {"gte": "2026-08-01", "lte": "2026-08-31"}},
  "order_by": [{"field": "contribution_margin", "dir": "desc"}],
  "limit": 20
}

| Column | Type | Notes | |---|---|---| | date | Date | The window end. | | window_start, window_end | Date | | | account_id | Text, nullable | Null on margin_summary. | | product_id | Text, nullable | Null on margin_summary and on variant_margin’s variant-level identity (which carries variant_id instead). | | variant_id | Text, nullable | Set only on variant_margin rows. | | title | Text | "Deleted product" when the catalog row is gone. | | canonical_account_id, canonical_product_id | Text | Clustered/canonical product identity. | | unclustered | Boolean | true when the row has no SKU match or resolvable title. | | sold_units, refunded_units | Integer | | | sold_revenue, refunded_revenue | Decimal | | | net_revenue | Decimal | sold_revenue - refunded_revenue. | | covered_revenue, covered_units | Decimal / Integer | The portion of sold revenue/units with a resolvable unit cost. | | cogs, refunded_cogs | Decimal, nullable | Null whenever margin_status isn’t reported. | | contribution_margin | Decimal, nullable | Null unless margin_status is reported. | | margin_pct | Decimal, nullable | contribution_margin / net_revenue; null unless reported. | | cost_coverage_revenue | Decimal, nullable | covered_revenue / sold_revenue; null with no sales. | | cost_coverage_units | Decimal, nullable | covered_units / sold_units. | | margin_status | Text (enum) | no_sales, reported (cost coverage at least 80%), or insufficient_coverage. | | fulfillment_cost | Decimal | Flat per-order cost x order count - meaningfully populated only on margin_summary; always 0 on product_margin/variant_margin rows. | | spend | Decimal, nullable | Ad spend (Meta + Google + TikTok) over the window - populated only on margin_summary; always null on product/variant rows. | | margin_mer | Decimal, nullable | contribution_margin / spend - margin_summary only. |

Current cost restates history

Current unit costs are applied to past sales, so a cost change today changes yesterday’s reported margin too. Always check cost_coverage_revenue/cost_coverage_units before trusting a margin figure - a query always returns a fixed notice: “Current unit costs re-state history; margin excludes tax and shipping. Inspect cost coverage.”

Payment fees

payment_fees_by_gateway returns one row per payment gateway for a bounded window. It requires a bounded date filter (eq, or both gte and lte) and does not support group_by; where, order_by, limit and cursor apply to the gateway rows.

Each successful, non-test sale, capture and refund of an included order (the Brand’s sales-channel policy) is priced on its processed date in the Brand calendar:

• actual: the fees Shopify reported on the transaction (Shopify Payments). A refund’s reported fees are booked as reported; a refund with none keeps the sale fee (a measured 0).
• rule: a Payment fee rule set in Settings → Payment fees: a percentage of the amount plus a fixed fee per sale, optionally per billing country and presentment currency, effective-dated. A refund gives back the percentage under the rule its sale used, never the fixed fee.
• missing: neither. The fee is null, never 0.

| Column | Type | Meaning | |---|---|---| | gateway / label | Text | Shopify’s gateway id (shopify_payments, paypal) and its display name. | | source | Text (enum) | actual, rule, or missing (any sale of the gateway without a fee). | | orders | Integer | Orders with a sale on the gateway in the window. | | sale_amount / refund_amount | Decimal | Base currency, each row converted at its own date. | | fee_amount | Decimal, nullable | Positive cost; null for a gateway with no priced sale. | | fee_rate | Decimal, nullable | fee_amount over the sales it prices. | | coverage | Decimal, nullable | Priced share of sale_amount. |

meta.coverage is the window’s revenue share with a fee. Orders synced before transaction capture count as uncovered (meta.uncaptured_orders) until an order re-sync. The profit_ladder level payment_fees (after cm1, before ad_spend) reports the same fees, split into components.actual and components.rule, at 80% coverage; below it the level is insufficient_coverage while contribution_after_ads still reports.

Returns

product_returns and variant_returns expose bounded refund activity as a quality signal - how often a product comes back, against the store’s own baseline. Both require a bounded date filter and do not support group_by.

{
  "from": "product_returns",
  "where": {"date": {"gte": "2026-08-01", "lte": "2026-08-31"}},
  "select": ["title", "sold_units", "refunded_units", "return_rate", "baseline_multiple", "high_return_severity"],
  "order_by": [{"field": "return_rate", "dir": "desc"}]
}

| Column | Type | Notes | |---|---|---| | date, window_start, window_end | Date | | | account_id, product_id | Text | | | variant_id | Text, nullable | Set only on variant_returns. | | title | Text | | | canonical_account_id, canonical_product_id | Text | | | sold_units, refunded_units | Integer | | | sold_revenue, refunded_revenue | Decimal | | | return_rate | Decimal, nullable | refunded_units / sold_units. | | revenue_return_rate | Decimal, nullable | refunded_revenue / sold_revenue. | | store_return_rate, store_revenue_return_rate | Decimal, nullable | Store-wide baseline for comparison. | | baseline_delta | Decimal, nullable | return_rate - store_return_rate. | | baseline_multiple | Decimal, nullable | return_rate / store_return_rate. | | high_return_severity | Text, nullable | warn or critical. Fires only above minimums (30+ units sold, 5+ refunded, a 100+ unit store baseline) and a real gap (+5 percentage points and 2x the baseline for warn, 3x for critical). | | advertising | Object, nullable | Linked active Meta ad spend - populated only on rows with high_return_severity set. |

Basis: refund activity on post-discount, pre-refund merchandise, excluding tax and shipping (the KRE-255 unit convention). A fixed notice always applies: refund activity is not a cohort probability, and rates can exceed 100%.

Inventory cover

inventory_cover reports current canonical-product stock against trailing sell-through, for stockout and dead-stock risk. It has no date filter at all - it always computes over a fixed trailing-28-completed-day velocity window ending at the brand’s reporting-calendar yesterday (or the end of any account-scoped window), and it does not support group_by.

{
  "from": "inventory_cover",
  "where": {"status": {"in": ["stockout_risk", "dead_stock"]}},
  "order_by": [{"field": "days_of_cover", "dir": "asc"}]
}

| Column | Type | Notes | |---|---|---| | account_id, product_id | Text | | | title | Text | | | available | Integer, nullable | Null means untracked inventory. | | velocity | Decimal | Units/day over selling_days. | | selling_days | Integer | Out of 28 - less any pre-first-sale days and stockout days. | | days_of_cover | Decimal, nullable | max(0, available) / velocity; null at zero velocity. | | status | Text (enum) | ok, untracked, no_stock_no_sales, insufficient_history, unclustered, dead_stock, or stockout_risk. | | severity | Text, nullable | warn or critical - set only for dead_stock/stockout_risk rows (critical when cover is at or below half the brand’s alert threshold). | | unclustered | Boolean | | | partially_tracked | Boolean | Some of the product’s variants are tracked, some aren’t. | | units_sold, stockout_days | Integer | Trailing 28 days. | | snapshot_coverage_days | Integer | Out of 28. | | projected_lost_revenue | Decimal, nullable | Set only for stockout_risk rows. | | advertising | Object, nullable | Linked active Meta ad spend - populated only on rows with severity set; partial and tagger-dependent. |

The alert threshold behind severity/status is per-brand (default 14 days of cover), set in the dashboard. A fixed notice always applies: Meta ad coverage is partial (Meta only, tagger-dependent), and linked spend is non-additive across products.

Partner attribution

partner_attribution and partner_unmapped_codes read the registry set_partner/remove_partner maintain. Both require a bounded date filter (via a special-cased date key, since date isn’t a stored column on these tables) and do not support group_by.

{
  "from": "partner_attribution",
  "where": {"date": {"gte": "2026-08-01", "lte": "2026-08-31"}},
  "order_by": [{"field": "net_revenue", "dir": "desc"}]
}

partner_attribution columns:

| Column | Type | Notes | |---|---|---| | partner_id, partner_name, partner_type | Text | | | orders | Integer | | | new_customer_orders, returning_customer_orders | Integer | Orders from an unidentified customer are excluded from both. | | new_customer_share | Decimal, nullable | Null with no identified orders. | | shared_orders | Integer | Orders matching more than one partner’s code - the full order revenue is credited to each matching partner, so partner rows are non-additive whenever this is nonzero. | | gross_revenue, refunded_revenue, net_revenue | Decimal | Order-date current-state net under the brand’s Revenue Basis. Unlike store_revenue_net, this retains refunds booked after the window too - a different rule than the cross-period-refund correction. | | aov | Decimal | net_revenue / orders. |

partner_unmapped_codes columns: code, orders, net_revenue, first_seen, last_seen - discount codes seen on orders that don’t match any registered partner, so you can register them.

Notices always disclose the current-state revenue basis and the new/returning definition; a shared_orders > 0 result adds an overlap warning, and an empty partner registry adds a note to register codes.

Plan vs actual

plan_vs_actual reads the brand’s budget plan (saved with set_plan) and puts each month’s actual next to it: one row per (plan month, line). It uses the active plan version unless where.version is {"eq": N}. The date filter is optional and keeps the months whose first day falls inside it; without it you get all 12 months of the version. It does not support group_by or account: the plan is brand-wide.

{
  "from": "plan_vs_actual",
  "where": {"date": {"gte": "2026-07-01", "lte": "2026-09-30"}, "line": {"in": ["net_revenue", "spend_meta"]}}
}

| Column | Type | Notes | |---|---|---| | date, period_end | Date | The month’s first day, and its last day (yesterday for the current month). | | line | Text | spend_meta, spend_google, spend_tiktok, other_marketing, net_revenue, new_customers, mer, cm1. | | plan | Decimal, nullable | Null is a blank plan cell, never 0. | | actual | Decimal, nullable | See below. | | variance, variance_pct | Decimal, nullable | actual - plan and variance / plan; null when either side is null, and on money lines when the plan and actuals are in different currencies. | | status | Text | reported, in_progress (month to date), future, no_source, pending_coverage, no_denominator, or the CM1 statuses insufficient_coverage / not_configured. | | version | Integer | The plan version the row reads. |

Each actual reuses a definition the rest of Kreel already reports, per month on the brand’s reporting calendar:

• net_revenue is refund-correct store revenue for that month under the brand’s Revenue Basis, computed for the month on its own.
• spend_<platform> is that platform’s paid spend, converted into the Base currency at each row’s date. A platform with no connected account is no_source, and one whose history has not synced back to the month yet is pending_coverage, never 0.
• new_customers counts Brand-new first orders.
• Without a store covering the month, net_revenue, new_customers, mer and cm1 are null with no_source or pending_coverage.
• mer is net revenue divided by Meta, Google and TikTok spend combined; it is null with no_denominator when there was no spend, and pending_coverage while an ad account is still syncing back to the month.
• cm1 is the profit ladder’s CM1 for the month and keeps its status, so below 80% cost coverage it is null with insufficient_coverage.
• other_marketing has no data source yet, so its actual is always null with no_source.

meta carries version, currency (the current Base currency) and plan_currency (the Base currency when the version was saved). When they differ, a notice says plan amounts are not converted, and money-line variance is null. A brand without a plan returns no rows and the notice “No budget plan set”.

Klaviyo audience growth

Three tables track list/segment membership over time: email_audience_daily, email_audience_weekly (Monday-start weeks), and email_audience_collection (sync status, no membership numbers). The two series tables require a bounded date window of at most 367 days and do not support group_by.

{
  "from": "email_audience_weekly",
  "where": {"date": {"gte": "2026-01-01", "lte": "2026-06-30"}},
  "select": ["name", "date", "end_date", "opening_members", "closing_members", "net_growth", "complete"]
}

email_audience_daily: connected_account_id, audience_type, audience_id, name, date, total_members, opening_members (nullable - null if the prior day isn’t aligned), net_growth (nullable), members_added, members_removed, net_members_changed, reconciled (nullable - cross-checks the three growth figures agree), source, observed_at, calendar_timezone, calendar_basis.

email_audience_weekly: the same audience identity columns, plus date (week’s Monday), end_date (week’s Sunday), opening_members, closing_members, net_growth (null unless the week is complete), observed_net_growth, measured_days (0-7), complete, members_added, members_removed, net_members_changed, reconciled, and largest_deviation (a boolean flag on the top-3-by-deviation-from-median weeks per audience).

email_audience_collection: connected_account_id, audience_type, audience_id, name, reason (nullable), history_start (nullable), attempted_start, attempted_end, successful_start, successful_end (nullable), attempted_at (nullable).

Fixed notices always apply: total membership isn’t unique subscribed profiles; audiences can overlap and never sum to a unique total; snapshots are point observations, so a missing interval stays unknown rather than interpolated; and largest_deviation ranks by distance from the median, not a statistical significance test.

Klaviyo deliverability

klaviyo_deliverability_daily and klaviyo_deliverability_collection back the get_deliverability tool - plain counts, no computed ratios or money columns, and normal group_by/pagination apply (these are real stored tables, not virtual).

klaviyo_deliverability_daily: connected_account_id, date, metric, grain, provider (empty string = unknown), message_id (empty string = not message-scoped), flow_id (empty string = not flow-scoped), bounce_type (empty string = not bounce-scoped), count, synced_at.

klaviyo_deliverability_collection: connected_account_id, date, metric, grain (default "detail"), status, reason (nullable), calendar_timezone, attempted_at, collected_at (nullable) - use this to check collection coverage before trusting an empty or thin klaviyo_deliverability_daily result.