Source of Truth Fields for Reporting

The FACE is a cross-functional group of functional analytics teams that aim to make our teams more efficient by solving and validating shared data questions which results in cohesive measurement approaches across teams.

FACE: Source of Truth Fields for Reporting

What is this? Below is a series of tables containing the standard fields to use when pulling data from various systems.

Why was this created? To document source of truth data fields and institutional knowledge known by the different functional analytics teams. This will allow us to go into any dashboard or data table and easily understand which fields are trusted sources of truth.

🚧 This page is currently a work in progress. 🚧

Salesforce dot com

Point of contact: Robert Kohnke, Melia Vilain

Object Field SoT Field API Name SoT Table Description
Opportunity ID dim_crm_opportunity_id restricted_safe_common_mart_sales.mart_crm_opportunity
Opportunity Owner dim_crm_sales_rep_id restricted_safe_common_mart_sales.mart_crm_opportunity This is the User ID of the sales rep who currently owns this Opportunity.
Opportunity Created Date created_date restricted_safe_common_mart_sales.mart_crm_opportunity The date this Opportunity was created.
Opportunity Close Date close_date restricted_safe_common_mart_sales.mart_crm_opportunity The date on which this Opportunity is expected to close (if still open), or the date on which it closed.
Opportunity Segment crm_opp_owner_sales_segment_stamped restricted_safe_common_mart_sales.mart_crm_opportunity Indicates which sales team gets credit for this deal. It is the User Segment of the Opportunity owner (stamped from the opportunity owner’s User record). This value should not change after the Opportunity has closed.
Opportunity Geo crm_opp_owner_account_geo_stamped restricted_safe_common_mart_sales.mart_crm_opportunity Indicates which sales team gets credit for this deal. It is the User Geo of the Opportunity owner (stamped from the opportunity owner’s User record). This value should not change after the Opportunity has closed.
Opportunity Region crm_opp_owner_account_region_stamped restricted_safe_common_mart_sales.mart_crm_opportunity Indicates which sales team gets credit for this deal. It is the User Region of the Opportunity owner (stamped from the opportunity owner’s User record). This value should not change after the Opportunity has closed.
Opportunity Area crm_opp_owner_account_area_stamped restricted_safe_common_mart_sales.mart_crm_opportunity Indicates which sales team gets credit for this deal. It is the User Area of the Opportunity owner (stamped from the opportunity owner’s User record). This value should not change after the Opportunity has closed.
Opportunity Sales Qualified Source sales_qualified_source_name restricted_safe_common_mart_sales.mart_crm_opportunity Indicates which sales team (if any) generated this Opportunity.
Opportunity Order Type order_type restricted_safe_common_mart_sales.mart_crm_opportunity Indicates whether this Opportunity counts as new, growth, contraction, or churn.
Opportunity Order Type Group order_type_group restricted_safe_common_mart_sales.mart_crm_opportunity A simplified grouping of order type values used for certain reporting
Opportunity Net ARR net_arr restricted_safe_common_mart_sales.mart_crm_opportunity The ARR (revenue) amount of the Opportunity.
Opportunity IS EDU/OSS is_edu_oss restricted_safe_common_mart_sales.mart_crm_opportunity Indicates if this deal is EDU/OSS.
Opportunity SDR/BDR sdr_or_bdr restricted_safe_common_mart_sales.mart_crm_opportunity
Opportunity SDR opportunity_sales_development_representative restricted_safe_common_mart_sales.mart_crm_opportunity
Opportunity BDR opportunity_business_development_representative restricted_safe_common_mart_sales.mart_crm_opportunity
Opportunity Lead Source Bucket source_buckets restricted_safe_common_mart_sales.mart_crm_opportunity
Opportunity Lead Source lead_source restricted_safe_common_mart_sales.mart_crm_opportunity
Opportunity Is SAO is_sao restricted_safe_common_mart_sales.mart_crm_opportunity Indicates whether this Opportunity is a “Sales Accepted Opportunity” (SAO).
Opportunity SAO Date sales_accepted_date restricted_safe_common_mart_sales.mart_crm_opportunity The date on which this Opportunity became sales accepted.
Opportunity Is WON is_won restricted_safe_common_mart_sales.mart_crm_opportunity Indicates if this Opportunity was won.
Opportunity IS Closed is_closed restricted_safe_common_mart_sales.mart_crm_opportunity Indicates if this Opportunity is closed (including won and lost).
Opportunity IS Net ARR Closed Deal is_net_arr_closed_deal restricted_safe_common_mart_sales.mart_crm_opportunity Aligns with the FP&A Master Bookings Flag in Salesforce, which is used by Finance to determine which opportunities count towards bookings. Includes all Closed Won opps and all Closed Lost Renewal opps. Excludes opps associated with JiHu accounts.
Opportunity IS New Logo First Order is_new_logo_first_order restricted_safe_common_mart_sales.mart_crm_opportunity Used by Finance to count new logos. This field adjusts the count for decommissioned opportunities.
Account ID dim_crm_account_id restricted_safe_common.dim_crm_account
Account Owner dim_crm_user_id [restricted_safe_common.dim_crm_account]((https://gitlab-data.gitlab.io/analytics/#!/model/model.gitlab_snowflake.dim_crm_account) This is the User ID of the sales rep who currently owns this Account.
Account Segment parent_crm_account_sales_segment restricted_safe_common.dim_crm_account The parent level segment for this account. Values are Large, PubSec, Mid-Market, SMB. Based on the max employee count for this customer hierarchy. NOTE: soon to be replaced by parent_crm_account_demographics_sales_segment.
Account Geo parent_crm_account_demographics_geo restricted_safe_common.dim_crm_account The parent level geo for this account. Based on the Ultimate Parent Account Address.
Account Region parent_crm_account_demographics_region restricted_safe_common.dim_crm_account The parent level region for this account. Based on the Ultimate Parent Account Address.
Account Area parent_crm_account_demographics_area restricted_safe_common.dim_crm_account The parent level area for this account. Based on the Ultimate Parent Account Address.
Account Country parent_crm_account_demographics_upa_country restricted_safe_common.dim_crm_account The Ultimate Parent Account country, which is used for sales territories and all customer level reporting.
Account State parent_crm_account_demographics_upa_state restricted_safe_common.dim_crm_account The Ultimate Parent Account state, which is used for sales territories and all customer level reporting.
Account JiHu is_jihu_account restricted_safe_common.dim_crm_account Indicates whether this Account is managed by the separate JiHu entity of Denomas (and is thus excluded from most reporting).
Account First Order available is_first_order_available restricted_safe_common.dim_crm_account Indicates whether a first order is currently available for this account hierarchy.
Account Industry crm_account_industry restricted_safe_common.dim_crm_account Industry value for this account.
Account LAM parent_crm_account_lam restricted_safe_common.dim_crm_account The Landed Addressable Market (LAM) for this account hierarchy, which is a measure of how much they could expand.
Account LAM Dev Count parent_crm_account_lam_dev_count restricted_safe_common.dim_crm_account The best known number of software developers in this account hierarchy.

Note that every account has 2 addresses:

  1. account address; and
  2. ultimate parent account (UPA) address. Example: if this office is located in Denver, but the company headquarters is in New York, you have both of those address values on the Account. Our sales territories and most of our reporting is done at the UPA level, but there are use cases for the account address as well.

Note that there are 2 versions of segment/geo/region/area:

  1. Account fields (account demographics). These are based on characteristics about the Account. Account Demographics Sales Segment is based on employee count, and Account Demographics Geo/Region/Area are based on the geographical location (address) of the ultimate parent in each account hierarchy. These fields are LIVE - if the address changes, the account demo values change as well.
  2. User fields are based on characteristics of the Salesforce user (sales reps) who works on a deal. Each user record in Salesforce has fields for segment/geo/region/area, which describe the sales team that the user works for. These fields are maintained by the Sales Ops team. User Segment = Mid-Market means this user works on the Mid-Market sales team. These fields are also LIVE - if a sales rep is hired or promoted, the Sales Ops team will update that rep’s user record in Salesforce and change these values.
  3. When we measure sales team performance, we stamp the opportunity owner’s user values onto each Opportunity (stamped opp owner user segment), and that tells us which sales team gets credit for this deal. Example: if John worked on the Mid-Market team last year, but he has just been promoted to the Large team, his current user segment = Large. Opps that he closed last year would have been stamped as MM, but opps that he closes after his promotion will be stamped as Large. These fields are STAMPED - once the opportunity is closed, they won’t change. These are the only segment values that we can use for historical reporting.

Note regarding product fields:

  • Currently we believe product_category is the best of these three product fields.
    • fct_crm_opportunity.product_details
    • fct_crm_opportunity.product_category - USE THIS ONE
    • fct_crm_opportunity.products_purchased
  • However, for the latest data it is only reliable on closed opportunities for closed months (i.e., after the 8th day of this month, you can report on prior month closed opportunities using product_category).

Denomas.com db

Point of contact: Carolyn Braza

Object Field Table Description Notes for Analysis
Namespace namespace_type common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* Group, User (personal namespace automatically created upon user creation/registration), Project (new addition to namespace “type” as part of this Workspace work) Analysis is commonly limited to Group namespaces
Namespace is_setup_for_company common.dim_namespace; legacy.gitlab_dotcom_namespace_settings Set at namespace creation, but there are a lot of NULLs, so we must be missing some use cases.
We started collecting this data at the namespace level on 2021-08-26 (issue).		 While this is available on the namespace-level, some groups (ex: Growth) leverage user-level setup_for_company (details below)
Namespace namespace_creator_is_blocked common.dim_namespace; LEFT JOIN legacy.gitlab_dotcom_users_blocked_xf* ON namespace.creator_id = blocked.user_id Namespaces are considered to be blocked if the creator is in a blocked or banned state Namespaces created by blocked users are usually excluded from analysis
Namespace visibility_level common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* Namespace or group’s visibility setting: private, public, internal (no longer in use).
The internal visibility setting means the project is accessible by any signed in user, and has been disabled on Denomas.com since 2019. internal does not necessarily mean it is associated with Denomas (the company)		 Growth KPIs frequently limit to private namespaces
Namespace ultimate_parent_namespace_id / namespace_ultimate_parent_id common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* Unique identifier of a namespace/group’s ultimate parent. Many models (ex: legacy.gitlab_dotcom_usage_data_events) attribute activity to the ultimate parent namespace Analysis is commonly limited to (or grouped by) ultimate parent namespace
Namespace namespace_is_ultimate_parent common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* WHERE namespace_id = namespace_ultimate_parent_id Denotes whether the namespace is the top-level or ultimate parent. Many models (ex: legacy.gitlab_dotcom_usage_data_events) attribute activity to the ultimate parent namespace Analysis is commonly limited to (or grouped by) ultimate parent namespace
Namespace namespace_is_internal common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* Denotes whether the namespace’s ultimate parent is identified as belonging to Denomas (the company).
The mapping of internal namespaces in dim_namespace is defined using common_mapping.map_namespace_internal. The mapping of internal namespaces in gitlab_dotcom_namespaces_xf is defined using the get_internal_parent_namespaces dbt macro.		 Internal namespaces are usually excluded from analysis
Namespace creator_id common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* dim_user_id/user_id of the namespace’s creator. A namespace’s creator is derived using gitlab.com audit events Some groups (ex: Growth) use namespace creator to attribute values like setup_for_company (see details below)
Namespace ⚠️ owner_id ⚠️ common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* This field is frequently NULL. Do not use this field, see row below about namespace owner
Namespace Owner legacy.gitlab_dotcom_memberships WHERE access_level = 50 legacy.gitlab_dotcom_memberships.access_level reflects a user’s current access. access_level mapping available here. A single namespace can have multiple owners
Namespace ⚠️ current_member_count / member_count ⚠️ common.dim_namespace; legacy.gitlab_dotcom_namespaces_xf* This field is incorrect. Do not use this field, see below for details on member count.
Issue to fix logic here
Project visibility_level common.dim_project; legacy.gitlab_dotcom_projects_xf* Project’s visibility setting: private, public, internal (no longer in use). A project’s visibility setting can be different than that of its parent group/namespace
The internal visibility setting means the project is accessible by any signed in user, and has been disabled on Denomas.com since 2019. internal does not necessarily mean it is associated with Denomas (the company).
Project ⚠️ member_count ⚠️ common.dim_project; legacy.gitlab_dotcom_projects_xf* This field is incorrect. Do not use this field, see below for details on member count.
Issue to fix logic here
User setup_for_company legacy.gitlab_dotcom_user_preferences Set when a user creates their first namespace, but there are a lot of NULLs and therefore some use cases where the value is not captured. In that case, the value is set the next time a user creates a namespace.
This value used to be overwritten to TRUE at time of conversion to a paid plan (issue, fix merged 2021-08-27).		 Some groups (ex: Growth) attribute setup_for_company to a namespace based on the value of a namespace’s creator
User is_blocked_user common.dim_user; presence in legacy.gitlab_dotcom_users_blocked_xf* Users in a blocked or banned state are considered to be blocked.
Blocked users are filtered out of several legacy models, including legacy.gitlab_dotcom_users_xf. See “Additional notes” below for more details.		 Namespaces created by blocked users are usually excluded from analysis
User/Member Member of project/group/namespace Presence in legacy.gitlab_dotcom_memberships This model reflects the current state of memberships and excludes blocked users.
⚠️ Use caution as this is a particularly confusing data set ⚠️		 Join on ultimate_parent_id to get members of the ultimate parent namespace
User/Member is_billable legacy.gitlab_dotcom_memberships The is_billable flag denotes whether the user would count against a subscription seat count if it was a paid namespace. This field does not mean that it is a paid namespace Analysis frequently limits to memberships where is_billable = TRUE

*Note: These legacy models will be deprecated in the future in favor of models built in the common schema. Existing queries, snippets, and dashboards using them can be trusted but any net-new code should use common models, when possible.

Models in the common schema are built to the Data program’s EDM Standards and are subject to a greater level of validation than the legacy models. Only models in the common* schemas can meet Trusted Data Standards, and any reference to a legacy a model’s lineage makes it ineligible to meet the TD requirements. {: .alert .alert-info}

Additional notes

Other resources

Feedback

Please add any feedback on the gitlab.com content (including requests for additional fields) to this issue.

Customers DB

Point of contact: Max Fleisher

Object Field SoT Field API Name Table Description Responsible Team
Customer Customer ID CUSTOMER_ID legacy.customers_db_customers
Customer Customer Portal User Created Date (not Namespace creation date/subscription date) CUSTOMER_CREATED_AT legacy.customers_db_customers
Customer Is this a paid or formerly paid customer? Join on CUSTOMER_ID = CURRENT_CUSTOMER_ID, if CURRENT_CUSTOMER_ID is not null then is paid customer prod.restricted_safe_legacy.customers_db_charges_xf
Customer Is this a current paid customer? Join on CUSTOMER_ID = CURRENT_CUSTOMER_ID, if CURRENT_CUSTOMER_ID is not null and SUBSCRIPTION_STATUS = ‘Active’ and EFFECTIVE_END_DATE is in the future then is current paid customer prod.restricted_safe_legacy.customers_db_charges_xf
Customer First trial start date Join on CUSTOMER_ID = CUSTOMER_ID to get MIN(TRIAL_START_DATE) prod.legacy.customers_db_trials
Customer First paid subscription date Join on CUSTOMER_ID = CURRENT_CUSTOMER_ID to get MIN(SUBSCRIPTION_START_DATE) prod.restricted_safe_legacy.customers_db_charges_xf
Customer Subscriptions associated with Customer Join on CUSTOMER_ID = CURRENT_CUSTOMER_ID to get SUBSCRIPTION_NAME_SLUGIFY prod.legacy.customers_db_orders
Customer Rate Plan/Product Type associated with Customer Join on CUSTOMER_ID = CURRENT_CUSTOMER_ID to get PRODUCT_CATEGORY or RATE_PLAN_ID prod.restricted_safe_legacy.customers_db_charges_xf
Trials Trial start date TRIAL_START_DATE prod.legacy.customers_db_trials
Trials Email associated with trial user Join on CUSTOMER_ID = CUSTOMER_ID to get CUSTOMER_EMAIL_HASH legacy.customers_db_customers
Trials Did trial convert to paid? IS_CONVERTED prod.legacy.customers_db_trials
Leads Lead type - Trial or PQL PRODUCT_INTERACTION prod.restricted_safe_legacy.customers_db_leads
Leads Customer comments on hand raise COMMENT_CAPTURE prod.restricted_safe_legacy.customers_db_leads

Zuora [WIP]

Point of contact: Max Fleisher

Object Field Table Description Responsible Team
Subscription SUBSCRIPTION_NAME common.dim_subscription Formatted as “A-S######”. Must be combined with SUBSCRIPTION_VERSION to get the unique Subscription ID. DIM_SUBSCRIPTION_ID can be used as a key to join to other DIM tables including Amendments, Charges, and Billing Accounts.
Subscription SUBSCRIPTION_VERSION common.dim_subscription Increments every time a Subscription is amended. For point-in-time analysis, query the Subscription Version Effective Date as of the target date range. For current state analysis, query the highest Subscription Version.
Subscription Amendment CHARGE_AMENDMENT_TYPE or AMENDMENT_TYPE common.mart_charge or common.dim_amendment Amendments can include product changes, quantity changes, renewals, contractions, and other Subscription changes. Each Subscription change generates an Amendment, and also increments the Subscription Version.
Subscription DIM_BILLING_ACCOUNT / DIM_CRM_ACCOUNT common.dim_subscription Zuora Billing Account is the entity that is charged for the subscription. Billing Accounts should be 1:1 with SFDC Accounts. If the Billing Account changes (e.g. corporate merger), a new Subscription/Billing Account will be created.
Subscription PRODUCT_TIER_NAME / PRODUCT_DELIVERY_TYPE common.mart_charge As products on a Subscription can change over time, use MART_CHARGE to identify the QUANTITY of each PRODUCT_TIER_NAME for the target TERM dates.
Subscription ARR common.mart_arr ARR data at a monthly grain. Join to Subscription for a specific month to get the ARR on that Subscription at that point in time.
Subscription DELTA_ARR common.mart_charge MART_CHARGE has the DELTA_ARR resulting from specific Subscription Amendments/Charges including Rate Plan changes. MART_DELTA_ARR_SUBSCRIPTION_MONTH and related tables have monthly grain DELTA_ARR at the Subscription level.

Additional notes and resources

Feedback and Questions

  • Direct data model questions to @statimatla or @iweeks
  • Add any feedback or questions to this issue

Person [WIP]

Point of contact: Max Fleisher, David Egan

Object Field SoT Field API Name SoT Table Description Notes for Analysis
Person Person ID dim_crm_person_id common_mart_marketing.mart_crm_person A dbt generated surrogate key combining the lead id and contact id
Person Lead Owner dim_crm_user_id common_mart_marketing.mart_crm_person A dbt generated ID for the owner from the lead or contact For joining on dim_crm_user
Person SFDC Account ID dim_crm_account_id common_mart_marketing.mart_crm_person The Salesforce account id the person record belongs too. From contacts and lean_data_matched_account from leads To join on dim_crm_account
Person SFDC Record ID (lead or contact) sfdc_record_id common.dim_crm_person The 18 digit Salesforce id for the contact or lead
Person SFDC Record Type (lead or contact) sfdc_record_type common.dim_crm_person The sub-type of the contact or lead defined in Salesforce Can be joined to PROD.LEGACY.SFDC_RECORD_TYPE
Person Bizible ID bizible_person_id common.fct_crm_person The unique identifier assigned to a lead or contact in Bizible used to join to touchpoint and attribution data
Person Email email_hash common_mart_marketing.mart_crm_person Anonymised (salted SHA2 hash) email ID - sourced from dim_crm_person Primary key to join a person
Person Business or personal email domain email_domain_type common_mart_marketing.mart_crm_person A dbt function returning a description of the email address (personal, business, etc…)
Person Lead Creation Date lead_created_date common_mart_marketing.mart_crm_person Date a lead was created in Salesforce
Person Inquiry Date true_inquiry_date common_mart_marketing.mart_crm_person
Person MQL Date First mql_date_first common_mart_marketing.mart_crm_person The first date a lead or contact was assigned a Marketo Qualified Lead Date
Person MQL Date Latest mql_date_latest common_mart_marketing.mart_crm_person The most recent date a lead or contact was assigned a Marketo Qualified Lead Date
Person Is MQL is_mql common_mart_marketing.mart_crm_person A boolean field created in the warehouse to flag Marketing Qualified Leads
Person Is Inquiry is_inquiry common_mart_marketing.mart_crm_person A boolean field created in the warehouse to flag an Inquiry
Person Converted to Contact? see sfdc record type not sure what this field refers too, can’t see it in dbt or mart_crm_person
Person Lead/Contact Status status common_mart_marketing.mart_crm_person A custom Salesforce field (picklist) with the following values defined in the handbook Commonly used in analysis to filter persons according to their current lead lifecycle
Person Original Lead Source (first lead by email) lead_source common_mart_marketing.mart_crm_person A custom Salesforce field (picklist) used to identify initial source (first “known” touch point) - Handbook Cannot be overwritten in Salesforce once set. Used to inform grouping of source buckets
Person Lead Person Score person_score common.dim_crm_person Current score applied to the lead by Marketo lead scoring process. Scoring model defined in handbook If analysing this data it is worth visiting the handbook page to understand the scoring model logic. A score of 100 triggers an MQL.
Person Associated with Trial if not null then this person started a trial Join legacy.customers_db_trials to legacy.customers_db_customers on CUSTOMER_ID to get CUSTOMER_EMAIL_HASH and MIN(TRIAL_START_DATE)
Person Segment account_demographic_sales_segment common_mart_marketing.mart_crm_person Based on Zoom info sourced / LeanData matched Account Demographics: Max Family Employee Count for each hierarchy
Person Geo account_demographics_geo common_mart_marketing.mart_crm_person Based on Zoom info sourced / LeanData matched: Tells us where an account is located geographically Account Demographics are SOT for all geo related segments in Salesforce. Example value: AMER
Person Region account_demographics_region common_mart_marketing.mart_crm_person Based on Zoom info sourced Account Demographics: Tells us the region an account is based in Example value: East
Person Country (as entered) country common_mart_marketing.mart_crm_person Example value: US
Person State (as entered) state common_mart_marketing.mart_crm_person Example value: NY
Person Company size (as entered) account_demographics_employee_count common_mart_marketing.mart_crm_person Based on Zoom info sourced / LeanData matched Account Demographics: This field tells us how many employees are within an account This field informs sales_segment
Person Zoominfo Company ID zoominfo_company_id Join to WORKSPACE_MARKETING.GITLAB_CONTACT_ENHANCE on EMAIL_HASH = EMAIL_ID_HASH
Person Zoominfo Segment zoominfo_company_segment Join to WORKSPACE_MARKETING.GITLAB_CONTACT_ENHANCE on EMAIL_HASH = EMAIL_ID_HASH

Bizible

Point of contact: Robert Kohnke, David Egan, Jerome Ahye

Object Field SoT Field API Name SoT Table Description Notes for Analysis
Bizible Touchpoint Count dim_crm_touchpoint_id common_mart_marketing.mart_crm_touchpoint Bizible ID for the touchpoint Use DISTINCT in analysis
Bizible Touchpoint Touchpoint Date bizible_touchpoint_date common_mart_marketing.mart_crm_touchpoint Date timestamp for the event
Bizible Touchpoint Touchpoint Position bizible_touchpoint_position common_mart_marketing.mart_crm_touchpoint Bizible - position of the touchpoint. The position of the touchpoint reflects the major milestone touchpoints in the customer journey (i.e. FT, Form, LC, OC, Closed) and depends on WHEN it occurred in the journey. Be aware that a single touchpoint can have more than one position. This displays as concatenated labels separated by “, "
Bizible Touchpoint Touchpoint Type bizible_touchpoint_type common_mart_marketing.mart_crm_touchpoint Bizible - ’type’ of touchpoint. Has standard values depending on how the event was collected. For .js touchpoints - the values are ‘Web Visit’, ‘Web Form’ or ‘Web Chat’. ‘CRM; for CRM campaign (often offline list uploads). Populates with the Task or Event Type for Activity Touchpoints.
Bizible Touchpoint Marketing Channel bizible_marketing_channel common_mart_marketing.mart_crm_touchpoint Bizible high level grouping of the marketing activity or marketing channel that the touchpoint belongs to. Example: ‘Paid Search’, ‘Direct’ etc. Touchpoints are grouped according to how we set our marketing channels up (mapped within Bizible).
Bizible Touchpoint Marketing Channel Path bizible_marketing_channel_path common_mart_marketing.mart_crm_touchpoint Concatenates Marketing Channel with sub-channel that the touchpoint belongs to. For example: Paid Search.AdWords. Again, grouping dependent on our own custom mapping.
Bizible Touchpoint Related Person dim_crm_person_id common_mart_marketing.mart_crm_touchpoint Salesforce person ID - relates to mart_crm_person, source is dim_crm_person Don’t use this to join touchpoints to a person
Bizible Touchpoint Related Person Email email_hash common_mart_marketing.mart_crm_touchpoint Salesforce anonymised email ID - sourced from dim_crm_person Primary means to join touchpoints to a person
Bizible Touchpoint Related Person Owner owner_id common_mart_marketing.mart_crm_touchpoint Salesforce id (dim_crm_user_id) of the user who owns the person record. Source is dim_crm_person
Bizible Touchpoint SFDC Campaign dim_campaign_id common_mart_marketing.mart_crm_touchpoint Salesforce campaign ID, source dim_campaign
Bizible Touchpoint Related Account dim_crm_account_id common_mart_marketing.mart_crm_touchpoint Salesforce unique If for an Account, source is dim_crm_account Useful for some use cases only, when want to dive deeper into the entities that make up a parent account
Bizible Touchpoint Related Parent Account dim_parent_crm_account_id common_mart_marketing.mart_crm_touchpoint Salesforce unique ID for Parent Account, source is dim_crm_account Parent account is more used in reporting and data models than Account
Bizible Touchpoint Is FMM Influenced is_fmm_influenced common_mart_marketing.mart_crm_touchpoint Field generated in the DWH to identify touchpoints with Field Marketing touches
Bizible Attribution Touchpoint Count dim_crm_touchpoint_id common_mart_marketing.mart_crm_attribution_touchpoint Bizible ID for the touchpoint Use DISTINCT in analysis
Bizible Attribution Touchpoint Touchpoint Date bizible_touchpoint_date common_mart_marketing.mart_crm_attribution_touchpoint Date timestamp for the event
Bizible Attribution Touchpoint Touchpoint Position bizible_touchpoint_position common_mart_marketing.mart_crm_attribution_touchpoint Bizible - position of the touchpoint. The position of the touchpoint reflects the major milestone touchpoints in the customer journey (i.e. FT, Form, LC, OC, Closed) and depends on WHEN it occurred in the journey. Be aware that a single touchpoint can have more than one position. This displays as concatenated labels separated by “, "
Bizible Attribution Touchpoint Touchpoint Type bizible_touchpoint_type common_mart_marketing.mart_crm_attribution_touchpoint Bizible - ’type’ of touchpoint. Has standard values depending on how the event was collected. For .js touchpoints - the values are ‘Web Visit’, ‘Web Form’ or ‘Web Chat’. ‘CRM; for CRM campaign (often offline list uploads). Populates with the Task or Event Type for Activity Touchpoints.
Bizible Attribution Touchpoint Marketing Channel bizible_marketing_channel common_mart_marketing.mart_crm_attribution_touchpoint Bizible high level grouping of the marketing activity or marketing channel that the touchpoint belongs to. Example: ‘Paid Search’, ‘Direct’ etc. Touchpoints are grouped according to how we set our marketing channels up (mapped within Bizible).
Bizible Attribution Touchpoint Marketing Channel Path bizible_marketing_channel_path common_mart_marketing.mart_crm_attribution_touchpoint Concatenates Marketing Channel with sub-channel that the touchpoint belongs to. For example: Paid Search.AdWords. Again, grouping dependent on our own custom mapping.
Bizible Attribution Touchpoint Related Person dim_crm_person_id common_mart_marketing.mart_crm_attribution_touchpoint Salesforce person ID - relates to mart_crm_person, source is dim_crm_person Don’t use this to join touchpoints to a person
Bizible Attribution Touchpoint Related Person Email email_hash common_mart_marketing.mart_crm_attribution_touchpoint Salesforce anonymised email ID - sourced from dim_crm_person Primary means to join touchpoints to a person
Bizible Attribution Touchpoint Related Person Owner owner_id common_mart_marketing.mart_crm_attribution_touchpoint Salesforce id (dim_crm_user_id) of the user who owns the person record. Source is dim_crm_person
Bizible Attribution Touchpoint SFDC Campaign dim_campaign_id common_mart_marketing.mart_crm_attribution_touchpoint Salesforce campaign ID, source dim_campaign
Bizible Attribution Touchpoint Related Account dim_crm_account_id common_mart_marketing.mart_crm_attribution_touchpoint Salesforce unique If for an Account, source is dim_crm_account Useful for some use cases only, when want to dive deeper into the entities that make up a parent account
Bizible Attribution Touchpoint Related Parent Account dim_parent_crm_account_id common_mart_marketing.mart_crm_attribution_touchpoint Salesforce unique ID for Parent Account, source is dim_crm_account Parent account is more used in reporting and data models than Account
Bizible Attribution Touchpoint Is FMM Influenced is_fmm_influenced common_mart_marketing.mart_crm_attribution_touchpoint Field generated in the DWH to identify touchpoints with Field Marketing touches
Bizible Attribution Touchpoint Count First Touch bizible_count_first_touch common_mart_marketing.mart_crm_attribution_touchpoint This field shows, in decimal form, the percentage of revenue credit given to a touchpoint according to a First Touch Model
Bizible Attribution Touchpoint Count Lead Creation Touch bizible_count_lead_creation_touch common_mart_marketing.mart_crm_attribution_touchpoint This field shows, in decimal form, the percentage of revenue credit given to a touchpoint according to a Lead Creation Model
Bizible Attribution Touchpoint Count Full Path bizible_attribution_percent_full_path common_mart_marketing.mart_crm_attribution_touchpoint This field shows, in decimal form, the percentage of revenue given to a touchpoint according to a Full Path Model
Bizible Attribution Touchpoint Count Custom Model bizible_count_custom_model common_mart_marketing.mart_crm_attribution_touchpoint This field shows, in decimal form, the percentage of attribution credit given to a touchpoint according to the values set in your Custom Model. Since this field relates to the Buyer Touchpoint Object, it is not a reflection of revenue credit, solely just attribution credit. This is our primary Bizible attribution model for FY23
Bizible Attribution Touchpoint Count U Shaped bizible_count_u_shaped common_mart_marketing.mart_crm_attribution_touchpoint This field shows, in decimal form, the percentage of revenue credit given to a touchpoint according to a U-Shaped Model
Bizible Attribution Touchpoint Count W Shaped bizible_count_w_shaped common_mart_marketing.mart_crm_attribution_touchpoint This field shows, in decimal form, the percentage of credit given to a touchpoint according to a W-Shaped Model
Bizible Attribution Touchpoint ARR Full Path bizible_revenue_full_path common_mart_marketing.mart_crm_attribution_touchpoint This field shows the dollar revenue amount attributed to a touchpoint according to the attribution percentage in the Full Path Model
Bizible Attribution Touchpoint ARR Custom Model bizible_revenue_custom_model common_mart_marketing.mart_crm_attribution_touchpoint This field shows the dollar revenue amount attributed to a touchpoint according to the attribution percentage set in your Custom Model
Bizible Attribution Touchpoint ARR First Touch bizible_revenue_first_touch common_mart_marketing.mart_crm_attribution_touchpoint This field shows the dollar revenue amount attributed to a touchpoint according to the attribution percentage in the First Touch Model
Bizible Attribution Touchpoint ARR Lead Creation bizible_revenue_lead_conversion common_mart_marketing.mart_crm_attribution_touchpoint This field shows the dollar revenue amount attributed to a touchpoint according to the attribution percentage in the Lead Creation Model
Bizible Attribution Touchpoint ARR U Shaped bizible_revenue_u_shaped common_mart_marketing.mart_crm_attribution_touchpoint This field shows the dollar revenue amount attributed to a touchpoint according to the attribution percentage in the U-Shaped Model
Bizible Attribution Touchpoint ARR W Shaped bizible_revenue_w_shaped common_mart_marketing.mart_crm_attribution_touchpoint This field shows the dollar revenue amount attributed to a touchpoint according to the attribution percentage in the W-Shaped Model
Bizible Attribution Touchpoint Related Opportunity dim_crm_opportunity_id common_mart_marketing.mart_crm_attribution_touchpoint Salesforce ID for the opportunity this touchpoint was attributed to

Google Analytics 360 DB

Point of contact: Dennis Charukulvanich

Object Field SoT Field API Name Table
dimension Client ID client_id ga360_session
dimension Session Date session_date ga360_session
dimension Source traffic_source ga360_session
dimension Medium traffic_source_medium ga360_session
dimension Device Category device_category ga360_session
dimension Country geo_network_country ga360_session
dimension Event Category event_category ga360_session_hit
dimension Event Action event_action ga360_session_hit
dimension Event Label event_label ga360_session_hit
dimension Page page_path ga360_session_hit
dimension Hostname host_name ga360_session_hit
dimension Custom Dimension dimension_index ga360_session_custom_dimension
metric Sessions count distinct (visit_id, visitor_id) ga360_session
metric Bounce Rate count (where total_pageviews = 1) / sessions ga360_session
metric Avg. Session Duration total_time_on_site / sessions ga360_session
metric Users count(client_id) ga360_session
metric New Users count distinct (client_id) where total_new_visits = 1 ga360_session
metric Pageviews sum(total_pageviews) ga360_session
metric Total Events count(hit_type like ‘EVENT’) ga360_session_hit

Coming in V2: Marketo, Snowplow

Last modified December 6, 2023: update (a27760f0)
View page source - Edit this page - please contribute. Creative Commons License