Salesforce

Salesforce is a customer relationship management (CRM) platform.

To learn how Salesforce entities map to SQL tables, consult the table mappings guide.

How to Setup

To connect your Salesforce account, you must have a Salesforce Enterprise or Salesforce Unlimited plan, as these plans include API access. Other Salesforce plans do not offer this feature. For further information, please consult the Salesforce pricing page.

To connect to a Salesforce data source you need to register the Salesforce credential. This requires:

• the name of the credential (which you can choose);
• URL
• Username
• Password
• Security Token
• Client ID
• API Version

URL

This is the login URL for the user’s Salesforce instance. You can find it within User Profile (top-right corner of Salesforce UI):

Username & Password

The username and password of the Salesforce account.

Security token

This is the personal token required in case the consumer (e.g. the platform) is not listed in the IP whitelist. You can find it in "Setup > Home > My Personal Information > Reset My Security Token".

Client ID

This is the consumer key associated with the dedicated Salesforce app for the connector.

In order to create the dedicated app, navigate to "Setup > PLATFORM TOOLS > Apps > App Manager > New Connected App" and then fill-in the form with the following information:

API Version

The latest version is 59.0, but there are minor changes between the last versions.

Supported Operations

This connector supports all CRUD (Create, Read, Update, Delete) operations, as demonstrated in the examples below.

Examples

Filtering Salesforce Contacts of a specific Account

Retrieve Salesforce Contacts of a specific Account.

select
  salesforce_account.id as account_id,
  salesforce_account.name as account_name,
  salesforce_account.description as account_description,
  salesforce_account.industry as account_industry,
  salesforce_account.created_date as account_creation_date,
  salesforce_account.website as account_website,
  salesforce_contact.id as contact_id,
  salesforce_contact.name as contact_name,
  salesforce_contact.email as contact_email,
  salesforce_contact.title as contact_title,
  salesforce_contact.department as contact_department
from "<credentials_name>".salesforce_account
  inner join "<credentials_name>".salesforce_contact
  on salesforce_account.id=salesforce_contact.account_id
where salesforce_account.name=:accountname;

Querying recent Salesforce Contact additions

Retrieve Salesforce Contacts created within the last 30 days.

select
  id,
  name,
  account_id,
  email,
  department
from
  "<credentials_name>".salesforce_contact
where created_date>now() - interval '30' day;

Salesforce Accounts and Contacts listing

Retrieve Salesforce Accounts and Contacts.

select
  salesforce_account.id as account_id,
  salesforce_account.name as account_name,
  salesforce_account.description as account_description,
  salesforce_account.industry as account_industry,
  salesforce_account.created_date as account_creation_date,
  salesforce_account.website as account_website,
  salesforce_contact.id as contact_id,
  salesforce_contact.name as contact_name,
  salesforce_contact.email as contact_email,
  salesforce_contact.title as contact_title,
  salesforce_contact.department as contact_department
from "<credentials_name>".salesforce_account
  inner join "<credentials_name>".salesforce_contact
  on salesforce_account.id=salesforce_contact.account_id;

Basic Salesforce account listing

List Salesforce accounts.

select
  id,
  name,
  description,
  industry,
  created_date,
  website
from
  "<credentials_name>".salesforce_account;

Salesforce account by industry type listing

List number of accounts by industry type.

select
  count(*),
  industry
from
  "<credentials_name>".salesforce_account
group by
  industry;

Basic Salesforce Assets listing

List Salesforce Assets.

select
  id,
  name,
  price,
  status,
  asset_level,
  contact_id,
  quantity
from
  "<credentials_name>".salesforce_asset;

Querying internal Salesforce Assets modified after a specific date

Retrieve internal Salesforce assets modified after a specific date.

select
  id,
  name,
  price,
  status,
  quantity
from
  "<credentials_name>".salesforce_asset
where is_internal
  and last_modified_date>'2013-05-03'::date;

Basic Salesforce Leads listing

List Salesforce Leads.

select
  id,
  name,
  company,
  email,
  industry,
  is_converted,
  rating,
  status,
  website
from
  "<credentials_name>".salesforce_lead;

Querying deleted Salesforce Pricebooks

Retrieve Salesforce deleted Pricebooks.

select
  name,
  description
  is_active,
  is_standard,
  created_by_id,
  created_date
from
  "<credentials_name>".salesforce_pricebook
where is_deleted;

Querying inactive Salesforce Pricebooks

Retrieve Salesforce inactive Pricebooks.

select
  id,
  name,
  family,
  is_active,
  created_by_id,
  quantity_unit_of_measure,
  stock_keeping_unit
from
  "<credentials_name>".salesforce_product
where not is_active;

Basic Salesforce Users listing

List Salesforce Users.

