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:

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 four 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
7. Pivot Tables
8. Heat Maps

Note: More widgets are planned for the roadmap, the above shows what is available as of 4/20/2026

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

 _xaxis       Pre-Opp Attribution   Post-Opp Attribution   Total Attribution   opps_attributed                 
 ------------ --------------------- ---------------------- ------------------- ----------------- 
  2016-01-01   1663233.0             2206767.0              3870000.0           35                              
  2017-01-01   26737807.0            12745435.0             39483242.0          209                             
  2018-01-01   11915888.0            5751872.0              17667760.0          134                             
  2019-01-01   24423127.0            9261591.0              33684718.0          197                             
  2020-01-01   22545718.0            14558554.0             37104272.0          116                             
  2021-01-01   26745707.0            17343778.0             44089485.0          131                             
  2022-01-01   45698314.0            27737468.0             73435782.0          206                             
  2023-01-01   52033116.0            22480716.0             74513832.0          181                             
  2024-01-01   53310272.0            19580322.0             72890594.0          143                             

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:

  _xaxis   _measure             _dimension  
 -------- -------------------- ------------ 
  2025     2578041.0            Lost        
  2025     10912786.0           Open        
  2025     2695809.0            Won         
  2024     46676738.999999993   Lost        
  2024     22141533.999999903   Won         
  2024     4072276.9999999963   Open        
  2023     32119784.9999999     Won         
  2023     41829854.000000037   Lost        
  2023     564198.0             Open        

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.

NOTE: The CaliberMind app automatically detects whether or not the data is shaped correctly for un-pivoted data. If the App detects unpivoted data, meaning there are exactly three columns, _xaxis, _measure, _dimension it will automatically pivot the data for chart rendering. Otherwise the app will assume the data is already pivoted and render accordingly.

NOTE: Order of columns does not matter

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.

Formatting values Data Grids

Columns can be formatted with friendly labels for currency (only $ as of 3/30) or percentages (%) the sql needs to return a numeric value type. Once the sql is ready, click the preview button on the right:

This will open up the preview window. On this window click the format button:

This will cause an additional window to appear allowing you to set the formatting:

After setting your desired columns, hit “Apply”, the close the preview screen by clicking off the window and save the widget.

NOTE

• You can set any column on your data grid. HOWEVER setting non-numeric type column (string, date, boolean ect.) will have no effect on the rendering of the chart
• This feature does NOT convert decimals to percentages (multiplying by 100) It merely appends (%) or prepends ($) the number rendered on the table with the corresponding symbol. You MUST convert decimals to percentages in the sql using either a * 100 or the safe_multiply() function (recommended)
• Sorting will still work correctly based on the numeric values
• The formatting can only be updated in this interface through the widget builder.
• Previously the recommended solution was to use sql to cast the numbers as string and use the format() function. This new (as of 4/1/26) functionality will work along side existing widgets using this methodology. However this version is simpler and will not break sorting.

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
• If you are attempting to incorporate databars on the column that is also linked using object linking, the linking takes precedence and the data bars will NOT render. This ALSO extends to reserved column aliased for linking, even if linking is NOT activated (ex: Campaign is a reserved string for object linking)

Example sql containing data bars

with a as (
  select
    campaign_type,
    sum(touch_value) as attribution
    FROM cm.cm_insights_attribution
    where default_model = true
    group by all
),
_max as ( select max(attribution) as denominator from a group by all)

select
  campaign_type as `Campaign Type`,
  cast(attribution as string format '$999,999,999,999.99') as `Total Attribution`,
    ifnull(safe_divide(a.attribution, _max.denominator),0) as `Total Attribution_databar`,
from a, _max

Note:

