AftersellQL (AQL) reference

Every query you build in the Explorer is an AftersellQL (AQL) statement. Most of the time you build queries visually and never write AQL by hand; this page is the reference for the metrics and dimensions you can pick, the AQL text form, and the timezone rules.

Available metrics

These are the metrics you can pick, grouped the same way the metric picker groups them.

Revenue & profit

Metric	Description
Revenue	Upsell revenue in your store’s native currency.
Revenue (USD)	Upsell revenue normalized to USD for cross-currency comparisons.
Revenue Per Visit	Upsell revenue per impression session. Cannot be broken down by product, placement, funnel, or device.
Avg. Conversion Value	Revenue per accepted offer. Also referred to as Average Upsell Value.
Upsell Revenue Per Order	Upsell revenue (USD) divided by total orders. Store-level only.
Product Profit	Revenue minus cost of goods sold (COGS) for upsold products. Relies on merchant-configured COGS, so treat it as an estimate: products without a tracked cost report revenue as profit, and cost coverage varies by store. Product-grain only; cannot be broken down by funnel, placement, or device.

Conversions

Metric	Description
Conversions	The number of accepted-offer events. One accepted offer is one conversion, so a session that accepts two offers counts twice.
Accept Rate	Session-based: the share of sessions that saw an offer and accepted at least one. Computed independently of Conversions, from a different rollup, so it is not Conversions ÷ Impressions.
Units Sold	Total units sold through upsell offers.
Decline Rate	Percent of post-purchase offers explicitly declined. Post-purchase only.

Engagement

Metric	Description
Impressions	Unique sessions that saw an offer.
Show Rate	Percent of decisions that resulted in an impression.

Store performance

Metric	Description
Total Store Revenue	Total Shopify-paid order revenue. Store-level only; cannot be broken down by surface, funnel, placement, or device.
Orders	Total Shopify-paid orders. Store-level only.
Average Total Order Value	Store revenue divided by orders. Store-level average order value.

Rokt network

Metric	Description
Rokt Revenue	Rokt-network revenue attributed to your store.
Rokt Transactions	Rokt-network transaction count for your store.
Rokt Revenue / Transaction	Rokt revenue divided by transactions per time bucket.
Rokt Impressions	Total Rokt-network impressions across your store’s placements. Distinct from upsell Impressions.
Rokt Referrals	Rokt-network referrals, positive engagements that sent the shopper to a Rokt partner.

Dimensions

Dimensions break a metric down by an attribute. Not all dimensions are compatible with every metric; the Explorer automatically prevents incompatible combinations (for example, Decline rate and Show rate cannot be broken down by Currency).

Available dimensions

Dimension	Description
Date	Groups results by day, week, or month.
Surface	The upsell surface: PPU (post-purchase), Checkout, Thank You Page, or Cart.
Funnel	The specific funnel the offer belongs to.
Product	The upselled product.
Placement	The placement within a funnel.
Device	The device type: Mobile, Desktop, or Unknown. There is no separate tablet value.
Currency	The ISO currency code (for example, USD, EUR, GBP). Useful for multi-currency stores.

Unavailable dimensions

The following are a work in progress. They appear in the picker but show as ‘Not compatible’ for every metric until implemented.

Dimension	Description
Flow type	The type of upsell flow.
Experiment	The A/B test or experiment variant.
Outcome	The decision outcome (for example, eligible, out of stock).
Reason code	The reason for a decision outcome.
Scope	The decision scope (Flow, Experience, Placement, or ItemSlot).
Response type	The offer response (Accepted, Declined, or Timeout).

AQL statement syntax

An AQL statement is a single question made of clauses. Only SELECT and a time range (SINCE) are required; everything else is optional. When you include optional clauses, they must appear in this order:

SELECT    <metrics>                    -- what to measure (required)
WHERE     <filters>                    -- narrow the data
GROUP BY  <dimensions>                 -- break the numbers down
SINCE     <time range>                 -- the period to cover (required)
GRAIN     <time grain>                 -- bucket size for time series
COMPARE   <comparison>                 -- compare against another period
CHART     <visualization>              -- how to display the result
TIMEZONE  "<timezone>"                 -- timezone for date buckets
ORDER BY  <field> <direction>          -- sort the results
LIMIT     <number>                     -- cap the number of rows