select
  username,
  alias,
  user_type,
  is_active,
  last_login_date
from
  "<credentials_name>".salesforce_user;

Removing specific Salesforce Case

Remove Salesforce Case with a specific ID.

delete from
    "<credentials_name>".salesforce_case
where
    id = :case_id

Updating details of Salesforce Users

Update the address of a specific Salesforce User.

update "<credentials_name>".salesforce_user
set
  country='US',
  state='IL',
  city='Chicago',
  street='1200 W Harrison St',
  postal_code='60607'
where first_name = 'Alice';

Inserting new Salesforce Lead

Insert a new Salesforce Lead.

insert into "<credentials_name>".salesforce_lead(
  email, is_converted, name, phone, status, annual_revenue,
  company, created_by_id, created_date, industry,
  last_modified_by_id, last_modified_date, lead_source,
  owner_id, rating, is_deleted,
  last_name, first_name, salutation, title,
  street, city, postal_code, country, currency_iso_code,
  is_unread_by_owner, system_modstamp,
  last_viewed_date, last_referenced_date,
  clean_status, is_priority_record)
values('daisy.johnson@raw-labs.com', false, 'Daisy Johnson', '‍+41 58 001 35 58', 'Working - Contacted', 900750000,
  'RAW Labs', '005Qy0000055xW1IAI', current_date, 'Agriculture',
  '005Qy0000055xW1IAI', current_date, 'Web',
  '005Qy0000055xW1IAI', 'Hot', false,
  'Johnson', 'Daisy', 'Ms.', 'PM',
  '‍EPFL Innovation Park, Building I', 'Lausanne', '1015', 'Switzerland', 'CHF',
  false, current_date,
  current_date, current_date, 'Pending', false);

Table Mappings

Once the Salesforce credential is registered, a new schema will be available for query. The name of this schema matches the name of the "Credential" you chose above.

Within this schema, you will find a set of tables, which contain the information stored in Salesforce. These tables are:

Table Name	Description
salesforce_account	Account
salesforce_account_contact_role	Account Contact Role
salesforce_asset	Asset
salesforce_contact	Contact
salesforce_contract	Contract
salesforce_lead	Leads
salesforce_object_permission	Object Permissions
salesforce_opportunity	Opportunity
salesforce_opportunity_contact_role	Opportunity Contact Role
salesforce_order	Order
salesforce_permission_set	Permission Sets
salesforce_permission_set_assignment	Permission Set Assignment
salesforce_pricebook	Pricebook
salesforce_product	Products
salesforce_user	Users
salesforce_<custom_object>__c	Dynamic/Custom objects

All custom table and column names in Salesforce are suffixed with the token __c.

Account

The salesforce_account table is a valuable resource for Sales or CRM Managers to access comprehensive information about Salesforce Accounts. These accounts represent entities like customers, competitors, and partners engaged in business interactions. The table features essential account details like names, addresses, and phone numbers. It's particularly useful for examining various aspects of accounts, such as recent activities, relationships among accounts, and confirming the accuracy of account information. This table is instrumental in providing a detailed view of business relationships and interactions within Salesforce.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_source	text	The source of the account record. For example, Advertisement, Data.com, or Trade Show.
annual_revenue	double precision	Estimated annual revenue of the account.
billing_address	jsonb	The billing adress of the account.
clean_status	text	Indicates the record's clean status as compared with Data.com. Values are: Matched, Different,Acknowledged,NotFound,Inactive,Pending, SelectMatch, or Skipped.
created_by_id	text	The id of the user who created the account.
created_date	timestamp with time zone	The creation date and time of the account.
description	text	Text description of the account.
id	text	Unique identifier of the account in Salesforce.
industry	text	Primary business of account.
is_deleted	boolean	Indicates whether the object has been moved to the Recycle Bin (true) or not (false).
last_modified_by_id	text	The id of the user who last changed the contact fields, including modification date and time.
last_modified_date	timestamp with time zone	The date and time of last modification to account.
name	text	Name of the account.
number_of_employees	double precision	Number of employees working at the company represented by this account.
owner_id	text	The ID of the user who currently owns this account. Default value is the user logged in to the API to perform the create.
ownership	text	Ownership type for the account, for example Private, Public, or Subsidiary.
phone	text	The contact's primary phone number.
rating	text	The account's prospect rating, for example Hot, Warm, or Cold.
shipping_address	jsonb	The shipping adress of the account.
sic	text	Standard Industrial Classification code of the company's main business categorization, for example, 57340 for Electronics.
ticker_symbol	text	The stock market symbol for this account.
tradestyle	text	A name, different from its legal name, that an org may use for conducting business. Similar to “Doing business as” or "DBA".
type	text	Type of account, for example, Customer, Competitor, or Partner.
website	text	The website of this account, for example, www.acme.com.

