Widget Builder SQL Style Guide

Create any report on any dataset!

CaliberMind has a central repository of data for all kinds of information. In addition to core use cases such as attribution, engagement scoring and funnels, there is a gold mine of data that can be accessed directly in reporting. We offer our advanced users seamless access to it all via our Google BigQuery data warehouse. Our custom report builder is integrated such that we can turn SQL into a chart.

For AI assistance in creating reporting-SQL please see this article: Ask Cal widget builder.

But if you do wish to vibe code, free code, or modify existing SQLin a reporting widget the below guid is for you!

Using Ask Cal widget editor is the most efficient way to get started!

How to build custom widgets using SQL:

The custom widget editor has 2 sections in the UI. The left section is a SQL editor that allows you to write raw SQL. The right section allows you to pick what type of widget you want to create, and provides functionality for customizing that widget. Upon selection of a widget type the UI will display additional relevant metadata fields which are used in the customization of the widgets. Currently there are five widget types available:

1. Bar Charts
   1. Stacked
   2. Side by Side
2. Card widgets
   1. Single Card
   2. Split Card 
3. Data Grids (tables)
4. Line Charts
   1. Single Axis
   2. Dual Axis
5. Pie Charts
6. Funnel Charts

Table references in SQL

Table references should not be explicitly tied to a specific project, but explicit to the table and column. For example:

-- Correct 
SELECT sum(touch_value) FROM cm.cm_insights_attribution 
-- Incorrect 
SELECT sum(touch_value) FROM `calibermind-prod-7276.cm.cm_insights_attribution`

While custom widgets and their SQL are specific to each customer’s environment, the hope is that custom widget SQL will be able to be copied and pasted into different customer environments as needed. Referencing a project id in the code will work but creates more work in the future should that SQL ever need to be recycled, therefore this should not be done. 

Guidelines for shaping & formatting data

For table and card widgets, there is zero data formatting and shaping available in the widget. All data formatting and shaping must be done via SQL . This includes for example formating for currency and dates.

For Single Y-axis line charts AND bar charts, data may be shaped as either pivoted or unpivoted outputs. Additionally since cannot accept strings for as measures for formating purposes numbers are automatically formatted when the chart renders, and there is an option to include a currency symbol (currently only $)

Shaping Data for Line and Bar Charts

Charts will set the X-axis based on a column aliased as “_xaxis”. Any SQL for a chart that does NOT contain a column labeled _xaxis will throw a validation error when previewing, and won’t save. The _xaxis must be a date or string type column. In order to  create charts with fiscal calendar labels, a sortable string output is required. In this circumstance it is recommended to use the cm.cm_fiscal_calendar table in order to generate correct labels for fiscal calendars. 

NOTE: When charting dates, there is no option to have the chart render as a “continuous timeline” That means that the the data output returns values for a row representing January, no row for values representing February, and then a row for values representing march, the chart will render January, then March, and will not leave a blank space where February would have been. This can be avoided by forcing a row of data with null or no values into the output. Examples of this can be seen in the sql of most standard widgets that chart data over time

When creating charts there is an option to format as currency. This is the only formatting available in the front end. All other formatting must be done in the SQL. Formatting as currency will add a “$” to all labels, you do not get to choose which columns have a dollar sign, it’s all or nothing.

Pivoted Data

Charts (both Bar and Line) can accept sql outputs with pivoted data. When using pivoted data, the app assumes each column represents a unique series, and will chart all of them. Charts will set the X-axis based on a column aliased as “_xaxis”. If a column called “_xaxis” does not exist, the widget throw a validation error and will not allow you to save it. This error will be displayed when trying to save, or when trying to preview the widget

Example of properly shaped pivoted output:

This data output will result in a chart displaying 4 series. Due to scale, the “opps_attributed” series will be very difficult in not impossible to see, but it will still be charted

Un-pivoted Data

Bar charts and single Y-axis line charts can also accept un-pivoted data. Un-pivoted data is critical when you don’t know what the specific values you are going to pivoting on. The app does not allow dynamic pivots in the sql code itself using execute immediate and stored variables. When using unpivoted data the app is expecting a specific output of EXACTLY 3 columns with the following labels:

