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.

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.

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"}],
  "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. | | 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

Run the query.
Check the response’s meta.has_more.
If it’s true, run the identical query again - same from, where, select, group_by, order_by, and limit - with cursor set to the previous response’s meta.next_cursor.
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.
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 | | 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.

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.
Monetary columns are in the brand’s single base currency, with no per-row currency conversion, except where a table explicitly stores its own currency column.
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, klaviyo_email, shopify_store. Only channels with a live connection appear. | | sub_channel | Text, nullable | Normalized Shopify sales channel (web, pos, and so on). Empty string means the source name wasn’t set. Always null for ad and email rows, and for Shopify rows materialized before this split existed. | | 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. |

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 or google. | | 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 | | | lifetime_budget | Decimal | | | bid_amount | Decimal | | | 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 | |

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 creative asset metadata - dimensions and identifiers, not performance and not media URLs.

| Column | Type | Notes | |---|---|---| | creative_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 | |

creative_intelligence

Kreel’s AI-generated creative analysis, one row per creative. Every text field is best-effort and can be null until the tagger has processed that creative.

| Column | Type | Notes | |---|---|---| | ad_meta_id | Text | Links to ads.ad_id. | | 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 | |

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

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

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.

| Column | Type | Notes | |---|---|---| | date | Date | | | platform | Text (enum) | meta or google. | | 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. |

Need help?

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