> ## Documentation Index
> Fetch the complete documentation index at: https://docs.aftersell.com/llms.txt
> Use this file to discover all available pages before exploring further.

# AftersellQL (AQL) reference

> The AftersellQL query language reference: available metrics and dimensions, AQL clause syntax with examples, and timezone rules for the Explorer.

Every query you build in the [Explorer](/aftersell/reports_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:

```text theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
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:

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
SELECT revenue, accept_rate
GROUP BY date
SINCE last_30d
GRAIN day
```

<Note>
  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.
</Note>

### 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`.

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
SELECT revenue
WHERE device = "mobile"
GROUP BY date
SINCE last_month
GRAIN day
```

<Warning>
  `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"`.
</Warning>

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

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
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.

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
-- 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
```

<Note>
  Reporting data starts in **February 2026**, so a window earlier than that returns empty for both periods.
</Note>

### 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](#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.

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
-- Placement funnel: conversion drop-off across placements
SELECT conversions
GROUP BY placement
SINCE last_30d
CHART funnel_chart
```

<Warning>
  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.
</Warning>

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
-- 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.

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
-- 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

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
-- Daily performance vs the previous period
SELECT revenue, impressions, conversions, accept_rate
GROUP BY date
SINCE last_30d
GRAIN day
COMPARE previous_period
```

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
-- Mobile vs desktop revenue over the last 90 days
SELECT revenue
GROUP BY device
SINCE last_90d
ORDER BY revenue DESC
```

<Note>
  `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.
</Note>

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
-- 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](/aftersell/reports_explorer#setting-a-timezone) for the toolbar steps).

<Note>
  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.
</Note>

### The TIMEZONE clause

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

```aql theme={"theme":{"light":"snazzy-light","dark":"github-dark"}}
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.