• _xaxis
• _dimension
• _measure

Example of properly shaped un-pivoted data:

When un-pivoted data selected in the widget configuration. The widget will pivot on the _dimension column, and chart each unique value in the _measure column as it’s own series

Shaping Data for Pie Charts

• Pie chart results are the equivalent of un-pivoted data. Each row of the results will be charted as it’s own unique wedge.
• Pie charts should NOT have percentages calculated in the sql. That calculation will be done in the front end by the widget

Data Grids

Data Grids (Tables) will order columns in the order they appear in the results. Once Data Grids are rendered on screen however, they become sortable by column. Data provide no special shaping requirements.

NOTE: data grids will do not auto format numbers. Any formatting for numbers into currency or abbreviations must be done in the sql.

Databars in Data Grids

If desired you can add data bars to any column on a data grid. Data bars are mini bar charts contained within each cell of your table:

Data bars work seamlessly with both dynamic group bys, and linking (release pending). To activate databars on a table, simply have your sql results return an additional numerical column with _databar appended to the end of the column name. The table is expecting the number in the _databar column to be between 0 and 1, with 0 being an empty data bar, and 1 being a completely full data bar. Any value returned above 1 will show a full data bar, and any value returned below 0 will show and empty data bar. If no value is returned (null) then the app will not render a data bar in that specific cell.

NOTE:

• A data bar does NOT have to exclusively be used for columns with numerical values, any value is acceptable though non-numerical columns will have limited use cases
• There is no validation on the _databar column returning strings in this column will cause empty data bars to render, but will not throw errors. Nulls will prevent specific cells from rendering data bars entirely
• A table may have an unlimited number of columns with data bars
• If the sql returns results containing a column ending with _databar, That column will NOT be displayed on the chart even if there is no corresponding column. _databar is a reserved ending string in the sql editor specifically for data bars
• As of 11/14/2025, You cannot pick your colors or show gradients in the data bars. Each column will render in a different color

Example sql containing data bars

Linking in Data Grids – Object Search Pages

Data grids have a unique feature to support linking in two ways. Links to object pages allow you to create links in you table to specific object search pages (company, campaign, opportunity, person). Links are automatically formatted upon detecting an object id in a column names. To take advantage of this feature, you must have a column that is aliased as the object id (company_id/person_id/campaign_id/opportunity_id). The following example query returns 4 columns each linking to a different object report:

NOTE: the column with the object id must formatted as outline above above. A column labeled opp_id will NOT activate linking. Additionally a non-id column aliased to one of the above object IDs will activate linking HOWEVER the link will NOT work correctly and will generate an error if clicked.

Friendly Names in Object Linking

A system has been created to allow for more friendly column names AND object record names as well

When you have an object id column to activate linking, that column will automatically be hidden the values collapsed into a column IF that column is aliased to a correct friendly name. Example:

Each object has two friendly name labels that follow the same pattern object_name or object name. When BOTH the object ID column AND an object friendly label column exists the object ID column will automatically be hidden from the table:

NOTE:

• if no value for the friendly name is found (The person in the above example) then the table will display the object ID number) this is still link-able.
• If no object id column exists but a friendly label column exists, the friendly label will display the results from the query without a link

Linking in Data Grids – Custom Modal

Linking in data grids using a custom modal allows you to trigger a modal to pop up by clicking on a linked value. The modal window contains a data grid widget. The modal takes sql input declared in source widget’s sql in the form of magic SQL. Since the modal contains a normal data grid widget, you can also have linking within the target widget (data grid in modal). Linking in the modal widget is limited to search pages (campaign, opportunity, company, person).

When linking to a modal, all dashboard level filters supported by the source widget will be passed automatically. Often times there will also be a group by dimension, this is also passed as a filter, but must be declared using magic SQL.

To make linking to custom modal SQL work there are four snippets of magic SQL:

