SmashQL Reference

~8 min read

Draft: This reference is based on current known SmashQL syntax. The product team is reviewing for accuracy — details may change before this page is marked final.

SmashQL is dashboardSMASHBOARD's query language for defining goal-tracking widgets (smashes). It extends standard SQL with two new clauses: GOAL and VISUALIZE. If you know SQL, you already know most of SmashQL.

Clause Reference

Clause	Syntax	Notes
`SELECT`	`SELECT col, agg(col) AS alias`	Standard SQL select. Use `AS` to name the value you'll reference in `GOAL`.
`FROM`	`FROM shopify.<table>`	Shopify data tables. Most smashes use `shopify.orders`.
`WHERE`	`WHERE col = 'value'`	Standard SQL filter. Use to scope to paid orders, a product, a date range, etc.
`GROUP BY`	`GROUP BY col`	Standard SQL grouping. Required when using aggregate functions.
`GOAL`	`GOAL <alias> >= <number>`	Defines the goal threshold. `alias` must match a `SELECT` alias. Operators: `>=`, `<=`, `>`, `<`, `=`.
`VISUALIZE`	`VISUALIZE <type>`	Declares the visualization. See Visualization types for the full list.

Visualization Types

Type	Description
`progress_arc`	Circular arc showing % progress toward goal
`progress_bar`	Horizontal bar from 0 to goal
`scorecard`	Large number with goal delta (above/below by how much)
`gauge`	Speedometer-style dial
`trend_line`	Line chart with goal threshold line overlaid

Built-in Functions

Aggregate Functions

Function	Description
`sum(col)`	Sum of all values in a column
`avg(col)`	Average (mean) of all values
`count(col)`	Count of non-null values
`min(col)`	Minimum value
`max(col)`	Maximum value

Date Functions

Function	Description
`date_trunc('period', col)`	Truncate a date to day, week, month, or year
`date_diff(col_a, col_b)`	Difference between two dates (in days)

Goal Helper Functions

Function	Description
`goal_pct(alias)`	Current value as a percentage of the goal target
`goal_delta(alias)`	Absolute difference between current value and goal target

Examples

Monthly Revenue Goal

SELECT date_trunc('month', created_at) AS month,
sum(total_price) AS total_revenue
FROM shopify.orders
WHERE financial_status = 'paid'
GROUP BY month
GOAL total_revenue >= 50000
VISUALIZE progress_arc

New Customer Count

SELECT count(customer_id) AS new_customers
FROM shopify.customers
WHERE orders_count = 1
GOAL new_customers >= 200
VISUALIZE scorecard

Average Order Value

SELECT avg(total_price) AS aov
FROM shopify.orders
WHERE financial_status = 'paid'
GOAL aov >= 85
VISUALIZE gauge

SmashQL vs. ShopifyQL

Feature	SmashQL	ShopifyQL
Standard SQL syntax	✓	✓
Shopify data tables	✓	✓
`GOAL` clause	✓	✗
`VISUALIZE` clause	✓	✗
Goal helper functions	✓	✗
Embedded in dashboardSMASHBOARD	✓	✗

Was this helpful? Thanks for the feedback!

Still need help?