Made in Builder.io

Watch the biggest Figma-to-code launch of the year

Builder.io logo
Talk to Us
Platform
Developers
Talk to Us

Blog

Home

Resources

Blog

Forum

Github

Login

Signup

×

Visual CMS

Drag-and-drop visual editor and headless CMS for any tech stack

Theme Studio for Shopify

Build and optimize your Shopify-hosted storefront, no coding required

Resources

Blog

Get StartedLogin

enterprise plans

You can modify or replace the SQL, or Structured Query Language, queries in the table and chart blocks of your custom dashboards to fully customize their displayed metrics.

To get the most out of this document, you should be familiar with Using Custom Dashboards.

You can edit a custom dashboard's SQL queries by opening your dashboard in the Visual Editor.

Begin by selecting the Edit button next to your dashboard inside the dashboards list on the Insights tab. If you don't have a custom dashboard yet, you can select New dashboard from the dashboards list to create one.

You can edit the SQL query or create your own inside the SQL editor in the Options tab on the right. As an alternative, Builder also provides a full-screen text editor that you can open by pressing the Edit full screen button above the editor.

The syntax for custom dashboard SQL statements is based on Google BigQuery.

You can additionally use JS interpolation with the ${} syntax to inject dynamic values into your query, as illustrated in the example below:

SELECT *
FROM @events 
WHERE 
  -- show results for the last week in milliseconds.
  -- 8.64e+7 = 1 day in ms
  _PARTITIONTIME >= "${sqlDate(Date.now() - Number(inputs.sinceDaysAgo || 7) * 8.64e+7)}"
  ${context.content ? `AND CONTENT_ID = "${context.content.id}"` : ''}
LIMIT 10

Interpolated JS statements have access to the browser's JS API as well as the context and inputs objects.

You can use context to get additional information, such as the id of the current content, allowing you to make queries that target only that content.

inputs is populated with the names and values of the inputs that you add to your SQL table or chart. You can add inputs by clicking on Show advanced in the options pane.

In the example below, we've added an enum text field input named userType that's displayed as a drop down options control on our custom dashboard. You can access the value of this control through inputs.userType in a JS interpolation.

You can scope your SQL queries to custom events using the type column and on the @events table. You can further refine those queries based on metadata associated with your custom events using the JSON_EXTRACT_SCALAR SQL function.

For example, the following JS code tracks adding an item to a shopping cart with a productId of abc123 and the property somethingElse set to true:

// Add custom properties
// You can add any key:value pairs you need in the `meta` object
// Values can be any valid JSON type - string, number, boolean, null, object, array
builder.track('addToCart', { meta: { productId: 'abc123', somethingElse:  true } })

You can use the following SQL to get every addToCart event that has a productId of abc123, including the one tracked above:

SELECT CONTENT_ID, COUNT(*) as count FROM @events 
WHERE
  type = "addToCart"
  AND JSON_EXTRACT_SCALAR(metadata, "$.productId") = "abc123"
GROUP BY CONTENT_ID
ORDER BY count DESC
LIMIT 10

The WHERE clause uses the type and metadata columns with JSON_EXTRACT_SCALAR to narrow the scope of returned records.

  • type = "addToCart" scopes the query to only return addToCart events.
  • JSON_EXTRACT_SCALAR(metadata, "$.productId") = "abc123" first expands the event's value on the metadata column, which contains all properties tracked using the meta property on the client side as a JSON object. Then it extracts the productId property from that object and compares it to abc123. If the event's productId matches abc123, then it's included in the returned record set.

In addition to permanently modifying a dashboard's SQL queries, you can run ad hoc queries on any dashboard on the Insights tab.

To start, open a dashboard from the dashboards list on the Insights tab and select the Toggle SQL button in the upper right corner of any table or chart.

You can use the SQL editor to write your own ad hoc query, which will update your table or chart using the query results. To run your query, select Run at the bottom right of the editor.

Your ad hoc query is added into your browser's current URL as a query parameter, which allows you to share your modified table or chart as a link.

However, your ad hoc query doesn't change the permanent SQL query that retrieves data for your table or chart, and only those with your link can see your modifications. Your ad hoc queries are never persisted.

NameTypeNotes

type

string

This is the event type: impression, click , conversion or if you're sending custom events, it'll be the type of your custom event.

content_id

string

Optional, will be in impression events.

url_path

string

window.location.pathname

date

timestamp

The date of the event.

url_query_string

string

window.location.search

test_variation_id

string

Optional, will be in impression events on content that has a/b tests on, and it represents the variation that the user have seen.

target_builder_element_id

string

Optional, will be in click events on content, and it represents the builder block that the user interacted with, used in builder Heatmaps.

session_id

string

In all events, represents the session where this event happened.

The maximum bytes that can be queried in one call is 350GB.


Looking to hire a third party to help with your project?

Submit a project request and our partnerships team will reach out to connect you with an Expert from our partner ecosystem.

Connect with us

Was this article helpful?

Product

Visual CMS

Theme Studio for Shopify

Sign up

Login

Featured Integrations

React

Angular

Next.js

Gatsby

Get In Touch

Chat With Us

Twitter

Linkedin

Careers

© 2020 Builder.io, Inc.

Security

Privacy Policy

Terms of Service

Newsletter

Get the latest from Builder.io

By submitting, you agree to our Privacy Policy