Using SHOPLINE QL to Customize Analytics Reports

SHOPLINE QL is an analytics query language that helps you build more flexible custom reports in SHOPLINE. With SHOPLINE QL, you can choose the metrics you want to analyze, filter report data, group results by dimensions, and create custom metrics with simple calculations. 

This guide introduces common SHOPLINE QL terms, basic query structure, frequently used clauses, query examples, and important usage notes.

Understanding Common SHOPLINE QL Terms

Before using the examples in this guide, you may find it helpful to understand these common SHOPLINE QL terms:

Understanding the Structure of a SHOPLINE QL

A SHOPLINE QL query is made up of clauses. Each clause tells SHOPLINE what data to query, which fields to show, how to filter or group the results, and which time range to use.

When creating a custom report with SHOPLINE QL, each query should include:

• FROM: Specifies the dataset.
• VIEW: Specifies the fields, metrics, or calculated values to display.
• A time range: Uses DURING, or uses SINCE / UNTIL to specify a date range.

A typical SHOPLINE QL query may look like this:

FROM sales
  VIEW total_sales, order_cnt
  FILTER payment_status IN ('Payment successful', 'Unpaid')
  GROUP BY day
  DURING last_30_day
  COMPARE previous_period
  EXTEND TOTALS
  ORDER BY total_sales DESC
  LIMIT 20

The table below summarizes the available SHOPLINE QL clauses and their purposes. Click a clause name to navigate to its dedicated section for more details and examples.

Configuring SHOPLINE QL in Custom Reports

You can enter and run SHOPLINE QL when creating or editing a custom report. Use the SHOPLINE QL editor to write your query, then run it to preview the report results before saving.

To configure SHOPLINE QL in a custom report:

1. From your SHOPLINE admin, go to Analytics > Reports.
2. Click Create custom report to create a new report, or open an existing custom report to edit it.
3. Enter a report name. Report names must be unique. If the name already exists, the system will ask you to rename it.
4. Select a dataset from the drop-down list. Currently, newly created custom reports only support Sales data.
5. Click Next, then in the report editor, enter your SHOPLINE QL query in the SHOPLINE QL editor.
6. Click Run to preview the report results.
7. Review the preview. If the result looks correct, save the report.

Using SHOPLINE QL Clauses in Queries

The following sections explain the most commonly used SHOPLINE QL clauses, including their purpose, syntax, and usage examples.

FROM and VIEW

FROM specifies the dataset you want to query. VIEW specifies the fields, metrics, or calculated values you want to display in the report.

For example, to view today’s sales:

FROM sales
  VIEW total_sales
  DURING today

You can also display multiple metrics in the same report:

FROM sales
  VIEW total_sales, net_sales, order_cnt
  DURING last_7_day

To create a new metric based on existing metrics, use arithmetic operators in VIEW, then use AS to name the new metric.

For example, to calculate average order value:

FROM sales
  VIEW (net_sales / order_cnt) AS average_order_value
  DURING last_month

For example, to calculate adjusted sales based on gross sales and discounts:

FROM sales
  VIEW (gross_sales - discounts) AS adjusted_sales
  DURING last_month

SHOPLINE QL supports the following arithmetic operators:

When a calculated field contains multiple operators, multiplication and division are calculated before addition and subtraction. You can also use parentheses to control the calculation order.

For example, to calculate adjusted sales first, then divide the result by order count:

FROM sales
  VIEW ((gross_sales - discounts) / order_cnt) AS average_adjusted_sales
  DURING last_month

FILTER

FILTER narrows the data before metrics are calculated. You can use it to include only records that match specific statuses, products, locations, or other conditions.

For example, to view only sales with successful payments:

FROM sales
  VIEW total_sales
  FILTER payment_status IN ('Payment successful')
  DURING today

For example, to view sales for products with names that contain shirt:

FROM sales
  VIEW total_sales, order_cnt
  FILTER product_spu_name CONTAINS ('shirt')
  DURING last_30_day

FILTER supports the following comparison operators:

For example, to include only records where sales are greater than 100:

FROM sales
  VIEW total_sales
  FILTER total_sales > 100
  DURING last_30_day

You can also use AND, OR, and NOT to combine multiple filter conditions, or use IN, NOT IN, CONTAINS, and NOT CONTAINS to match specific values.

For example, to include successful payments and exclude selected customer types:

