BI Query Developer Guide

Note: The BI Query API is accessible only to accounts on the Pro Plan

Overview

The BI Query API allows clients to query their own data from Boxly’s semantic layer. This guide covers everything you need to get started, including authentication, query patterns, supported operations, and best practices specific to your analytics infrastructure.

Important: The data that you receive is not live. It is refreshed daily around 1 - 2 AM UTC.

Connection Details

For connection instructions, please log into app.boxly.ai and view the “SQL Data Access” section in “Settings”.

Query Format

Note: Boxly uses Cubejs to power its semantic layer. For additional reference on query structure and response formats, see the Cube documentation

Queries to the REST API are plain JavaScript objects, describing an analytics query.

A query object has the following properties.

measures (Array<string>): The metrics you would like to query for
- eg: [“eb_pub_leads.count”]
dimensions (Array<string>): The attributes to group the metrics by
- eg: [“eb_pub_leads.status”]
filters (Array<objects>): The list of attributes to filter by
- eg: [{
  “member”: “eb_pub_leads.channel”,
  “operator”: “equals”,
  “values”: [“FACEBOOK”, “INSTAGRAM”]
  }]
timeDimensions: (Array<objects>): A convenient way to specify a time dimension with a filter.
- eg: [{
  “dimension”: “eb_pub_leads.created_at”,
  “dateRange”: “last week”
  }]
order (objects): An object, where the keys are measures or dimensions to order by and their corresponding values are either asc or desc.
- eg: {
  “eb_pub_leads.created_at”: “asc”,
  “eb_pub_leads.channel”: “desc”
  }
limit (number): The maximum number rows to return
offset (number): The initial number of rows to skip
total (boolean): If set to true, the API will return the total number of rows as if no row limit or offset are set in the query.

Request Format

A simple query that return the number of leads in your CRM:

curl -X POST \
'https://<base_url>/cubejs-api/v1/load' \
-H 'Content-Type: application/json' \
-H 'Authorization: <DATA_ACCESS_TOKEN>' \
--data '{
"query": {
"measures": ["eb_pub_leads.count"]
}
}'

Sample Query payloads

Count Leads by Status

{
"query": {
"measures": ["eb_pub_leads.count"],
"dimensions": ["eb_pub_leads.status"],
"filters": [
{
"member": "eb_pub_leads.status",
"operator": "set"
}
],
"limit": 100
}
}

Lead count by Month filtered by Created Date

{
"query": {
"measures": ["eb_pub_leads.count"],
"dimensions": ["eb_pub_leads.status"],
"timeDimensions": [
{
"dimension": "eb_pub_leads.created_at",
"dateRange": ["2025-01-01", "2025-12-31"],
"granularity": "month"
}
],
"order": {
"eb_pub_leads.created_at": "asc"
},
"limit": 500
}
}

Leads assigned to a specific user with multiple filters

{
"query": {
"measures": ["eb_pub_leads.count"],
"dimensions": ["eb_pub_leads.status", "eb_pub_leads.source"],
"filters": [
{
"member": "eb_pub_leads.assigned_to_id",
"operator": "equals",
"values": ["USER_UUID_HERE"]
},
{
"member": "eb_pub_leads.is_closed",
"operator": "equals",
"values": [false]
}
],
"limit": 250
}
}

Call Log Metrics with Multiple Aggregations

{
"query": {
"measures": ["eb_pub_call_log.count"],
"dimensions": ["eb_pub_leads.status", "eb_pub_call_log.date"],
"timeDimensions": [
{
"dimension": "eb_pub_call_log.created_at",
"dateRange": ["2025-01-01", "2025-03-31"],
"granularity": "day"
}
],
"filters": [
{
"member": "eb_pub_call_log.note",
"operator": "set"
}
],
"order": {
"eb_pub_call_log.created_at": "desc"
},
"limit": 1000
}
}

Lead Sources and Locations with Pagination

{
"query": {
"measures": ["eb_pub_leads.count"],
"dimensions": ["eb_pub_lead_sources.name", "eb_pub_locations.location_name"],
"filters": [
{
"member": "eb_pub_leads.status",
"operator": "in",
"values": ["qualified", "negotiation", "converted"]
}
],
"limit": 50,
"offset": 100,
"order": {
"eb_pub_leads.count": "desc"
}
}
}

ER Diagram

Tables Information

eb_pub_accounts
Stores account (sub-organization) information including industry type, location settings, and organization configuration.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the account record.
created_at	TIMESTAMP	dimension	Timestamp when the account was created.
updated_at	TIMESTAMP	dimension	Timestamp when the account was last updated.
account_id	UUID	dimension	Account ID
account_name	VARCHAR(255)	dimension	Account's name