• {{link_sql:}}
  • This SQL represents the SQL that will be injected into the query on the source widget
• {{link_target_sql:}}
  • This is the SQL that will be used to create the data grid in the modal/target data grid
  • SQL will be wrapped in triple quotes
• {{link_row:}}
  • This declares what table.column to pass the value of the row clicked on as a filter to. This also accepts a dynamic group by
• {{link_value}}
  • this declares what column in the SQL output to get the value to use as a filter in the model

Select
company_region,
{{link_sql:"count(distinct company_id)"|link_target_sql:"""Select company_id, company_name as Company Name from cm.cm_companystats LEFT JOIN cm.cm_oppportunity on cm_opportunity.company_id = cm_companystats.company_id LEFT JOIN cm.cm_persons on cm_person.company_id = cm_companystats.company_id where {{filters:company|opportunity|person}}"""|link_row:cm_companystats.company_region|link_value:company_region}} as Companies
FROM cm.cm_companystats
LEFT JOIN cm.cm_oppportunity on cm_opportunity.company_id = cm_companystats.company_id
LEFT JOIN cm.cm_persons on cm_person.company_id = cm_companystats.company_id
WHERE
{{filters:company|opportunity|person}}
GROUP BY ALL

In this example:

• link_sql:"count(distinct company_id)"
  • when the SQL for the source widget is generated this translates to
    • count(distinct company_id)
    • Final SQL for widget from example looks like this
      • Select company_region, count(distinct company_id) as `Companies` FROM cm.cm_companystats GROUP BY ALL
• A bar | is used to denote the end of the link_sql string
• link_target_sql:"""Select company_id, company_name as `Company Name` from cm.cm_companystats LEFT JOIN cm.cm_oppportunity on cm_opportunity.company_id = cm_companystats.company_id LEFT JOIN cm.cm_persons on cm_person.company_id = cm_companystats.company_id where {{filters:company|opportunity|person}}"""
  • This sets the SQL to use in the target modal
  • The SQL is wrapped within triple quotes
  • NOTE: contained within this example SQL is more magic SQL for filters. The magic SQL for filters must contain all filter objects in the source widget. IF an object is missing the widget will NOT validate
  • NOTE: since company_id id is present links to company objects will also be rendered in the modal
• {{link_row:cm_companystats.company_region}}
  • This SQL declares a filter based on the row and the group by. Since the ‘group by’ isn’t set at the system level
  • The value of the row is taken from the results of the SQL
  • That value is passed as a filter into the model using the table and column declared in {{link_row}}.
  • NOTE: the table must be joined in the query. If the table is not joined, validation will fail.
• {{link_value:company_region}}
  • This string after the : is needed to identify what column in the output SQL name
  • It’s important that this string EXACTLY MATCH a column output.
    • Examples:
      • when the output of the final table is company_region
        • use {{link_value:company_region}}
      • when the output of the final table is Company Region
        • {{link_value:Company Region}}

Notes on linking

• The Best practice is to declare the same table.colum in your link_row that you are using in your group by.
• Filters hardcoded into the sql will not automatically be passed into the target modal data grid sql. Those must be manually entered into the link_target_sql

Linking Magic SQL with multiple dimension columns:

The above example demonstrates how to link the most common use case, a table with only one dimension column. However, the product supports linking and passing filters for an unlimited number of dimension columns. This is done by adding additional strings to the link_value and link_row parts of the magic SQL separated by an &. For example:

with df as (
SELECT
*,
opp_cm_created_date as filter_date
FROM cm.cm_insights_attribution
WHERE
{{filters:attributionModel}}
),
attribution as (
select
cm_companystats.industry_short AS Industry,
cm_companystats.company_region AS Region,
{{link_sql:"cast(ifnull(sum(touch_value),0) as string format '$999,999,999,999') as total attribution"|link_target_sql:"
WITH df as (
select
*, opp_cm_created_date as filter_date
from cm.cm_insights_attribution)
SELECT
member_id,
touch_value
FROM df as cm_insights_attribution
LEFT JOIN cm.cm_companystats on cm_insights_attribution.company_id = cm_companystats.company_id
WHERE
{{filters:datePeriod|attributionModel}}
AND touch_value is not null"|
link_row: cm_companystats.industry_short&cm_companystats.company_region|link_value:Industry&Region}},
FROM df as cm_insights_attribution
LEFT JOIN cm.cm_companystats on cm_insights_attribution.company_id = cm_companystats.company_id
WHERE
{{filters:datePeriod}}
GROUP BY ALL
)
select * FROM attribution