A minimal example, daily upsell revenue and accept rate for the last 30 days:

SELECT revenue, accept_rate
GROUP BY date
SINCE last_30d
GRAIN day

Keywords are not case-sensitive (SELECT and select both work) and statements do not end with a semicolon. String values are wrapped in double quotes; numbers and lists are not.

SELECT and GROUP BY

SELECT lists the metrics to measure, separated by commas, for example SELECT revenue, impressions, accept_rate.
GROUP BY breaks those metrics down by one or more dimensions, such as date, device, surface, or funnel. Without GROUP BY, you get a single total for the whole period.

Filtering with WHERE

WHERE narrows the data before it is measured. Combine conditions with AND.

SELECT revenue
WHERE device = "mobile"
GROUP BY date
SINCE last_month
GRAIN day

impressions, accept_rate, and rpv cannot be filtered or grouped by device, funnel, placement, or product; their source rollup has no such column. Adding WHERE device = "mobile" to a query selecting any of them is rejected with metric "impressions" cannot be filtered by "device".

Supported comparisons are =, !=, IN, NOT IN, >, <, >=, and <=. Use a list with IN to match several values:

WHERE surface IN ["PPU", "Checkout"]

experiment is not a filterable field; it has no rollup source, so WHERE experiment IN [...] is rejected with filters on "experiment" are not supported. The funnel filter supports multi-select: is one of (IN) includes only the selected funnels, is not one of (NOT IN) excludes them. When you group by Funnel and apply an is one of filter, the chart shows one line per selected funnel, with no “Other” collapsing.

Time ranges and comparisons

Every query needs a time range, set with SINCE:

Form	Example	Meaning
Preset	`SINCE last_30d`	A rolling window ending yesterday (UTC). The in-flight current day is deliberately excluded, so `last_1d` means yesterday only, and `this_month` runs from the 1st to yesterday.
Custom window	`SINCE 2026-07-02 UNTIL 2026-07-05`	A fixed range, using ISO dates (`YYYY-MM-DD`).

Available presets: last_1d, last_7d, last_30d, last_90d, this_month, last_month, and this_year.

GRAIN sets the bucket size for time series: day, week, or month. (hour parses but no rollup serves hourly data, so such a query is rejected with group_by / time_grain combination is not supported.)
COMPARE overlays a second period. Use previous_period, the equal-length window immediately before. previous_year is hidden from the Compare picker because the warehouse holds no data before February 2026; it remains typeable in AQL only so previously saved queries keep parsing.

-- A four-day sale vs the four days immediately before it
SELECT revenue, impressions, accept_rate
GROUP BY date
SINCE 2026-07-02 UNTIL 2026-07-05
GRAIN day
COMPARE previous_period

Reporting data starts in February 2026, so a window earlier than that returns empty for both periods.

Choosing a chart

CHART sets how the result is displayed: scorecard, line_chart, bar_chart, area_chart, funnel_chart, or table.
TIMEZONE sets the timezone used to bucket dates, as a quoted IANA name, for example TIMEZONE "America/New_York". Defaults to UTC (see Timezones).

The funnel_chart type has specific requirements:

Placement mode. Group by placement and select one metric. Stages are ordered by the canonical placement sequence (upsell default, then downsell, then additional upsells). Only the first metric is plotted; additional metrics are noted in a footnote.
Metric mode. Select two or more metrics with no GROUP BY. Each metric becomes a funnel stage in query order (for example, SELECT impressions, conversions shows an impressions to conversions drop-off). All metrics must share the same unit.

-- Placement funnel: conversion drop-off across placements
SELECT conversions
GROUP BY placement
SINCE last_30d
CHART funnel_chart

Placement mode needs a metric that can be broken down by placement. impressions, accept_rate, and rpv cannot; the funnel chart shows “These metrics can’t be grouped by placement” for those.

-- Metric funnel: impressions to conversions drop-off
SELECT impressions, conversions
SINCE last_30d
CHART funnel_chart

Sorting and limiting

ORDER BY sorts results by a metric or dimension, with ASC or DESC.
LIMIT caps the number of rows returned, useful for “top N” questions.