eb_pub_box_stages
Stores stage definitions within boxes that represent different steps in the lead pipeline.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the stage.
created_at	TIMESTAMP	dimension	Timestamp when the stage was created.
updated_at	TIMESTAMP	dimension	Timestamp when the stage was last updated.
account_id	UUID	dimension	Account ID for user
stage_id	UUID	dimension	Stage ID
box_id	UUID	dimension	ID of the associated box
stage_name	VARCHAR(255)	dimension	Name of the box stage
milestone_id	UUID	dimension	Associated milestone ID
is_milestone_activated	BOOLEAN	dimension	Flag if the milestone for this stage is activated

eb_pub_boxes
Stores boxes (lead containers/pipelines) that organize leads within an account.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the box.
created_at	TIMESTAMP	dimension	Timestamp when the box was created.
updated_at	TIMESTAMP	dimension	Timestamp when the box was last updated.
box_id	UUID	dimension	Unique identifier for the box
box_name	VARCHAR(255)	dimension	Name of the box
account_id	UUID	dimension	Account ID for user

eb_pub_call_log
Stores call log records for leads including call duration, outcome, and notes.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the call log
created_at	TIMESTAMP	dimension	Timestamp when the call log was created
updated_at	TIMESTAMP	dimension	Timestamp when the call log was last updated
account_id	UUID	dimension	Account ID for user
date	TIMESTAMP	dimension	Date of the call
duration	VARCHAR(50)	dimension	Duration of the call
note	TEXT	dimension	Call note
created_by_id	UUID	dimension	User who created the call log
lead_id	UUID	dimension	Lead associated with the call log
outcome_id	UUID	dimension	Outcome ID of the call
outcome_title	VARCHAR(255)	dimension	Title of the outcome
outcome_is_active	BOOLEAN	dimension	Is the outcome active?
outcome_is_default	BOOLEAN	dimension	Is the outcome default?

eb_pub_connected_channels
Stores connected channel integrations (Facebook, Instagram, WhatsApp, SMS, etc.) for accounts.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the connected channel
created_at	TIMESTAMP	dimension	Timestamp when the channel was connected
updated_at	TIMESTAMP	dimension	Timestamp when the channel connection was last updated
name	VARCHAR(255)	dimension	Name of the connected channel instance
is_connected	BOOLEAN	dimension	Indicates if the channel integration is currently active/connected
connected_at	TIMESTAMP	dimension	Timestamp when the channel was connected/authorized
channel_id	UUID	dimension	Reference UUID for the main Channel entity
api_channel_type	VARCHAR(50)	dimension	A human-readable label for the API Channel Type field
only_phone_registry	BOOLEAN	dimension	Indicates if only phone registry is used for this integration
deleted_at	TIMESTAMP	dimension	Timestamp the connected channel was marked as deleted
channel_type	VARCHAR(100)	dimension	A human-readable label for the channel_type field
account_id	UUID	dimension	Account ID

eb_pub_labels
Stores label definitions that can be applied to leads for categorization and value tracking.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the label
created_at	TIMESTAMP	dimension	Timestamp when the label was created
updated_at	TIMESTAMP	dimension	Timestamp when the label was last updated
label_name	VARCHAR(255)	dimension	Label name
account_id	UUID	dimension	Account ID for user
category_name	VARCHAR(255)	dimension	Category name for this label
type	VARCHAR(50)	dimension	Label type (choice mapped from ENUM)
value	DECIMAL(10,2)	dimension	Default value assigned to the label

eb_pub_lead_labels
Junction table mapping labels to leads with specific values for each assignment.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the lead-label mapping
created_at	TIMESTAMP	dimension	Timestamp when the label was assigned to the lead
updated_at	TIMESTAMP	dimension	Timestamp when the mapping was last updated
label_id	UUID	dimension	Label ID for this mapping
lead_id	UUID	dimension	Lead ID for this mapping
label_value	DECIMAL(10,2)	dimension	Label value for this lead
account_id	UUID	dimension	Account ID alias

eb_pub_lead_lost_reasons
Stores lost reasons assigned to leads when they are marked as lost, with optional additional information.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the lead lost reason assignment
created_at	TIMESTAMP	dimension	Timestamp when the lost reason was assigned
updated_at	TIMESTAMP	dimension	Timestamp when the assignment was last updated
additional_info	TEXT	dimension	Any free-text extra information user entered for this lost lead
lead_id	UUID	dimension	Lead ID for which this lost reason was applied
lost_reason_id	UUID	dimension	Lost reason ID associated with this lost lead
reason	VARCHAR(255)	dimension	Lost reason lost lead
account_id	UUID	dimension	Account ID for user

eb_pub_lead_lost_sub_reasons
Junction table mapping sub-reasons to lead lost reason assignments for more detailed categorization.