Account Contact Role

The salesforce_account_contact_role table is an essential tool within Salesforce that helps administrators and sales representatives understand the specific roles and responsibilities of contacts within accounts. This feature not only streamlines the management of customer relationships but also provides a clear insight into the hierarchical structure and interaction dynamics within an organization. It's especially useful for identifying key contacts and comprehending the intricate relationships that define customer interactions in Salesforce.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	ID of the Account.
contact_id	text	ID of the Contact associated with this account.
created_by_id	text	Id of the user who created contact role record.
created_date	timestamp with time zone	Date and time of the creation of the contact role record.
id	text	Unique identifier of the account contact role in Salesforce.
is_primary	boolean	Specifies whether the Contact plays the primary role on the Account (true) or not (false). Note that each account has only one primary contact role.
last_modified_by_id	text	Id of the user who most recently changed the contact role record.
last_modified_date	timestamp with time zone	Date of most recent change in the contact role record.
role	text	Name of the role played by the Contact on this Account, such as Decision Maker, Approver, Buyer, and so on.

Asset

The salesforce_asset table is a critical resource in Salesforce for sales and customer service teams, offering a window into the assets linked to customer accounts. This table tracks products or services sold by a company, providing details such as purchase dates, values, and current statuses. It's instrumental for understanding customer purchase patterns, managing warranties, and enhancing the quality of customer service by offering a comprehensive view of purchased items and their service histories.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	ID of the Account associated with this asset.
asset_level	bigint	The asset's position in an asset hierarchy. If the asset has no parent or child assets, its level is 1.
asset_provided_by_id	text	The account that provided the asset, typically a manufacturer.
asset_serviced_by_id	text	The account in charge of servicing the asset.
contact_id	text	ID of the Contact associated with this asset.
created_by_id	text	The id of the user who created the asset.
created_date	timestamp with time zone	Date and time of the creation of the price book record.
description	text	Description of the asset.
id	text	Unique identifier of the product in asset.
install_date	timestamp with time zone	Date when the asset was installed.
is_competitor_product	boolean	Indicates whether this Asset represents a product sold by a competitor (true) or not (false).
is_deleted	boolean	The Deleted.
is_internal	boolean	Indicates that the asset is produced or used internally (true) or not (false). Default value is false.
last_modified_by_id	text	The Last Modified By ID.
last_modified_date	timestamp with time zone	The Last Modified Date.
last_referenced_date	timestamp with time zone	The date and time that the asset was last modified.
last_viewed_date	timestamp with time zone	The date and time that the asset was last viewed.
name	text	Name of the asset.
owner_id	text	The asset's owner. By default, the asset owner is the user who created the asset record.
parent_id	text	The asset's parent asset.
price	double precision	Price paid for this asset.
product_2_id	text	ID of the Product2 associated with this asset. Must be a valid Product2 ID.
product_code	text	The product code of the related product.
purchase_date	timestamp with time zone	Date on which this asset was purchased.
quantity	double precision	Quantity purchased or installed.
root_asset_id	text	The top-level asset in an asset hierarchy. Depending on where an asset lies in the hierarchy, its root could be the same as its parent.
serial_number	text	Serial number for this asset.
status	text	Customizable picklist of values. The default picklist includes the following values: Purchased, Shipped, Installed, Registered, Obsolete.
stock_keeping_unit	text	The SKU assigned to the related product.
system_modstamp	timestamp with time zone	The System Modstamp.
usage_end_date	timestamp with time zone	Date when usage for this asset ends or expires.

Contact