Note the corresponding changes to link_row and link_value:

• link_row: cm_companystats.industry_short&cm_companystats.company_region
• link_value:Industry&Region
  • NOTE
    • These values are NOT encased in any characters
    • These values must exactly match the corresponding column. If the column is aliased to a value with a space in it, that space should be included here as well. The validation determines the end of the string by looking for }} and parses using &
• IMPORTANT: Order of the values matters. The order of the values in link_row & link_value MUST align with the order of the columns in the sql query. Failing to put these in the same order will result in filters and values for those filters being misapplied.

Linking Magic SQL example with dynamic group by:

with df as (
SELECT
*,
opp_cm_created_date as filter_date
FROM cm.cm_insights_attribution
WHERE
{{filters:attributionModel}}
),
attribution as (
select
{{groupBy:cm_companystats.industry_short|cm_companystats.company_region|cm_companystats.company_tier}} as _groupBy,
{{link_sql:"cast(ifnull(sum(touch_value),0) as string format '$999,999,999,999') as total attribution"|
link_target_sql:"
WITH df as (
select
*,
opp_cm_created_date as filter_date
from cm.cm_insights_attribution
)
SELECT
member_id,
touch_value
FROM df as cm_insights_attribution
LEFT JOIN cm.cm_companystats ON cm_companystats.company_id = cm_insights_attribution.company_id
WHERE
{{filters:datePeriod|attributionModel|company}}
AND touch_value is not null"|
link_row:_groupBy|
link_value:_groupBy}},
FROM df as cm_insights_attribution
LEFT JOIN cm.cm_companystats ON cm_companystats.company_id = cm_insights_attribution.company_id
WHERE
{{filters:datePeriod|company|attributionModel}}
GROUP BY ALL
)
select
*
FROM attribution

NOTE

• link_row and link_value should be hardcoded to _groupBy
• Dynamic group by is only supported to be used in one column per widget, HOWEVER secondary dimension columns can be hard coded in addition to the dynamic group by. To combine a dynamic group by dimension column AND a hard coded dimension column in linking, follow the instructions outlined above using _groupBy to represent the dynamic group by

Data Shape for Bar & Line Charts

In this example, each column represents a series of data that will be rendered on the chart. Given the all of these numbers will be rendered on the same y-axis (this current result is incompatible with a dual axis line chart) the opps attributed column will be extremely difficult if not impossible to see.

Data Shape for Card Widgets

In this example for a split card widget. Currency has already been formatted, and exactly 1 row is returned with exactly two columns. Note that while column names have been formatted that will not be reflected in the title displayed on the widget. That is set in the metadata using the settings on the right side of the widget builder

Formatting Data

As mentioned above, other than formatting labels as currency for charts, there is no formatting of numbers or text built into the widget editor; this must be done in SQL. 

Formatting Currency

For tables and card widgets currency formats must be part of the code. When building a Dual Y-axis Line Chart, both axes must be either currency formatted or not. You cannot do a combination of the two. An example of the SQL is below:

In order to output currency like: $123,456, use the below format:

Reference BigQuery’s documentation for additional formatting options

Formatting Friendly labels