name	type	query type	description
id	INTEGER	dimension	Primary key. Unique identifier for the mapping
leadlostreason_id	UUID	dimension	ID for the lost reason instance selected for a lead
subreason_id	UUID	dimension	Subreason ID (box_subreason) picked by user
sub_reason	VARCHAR(255)	dimension	Subreason picked by user for this lost lead
lead_id	UUID	dimension	ID of the affected lead
lost_reason_id	UUID	dimension	Lost reason ID that this subreason is tied under
account_id	UUID	dimension	Account ID for user

eb_pub_lead_milestones
Tracks when leads reach specific milestones in their journey with associated values and stage information.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the milestone history record
created_at	TIMESTAMP	dimension	Timestamp when the record was created
updated_at	TIMESTAMP	dimension	Timestamp when the record was last updated
value	DECIMAL(10,2)	dimension	Numeric milestone value (10,2 precision)
milestone_entered_at	TIMESTAMP	dimension	Timestamp when the lead entered this milestone
box_id	UUID	dimension	Reference to Box entity (UUID)
lead_id	UUID	dimension	Reference to Lead entity (UUID)
milestone_id	UUID	dimension	Associated milestone (UUID)
stage_id	UUID	dimension	Lead stage for this milestone history (UUID)
lead_assigned_to	UUID	dimension	User/Entity assigned to the lead at this milestone
account_id	UUID	dimension	Account ID for user

eb_pub_lead_sources
Stores lead source definitions that categorize where leads originated from.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the lead source
created_at	TIMESTAMP	dimension	Timestamp when the lead source was created
updated_at	TIMESTAMP	dimension	Timestamp when the lead source was last updated
name	VARCHAR(255)	dimension	Source display name
type	VARCHAR(50)	dimension	Source type (integer enum)
account_id	UUID	dimension	Account identifier

eb_pub_leads
Core table storing all lead information including contact details, status, channel, assignments, and classification.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the lead
created_at	TIMESTAMP	dimension	Timestamp when the lead was created
updated_at	TIMESTAMP	dimension	Timestamp when the lead was last updated
name	VARCHAR(255)	dimension	Customer full name
first_name	VARCHAR(100)	dimension	Customer first name
last_name	VARCHAR(100)	dimension	Customer last name
phone_number_prefix	VARCHAR(10)	dimension	Customer phone prefix
phone_number	VARCHAR(20)	dimension	Customer phone number
email	VARCHAR(255)	dimension	Customer email address
channel	VARCHAR(100)	dimension	Source channel label
assigned_to_id	UUID	dimension	Assigned user ID
box_id	UUID	dimension	Box (container) ID
box_stage_id	UUID	dimension	Stage ID within the box
paid_add	BOOLEAN	dimension	Flag for paid leads
organic_add	BOOLEAN	dimension	Flag for organic leads
in_business_hours	BOOLEAN	dimension	Within business hours flag
lead_source_id	UUID	dimension	Lead source FK
location_id	UUID	dimension	Location FK
channel_id	UUID	dimension	Connected channel FK
is_opened	BOOLEAN	dimension	Lead opened flag
lead_summary	TEXT	dimension	Lead summary text
status	VARCHAR(50)	dimension	Status label
practitioner_id	UUID	dimension	Practitioner ID
contract_value	DECIMAL(10,2)	dimension	Lead contract value (denormalized)
is_closed	BOOLEAN	dimension	If the lead is closed
is_spam	BOOLEAN	dimension	Is spam flag
classification	VARCHAR(50)	dimension	Readable classification label
referring_practitioner_id	UUID	dimension	Referring practitioner
referring_practice_id	UUID	dimension	Referring practice
call_log_count	INTEGER	dimension	Number of call logs
sub_channel_name	VARCHAR(255)	dimension	Sub channel name
sub_channel_id	VARCHAR(255)	dimension	Sub channel id
account_id	UUID	dimension	Alias for account joins

eb_pub_locations
Stores location definitions for accounts that have multi-location features enabled.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the location
created_at	TIMESTAMP	dimension	Timestamp when the location was created
updated_at	TIMESTAMP	dimension	Timestamp when the location was last updated
location_name	VARCHAR(255)	dimension	Name of the location
account_id	UUID	dimension	Alias for sub-organization location constraint id, for analytics/account join