The salesforce_contact table in Salesforce CRM is a pivotal tool for sales representatives and customer relationship managers, enabling them to access detailed information about customers. This table centralizes data like names, emails, phone numbers, and related account details, facilitating a deeper understanding of customer interactions, activities, and communication patterns. It's an invaluable asset for maintaining robust customer relationships and staying informed about the customer base.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	ID of the account that's the parent of this contact.
assistant_name	text	The Assistant's Name.
assistant_phone	text	The Assistant's Phone.
birthdate	timestamp with time zone	The contact's birthdate.
clean_status	text	Indicates the record's clean status.
created_by_id	text	The Created By ID.
created_date	timestamp with time zone	The Created Date.
department	text	The contact's department.
description	text	The description of the contact
email	text	The contact's email address.
email_bounced_date	timestamp with time zone	If bounce management is activated and an email sent to the contact bounces, the date and time of the bounce.
email_bounced_reason	text	If bounce management is activated and an email sent to the contact bounces, the reason for the bounce.
fax	text	The contact's fax number.
home_phone	text	The contact's home telephone number.
id	text	ID of the account that's the parent of this contact.
individual_id	text	ID of the data privacy record associated with this contact. This field is available if Data Protection and Privacy is enabled.
is_deleted	boolean	Indicates whether the object has been moved to the Recycle Bin (true) or not (false).
is_email_bounced	boolean	If bounce management is activated and an email is sent to a contact, indicates whether the email bounced (true) or not (false).
jigsaw	text	The Data.com Key.
jigsaw_contact_id	text	References the company's ID in Data.com. If an account has a value in this field, it means that the account was imported from Data.com.
last_activity_date	timestamp with time zone	Value is the most recent of either: Due date of the most recent event logged against the record. Due date of the most recently close task associated with the record.
last_cu_request_date	timestamp with time zone	The Last Stay-in-Touch Request Date.
last_cu_update_date	timestamp with time zone	The Last Stay-in-Touch Save Date.
last_modified_by_id	text	The Last Modified By ID.
last_modified_date	timestamp with time zone	The Last Modified Date.
last_referenced_date	timestamp with time zone	The timestamp when the current user last accessed this record, a record related to this record, or a list view.
last_viewed_date	timestamp with time zone	The timestamp when the current user last viewed this record or list view. If this value is null, the user might have only accessed this record or list view (LastReferencedDate) but not viewed it.
lead_source	text	The lead's source.
mailing_address	jsonb	The Mailing Address.
master_record_id	text	If this record was deleted as the result of a merge, this field contains the ID of the record that remains.
mobile_phone	text	Contact's mobile phone number.
name	text	The full name of the contact.
other_address	jsonb	The Other Address.
other_phone	text	The other phone of the contact.
owner_id	text	The ID of the owner of the account associated with this contact.
phone	text	Buisness telephone number for the contact.
photo_url	text	The Photo URL.
reports_to_id	text	The Reports To ID.
system_modstamp	timestamp with time zone	The System Modstamp.
title	text	Title of the contact, such as CEO or Vice President.

Contract

The salesforce_contract table in Salesforce is a key resource for administrators, offering a detailed view of customer agreements. This table includes essential contract details such as status, start and end dates, and associated accounts, enabling users to track and manage contract information effectively. It's particularly useful for monitoring contracts nearing expiration and verifying specific contract details, thereby facilitating efficient contract management.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	ID of the Account associated with this contract.
activated_by_id	text	ID of the User who activated this contract.
activated_date	timestamp with time zone	Date and time when this contract was activated.
billing_address	jsonb	Billing address of the account.
company_signed_date	timestamp with time zone	Date on which the contract was signed by organization.
company_signed_id	text	ID of the User who signed the contract.
contract_number	text	Number of the contract.
contract_term	bigint	Number of months that the contract is valid.
created_by_id	text	Id of the user who created the contract record.
created_date	timestamp with time zone	Date and time when contract record was created.
customer_signed_date	timestamp with time zone	Date on which the customer signed the contract.
customer_signed_id	text	ID of the Contact who signed this contract.
customer_signed_title	text	Title of the contact who signed the contract.
description	text	Statement describing the contract.
end_date	timestamp with time zone	Calculated end date of the contract. This value is calculated by adding the ContractTerm to the start_date.
id	text	Unique identifier of the contract in Salesforce.
is_deleted	boolean	Indicates whether the object has been moved to the Recycle Bin (true) or not (false).
last_activity_date	timestamp with time zone	Value is one of the following, whichever is the most recent. a) Due date of the most recent event logged against the record. b) Due date of the most recently closed task associated with the record.
last_approved_date	timestamp with time zone	Last date the contract was approved.
last_modified_by_id	text	The id of user who most recently changed the contract record.
last_modified_date	timestamp with time zone	The date and time of the last change to contract record.
last_referenced_date	timestamp with time zone	The timestamp when the current user last accessed this record, a record related to this record, or a list view.
last_viewed_date	timestamp with time zone	The timestamp when the current user last viewed this record or list view. If this value is null, the user might have only accessed this record or list view (last_referenced_date) but not viewed it.
owner_expiration_notice	text	Number of days ahead of the contract end date (15, 30, 45, 60, 90, and 120). Used to notify the owner in advance that the contract is ending.
owner_id	text	ID of the user who owns the contract.
pricebook_2_id	text	ID of the pricebook, if any, associated with this contract.
special_terms	text	Special terms that apply to the contract.
start_date	timestamp with time zone	Start date for this contract.
status	text	The picklist of values that indicate order status. A contract can be Draft, InApproval, or Activated.
system_modstamp	timestamp with time zone	The date and time when contract was last modified by a user or by an automated process.

Leads