• the cte on line 9 exists to grab the largest possible value to use to convert to a percentage
• Line 13 creates the percentage by dividing the attribution for a campaign type (line 12) and dividing it by the largest possible value (calculated in line 9
• This sql will render a data bar for the campaign type with the most attribution as completely full, and every other data bar length relative to that value

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:

select
    a.person_id,
    a.company_id,
    a.opp_id as opportunity_id,
    a.campaign_id,
from cm.cm_attribution a

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:

select
    a.person_id as person_id, -- Person Object ID
    p.name as person_name, -- Person Object Friendly Label 1
    p.name as `Person Name`, -- Person Object Friendly Label 2
    a.company_id,
    cs.company_name,
    cs.company_name as `Company Name`,
    a.opp_id as opportunity_id,
    o.cm_name as opportunity_name,
    o.cm_name as `Opportunity Name`,
    a.campaign_id,
    a.campaign_name,
    a.campaign_name as `Campaign Name`
from cm.cm_attribution a
left join cm.cm_companystats cs on cs. company_id = a.company_id
left join cm.cm_person p on p.id = a.person_id
left join cm.cm_opportunity o on o.cm_id = a.opp_id
limit 10

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
• If you are attempting to incorporate databars on the column that is also linked in this method, the linking takes precedence and the data bars will NOT render

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. Declaring a group by can also be done in conjunction with using a dynamic group by magic sql (feature coming soon)

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 sql is the sql that will be used to create the data grid in the modal/target data grid
  • sql will be wrapped in 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. There is no validation in place to ensure this
• 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. There is no validation to ensure the correct order, and thus no warnings will be displayed if not ordered correctly

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<Industry>|cm_companystats.company_region<Region>|cm_companystats.company_tier<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 repesent the dynamic group by

Permanently Hiding Columns in Data Grid (unofficial)

Under certain circumstances you may need a column in the final sql output, but do NOT want it displayed on the datagrid. To do this alias any column so it ends with __hidden Doing so will prevent the app from rendering this column.

select
    p.name as person_name, -- displays as column name "person_name"
    p.name as person_name__hidden, -- will not be rendered
    sum(touch_value) as attribution
from cm.cm_attribution a
left join cm.cm_person p on p.id = a.person_id
GROUP BY ALL
limit 10

NOTE:

• If this feature is used, the column will not be rendered NOR will a user be able to add it to the UI when viewing the report
• When this feature is activated the sql output will not change, meaning there might be duplicate row values across rows that can make the widget appear incorrect.
• This feature was built to help support object linking, but has uses in particularly complex sql and linking instances
• This is considered an “unofficial” feature. It has not been tested to the same extent as other features, use with caution

Pivot Tables

Pivot tables are a variation of Data Grids that allow you to pivot the output of your sql as desired and save that formatting. Once the pivoting is configured, it cannot be updated by a user inside of a dashboard, it can only be updated through the widget editor. To create a pivot table,

• Select the Data Grid (Pivot) widget type
• Write your sql
• Click the Configure Pivot button

Clicking this will trigger the preview screen screen to pop in:

• Drag values into desired rows, columns and values.
  • NOTE: you can have multiple values in a row or column section, and the app will nest the columns in order
• Click Off of the preview tab
  • NOTE: If there is a SQL or validation error the preview window will pop up and display the error instead of the pivot configuration
• Click the Save button

Pivot Table Limitations:

• Databars are not supported
• Dynamic Group By is not supported
• Custom SQL linking is not supported
• To do any mathematical operation other than count (Sum, Avg, Max, Min) the columns in the Values field must be a numeric type

Currency Formating in Pivot Tables

Formatting currency and percentages is identical to data grids. Currentcy formating documentation

Heatmaps

Heatmaps are specialty date grid type widgets. While that lack some functionality of data grids, they allow you to shade individual cells in the datagrid. This is useful for providing easy to understand visual cues to indicate what cells have higher values in them, and how much higher those values are relative to others without having to read actual numbers. This is more visually appealing and allows quicker interpretation of charts.

Heatmap data shape

• 4 – 5 columns expected, most commonly will be 4
• Reqiured column output:
  • _measure
    • Must be numeric
  • _row
    • Any value type
  • _min: required
    • Used to determine the color gradient and shading of cells
    • Must be numeric
    • The same value should be returned in every row
  • _max: required
    • Used to determine the color gradient and shading of cells
    • Must be numeric
    • The same value should be returned in every row
• Optional
  • _column
    • Any value type
• Example sql:

WITH date_spine_prep AS (
  SELECT
    calendar_date as filter_date,
    date_trunc(cm_fiscal_calendar.calendar_date, month) AS date_label,
  FROM cm.cm_fiscal_calendar
  GROUP BY ALL
  ),
date_spine as (
  SELECT
    date_label
  FROM date_spine_prep
  WHERE 1=1 AND extract(year from filter_date) = 2025
    -- {{filters:datePeriod}}
  GROUP BY ALL
),
attribution_prep as (
  SELECT
    cm_insights_attribution.*,
    cast(response_datetime as date) as filter_date
  FROM cm.cm_insights_attribution
  LEFT JOIN cm.cm_companystats USING (company_id)
  LEFT JOIN cm.cm_opportunity on cm_opportunity.cm_id = cm_insights_attribution.opp_id
  WHERE 1=1
    -- {{filters:attributionModel|company|opportunity}}
),
attribution as (
  SELECT
    date_trunc(filter_date, MONTH) as _column,
    industry_short as _row,
    sum(touch_value) as _measure
  FROM attribution_prep
  WHERE 1=1
    -- {{filters:datePeriod}}
  GROUP BY ALL
),
cte_final as (
select 
  date_label as _column,
  _row as _row,
  ifnull(_measure,0) as _measure
  from date_spine ds
  LEFT JOIN attribution a on a._column =  ds.date_label
),
min_max as (
  SELECT
    max(_measure) as _max,
    min(_measure) as _min
  FROM cte_final
)
SELECT * FROM cte_final, min_max

NOTE:

The cte min_max above returns 1 row with 2 columns. Those columns are then cross joined with the final formatted data output in cte_final. This allows to dynamically calculate the shading gradient based on the largest and smallest value returned in the _measure. If your values for the _min and _max column vary across rows in output the shading will be based on the numbers returned in the first row of the output. For simplicity it is highly recommended that you have these columns return the same values across all rows.

Sorting for heatmaps

Unlike data grids, when viewing a heatmap on a dashboard sorting or re-ordering columns is NOT available. You must chose how to sort your values within the widget editor:

Note

• Sorting is done alphabetically, and can be updated at any time from the widget editor only
• The sorting is done after all filters are being applied, meaning if a filter affects the row or column values, the sorting in the widget will update to reflect that
• There is NO sorting based on the values in the _measure column
• This sorting selection is NOT available on other widgets

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

  Total Attribution   Opps Attributed                          
 ------------------- ----------------- 
  $987,654,321        6,543                                                              

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:

-- This line    
cast(sum(touch_value) as string format '$999,999,999,999')  as `Pre-Opp Attribution`
-- outputs currency formated like this:
"$123,456,789,012" 

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:

-- This line    
cast(sum(touch_value) as string format '$999,999,999,999')  as `Pre-Opp Attribution`
-- outputs this as a column label
"Pre-Opp Attribution"

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

Abbreviating Numbers

In some cases users might want numbers abbreviated so 123,456,789 becomes 123.46M. Doing this must be handled in the SQL. The recommended method is using a case statement. An example of this while also formating numbers as currency:

SELECT
    number,
    CASE
        WHEN number >= 1000000000 THEN CONCAT('$',FORMAT('%.2f', number / 1000000000), 'B')
        WHEN number >= 1000000 THEN CONCAT('$',FORMAT('%.2f', number / 1000000), 'M')
        WHEN number >= 1000 THEN CONCAT('$',FORMAT('%.2f', number / 1000), 'K')
        ELSE  CONCAT('$',FORMAT('%.2f', number)
    END AS shortened_number
FROM UNNEST([
    981234567890,
    87654321,
    12345,
    567
]) AS number;

If you do not wish to format as currency, remove ‘$’, from the concat function

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 being created:
  • There are 3 required columns
    • _xaxis (string or date data type)
    • _dimension (any data type)
    • _measure (numerical type)
  • There is 1 optional Column
    • _hover_label (string 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 with the exception of columns ending in _hover_label
• How _hover_label works:
  • This column is an override to default display values when a user hovers over a section of the chart
  • Conceptually it allows you to add additional information into the chart that can only be seen when a user hovers on it
  • Most commonly used in funnel and pie charts, it retains the same functionality for bar and line chart

Line Charts

• The SQL for the chart should return at least one row of data.
• When Un-pivoted data is selected:
  • There are 3 required columns
    • _xaxis (string or date data type)
    • _dimension (any data type)
    • _measure (numerical type)
  • There is 1 optional Column
    • _hover_label (string 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
  • All columns will be charted with the exception of columns ending in _hover_label
  • Dual axis line charts
    • 3 required columns
      • “_xaxis” being one of those two columns
      • 2 remaining columns can be aliased to anything
    • 2 optional columns
      • _hover_label columns for each remaining column
  • Single Y-Axis charts do not have a maximum number of columns
• How _hover_label works:
  • This column is an override to default display values when a user hovers over a section of the chart
  • Conceptually it allows you to add additional information into the chart that can only be seen when a user hovers on it
  • Most commonly used in funnel and pie charts, it retains the same functionality for bar and line chart

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

Funnel Charts

• The SQL should return 2 or 3 columns
• Required Columns
  • _measure (numeric or string 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
• The order of slices in the chart is based of the data in sql results. Each row represents one slice of the funnel widget with the first row returned being the top slice and the last row being the bottom slice.

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. In general, this is not recommended as it can and will lead to by the user. If choosing to hardcode AND apply dynamic filters, it is strongly recommended that you indicate what has been hardcoded either in the widget tool tip, or in the title.

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_eventstats
  • The cm_eventstats table also contains all data from cm_channel including Channel and Sub-Channel
• cm_item
• cm_opportunity
• cm_person
• preOpp
  • This is a unique non-object filter that allows the attribution type (pre, post all attirbution) filter to work with widgets
  • Write sql as cm_insights_attribution.pre_opp = false
    • You must have a table aliased as cm_insights_attribution
  • To use this filter, you must have joined either cm_attribution OR cm_insights_attribution into the query

Applying Dynamic Filters

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

-- This goes in the widget editor
WHERE 
   {{filters:channel|opportunity|company|item|attributionModel}}

NOTE:

• the trigger bracket keyword for the cm_item table is items as opposed to every other keyword trigger which is singular
• 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:

WHERE
   cm_insights_attribution.model_name = 'Even-Weighted'
   AND cm_opportunity.opp_type in ('New Business')
   AND cm_companystats.company_region in ('Americas','EMEA')

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

WHERE
   --ADD dyanmic filters by table by adding their labels seperated by a "|" 
   {{filters:channel|opportunity|company|attributionModel}}
   AND cm_insights_attribution.pre_opp = TRUE

Might generate the following SQL:

-- EXAMPLE WITH SELECT DYNAMIC FILTERS IN USE
WHERE
   cm_insights_attribution.model_name = 'Even-Weighted'
   AND cm_opportunity.opp_type in ('New Business')
   AND cm_companystats.company_region in ('Americas','EMEA')
   AND cm_insights_attribution.pre_opp = TRUE 

-- EXMAMPLE WITH NO DYNAMIC FILTERS IN USE
WHERE
   1=1
   AND cm_insights_attribution.pre_opp = TRUE 

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:

--EXAMPLE SQL in widget builder:
WHERE
  cm_insights_attribution.pre_opp = TRUE 
  {{filters:channel|opportunity|company|attributionModel}}
  
--Generates the following invalid SQL
WHERE
   cm_insights_attribution.pre_opp = TRUE
   cm_insights_attribution.model_name = 'Even-Weighted'
   AND cm_opportunity.opp_type in ('New Business')
   AND cm_companystats.company_region in ('Americas','EMEA')
   AND cm_insights_attribution.pre_opp = TRUE   

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: 

WHERE cast(filter_date as date) BETWEEN ‘2025-01-01’ AND ‘2025-12-31

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

With df as (
	SELECT
		*,
		date_trunc(opp_cm_created_date, MONTH) as filter_date, -- For using create date as filter
		-- date_trunc(opp_cm_close_date, MONTH) as filter_date, -- For using close date as filter
	FROM cm.cm_insights_attribution
)
Select
    cast(sum(touch_value) as STRING FORMAT'$999,999,999,999.99')
FROM df
WHERE 
	{{filters:datePeriod|attributionModel}}

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.

Alternative Method For Applying Date Filters (Attribution ONLY)

Most system (non-custom) widgets focused on attribution support a dynamic “date type” filter that allows users to switch between opp create date, opp close date, and opp touch date. This method explains how to incorporate both a date filter and the date type into customer attribution widgets.

Example:

SELECT
    date_trunc({{dateTypeColumn}}, MONTH) as `Date`,
    sum(touch_value) as `Attribution`
FROM cm.cm_insights_attribution
WHERE
    {{filters:dateType|attributionModel}}
GROUP BY ALL
ORDER BY 1 DESC

The following example contains magic sql slugs:

• {{filters:dateType}}
  • This required magic SQL slug is used to control the data fitlers in the where clause of a sql query. It is designed to be used as an alternative to the {{filters:datePeriod}} slug
  • When the Opportunity Date Category filter is set to Created Date, the Sql inserted is:
    • CAST(cm_insights_attribution.opp_cm_created_date AS DATE) BETWEEN ‘2025-10-27’ AND ‘2026-02-24’
  • When the Opportunity Date Category filter is set to Closed Date, the Sql inserted is:
    • CAST(cm_insights_attribution.opp_cm_close_date AS DATE) BETWEEN ‘2025-10-27’ AND ‘2026-02-24’
  • When the Opportunity Date Category filter is set to Touch Date, the Sql inserted is:
    • CAST(cm_insights_attribution.response_datetime AS DATE) BETWEEN ‘2025-10-27’ AND ‘2026-02-24’
  • NOTE
    • The strings outlined above are hardcoded; meaning to use these there must be a table aliased as cm_insights_attribution. If there is no table with that alias, the widget will throw an error
    • The cast() function is hard coded in as a safety measure to minimize the chances of invalid sql being written that cannot be easily controlled via sql
• {{dateTypeColumn}}
  • This OPTIONAL magic SQL slug is used for when you want to use the date as a dimension on a table OR as an axis for a chart.
  • When the Opportunity Date Category filter is set to Created Date, the Sql inserted is:
    • cm_insights_attribution.opp_cm_created_date
    • In the example above line 2 becomes: date_trunc({{cm_insights_attribution.opp_cm_created_date}}, MONTH) as Date,
  • When the Opportunity Date Category filter is set to Closed Date, the Sql inserted is:
    • cm_insights_attribution.cm_insights_attribution.opp_cm_close_date
    • In the example above line 2 becomes: date_trunc({{cm_insights_attribution.cm_insights_attribution.opp_cm_close_date}}, MONTH) as Date,
  • When the Opportunity Date Category filter is set to Touch Date, the Sql inserted is:
    • cm_insights_attribution.cm_insights_attribution.cm_insights_attribution.response_datetime
    • In the example above line 2 becomes: date_trunc({{cm_insights_attribution.cm_insights_attribution.cm_insights_attribution.response_datetime}}, MONTH) as Date,
  • NOTE
    • When using this method there is no need to create a cte to alias a date column to filter_date
    • The strings outlined above are hardcoded; meaning to use these there must be a table aliased as cm_insights_attribution. If there is no table with that alias, the widget will throw an error
    • The table.column path was intentionally not auto-wrapped in a cast function to preserve flexibility.

Required Filter Details

Attribution model

--Widget builder SQL looks like this:
Select
    cast(sum(touch_value) as STRING FORMAT'$999,999,999,999.99')
FROM cm.cm_insights_attribution
WHERE
    {{filters:attributionModel}}

--Generates SQL that looks like this:
Select
    cast(sum(touch_value) as STRING FORMAT'$999,999,999,999.99')
FROM cm.cm_insights_attribution
WHERE
    model_name = 'Chain-Based'

Engagement model

--Widget builder SQL looks like this:

Select
    cast(sum(touch_score_raw) as STRING FORMAT'999,999,999,999.99')
FROM cm.cm_scoring
WHERE
    {{filters:engagementModel}}

--Generates SQL that looks like this:
Select
    cast(sum(touch_score_raw) as STRING FORMAT'999,999,999,999.99')
FROM cm.cm_scoring
WHERE
    model_name = 'Relative Weights (standard90)'

Funnel model

--Widget builder SQL looks like this:
Select
    stage_order_name,
    count(distinct journey_id)
FROM cm.cm_insights_funnel_journey_history
WHERE
    {{filters:funnelId}}
    AND stage_order_recency = 1
    AND is_journey_active = 1
GROUP BY ALL

--Generates SQL that looks like this:
Select
    stage_order_name,
    count(distinct journey_id)
FROM cm.cm_insights_funnel_journey_history
WHERE
    funnel_id = '2f1c0189-120d-4a2e-91e5-898bffc303ca'

    AND stage_order_recency = 1
    AND is_journey_active = 1
GROUP BY ALL  

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

SQL Requirements

The format for the magic sql looks like this:

{{groupBy:cm_companystats.industry_short<Industry>|cm_companystats.region<Region>|cm_companystats.company_tier<Company Tier>}}

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
• • 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.

Column Aliasing

Data Grids

When including a dynamic group by in data grids, the column with the dynamic group by in it MUST be aliased as “_groupBy”. This is case sensitive. Failing to do this will cause validation to fail and throw an error. When viewing a dashboard, the CaliberMind app will automatically over-write this alias with the label used in the friendly name.

Charts

When including a dynamic group by in charts, the normal column aliasing rules still apply. In most cases this means aliasing the column with the dynamic group by as _dimension

Example Query

select
  {{groupBy:cm_companystats.industry_short<Industry>|cm_companystats.company_region<Region>|cm_campaign.campaign_type<Campaign Type>|cm_companystats.company_fit<Company Fit>|cm_companystats.customer_status<Customer Status>}} as _dimension,
  sum(touch_value) as _measure,
  'Opps Attributed: '||count(distinct opp_Id) as _hover_label
from cm.cm_insights_attribution
left join cm.cm_companystats using (company_id)
left join cm.cm_campaign on cm_campaign.cm_id = cm_insights_attribution.campaign_id
where default_model = true
group by all

Advanced Use Cases

• The dynamic group by magic sql can be used in multiple times in the same query.
  • NOTE: the dynamic group by snippet must be identical everywhere in the sql variable snippets are not supported
  • The dynamic group by can be nested inside of, or more commonly, contain sql functions.
    • Example {{groupBy:coalesce(cm_companystats.industry_short,’No Industry’)<Industry>}}
  • At it’s core this feature is allows the user to manipulate the sql from a drop down. The most common use case is has been outlined above but other edge cases might exist that this feature can be used for

Notes

• 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

Dynamic Measure

Dynamic measures work similarly and along side dynamic groupBys, and are supported on charts, tables, and pivot tables. They are NOT currently supported for card widgets.

• The controls for dynamic measures are always on the widget and never in the filter drawer
• The measure selection in custom sql widgets is 100% configurable
• Multiple dynamic measures are supported in the same widget. Bother measures are selected independently of each other.

To activate dynamic measure 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 required
• When using tables or pivot tables the column title will NOT be displayed in the widget.

SQL Requirements

The format for the magic sql looks like this:

{{measure:sum(touch_value)/4<1/4 attribution>|sum(touch_value)/2<Half Attribution>|sum(touch_value)/3<1/3 Attribution>}} as _measure,

There are 3 parts:

• {{measure:
  • this declares the start of the group by
• sum(touch_value)/4
  • this is the sql that will be inserted when this measure is selected
• <1/4 attribution>
  • 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 {{_measure}} 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.

Multiple Dynamic Measures in the same Widget

Adding a second dynamic measure simply requires adding and _2 to your sql alias and in the title. For example:

SELECT {{measure:sum(touch_value)/4<1/4 attribution>|sum(touch_value)/2<Half Attribution>}} as _measure, {{measure:sum(touch_value)/5<1/5 attribution>|sum(touch_value)6<1/6 Attribution>|}} as _measure_2, FROM cm.cm_insights_attribution LEFT JOIN cm.cm_companystats USING (company_id) WHERE {{filters:dateType|attributionModel}} GROUP BY ALL

Note on line 3:

• the declaration using {{measure: does NOT change
• The column alias at the end appends _2 so the final column alias is _measure_2
• The widget title would contain both {{measure}} AND {{measure_2}}
  • The selections in the menus will follow the slugs, so if {{_measure_2}} is placed before {{_measure}} then the first drop down will correspond to the values in {{measure_2}} and the second will correspond to {{measure}}

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:

with date_spine as (
  SELECT
    DATE_TRUNC(calendar_date, MONTH) as _date
  FROM cm.cm_fiscal_calendar
  WHERE calendar_date BETWEEN '2024-01-01' AND '2025-12-31'
  GROUP BY ALL
),
attribution as (
  select
    DATE_TRUNC(CAST(opp_create AS DATE), MONTH) AS _date,
    opp_won_lost_open as opp_status,
    SUM(touch_value) as touch_value,
  FROM cm.cm_insights_attribution
  WHERE opp_create BETWEEN '2024-01-01' AND '2025-12-31'
  GROUP BY ALL
)
SELECT
  date_spine._date as _xaxis,
  ifnull(opp_status, (select opp_status from attribution limit 1)) as _dimension,
  round(IFNULL(touch_value, 0),0) as _measure
FROM date_spine
LEFT JOIN attribution USING (_date)
ORDER BY _date asc

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.

WITH attribution_prep as (
  SELECT
    cm_insights_attribution.*,
    opp_create as filter_date
  FROM cm.cm_insights_attribution
  LEFT JOIN cm.cm_campaign on cm_campaign.cm_id = cm_insights_attribution.campaign_id
  WHERE
    {{filters:attributionModel|campaign}}
),
attribution as (
  SELECT
    DATE_TRUNC(CAST(opp_create AS DATE), MONTH) AS _xaxis,
    campaign_type as _dimension,
    SUM(touch_value) as _measure,
  FROM attribution_prep
  WHERE 
    {{filters:datePeriod}}
  GROUP BY ALL
),
top_ten as (
  SELECT
    _dimension,
    SUM(_measure)
  FROM attribution
  GROUP BY ALL
  ORDER BY 2 DESC
  LIMIT 10
)
SELECT
  attribution.*
FROM attribution
RIGHT JOIN top_ten using (_dimension) 

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:

WITH attribution_prep as (
  SELECT
    cm_insights_attribution.*,
    opp_create as filter_date
  FROM cm.cm_insights_attribution
  LEFT JOIN cm.cm_campaign on cm_campaign.cm_id = cm_insights_attribution.campaign_id
  WHERE
    {{filters:attributionModel|campaign}}
),
attribution as (
  SELECT
    DATE_TRUNC(CAST(opp_create AS DATE), MONTH) AS _xaxis,
    campaign_type as _dimension,
    SUM(touch_value) as _measure,
  FROM attribution_prep
  WHERE 
    {{filters:datePeriod}}
  GROUP BY ALL
),
top_ten as (
  SELECT
    _dimension,
    SUM(_measure)
  FROM attribution
  GROUP BY ALL
  ORDER BY 2 DESC
  LIMIT 5
)
SELECT
  _xaxis,
  CASE WHEN _dimension IN (SELECT DISTINCT _dimension FROM top_ten) THEN _dimension ELSE "Other" End AS _dimension,
  _measure
FROM attribution

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:

WITH performance AS (
      SELECT
        cm_performance.source AS platform,
        ifnull(round(sum(cm_performance.cm_cost),0), 0) AS cost,
        ifnull(sum(cm_performance.impressions), 0) AS impressions,
        ifnull(sum(cm_performance.clicks), 0) AS clicks,
        ifnull(sum(cm_performance.leads), 0) AS leads,
    FROM cm.cm_performance cm_performance
     LEFT JOIN cm.cm_campaign ON cm_campaign.cm_id = cm_performance.campaign_id

    WHERE
      1=1 AND
      CAST(cm_performance.cm_start_date AS DATE) BETWEEN '2024-07-10' AND '2025-07-10' 
AND cm_performance.source in ("Facebook","G2","Google Ads","LinkedIn")
      
    GROUP BY ALL
    ),
    ranked AS (
    SELECT
      *
      , row_number() OVER (ORDER BY cost DESC) AS _rank
      -- rank() OVER (ORDER BY cost DESC) AS _rank
      -- You rank() also works but may unexpectedly return fewer than 10 rows in the result set 
    FROM performance
    )
    SELECT
      CASE WHEN _rank <= 10 THEN platform ELSE 'Other' END AS _dimension,
      sum(cost) AS _measure,
      'Impressions: '||sum(impressions)||'<br>'
      ||'Clicks: '||sum(clicks)||'<br>'
      ||'CTR: '||round(ifnull(safe_multiply(safe_divide(sum(clicks),sum(impressions)),100),0),2)||'%' AS _hover_label
      --"\r" forces a carriage return into the results
      --"<br>" The widget will read and encode HTML. Adding the <br> is HTML to move to the next line
      
    FROM ranked
    GROUP BY ALL

• 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

Filter Logic Cheat/Magic SQL cheat sheet (pinned to bottom)

Optional Filters

• cm_campaign {{filters:campaign}}
• cm_companystats {{filters:company}}
• cm_item {{filters:item}}
• cm_opportunity {{filters:opportunity}}
• cm_person {{filters:person}}
• cm_eventstats {{filters:event}}

Required Filters (these are always applied on dashboards)

• {{filters:attributionModel}}
  • applies to cm_insights_attribution.model_name
• {{filters:engagementModel}}
  • applies to cm_scoring.model_name
• {{filters:funnelId}}
  • applies to cm_insights_funnel_journey_history.funnel_id
• {{filters:datePeriod}}
  • applies to filter_date
  • a date type column MUST be aliased filter_date
• {{filters:dateTypeColumn}}
  • applies to cm.cm_insights_attribution
  • works with date type selection
  • strongly recommended wrapping in a cast for safety as some fields are dates and others are timestamps
    • cast({{filters:dateTypeColumn}} as date)

Not Supported yet

• cm_performance
• pre_opp Attribution (expected 5/26)
• Date Aggregation (expected 5/26)