--- title: WITH description: Add result columns and calculation options to a ShopifyQL query. api_version: 2026-07 source_url: html: 'https://shopify.dev/docs/api/shopifyql/latest/syntax/with' md: 'https://shopify.dev/docs/api/shopifyql/latest/syntax/with.md' api_name: shopifyql --- # WITH Use `WITH` to apply optional modifiers, which are keywords that add result columns or change how ShopifyQL calculates results. Modifiers can add [generated result columns](#generated-result-columns), apply [attribution models](#attribution-models), change [currency and timezone](#currency-and-timezone) calculations, or calculate [cumulative values](#cumulative-values). Use one `WITH` clause for each query. To apply more than one modifier, separate modifiers with commas in the same `WITH` clause. ## Syntax ```shopifyql WITH modifier[, ...] modifier: TOTALS GROUP_TOTALS PERCENT_CHANGE CUMULATIVE_VALUES CURRENCY '' TIMEZONE '' FIRST_CLICK_ATTRIBUTION | LAST_CLICK_ATTRIBUTION | LAST_NON_DIRECT_CLICK_ATTRIBUTION | ANY_CLICK_ATTRIBUTION | LINEAR_ATTRIBUTION ``` *** ## Modifiers Choose optional query modifiers for generated columns, currency and timezone calculations, attribution, or cumulative values. Modifier-specific sections describe output columns and constraints. * **TOTALS** **WITH TOTALS** Adds top-level metric total columns named `{metric_name}__totals`. * **GROUP\_​TOTALS** **WITH GROUP\_TOTALS** Adds totals for grouped dimensions named `{metric_name}__{dimension_names}_totals`. * **PERCENT\_​CHANGE** **WITH PERCENT\_CHANGE** Adds percent-change columns for each comparison metric. * **CUMULATIVE\_​VALUES** **WITH CUMULATIVE\_VALUES** Adds running-total columns for additive metrics. * **CURRENCY** **WITH CURRENCY '\'** Runs currency calculations in the specified currency. * **TIMEZONE** **WITH TIMEZONE '\'** Runs time-based calculations in the specified IANA timezone. * **attribution model** **WITH FIRST\_CLICK\_ATTRIBUTION | LAST\_CLICK\_ATTRIBUTION | LAST\_NON\_DIRECT\_CLICK\_ATTRIBUTION | ANY\_CLICK\_ATTRIBUTION | LINEAR\_ATTRIBUTION** Applies marketing attribution models to eligible sales metrics. Examples ### Examples * #### ##### Description Track daily \[\`total\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-totalsales) over the last 30 days with both an overall total and a running total. This example uses \[\`WITH TOTALS\`]\(#total-columns) for the period total and \[\`CUMULATIVE\_VALUES\`]\(#cumulative-values) for the running sum that builds across the series. ##### ShopifyQL ```shopifyql FROM sales SHOW total_sales TIMESERIES day WITH TOTALS, CUMULATIVE_VALUES SINCE -30d UNTIL today ORDER BY day ASC ``` * #### ##### Description Group \[\`sessions\`]\(/docs/api/shopifyql/latest/schemas/sessions\_and\_behavior/sessions#sessionsmetric-propertydetail-sessions) \[by country]\(/docs/api/shopifyql/latest/schemas/sessions\_and\_behavior/sessions#sessionsdimension-propertydetail-sessioncountry) and \[device type]\(/docs/api/shopifyql/latest/schemas/sessions\_and\_behavior/sessions#sessionsdimension-propertydetail-sessiondevicetype) last month with \[\`GROUP\_TOTALS\`]\(#group-total-columns) applied. Grouping by these two dimensions adds a \`sessions\_\_session\_country\_totals\` column with each country's subtotal across device types. ##### ShopifyQL ```shopifyql FROM sessions SHOW sessions GROUP BY session_country, session_device_type WITH GROUP_TOTALS DURING last_month ORDER BY sessions DESC ``` *** ## Generated result columns Some `WITH` modifiers add generated result columns in addition to the columns that [`SHOW`](https://shopify.dev/docs/api/shopifyql/latest/syntax/from-and-show#show) returns. ShopifyQL bases each generated column name on the metric name that the query returns. If a field in `SHOW` uses [`AS`](https://shopify.dev/docs/api/shopifyql/latest/syntax/from-and-show#as), then ShopifyQL uses the alias in the generated column name. You can use generated result columns in clauses that operate on returned values, such as [`ORDER BY`](https://shopify.dev/docs/api/shopifyql/latest/syntax/order-by) and [`VISUALIZE`](https://shopify.dev/docs/api/shopifyql/latest/syntax/visualize#visualize), when the columns are valid for the query. **Note:** When a `WITH` modifier adds generated result columns, not all of them can be used in `HAVING`. For more information about filtering aggregated results, refer to [`HAVING`](https://shopify.dev/docs/api/shopifyql/latest/syntax/having). ### Total columns `WITH TOTALS` adds a total column for each metric, aggregated across the entire result set, when a query groups results with [`GROUP BY`](https://shopify.dev/docs/api/shopifyql/latest/syntax/group-by) or [`TIMESERIES`](https://shopify.dev/docs/api/shopifyql/latest/syntax/timeseries). ShopifyQL sums additive metrics like `net_sales`, and recomputes ratio and percentage metrics, such as `average_order_value`, over the full result set instead of summing the grouped rows. ShopifyQL adds the `__totals` suffix to each metric name: * `net_sales__totals` is the totals column generated for `net_sales`. ### Group total columns `WITH GROUP_TOTALS` adds subtotal columns when a query groups by at least two dimensions. ShopifyQL generates a subtotal column for each prefix of the grouped dimensions except the full set, so grouping by three dimensions adds two subtotal columns. ShopifyQL names each column after the metric and the dimensions in its prefix: * Grouping by two dimensions adds one column, `{metric_name}__{first_dimension}_totals`, such as `total_sales__country_totals` for the `country` subtotal of `total_sales`. * Grouping by three dimensions adds two columns, `{metric_name}__{first_dimension}_totals` and `{metric_name}__{first_dimension}_{second_dimension}_totals`, in the order of the grouped dimensions. ### Percent-change columns When the reporting period runs up to `now` or `today`, ShopifyQL compares each period only through the same point in its grain, so a partial current period is measured against an equal period-to-date instead of against a full prior period. For example, on the 17th of a month, each comparison month is measured through its own 17th. `WITH PERCENT_CHANGE` adds columns for date comparison metrics created by [`COMPARE TO`](https://shopify.dev/docs/api/shopifyql/latest/syntax/compare-to). Without a date comparison, this modifier doesn't add percent-change result columns. ShopifyQL names each column after the compared metric and the comparison period: * A relative comparison, such as `COMPARE TO previous_year`, generates `percent_change_net_sales__previous_year`. * A date-function comparison generates a column such as `percent_change_total_sales__startOfQuarter_sub_3q`, where minus signs in the date function become `sub`. Examples ### Examples * #### ##### Description List the 10 \[sales channels]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesdimension-propertydetail-saleschannel) with the highest \[\`net\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-netsales) over the last 30 days, each shown beside the store-wide total. This example uses \[\`WITH TOTALS\`]\(#total-columns) to generate a \`net\_sales\_\_totals\` column. The ranking comes from \[\`ORDER BY\`]\(/docs/api/shopifyql/latest/syntax/order-by) \`net\_sales DESC\`, because the total is identical on every row. ##### ShopifyQL ```shopifyql FROM sales SHOW net_sales GROUP BY sales_channel WITH TOTALS SINCE -30d UNTIL today ORDER BY net_sales__totals DESC, net_sales DESC LIMIT 10 ``` * #### ##### Description Rank billing-country and product-type rows by \[\`total\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-totalsales) for last month. This example uses \[\`WITH GROUP\_TOTALS\`]\(#group-total-columns) to generate a \`total\_sales\_\_billing\_country\_totals\` column, then orders by the country subtotal before each product-type value. ##### ShopifyQL ```shopifyql FROM sales SHOW total_sales GROUP BY billing_country, product_type WITH GROUP_TOTALS DURING last_month ORDER BY total_sales__billing_country_totals DESC, total_sales DESC LIMIT 10 ``` * #### ##### Description Group \[\`net\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-netsales) by day, sales channel, and product type for last month. This example uses \[\`WITH GROUP\_TOTALS\`]\(#group-total-columns) to generate subtotal columns for each grouped-dimension prefix, including \`net\_sales\_\_day\_sales\_channel\_totals\`. ##### ShopifyQL ```shopifyql FROM sales SHOW net_sales GROUP BY day, sales_channel, product_type WITH GROUP_TOTALS DURING last_month ORDER BY net_sales__day_sales_channel_totals DESC, day ASC LIMIT 10 ``` * #### ##### Description Compare monthly \[\`net\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-netsales) for this year against last year. This example uses \[\`PERCENT\_CHANGE\`]\(#percent-change-columns) with a relative \[\`COMPARE TO\`]\(/docs/api/shopifyql/latest/syntax/compare-to) period, then visualizes the generated \`percent\_change\_net\_sales\_\_previous\_year\` column. ##### ShopifyQL ```shopifyql FROM sales SHOW net_sales TIMESERIES month WITH PERCENT_CHANGE DURING this_year COMPARE TO previous_year ORDER BY month ASC VISUALIZE percent_change_net_sales__previous_year TYPE line ``` * #### ##### Description Compare \[\`total\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-totalsales) by sales channel for a previous quarter against an earlier quarter. This example uses \[\`PERCENT\_CHANGE\`]\(#percent-change-columns) with date-function \[\`COMPARE TO\`]\(/docs/api/shopifyql/latest/syntax/compare-to) arguments, then orders by the generated \`percent\_change\_total\_sales\_\_startOfQuarter\_sub\_3q\` column. ##### ShopifyQL ```shopifyql FROM sales SHOW total_sales GROUP BY sales_channel WITH PERCENT_CHANGE SINCE startOfQuarter(-1q) UNTIL endOfQuarter(-1q) COMPARE TO startOfQuarter(-3q) UNTIL endOfQuarter(-3q) ORDER BY percent_change_total_sales__startOfQuarter_sub_3q DESC LIMIT 10 ``` *** ## Attribution models Attribution models reallocate conversion credit for eligible sales metrics across the marketing touchpoints in a customer's journey, so you can compare how different crediting rules change a metric like `net_sales`. Group by a marketing dimension, such as `utm_campaign` or `referring_channel`, so ShopifyQL can attribute results to touchpoints, and reference the [generated attribution columns](#attribution-result-columns) by their suffixed names. `ANY_CLICK_ATTRIBUTION` gives full credit to every touchpoint, so its attributed totals can add up to more than the metric's overall value. Use attribution model modifiers to add marketing-attribution variants for eligible sales metrics. You can include multiple attribution models in the same `WITH` clause to compare models side by side. | Attribution model | Credit assignment | | - | - | | FIRST\_CLICK\_ATTRIBUTION | Assigns all conversion credit to the first clicked touchpoint. Column suffix: `__first_click`. | | LAST\_CLICK\_ATTRIBUTION | Assigns all conversion credit to the final clicked touchpoint. Column suffix: `__last_click`. | | LAST\_NON\_DIRECT\_CLICK\_ATTRIBUTION | Assigns credit to the last non-direct marketing touchpoint. Column suffix: `__last_non_direct_click`. | | ANY\_CLICK\_ATTRIBUTION | Assigns full credit to every clicked touchpoint. Column suffix: `__any_click`. | | LINEAR\_ATTRIBUTION | Divides credit equally across touchpoints. Column suffix: `__linear`. | | multiple models | Multiple models can be combined to compare attribution strategies side by side. | ### Attribution result columns Attribution result columns use the returned metric name with a suffix for each attribution model. If the metric is aliased in [`SHOW`](https://shopify.dev/docs/api/shopifyql/latest/syntax/from-and-show#show), then ShopifyQL uses the alias in the generated column name. * **FIRST\_​CLICK\_​ATTRIBUTION** **net\_sales\_\_first\_click** First-click attribution column. * **LAST\_​CLICK\_​ATTRIBUTION** **net\_sales\_\_last\_click** Last-click attribution column. * **LAST\_​NON\_​DIRECT\_​CLICK\_​ATTRIBUTION** **net\_sales\_\_last\_non\_direct\_click** Last-non-direct-click attribution column. * **ANY\_​CLICK\_​ATTRIBUTION** **net\_sales\_\_any\_click** Any-click attribution column. * **LINEAR\_​ATTRIBUTION** **net\_sales\_\_linear** Linear attribution column. Examples ### Examples * #### ##### Description Split \[\`net\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-netsales) by \[referring channel]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesdimension-propertydetail-referringchannel) over the last 30 days, crediting each sale to the customer's first click. This example uses \[\`FIRST\_CLICK\_ATTRIBUTION\`]\(#attribution-models) to generate a \`net\_sales\_\_first\_click\` column, surfacing the channels that introduce customers to your store. ##### ShopifyQL ```shopifyql FROM sales SHOW net_sales GROUP BY referring_channel WITH FIRST_CLICK_ATTRIBUTION SINCE -30d UNTIL today ORDER BY net_sales__first_click DESC ``` * #### ##### Description Rank \[referring channels]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesdimension-propertydetail-referringchannel) by first-click \`revenue\` over the last 30 days, with last-click revenue alongside. This example lists both \[attribution models]\(#attribution-models) in one \`WITH\` clause to show how credit shifts from acquisition to closing. ##### ShopifyQL ```shopifyql FROM sales SHOW net_sales AS revenue GROUP BY referring_channel WITH FIRST_CLICK_ATTRIBUTION, LAST_CLICK_ATTRIBUTION SINCE -30d UNTIL today ORDER BY revenue__first_click DESC ``` *** ## Cumulative values `WITH CUMULATIVE_VALUES` adds a running-total column for each additive metric, where summing values across time is meaningful, such as sums and counts like `net_sales`, `orders`, and `customers`. Ratio and average metrics, such as `average_order_value` or `conversion_rate`, don't generate cumulative columns. Cumulative values require a time-based order, so combine `CUMULATIVE_VALUES` with [`TIMESERIES`](https://shopify.dev/docs/api/shopifyql/latest/syntax/timeseries) or [`ORDER BY`](https://shopify.dev/docs/api/shopifyql/latest/syntax/order-by) on a time dimension. Reference a generated running total by the metric's `__cumulative` column, or by its alias if the metric is renamed with [`AS`](https://shopify.dev/docs/api/shopifyql/latest/syntax/from-and-show#as). Examples ### Examples * #### ##### Description Track daily \[\`net\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-netsales) for last month with a generated \`net\_sales\_\_cumulative\` column. This example visualizes the cumulative column so the chart shows the running total instead of each day's value. ##### ShopifyQL ```shopifyql FROM sales SHOW net_sales TIMESERIES day WITH CUMULATIVE_VALUES DURING last_month ORDER BY day ASC VISUALIZE net_sales__cumulative TYPE line ``` * #### ##### Description Track weekly \[\`total\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-totalsales) and \`orders\` for this quarter, each with a running-total column. This example uses \[\`CUMULATIVE\_VALUES\`]\(#cumulative-values) with eligible additive metrics, so each week shows the quarter so far. ##### ShopifyQL ```shopifyql FROM sales SHOW total_sales, orders TIMESERIES week WITH CUMULATIVE_VALUES DURING this_quarter ORDER BY week ASC ``` * #### ##### Description Summarize \[\`net\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-netsales) by day over the last 30 days. This example shows how \[\`CUMULATIVE\_VALUES\`]\(#cumulative-values) works on a grouped time dimension when \[\`ORDER BY\`]\(/docs/api/shopifyql/latest/syntax/order-by) sorts the dates in ascending order. ##### ShopifyQL ```shopifyql FROM sales SHOW net_sales GROUP BY day WITH CUMULATIVE_VALUES SINCE -30d UNTIL today ORDER BY day ASC ``` *** ## Currency and timezone Use `WITH CURRENCY ''` to run currency calculations using a valid currency code. Use `WITH TIMEZONE ''` to run time-based calculations using a valid IANA timezone. Both values are strings, so wrap them in single quotes. Examples ### Examples * #### ##### Description Report \[\`gross\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-grosssales), \`discounts\`, and \`net\_sales\` for last month in US dollars. This example uses \[\`WITH CURRENCY 'USD'\`]\(#currency-and-timezone) to present the monetary metrics in that currency. ##### ShopifyQL ```shopifyql FROM sales SHOW gross_sales, discounts, net_sales WITH CURRENCY 'USD' SINCE startOfMonth(-1m) UNTIL endOfMonth(-1m) ``` * #### ##### Description Report \[\`gross\_sales\`]\(/docs/api/shopifyql/latest/schemas/sales\_revenue/sales#salesmetric-propertydetail-grosssales), \`discounts\`, and \`net\_sales\` for last month in the \`America/New\_York\` timezone. This example uses \[\`WITH TIMEZONE\`]\(#currency-and-timezone) to resolve the \`startOfMonth\` and \`endOfMonth\` boundaries in that zone, so sales near the start or end of the month shift into or out of the last-month total. ##### ShopifyQL ```shopifyql FROM sales SHOW gross_sales, discounts, net_sales WITH TIMEZONE 'America/New_York' SINCE startOfMonth(-1m) UNTIL endOfMonth(-1m) ``` ***