The salesforce_lead table in Salesforce is crucial for sales managers and marketing professionals, offering a comprehensive look at potential sales opportunities. This table includes key details about leads, such as personal information, source, status, and related company data. It's invaluable for tracking prospective sales, understanding customer interests, and developing focused marketing strategies.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
address	jsonb	Street address for the lead.
annual_revenue	double precision	Annual revenue for the lead's company.
company	text	The lead's company.
converted_date	timestamp with time zone	Date on which this lead was converted.
created_by_id	text	Id of the user who created the lead.
created_date	timestamp with time zone	Creation date and time of the lead.
email	text	The lead's email address.
id	text	Unique identifier of the lead in Salesforce.
industry	text	Primary business of lead's company.
is_converted	boolean	Indicates whether the lead has been converted (true) or not (false).
last_modified_by_id	text	Id of the user who last changed the lead record.
last_modified_date	timestamp with time zone	Date and time of the last changes to lead record.
lead_source	text	Source of lead, for example, Advertisement, Partner, or Web.
name	text	Name of the lead, as displayed on lead detail page.
number_of_employees	bigint	Number of employees at the lead's company.
owner_id	text	Id of the assigned owner of the lead.
phone	text	Lead's primary phone number.
rating	text	Indicates value or prospect of the lead, for example, Hot, Warm, Cold.
status	text	Status of the lead, for example, Open, Contacted, Qualified.
website	text	URL of the lead's company's website.

Object Permissions

The salesforce_object_permission table is an essential tool for Salesforce administrators, providing a detailed overview of user permissions on Salesforce objects. This table includes specific information such as the type of object, the nature of the permission, and the user or profile associated with each permission. It's a valuable resource for managing and ensuring appropriate user access levels to various Salesforce objects like accounts, contacts, leads, and opportunities, thereby maintaining the security and integrity of Salesforce data.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
id	text	The ObjectPermissions ID.
parent_id	text	The Id of this object's parent PermissionSet.
permissions_create	boolean	If true, users assigned to the parent PermissionSet can create records for this object. Requires PermissionsRead for the same object to be true.
permissions_delete	boolean	If true, users assigned to the parent PermissionSet can delete records for this object. Requires PermissionsRead and PermissionsEdit for the same object to be true.
permissions_edit	boolean	If true, users assigned to the parent PermissionSet can edit records for this object. Requires PermissionsRead for the same object to be true.
permissions_modify_all_records	boolean	If true, users assigned to the parent PermissionSet can edit all records for this object, regardless of sharing settings. Requires PermissionsRead, PermissionsDelete, PermissionsEdit, and PermissionsViewAllRecords for the same object to be true.
permissions_read	boolean	If true, users assigned to the parent PermissionSet can view records for this object.
permissions_view_all_records	boolean	If true, users assigned to the parent PermissionSet can view all records for this object, regardless of sharing settings. Requires PermissionsRead for the same object to be true.
sobject_type	text	The object's API name. For example, Merchandise__c.

Opportunity

The salesforce_opportunity table in Salesforce Sales Cloud is a vital resource for sales managers and data analysts, offering a comprehensive view of the sales pipeline and potential revenue. This table details each opportunity, including aspects like deal value, expected closing date, and associated account information. It enables users to track sales performance, assess the value of deals in the pipeline, and monitor the progression of deals through various sales stages.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	ID of the account associated with this opportunity.
amount	double precision	Estimated total sale amount. For opportunities with products, the amount is the sum of the related products.
campaign_id	text	ID of a related Campaign. This field is defined only for those organizations that have the campaign feature Campaigns enabled.
close_date	timestamp with time zone	Date when the opportunity is expected to close.
created_by_id	text	Id of the user who created the opportunity.
created_date	timestamp with time zone	The creation date and time of the opportunity.
description	text	Description of the opportunity.
expected_revenue	double precision	Calculated revenue based on the Amount and Probability fields.
fiscal_quarter	bigint	Represents the fiscal quarter. Valid values are 1, 2, 3, or 4.
fiscal_year	bigint	Represents the fiscal year, for example, 2006.
forecast_category	text	Forecast category name displayed in reports, opportunity detail and edit pages, opportunity searches, and opportunity list views.
forecast_category_name	text	Name of the forecast category.
has_open_activity	boolean	Indicates whether an opportunity has an open event or task (true) or not (false).
has_opportunity_line_item	boolean	Indicates whether the opportunity has associated line items. A value of true means that Opportunity line items have been created for the opportunity.
has_overdue_task	boolean	Indicates whether an opportunity has an overdue task (true) or not (false).
id	text	Unique identifier of the opportunity in Salesforce.
is_closed	boolean	Indicates that the opportunity is closed.
is_deleted	boolean	Indicates that the opportunity is deleted.
is_private	boolean	Indicates that the opportunity is private.
is_won	boolean	Indicates that the quote or proposal has been signed or electronically accepted.
last_activity_date	timestamp with time zone	Value is one of the following, whichever is the most recent of a) Due date of the most recent event logged against the record or b) Due date of the most recently closed task associated with the record.
last_modified_by_id	text	The id of the user who last modified the oppurtinity record.
last_modified_date	timestamp with time zone	The data and time of the last modification of the oppurtinity record.
lead_source	text	Source of this opportunity, such as Advertisement or Trade Show.
name	text	A name for this opportunity.
next_step	text	Description of next task in closing opportunity.
owner_id	text	ID of the User who has been assigned to work this opportunity.
pricebook_2_id	text	ID of a related Pricebook2 object. The Pricebook2Id field indicates which Pricebook2 applies to this opportunity. The Pricebook2Id field is defined only for those organizations that have products enabled as a feature.
probability	double precision	Percentage of estimated confidence in closing the opportunity.
stage_name	text	Current stage of opportunity.
system_modstamp	text	The date and time when opportunity was last modified by a user or by an automated process.
total_opportunity_quantity	text	Number of items included in this opportunity. Used in quantity-based forecasting.
type	text	Type of opportunity, such as Existing Business or New Business.

