SmashQL Reference
~8 min readSmashQL 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
Revenue by Day (Trend)
SELECT date_trunc(‘day’, created_at) AS day,
sum(total_price) AS daily_revenue
FROM shopify.orders
WHERE financial_status = ‘paid’
GROUP BY day
VISUALIZE trend_line
Top Products by Revenue
SELECT product_title,
sum(price) AS revenue
FROM shopify.orders
GROUP BY product_title
ORDER BY revenue DESC
LIMIT 10
SmashQL vs. ShopifyQL
| Feature | SmashQL | ShopifyQL |
|---|---|---|
| Standard SQL syntax | ✓ | ✓ |
| Shopify data tables | ✓ | ✓ |
GOAL clause
|
✓ | ✗ |
VISUALIZE clause
|
✓ | ✗ |
| Goal helper functions | ✓ | ✗ |
| Embedded in dashboardSMASHBOARD | ✓ | ✗ |
Common Errors
| Error | Cause | Fix |
|---|---|---|
Unknown alias in GOAL |
The alias in GOAL doesn’t match a SELECT alias |
Check that the AS name matches exactly (case-insensitive) |
Missing GROUP BY |
Using an aggregate function without GROUP BY |
Add GROUP BY for the non-aggregated column |
Invalid table |
Table name is not in shopify.* namespace |
Check smash types for valid table names |
GOAL clause with no alias |
GOAL references a column name, not an alias |
Use AS your_alias in SELECT and reference that |
Next Steps
Still need help?