-- Top 20 products by upsell revenue this month
SELECT revenue, conversions, avg_conversion_value
GROUP BY product
SINCE this_month
ORDER BY revenue DESC
LIMIT 20

More examples

-- Daily performance vs the previous period
SELECT revenue, impressions, conversions, accept_rate
GROUP BY date
SINCE last_30d
GRAIN day
COMPARE previous_period

-- Mobile vs desktop revenue over the last 90 days
SELECT revenue
GROUP BY device
SINCE last_90d
ORDER BY revenue DESC

impressions, accept_rate, and rpv can’t be broken down by device; their rollup is shop × surface × day, with no device column. Use revenue (or another conversion-sourced metric) for device comparisons.

-- Which surface is driving the most revenue?
SELECT revenue, impressions, accept_rate
GROUP BY surface
SINCE last_30d
ORDER BY revenue DESC

Timezones

By default, queries run in UTC. You can override the timezone so that date-bucketed results reflect local time (see Setting a timezone for the toolbar steps).

Queries that include Impressions, Accept Rate, or Revenue Per Visit always bucket dates in UTC, regardless of the timezone you select, because they’re sourced from a daily rollup reported in UTC days. If a query mixes one of these with other metrics, the whole result set falls back to UTC so date buckets stay aligned.

The TIMEZONE clause

Specify a timezone directly in AQL with the TIMEZONE clause, which appears between CHART and ORDER BY:

SELECT ...
CHART ...
TIMEZONE "Asia/Tokyo"
ORDER BY ...

When present, the clause overrides the toolbar selection for that query, and is preserved when you save and reload.

Available timezones

The picker (and the TIMEZONE clause) accepts a closed set of ten zones. Any other IANA timezone is rejected as unsupported.

Timezone	Example location
UTC	Universal Coordinated Time
America/New_York	New York (ET)
America/Chicago	Chicago (CT)
America/Denver	Denver (MT)
America/Los_Angeles	Los Angeles (PT)
Europe/London	London (GMT/BST)
Europe/Paris	Paris (CET/CEST)
Asia/Tokyo	Tokyo (JST)
Asia/Singapore	Singapore (SGT)
Australia/Sydney	Sydney (AEST/AEDT)

Account default and how timezone affects results

If Lock reporting timezone is enabled in your analytics settings, selecting Account default uses that locked timezone (the toolbar shows the resolved zone, for example Timezone: Account default (Paris (CET))). The analytics settings page accepts the full IANA list, but Reports honours only the ten zones above; if your locked zone isn’t one of them, Account default silently resolves to UTC. If Lock reporting timezone is not enabled, Account default falls back to UTC. When a timezone is set, date-bucketing uses local time instead of UTC. For example, an event at 2026-03-29T01:30:00Z falls on March 28 in New York (ET) but March 29 in Paris (CET). Queries without a timezone, including previously saved ones, continue to run in UTC.

Strategies

Post-Purchase Upsells

Subscription Upgrades

Checkout Upsells

Thank You Page

Analytics

Available metrics

Revenue & profit

Conversions

Engagement

Store performance

Rokt network

Dimensions

Available dimensions

Unavailable dimensions

AQL statement syntax

SELECT and GROUP BY

Filtering with WHERE

Time ranges and comparisons

Choosing a chart

Sorting and limiting

More examples

Timezones

The TIMEZONE clause

Available timezones

Account default and how timezone affects results

​Available metrics

​Revenue & profit

​Conversions

​Engagement

​Store performance

​Rokt network

​Dimensions

​Available dimensions

​Unavailable dimensions

​AQL statement syntax

​SELECT and GROUP BY

​Filtering with WHERE

​Time ranges and comparisons

​Choosing a chart

​Sorting and limiting

​More examples

​Timezones

​The TIMEZONE clause

​Available timezones

​Account default and how timezone affects results

Available metrics

Revenue & profit

Conversions

Engagement

Store performance

Rokt network

Dimensions

Available dimensions

Unavailable dimensions

AQL statement syntax

SELECT and GROUP BY

Filtering with WHERE

Time ranges and comparisons

Choosing a chart

Sorting and limiting

More examples

Timezones

The TIMEZONE clause

Available timezones

Account default and how timezone affects results