Opportunity Contact Role

The salesforce_opportunity_contact_role table in Salesforce is an invaluable tool for sales managers and business analysts. It sheds light on the roles contacts play in specific sales opportunities, such as their level of influence and involvement. This table is key to understanding the dynamic relationships between contacts and opportunities, identifying decision-makers and key influencers in business deals.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
contact_id	text	ID of an associated Contact.
created_by_id	text	Id of the user who created contact role record.
created_date	timestamp with time zone	Date and time of the creation of the contact role record.
id	text	Unique identifier of the opportunity contact role in Salesforce.
is_primary	boolean	Indicates whether the associated Contact plays the primary role on the Opportunity (true) or not (false).
last_modified_by_id	text	Id of the user who most recently changed the contact role record.
last_modified_date	timestamp with time zone	Date of most recent change in the contact role record.
opportunity_id	text	Required. ID of an associated Opportunity.
role	text	Name of the role played by the associated Contact on the Opportunity, such as Business User or Decision Maker.

Order

The salesforce_order table in Salesforce is a key resource for Sales and Customer Service teams, offering a comprehensive view of customer orders. This table includes detailed information on each order, such as the order number, status, account details, and related metadata. It's particularly useful for tracking the status of orders, whether they are pending or completed, and associating them with specific accounts, thus enhancing order management and customer service efficiency.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	ID of the Account associated with this order.
activated_by_id	text	ID of the User who activated this order.
activated_date	timestamp with time zone	Date and time when the order was activated.
bill_to_contact_id	text	ID of the contact that the order is billed to.
billing_address	jsonb	The billing adress for the order.
company_authorized_by_id	text	ID of the user who authorized the account associated with the order.
company_authorized_date	timestamp with time zone	The date on which your organization authorized the order.
contract_id	text	ID of the contract associated with this order. Can only be updated when the order's StatusCode value is Draft.
created_by_id	text	Id of the user who created the order record.
created_date	timestamp with time zone	Creation date and time of the order record.
customer_authorized_by_id	text	ID of the contact who authorized the order.
customer_authorized_date	timestamp with time zone	Date on which the contact authorized the order.
description	text	Description of the order.
effective_date	timestamp with time zone	Date at which the order becomes effective.
end_date	timestamp with time zone	Date at which the order ends.
id	text	Unique identifier of the order in Salesforce.
is_deleted	boolean	Indicates that the order is deleted.
is_reduction_order	boolean	Determines whether an order is a reduction order.
last_modified_by_id	text	Id of the user who most recently changed the order record.
last_modified_date	timestamp with time zone	Date of most recent change in the order record.
last_referenced_date	timestamp with time zone	The timestamp when the current user last accessed this record, a record related to this record, or a list view.
last_viewed_date	timestamp with time zone	The timestamp when the current user last viewed this record or list view. If this value is null, the user might have only accessed this record or list view (LastReferencedDate) but not viewed it.
name	text	Title for the order that distinguishes it from other orders.
order_number	text	Order number assigned to this order.
order_reference_number	text	Reference number assigned to the order.
original_order_id	text	Optional. ID of the original order that a reduction order is reducing, if the reduction order is reducing a single order.
owner_id	text	ID of the User or queue that owns this order.
po_date	timestamp with time zone	Date of the purchase order.
po_number	text	Number identifying the purchase order.
pricebook_2_id	text	ID of the price book associated with this order.
ship_to_contact_id	text	ID of the contact that the order is shipped to.
shipping_address	jsonb	The shipping adress for the order.
status	text	The Status field specifies the current state of an order. Status strings represent its current state (Draft or Activated).
status_code	text	Status code of the stage that the order has reached in the order business process.
system_modstamp	timestamp with time zone	The date and time when order record was last modified by a user or by an automated process.
total_amount	double precision	Total amount of the order.
type	text	Type of order.