Friendly Labels including capitalization and spaces can be achieved by wrapping the desired output in back ticks (`). An example of this is:

An alternative way to format integers looking like this:

FORMAT( "%'d", count(distinct person_id)) as `Scored People` -- outpus this as a value "123,456"

NOTE: this methodology ONLY works for integers

SQL Validation

Each SQL statement is validated during the widget creation process, this validation happens when previewing the widget and also when saving the widget. The validation checks not only for properly formatted valid sql, but also that the output is expected based on the widget type selected

Validation during preview:

SQL validation during save:

Bar Charts

• The SQL for the chart should return at least one row of data.
• When Un-pivoted data is selected:
  • Exactly 3 columns should be returned
    • _xaxis (string or date data type)
    • _dimension (any data type)
    • _measure (numerical type)
• When Pivoted data is selected
  • A minimum of 2 columns are required
  • One column must be called “_xaxis” this column will be used for charting the other columns on the X-axis
    • This column may be a string or date type
  • Additional columns may have any name or alias desired
  • All columns will be charted

Line Charts

• The SQL for the chart should return at least one row of data.
• When Un-pivoted data is selected:
  • Exactly 3 columns should be returned
    • _xaxis (string or date data type)
    • _dimension (any data type)
    • _measure (numerical type)
• When Pivoted data is selected
  • Single Y-axis chart type ONLY (dual access not allowed)
  • One column must be called “_xaxis” this column will be used for charting the other columns on the X-axis
    • This column may be a string or date type
  • Additional columns may have any name or alias desired
  • Dual axis line charts should return exactly 3 columns, with “_xaxis” being one of those two columns
  • Single Y-Axis charts do not have a maximum number of columns

Data Grids (Tables)

• The SQL for the tables should return at least one row of data.

Card Widgets

• The SQL for the chart should return one row of data only.
• Single card widgets should return only one column
• Split card widgets should return exactly two columns
• Results will be displayed exactly as returned in the sql. All formatting should be done within the sql code
• Titles/Labels for card widgets are using the settings on the right side setting section and are required.
• Column names/aliases do not affect titles and labels

Pie Charts

• The SQL should return 2 or 3 columns
• Required Columns
  • _measure (numeric data type)
  • _dimension (string data type)
• Optional Columns
  • _hover_label (string data type)
• The sql can return any number of rows
  • it is recommended for usability to returned data to 10 rows or fewer for general usability
  • Grouping _dimension values together must be done in the SQL
• You DO NOT calculate the percent of total for each dimension, that is handled in the front end by the widget itself

Filter Setup for Custom Widgets

Dynamic Filter Logic

Dynamic filters are designed to coexist with filters that are hardcoded in the SQL. Note that if you hard code a filter and then apply it as an optional filter, both the optional filter (based on selection made when viewing the dashboard) and the hard coded filter will apply simultaneously, this is not a good idea and will lead to confusion.

There are two types of dynamic filters: optional and required. Required filters are filters that must be set to some value at all times (dates, attribution/scoring model etc.). Optional filters are not required to be set, and if they aren’t set, will not appear in the SQL

In order for the widget to produce valid SQL the corresponding table must be joined anywhere you apply filters. Custom widgets do not auto detect filters and automatically add the necessary joins. 

NOTE about limiting joins: In BigQuery joins are always executed. Thus if you join cm.cm_insights_attribution to cm_channel, but fail to use any columns from cm_channel within the query outside of the join, Big Query will still scan one column in cm_channel and apply the join. Removing unnecessary joins is a way to increase query performance.

Required Filter Logic

Required filters are designated as such because they do not allow null filter values to be submitted. Once added to SQL they will automatically apply the default org value for the Organization Default Filter Set.

Optional Filter Logic

Filter availability for optional filters is determined at the CM table level. These include:

• cm_campaign
• cm_companystats
• cm_opportunity
• cm_person

Applying Dynamic Filters

To insert a dynamic filters into a SQL query use the following format:

-- This goes in the widget editor

NOTE:

• The dynamic filter block assumes it is the first line in the where clause. This is important for proper SQL generation. For example, the above code block might generate this SQL:

Inserting in a hardcoded filter should be done AFTER the dynamic filter code block. For example:

Might generate the following SQL:

Flipping the dynamic filter code block and the hard coded SQL will result in this SQL being generated which is not valid and will cause an error:

Applying Date Filters

The date filter is a unique filter in that it is usable across many report types, but needs to be able to reference different date columns. In order to do this you must alias the desired date column you want to filter on to “filter_date.” When date filters are applied the SQL generated will look like this: 

The recommended way to do this is by first creating a cte, and aliasing the column within the cte:

The benefits to using this technique are largely around simplicity without increasing query costs. Normally select * is frowned upon however using a select * in a cte like this BigQuery will automatically only select the columns needed to run the query.

NOTE the SQL assumes the filter_date is either already a date type or a type that can be cast into a date. To ensure stability and reliability of widgets, it is highly recommended to cast the date filter into a date type instead of relying on the casting done in the filter itself.

Required Filter Details

Attribution model

--Widget builder SQL looks like this:

--Generates SQL that looks like this:

Engagement model

--Widget builder SQL looks like this:

--Generates SQL that looks like this:

Funnel model

--Widget builder SQL looks like this:

--Generates SQL that looks like this:

NOTE: The Filter Drawer will display a list of Funnel Model Names and not IDs.

Dynamic Group By

Dynamic group bys are support in Bar, Single Axis Line Charts, Pie Charts, and Data Grids. Dynamic group bys in custom sql widgets look and behave nearly identical to migrated widgets. There are a few key differences:

• Dynamic group bys in custom sql widgets group by selections all function independently as opposed to widgets migrated from the same page have the group by selection linked
• The controls for dynamic group bys are always on the widget and never in the filter drawer as opposed to most migrated widgets where they will exist in both the widget and the filter drawer
• The group by selection in custom sql widgets is 100% configurable while migrated widgets are not

To activate dynamic groups by there are two unique magic sql snippets. One of these snippets appears in the sql editor, while the other appears in the widget title. NOTE: putting magic sql in the widget title is unique to this feature and required

The format for the magic sql looks like this:

There are 3 parts:

• {{groupBy:
  • this declares the start of the group by
• cm_companystats.industry_short
  • this is the sql that will be inserted when this group by is selected
  • This must reference a table.name
  • coalesce/ifnull functions are allowed
• <Industry>
  • this is the friendly name that will be displayed in the drop down

Each drop down selection is followed by a | which is used to indicate the start of a new selection. After all desired drop down selections are made the magic sql must be closed with }}

Widget Title Requirements

Somewhere in the widget title the string {{groupBy}} must exist. When viewing a widget in the dashboard, the {{groupBy}} will be replaced by the selected friendly label declared in the magic sql outlined above. This text will also be a different color. Clicking on the friendly label will cause a menu to appear allowing you to select a different group by value. If the magic sql is detected in the sql, but NOT in the widget title, the widget will throw an error.

Example Query:

Note

• The default group by selection is the first option in the magic sql.
• The drop down menu will populate in the order that is written in the magic sql. it will not apply any sorting of friendly labels
• On clicking preview or save the validation will check to ensure all selections of the dynamic group by are valid, including the sql. Depending on how many options there are, and how big the query is, this may take longer than normal time
• In the preview screen on the widget editor, the group by is not fully functional clicking the label in the chart title will cause the drop down to appear but no options are selectable. If you get this far it means the dynamic group by is setup correctly
• All other widget or filter type specific rules will still apply when constructing a widget with a dynamic group by

Charting a Continuous timeline

As mentioned earlier there is no option to chart data on a continuous timeline. The app will chart every row that gets returned and will not fill in the gaps automatically. That means if there is a Row for the year 2020, and a row for the years 2022 – 2025, then the widget would chart 2020, then go straight to 2022 leaving no gap for 2021. In order to prevent this from happening you can create a “Date Spine” in the sql code to force an empty row of data into the chart. An example of that sql looks like this:

In this example the date spine is built off the table cm_fiscal_calendar, a table that contains exactly one row for every date. The date is then truncated to the desired date aggregation level (in this case month) and a date filter applied. This ensures there is exactly one row for each month in our date spine table. After building the date spine cte, the query then bulids an attribution cte with the desired measure and dimensions. The tables are then joined together in a final select statement.

Any months in the timeframe in which no attribution exists

NOTE:

• In this example you must use the date spine table as your table in the FROM clause, and join the attribution table. Reversing these two table is not guaranteed to result in a continuous timeline in your output.
• You must use a LEFT join any other join type will not guarantee a continuous timeline in your output.
• The IFNULL() statement on line 19 is used to prevent an extra series from appearing in the hover over and legend in the chart. This statement is forcing a value into a row that would have otherwise not existed in a way that will not create an extra series. This part is optional but is recommended as it looks nicer
• The IFNULL() statement on line 20 is used as a backup. Null values are supported and on chart hover will show zero. This IFNULL() function is optional and serves as backstop in case changes are made and Null values are no longer supported

Limiting Break by Dimensions in Charts

When charting data having too many series on a chart will make the chart un-readable and could potentially cause app performance issues as well. Because of this it is highly recommended to limit the dimensions charted to no more than 10 unique series/dimension values. The app itself does not have the capability to enforce these limits therefore it must be done directly in the sql. There are two suggested ways to group data. The recommended is method is to take the top N (where N <= 10) dimensions. The alternative method is to take the top N (where N <= 10) dimensions and group the rest into an “Other” category. The example below demonstrates a method for limiting the chart to just the top 10.

NOTE: in this example, a RIGHT JOIN is being used as a filter, this is not the only way to limit data to a top ten, there are several that all work equally well.

An example of how to bucket everything not in the top N together is below:

SQL Best Practices

Many of the examples above include best practices that are specific to making sql for custom widgets work. However there are other more general best practices that should be used to help ensure more efficient and faster queries

• When constructing a query with multiple steps, using CTEs are preferred to sub-queries. CTEs have two distinct advantages over sub-queries. CTEs are re-usable in other parts of the query, and CTEs are often easier to understand since they are not nested with the specific part of sql where they are needed.
• When constructing a query with multiple steps push filters as far upstream as possible. If you create a multi-step query where you can push your filters into step one, you will gain meaningful benefits over putting those filters in the final select statement. When filters exist upstream, if a user inputs a filter that filters out 50% of the data, then the amount of data being scanned and used for calculations in all subsequent steps is reduce. This will can cause meaningful increases in query performance, which means fast widget load times
• Use select * carefully and strategically. Select * means BigQuery in most cases will read every row and every column of data (there are exceptions like using select * in a cte). BigQuery is columnar meaning if you have a 100 column table that is 1tb in size, a select * on that table will scan 1tb of data and take time to return results. Writing select column_1, column_2 on the other hand means only 2 columns will be scanned. If every cell in a table was the same size in bytes the select * would cause BigQuery to process 1tb of data where as select column_1, column_2 would only process 20gb, a 98% reduction in data scanned.
• Only join tables when necessary. When BigQuery encounters a join in sql, it will always scan the necessary columns on each table to apply that join. Even if the table being joined is unnecessary for the query and no actual columns are needed from the joined table. This is different from including unnecessary columns on the base table (the table after from in your sql), which if BigQuery sees a column was included but not needed, it will not scan that query.
• When writing sql for charts, it is recommended (but not required) to remove nulls from the query results, and instead replace them with zeroes. This can be easily done using either coalesce() or ifnull(). Within the CaliberMind app, you’ll find that ifnull() is generally preferred over coalesce(). However there are use cases where ifnull() is not suitable and coalesce() is required

Pie Chart SQL best Practices

• It is recommended that you limit the number of slices in the pie chart for visibility purposes in the same way you limit dimensions in bar and line charts
• Instead it is suggested that you group everything outside of the Top N together. Since pie charts show a percentage of the the total, removing the Top N will chart what percent of total of the Top N each wedge is as opposed to the percent of the actual total
• The optional hover over label was designed to include additional metrics, but must be a string type

Example pie chart SQL:

• This sql shows the top 10 platforms by add spend and lumps any other platforms into an “other” category
• The _hover_label constructs a multiline string with additional information that can be seen when hovering over an individual slice of the chart