eb_pub_milestones
Stores milestone definitions that track key events in the lead journey (e.g., Won, Lost, Appointment Booked).

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the milestone
created_at	TIMESTAMP	dimension	Timestamp when the milestone was created
updated_at	TIMESTAMP	dimension	Timestamp when the milestone was last updated
milestone_name	VARCHAR(255)	dimension	Name of the milestone
has_revenue	BOOLEAN	dimension	Indicates if the milestone involves revenue
color	VARCHAR(20)	dimension	Color code for milestone display
milestone_position	VARCHAR(50)	dimension	Ordered position within sub-organization
account_id	UUID	dimension	Account ID for user
kind	VARCHAR(50)	dimension	Kind/status of milestone (Other, Won, Lost etc.)

eb_pub_practitioner_locations
Junction table mapping practitioners to locations they serve.

name	type	query type	description
id	INTEGER	dimension	Primary key ID (autoincrement)
account_id	UUID	dimension	Account ID
practitioner_id	UUID	dimension	Practitioner UUID
locations_id	UUID	dimension	ID referencing location (UUID)

eb_pub_practitioners
Stores practitioner information for accounts (e.g., dentists, doctors, service providers).

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the practitioner
created_at	TIMESTAMP	dimension	Timestamp when the practitioner was created
updated_at	TIMESTAMP	dimension	Timestamp when the practitioner was last updated
practitioner_id	UUID	dimension	Practitioner UUID (primary key)
name	VARCHAR(255)	dimension	Practitioner name
account_id	UUID	dimension	Account ID
is_deleted	BOOLEAN	dimension	If practitioner is marked as deleted

eb_pub_referral_practices
Stores referring practice information for referral tracking.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the referring practice
created_at	TIMESTAMP	dimension	Timestamp when the referring practice was created
updated_at	TIMESTAMP	dimension	Timestamp when the referring practice was last updated
deleted_at	TIMESTAMP	dimension	Timestamp when the practice record was deleted, if applicable
name	VARCHAR(255)	dimension	Name of the referring practice
account_id	UUID	dimension	Account ID for user

eb_pub_referral_practitioner_practices
Junction table mapping referring practitioners to their associated practices.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the mapping
created_at	TIMESTAMP	dimension	Timestamp when the mapping was created
updated_at	TIMESTAMP	dimension	Timestamp when the mapping was last updated
deleted_at	TIMESTAMP	dimension	Timestamp when the association was deleted, if applicable
is_approved	BOOLEAN	dimension	If the practitioner-practice association is approved
referring_practice_id	UUID	dimension	UUID of the referred practice
ref_practitioner_id	UUID	dimension	UUID of the referred practitioner
account_id	UUID	dimension	Account ID for user

eb_pub_referral_practitioners
Stores referring practitioner information for referral tracking.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the referring practitioner
created_at	TIMESTAMP	dimension	Timestamp when the referring practitioner was created
updated_at	TIMESTAMP	dimension	Timestamp when the referring practitioner was last updated
deleted_at	TIMESTAMP	dimension	Timestamp when the practitioner record was deleted, if applicable
name	VARCHAR(255)	dimension	Name of the referring practitioner
account_id	UUID	dimension	Account ID for user

eb_pub_referral_treatments
Stores treatment types that can be associated with referrals.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the referral treatment
created_at	TIMESTAMP	dimension	Timestamp when the treatment was created
updated_at	TIMESTAMP	dimension	Timestamp when the treatment was last updated
deleted_at	TIMESTAMP	dimension	Timestamp when the treatment record was deleted, if applicable
treatment_name	VARCHAR(255)	dimension	Name of the treatment being referred
is_active	BOOLEAN	dimension	Marks if the treatment is currently active
treatment_label_id	UUID	dimension	Label/category for the treatment
account_id	UUID	dimension	Account ID for user

eb_pub_users
Stores user information and their access/role within accounts.

name	type	query type	description
id	UUID	dimension	Primary key. Unique identifier for the user access record
created_at	TIMESTAMP	dimension	Timestamp when the user access was created
updated_at	TIMESTAMP	dimension	Timestamp when the user access was last updated
access_id	UUID	dimension	Access ID for the user
account_id	UUID	dimension	Account ID for user
user_id	UUID	dimension	User ID
username	VARCHAR(150)	dimension	User's username
email	VARCHAR(255)	dimension	User's email address
first_name	VARCHAR(100)	dimension	User's first name
middle_name	VARCHAR(100)	dimension	User's middle name
last_name	VARCHAR(100)	dimension	User's last name
full_name	VARCHAR(300)	dimension	User's full name
is_deleted	BOOLEAN	dimension	Flag indicating if the user is deleted
is_active	BOOLEAN	dimension	Flag indicating if the user is active
role	VARCHAR(50)	dimension	Role for the organisation user

Boxly is here to help you work smarter, not harder - let’s keep streamlining your lead management together!

Help center

Help center

BI Query Developer Guide

Boxly BI Query Developer Guide

Overview

Connection Details

Query Format

Request Format

Sample Query payloads

ER Diagram

Tables Information