Permission Sets

The salesforce_permission_set table is crucial for Salesforce administrators, offering insights into the Permission Sets within a Salesforce organization. This table details each permission set, including the specific permissions granted, access settings, and associated metadata. It's invaluable for managing user access, understanding the permissions within each set, and verifying access configurations.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
created_by_id	text	The contact id of the user who created this permission set.
created_date	timestamp with time zone	The Created Date.
description	text	The description of the permission set.
has_activation_required	boolean	Indicates whether the permission set requires an associated active session (true) or not (false).
id	text	The unique id of the permission set.
is_custom	boolean	If true, the permission set is custom (created by an admin); if false, the permission set is standard and related to a specific permission set license.
is_owned_by_profile	boolean	If true, the permission set is owned by a profile.
label	text	The permission set label, which corresponds to Label in the user interface.
last_modified_by_id	text	The Last Modified By ID.
last_modified_date	timestamp with time zone	The Last Modified Date.
license_id	text	The ID of either the related PermissionSetLicense or UserLicense associated with this permission set.
name	text	The permission set unique name in the API.
namespace_prefix	text	The namespace prefix for a permission set that's been installed as part of a managed package. If the permission set isn't packaged or is part of an unmanaged package, this value is empty.
permission_set_group_id	text	If the permission set is owned by a permission set group, this field returns the ID of the permission set group.
profile_id	text	If the permission set is owned by a profile, this field contains the ID of the Profile.
system_modstamp	timestamp with time zone	The date and time when order record was last modified by a user or by an automated process.

Permission Set Assignment

The salesforce_permission_set_assignment table in Salesforce is a key tool for administrators, providing a clear view of the Permission Set Assignments. This table helps in reviewing specific user permissions and access settings, enabling administrators to efficiently manage user access and security settings. It's particularly useful for monitoring assignments with particular permissions, understanding user-permission relationships, and ensuring adherence to access policies.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
assignee_id	text	ID of the User to assign the permission set specified in PermissionSetId.
id	text	The Permission Set Assignment ID.
permission_set_group_id	text	If associated with a permission set group, this is the ID of that group.
permission_set_id	text	ID of the PermissionSet to assign to the user specified in AssigneeId.
system_modstamp	timestamp with time zone	The Date Assigned.

Pricebook

The salesforce_pricebook table in Salesforce is an essential resource for sales managers and business analysts, offering insights into the pricing structures of products. This table details various aspects of Pricebooks, including related products, standard pricing, and relevant metadata. It's a valuable tool for analyzing different pricing strategies tailored to specific business units or customers, and for verifying the consistency of pricing policies.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
created_by_id	text	The id of the user who created the price book.
created_date	timestamp with time zone	Date and time of the creation of the price book record.
description	text	Description of the price book.
id	text	Unique identifier of the product in pricebook.
is_active	boolean	Indicates whether the price book is active (true) or not (false).
is_archived	boolean	Describes whether the price book is archived. The default value is false.
is_deleted	boolean	Indicates whether the price book has been archived (true) or not (false).
is_standard	boolean	Indicates whether the price book is the standard price book for the org (true) or not (false). Every org has one standard price book—all other price books are custom price books.
last_modified_by_id	text	Id of the user who most recently changed the product record.
last_modified_date	timestamp with time zone	Date of most recent change in the product record.
last_referenced_date	timestamp with time zone	The timestamp for when the current user last viewed a record related to this record.
last_viewed_date	timestamp with time zone	The timestamp for when the current user last viewed this record. If this value is null, it's possible that this record was referenced (LastReferencedDate) and not viewed.
name	text	The Price Book Name.
system_modstamp	text	The date and time when order record was last modified by a user or by an automated process.

Products

The salesforce_product table in Salesforce is a valuable resource for Sales and Business Analysts, offering a detailed view of the products offered by a business. This table includes information on both standard and custom fields, allowing users to gain insights into the products, their associations with Opportunities or Quotes, and to verify the details of custom fields.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
created_by_id	text	The id of the user who created the product record, with the date and time of creation.
created_date	timestamp with time zone	Date and time of the creation of the product role record.
description	text	Description of the product.
display_url	text	URL leading to a specific version of a record in the linked external data source.
external_data_source_id	text	The id of the related external data source.
external_id	text	The unique identifier of a record in the linked external data source.
family	text	Name of the product family associated with this record.
id	text	Unique identifier of the product in Salesforce.
is_active	boolean	Indicates that the product is ready for use in a price book, opportunity, or quote, and whether you can see the product in views.
is_archived	boolean	Describes whether the product is archived. The default value is false.
is_deleted	boolean	Indicates whether the object has been moved to the Recycle Bin (true) or not (false).
last_modified_by_id	text	Id of the user who most recently changed the product record.
last_modified_date	timestamp with time zone	Date of most recent change in the product record.
last_referenced_date	timestamp with time zone	The timestamp when the current user last viewed product record.
last_viewed_date	timestamp with time zone	The timestamp when the current user last viewed this record. If this value is null, this record might only have been referenced (last_referenced_date) and not viewed by the current user.
name	text	The product's name.
product_code	text	The internal code or product number that you use to identify the product.
quantity_unit_of_measure	text	Unit of the product—for example, kilograms, liters, or cases.
stock_keeping_unit	text	The product's SKU, which can be used with or in place of the Product Code field.
system_modstamp	text	The date and time when order record was last modified by a user or by an automated process.