FROM sales
  VIEW total_sales
  FILTER (payment_status IN ('Payment successful')) AND NOT buyer_type IN ('New customer', 'Returning customer')
  DURING today

For example, to exclude orders with specific order statuses:

FROM sales
  VIEW total_sales
  FILTER order_status NOT IN ('Completed', 'Shipped')
  DURING today

POSTFILTER

POSTFILTER filters the results after metrics are calculated. This is useful when you want to group the data first, then keep only the grouped results that meet certain conditions.

For example, to view sales by state/province over the last 30 days and show only locations where sales are greater than 1000:

FROM sales
  VIEW total_sales
  GROUP BY province
  DURING last_30_day
  POSTFILTER total_sales > 1000

FILTER and POSTFILTER are applied at different stages. FILTER filters the original data before metrics are calculated. POSTFILTER filters the calculated results after the metrics are generated.

For example, if you want to calculate sales for each state/province first, then show only locations where sales reach a specific amount, use POSTFILTER.

GROUP BY

GROUP BY groups report data by dimension. Without GROUP BY, the report usually returns a summary result. After you add GROUP BY, the report breaks down results by the dimensions you specify.

For example, to view sales by day over the last 30 days:

FROM sales
  VIEW total_sales
  GROUP BY day
  DURING last_30_day

For example, to view sales by state/province and city over the last 30 days:

FROM sales
  VIEW total_sales
  GROUP BY province, city
  DURING last_30_day

Common time dimensions include:

DURING, SINCE, and UNTIL

Each SHOPLINE QL query should include a time range. Use DURING to select a preset time range, or use SINCE and UNTIL to specify exact dates.

To use a preset time range:

FROM sales
  VIEW total_sales
  DURING last_30_day

To use a specific date range:

FROM sales
  VIEW total_sales
  SINCE '2026-05-01' UNTIL '2026-05-31'

To specify both date and time:

FROM sales
  VIEW total_sales
  SINCE '2026-05-01 00:00:00' UNTIL '2026-05-31 23:59:59'

Common DURING time ranges include:

COMPARE

COMPARE compares the selected time range with another time range.

For example, to compare data from the last 30 days with the previous period:

FROM sales
  VIEW total_sales
  GROUP BY day
  DURING last_30_day
  COMPARE previous_period

Currently supported comparison types include:

EXTEND TOTALS

EXTEND TOTALS adds summary totals to the report results. This is useful when you group data by location, product, or another dimension and also want to see the overall total.

For example, to view sales by state/province over the last 30 days and include summary totals:

FROM sales
  VIEW total_sales, order_cnt
  GROUP BY province
  DURING last_30_day
  EXTEND TOTALS

ORDER BY, LIMIT, and OFFSET

ORDER BY sorts the results. LIMIT controls the number of rows returned by the query. OFFSET skips a specified number of rows and is usually used with LIMIT.

For example, to view the top 10 products by sales over the last 30 days:

FROM sales
  VIEW total_sales, order_cnt
  GROUP BY product_spu_name
  DURING last_30_day
  ORDER BY total_sales DESC
  LIMIT 10

ASC sorts results from low to high. DESC sorts results from high to low.

To skip the first 40 rows and view the next 20 rows:

FROM sales
  VIEW total_sales
  GROUP BY product_spu_name
  DURING last_30_day
  ORDER BY total_sales DESC
  LIMIT 20
  OFFSET 40

FROM sales
  VIEW total_sales, order_cnt
  GROUP BY product_spu_name
  DURING last_30_day
  ORDER BY total_sales DESC
  LIMIT 10

FROM sales
  VIEW total_sales, net_sales
  GROUP BY day
  DURING last_30_day
  COMPARE previous_period
  ORDER BY day ASC
  LIMIT 30

FROM sales
  VIEW total_sales, order_cnt
  FILTER product_spu_name CONTAINS ('shirt')
  GROUP BY product_spu_name
  DURING last_7_day
  ORDER BY total_sales DESC
  LIMIT 20

FROM sales
  VIEW total_sales, order_cnt
  GROUP BY province
  DURING this_month
  EXTEND TOTALS
  ORDER BY total_sales DESC
  LIMIT 50

FROM sales
  VIEW total_sales
  GROUP BY province
  DURING last_30_day
  POSTFILTER total_sales > 1000

FROM sales
  VIEW total_sales
  FILTER payment_status IN ('success', 'failed')
  SINCE '2026-05-01 00:00:00' UNTIL '2026-05-20 23:59:59'