Users

The salesforce_user table in Salesforce is an important tool for administrators and auditors, providing comprehensive insights into the users of a Salesforce organization. This table helps explore individual user profiles, including their roles, status, and level of access. It's useful for assessing user roles, understanding access levels, and monitoring user activity statuses within the organization.

Fields

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	ID of the Account associated with a Customer Portal user. This field is null for Salesforce users.
alias	text	The user's alias. For example, jsmith.
created_by_id	text	Id of the user who created the user including creation date and time.
department	text	The company department associated with the user.
email	text	The user's email address.
employee_number	text	The user's employee number.
forecast_enabled	boolean	Indicates whether the user is enabled as a forecast manager (true) or not (false).
id	text	Unique identifier of the user in Salesforce.
is_active	boolean	Indicates whether the user has access to log in (true) or not (false).
last_login_date	timestamp with time zone	The date and time when the user last successfully logged in. This value is updated if 60 seconds elapses since the user's last login.
last_modified_by_id	text	Id of the user who last changed the user fields, including modification date and time.
name	text	Display name of the user.
profile_id	text	ID of the user's Profile.
state	text	The state associated with the User.
user_type	text	The category of user license. Can be one of Standard, PowerPartner, CSPLitePortal, CustomerSuccess, PowerCustomerSuccess, CsnOnly, and Guest.
username	text	Login name of the user.

Dynamic Objects

Salesforce dynamic objects are a feature in Salesforce that allows for the flexible creation and management of custom objects. These objects can be tailored to meet the specific needs of a business and are used to store information unique to the organization. Dynamic objects in Salesforce are highly customizable, enabling users to define their own fields, relationships, and behaviors. This functionality is particularly useful for organizations that require specialized data structures beyond the standard Salesforce objects, allowing for a more tailored CRM experience.

The salesforce_{object_name}__c table in Salesforce, where {object_name} represents the custom object name and __c is automatically added by the platform, is a resource for administrators and developers that offers a detailed understanding of a specific dynamic Salesforce object. This table provides a deep dive into the properties, relationships, and metadata of the object, aiding in a comprehensive analysis of its characteristics, how it interacts with other objects, and the ways it can be manipulated within the Salesforce ecosystem.

Fields

Let's create a custom object called Customer Feedback. This object holds feedback of customers for specific products. It stores pointers to Contact and Product Salesforce objects.

To create this object we need to log into our Salesforce account, go to Setup, then Object Manager and create the object with the following fields:

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
id	text	ID of the customer feedback entry.
owner_id	text	The ID of the user who currently owns this account. Default value is the user logged in to the API to perform the create.
created_date	timestamp with time zone	Date and time of the creation of the product role record.
created_by_id	text	The id of the user who created the product record, with the date and time of creation.
last_modified_by_id	text	The id of the user who last changed the contact fields, including modification date and time.
last_modified_date	timestamp with time zone	The date and time of last modification to account.
system_modstamp	timestamp with time zone	The System Modstamp.
last_viewed_date	timestamp with time zone	The date and time that the asset was last viewed.
last_referenced_date	timestamp with time zone	The date and time that the asset was last modified.
contact_id__c	text	CUSTOM FIELD for the ID of the Contact associated with this asset.
product_id__c	text	CUSTOM FIELD for the ID of the Product associated with this asset.
date_received__c	timestamp with time zone	CUSTOM FIELD for the creation date of the customer feedback.
feedback_type__c	text	CUSTOM FIELD for the type of feedback, e.g. Complaint, Compliment, Suggestion and Inquiry.
description__c	text	CUSTOM FIELD for the customer feedback text.

This is how our custom object Customer Feedback is mapped to the logical relational table salesforce_customer_feedback__c:

note

All custom table and column names in Salesforce are suffixed with the token __c.

Acknowledgement

We would like to acknowledge the amazing work from the Steampipe community, from which this documentation and examples were derived.