Redshift Data Warehouse Schema Version 2

Data Warehouse (DW) allow the user to take all of Bizible’s recorded data and slice and dice it anyway they want. This includes hooking up the Data Warehouse to a Business Intelligence (BI) platform like Tableau or Domo and using that data to share with the rest of the org. The advantage of using DW is that it contains much more data than what’s shown in Salesforce, such as costs and more data than what we report in the Marketing ROI Dashboard, such as impressions, sessions, and page views.

Below, learn more about the tables that are part of Bizible’s Data Warehouse schema, as well as the columns that are in each table and what relationships and “joins” are possible between each.

Biz_Accounts

All CRM Accounts that are imported, along with custom Bizible fields.

Column Type (length) Description Possible Relationships
Id varchar (40) CRM AccountId
Created_Date_CRM timestamp The created date of the Account from the CRM
Modified_Date_CRM timestamp The last modified date of the Account from the CRM
Name varchar (40) The Account Name, as recorded in the CRM
Web_Site varchar (100) Website that is entered for the Account, used for Lead2Account mapping
Engagement_Rating varchar (10) A letter grade (A, B, C, D, N/A) that is generated from Bizible’s Machine Learning model. This will be null if ABM is disabled.
Engagement_Score varchar (10) A numerical score calculated by Bizible’s Machine Learning to generate the Predictive Engagement Score (Engagement_Rating). This will be null if ABM is disabled.
Domain varchar (100) The parsed down version of the website, only storing the domain.
Is_Deleted boolean Whether or not the record is deleted in the CRM.

Biz_Account_To_Emails

Lead and Contact email addresses that are mapped and known under CRM Accounts. This table will be empty if ABM is disabled.

Column

Type (length)

Description

Possible Relationships

Account_Id

varchar (40)

CRM AccountId

Account.Id

Email

varchar (100)

Any email address that has been mapped to the Account, whether through Contact relationships or from Lead2Account mapping



Biz_Attribution_Touchpoints

All touchpoints that are associated with an Opportunity. This table will be empty if Opportunity Touchpoints is disabled.

Column Type (length) Description Possible Relationships
Id varchar (100) A unique Id for the touchpoint
User_Touchpoint_Id varchar (256) A lookup to the Biz_User_Touchpoints table to select columns such as email, campaignmember_id, session_id Biz_User_Touchpoints.Id
Opportunity_Id varchar (40) A lookup to the Biz_Opportunities table to select columns such as Amount or Close Date Biz_Opportunities.Id
Contact_Id varchar (40) A lookup to the Biz_Contacts table to select columns such as Created Date Biz_Contacts.Id
Account_Id varchar (40) A lookup to the Biz_Accounts table to select columns such as Website or Engagement Rating Biz_Accounts.Id
Touchpoint_Date timestamp The date of the recorded touchpoint, which is the date that the session occurred
Marketing_Touch_Type varchar (32) Referred to in the CRM as “Touchpoint Type.” This records the type of activity, whether it was a Web Visit, Web Form, Web Chat, Phone Call, [CRM] Campaign, or [CRM] Activity
Channel varchar (64) Referred to in the CRM as “Marketing Channel - Path.” This is the channel that the touchpoint falls into, as defined in the custom channel definitions within the Bizible App.
Categories varchar (256) Referred to in the CRM as “Segments.” These are the categories and segments that the touchpoint falls into, as defined in the segment definitions within the Bizible App.
Browser_Name varchar (32) From the javascript and IP address, the detected browser that the user was on during the session. For example, Chrome or Internet Explorer.
Browser_Version varchar (20) From the javascript and IP address, the detected version of the browser that the user was on during the session. For example, Chrome or Internet Explorer.
Platform_Name varchar (32) From the javascript and IP address, the detected platform that the user was on during the session. For example, Mac or Windows.
Platform_Version varchar (20) From the javascript and IP address, the detected version of the platform that the user was on during the session. For example, Mac or Windows.
Landing_Page varchar (256) Referred to in the CRM as “Landing Page - Raw.” This is the first landing page of the session that resulted in a touchpoint. A raw landing page will contain all query parameters in the URL.
Referrer_Page varchar (256) Referred to in the CRM as “Referrer Page - Raw.” This is the usually the external landing page immediately before the user comes onto the website. A raw referrer page may contain query parameters in the URL.
Form_Page varchar (256) Referred to in the CRM as “Form - URL.” This is the first form that is recorded in a session that resulted in a touchpoint. Subsequent form submissions will not show up in the Biz_Attribution_Touchpoints table, but rather in the Biz_Form_Submits table.
Form_Date timestamp The date that the form submission occurred.
City varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Seattle or Vancouver.
Region varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Washington or British Columbia.
Country varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, United States or Canada.
Medium varchar (32) Used to define the medium that resulted in the touchpoint. This can either be parsed out from the URL from utm_medium or if Bizible is able to resolve an ad, it may be values such as “cpc” or “display.”
Web_Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Search_Phrase varchar (200) The value that the user entered in the browser to search for and end up on the website. Depending on the keyword buys, this may or may not match the keywords purchased from the Paid Search platform.
Ad_Provider varchar (30) The ad platform that Bizible was able to resolve from, typically one of our integration partners.
Account_Name varchar (32) The name of the ads account in which the ad was resolved from, such as Bizible-US.
Account_Unique_Id varchar (32) The Id of the ads account in which the ad was resolved from.
Advertiser_Name varchar (64) The name of the advertiser from the ads account in which the ad was resolved from, such as Bizible Inc.
Advertiser_Unique_Id varchar (64) The Id of the advertiser from the ads accounts in which the ad was resolved from.
Site_Name varchar (64) The site name from the ads account in which the ad was resolved from, such as Quantcast or TechCrunch. This only applies to Doubleclick Campaign Manager.
Site_Unique_Id varchar (64) The Id of the site from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Placement_Name varchar (64) The placement name from the ads account in which the ad was resolved from, such as 300x250 or Home Page Takeover. This only applies to Doubleclick Campaign Manager.
Placement_Unique_Id varchar (64) The Id placement from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Campaign_Name varchar (128) The campaign name from the ads account in which the ad was resolved from, such as Winter Promotion 2018 or Planning Your Budget Webinar.
Campaign_Unique_Id varchar (64) The Id of the campaign from the ads account in which the ad was resolved from.
Ad_Group_Name varchar (200) The ad group name from the ads account in which the ad was resolved from. This only applies to Google AdWords.
Ad_Group_Unique_Id varchar (64) The Id of the ad group from the ads account in which the ad was resolved from. This only applies to Google Adwords.
Ad_Name varchar (64) The ad name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Doubleclick Campaign Manager and Facebook (display).
Ad_Unique_Id varchar (64) The id of the ad from the ads account in which the ad was resolved from. This applies to Doubleclick Campaign Manager and Facebook (display).
Creative_Name varchar (100) The creative name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Google AdWords and Bing Ads (search).
Creative_Unique_Id varchar (64) The id of the creative from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_1 varchar (100) The first line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_2 varchar (100) The second line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Destination_URL varchar (200) The landing page that clicks through from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Display_URL varchar (200) The friendly URL name that’s shown on the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Keyword_Name varchar (100) The keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from, such as Software Discount or Annual Budget. This applies to Google AdWords and Bing Ads (search)
Keyword_Unique_Id varchar (64) The Id of the keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search
Keyword_Match_Type varchar (16) The type of match that was found between the search phrase and the purchased keyword, such as “broad” or “exact”
Is_First_Touch boolean Whether or not this touchpoint is treated as the first touch of the opportunity journey
Is_Lead_Creation_Touch boolean Whether or not this touchpoint is treated as the lead creation touch of the opportunity journey
Is_Opp_Creation_Touch boolean This column is blank for Biz_Touchpoints. Please refer to Biz_Attribution_Touchpoints table for this information.
Is_Closed_Touch boolean This column is blank for Biz_Touchpoints. Please refer to Biz_Attribution_Touchpoints table for this information.
Stages_Touched varchar (100) This column is blank for Biz_Touchpoints. Please refer to Biz_Attribution_Touchpoints table for this information.
Is_Form_Submission_Touch boolean Whether or not this touchpoint had a form fill during the session
Is_Impression_Touch boolean Whether or not this touchpoint is treated as the first impression touch of the opportunity journey
First_Click_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s a first touch (See Is_First_Touch)
Last_Anon_Click_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s a lead creation touch (See Is_Last_Anon_Click_Percentage)
U_Shape_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s part of a u-shaped touch (See Is_First_Touch and Is_Lead_Creation_Touch)
W_Shape_Percentage real This column is blank for Biz_Touchpoints. Please refer to Biz_Attribution_Touchpoints table for this information.
Full_Path_Percentage real This column is blank for Biz_Touchpoints. Please refer to Biz_Attribution_Touchpoints table for this information.
Custom_Model_Percentage real This column is blank for Biz_Touchpoints. Please refer to Biz_Attribution_Touchpoints table for this information.
Is_Deleted boolean Whether this touchpoint is deleted or not. Used for diagnostics to see if a record was deleted in CRM.

Biz_Campaign_Members

All CRM Campaign Members that are imported, along with custom Bizible fields. This table will be empty if Campaign Sync is disabled.

Column Type (length) Description Possible Relationships
Id varchar (40) CRM CampaignMember Id
Created_Date_CRM timestamp The created date of the Campaign Member from the CRM
Modified_Date_CRM timestamp The last modified date of the Campaign Member from the CRM
Bizible_Touch_Point_Date timestamp (optional) The date and time that the customer sets to override the campaign date and use this value for the Touchpoint Date instead
Lead_Id varchar (40) The Id for the related Lead that this Campaign Member is tied to Biz_Leads.Id
Lead_Email varchar (100) The email for the related Lead that this Campaign Member is tied to Biz_Leads.Email
Contact_Id varchar (40) The Id for the related Contact that this Campaign Member is tied to Biz_Contacts.Id
Contact_Email varchar (100) The email for the related Contact that this Campaign Member is tied to Biz_Contacts.Email
Status varchar (100) The Campaign Member’s status, usually set to Sent or Responded or another custom value. This status is tied to the Campaign_Sync_Type to determine which Campaign Members to create touchpoints for
Has_Responded boolean A true/false value that tells if the Campaign Member was marked as “Responded” from the Status picker
First_Responded_Date timestamp A date field that is automatically populated if Has_Responded is marked as true
Campaign_Name varchar (128) The name for the related Campaign that this Campaign Member is apart of
Campaign_Id varchar (50) The Id for the related Campaign that this Campaign Member is apart of
Campaign_Type varchar (64) The Type that’s selected on the related Campaign that this Campaign Member is apart of. The Type is used to map the Marketing Channel.
Campaign_Sync_Type varchar (64) Used to determine which Campaign Members to create touchpoints for. The possible values are: Include All Campaign Members, Include “Responded” Campaign Members, Exclude All Campaign Members
Lead_Sync_Status varchar (64) Used as an audit field to tell whether or not a touchpoint was generated for this Lead as a Bizible Touchpoint and if not, the reason why it didn’t qualify
Contact_Sync_Status varchar (64) Used as an audit field to tell whether or not a touchpoint was generated for this Contact as a Bizible Touchpoint and if not, the reason why it didn’t qualify
Opp_Sync_Status varchar (64) Used as an audit field to tell whether or not a touchpoint was generated for this Contact as a Bizible Attribution Touchpoint and if not, the reason why it didn’t qualify
Is_Deleted boolean Finds whether or not the Campaign Member that was in the system is deleted. Used for diagnostics to see if a record was deleted in CRM.

Biz_Chats

Chats that have occurred which contain a VisitorId and is related to a Biz_Session. This will contain the email address that was submitted during the chat. This table will be empty if Web Chat is disabled.

Column Type (length) Description Possible Relationships
Id varchar (128) A unique Id for the chat record
Session_Id varchar (128) The recorded session Id at the time the chat was logged. Biz_Sessions.Id
Visitor_Id varchar (100) The first cookie of the related visitor Id Biz_Email_to_Visitor.Id
Cookie_Id varchar (100) The recorded cookie Id at the time the chat was logged.
Event_Date timestamp The date and time that the chat was logged.
Current_Page varchar (256) The URL of the page where the chat took place.
Email_In_Chat varchar (100) The email address that was provided and captured during the chat
IP_Address varchar (40) The recorded IP address at the time the chat was logged
City varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Seattle or Vancouver.
Region varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Washington or British Columbia.
Country varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, United States or Canada.
ISP_Name varchar (64) The name of the internet service provider. Populated for customers with advanced Geo IP tracking.
Chat_Source varchar (50) The web source of the web chat.
Is_Deleted boolean Used for diagnostics to see if a record was deleted.

Biz_Contacts

All CRM Contacts that are imported, along with custom Bizible fields

Column Type (length) Description Possible Relationships
Id varchar (40) CRM Contact Id
Created_Date_CRM timestamp The created date of the Contact from the CRM
Modified_Date_CRM timestamp The last modified date of the Contact from the CRM
Email varchar (100) The email address of the Contact from the CRM
Account_Id varchar (50) The Id of the related Account that this Contact is tied to Biz_Accounts.Id
Lead_Source varchar (64) The source in which the contact was created
Odds_Of_Conversion real Bizible’s algorithm of estimating whether a contact will help an opportunity close based on the age and stage
Bizible_Stage varchar (100) The current stage that the Contact is sitting in, recognized as a custom stage that can be created in Bizible’s application.
Bizible_Stage_Previous varchar (100) A string of all stages that the Contact had previously gone through, recognized as custom stages that can be created in Bizible’s application.
Is_Deleted boolean Finds whether or not the Contact that was in the system is deleted

Biz_Costs

All cost data, either from third party integrations or self reported marketing spend, segmented by various dimensions like Account, Channel, Campaign, Creative, Keyword, AdGroup, Site, Placement.

Column Type (length) Description Possible Relationships
Id varchar (70) A unique Id for the cost record
Cost_Date timestamp The date that the cost was reported on
Source varchar (64) The source of the reported cost
Cost_In_Micro bigint The reported cost of the day in millions. User will need to divide the value by 1000000 before using this column.
Clicks bigint The number of clicks reported for the group for the day
Impressions bigint The number of impressions reported for the group for the day
Estimated_Total_Possible_Impressions bigint The total number of impressions that are estimated from DCM for the group for the day
Is_Deleted boolean Whether or not the record was deleted, used as an audit trail.
Channel_Unique_Id varchar (32) The Id for the marketing channel, created by Bizible
Channel_Name varchar (64) The name for the marketing channel, created by the customer in the Bizible application
Channel_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the channel, filter this column by “TRUE”
Advertiser_Unique_Id varchar (64) The Id of the advertiser pulled from the ads connection
Advertiser_Name varchar (64) The name of the advertiser pulled from the ads connection
Advertiser_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the advertiser, filter this column by “TRUE”
Account_Unique_Id varchar (32) The Id of the ads account pulled from the ads connection
Account_Name varchar (64) The name of the ads account pulled from the ads connection
Account_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the ads account, filter this column by “TRUE”
Campaign_Unique_Id varchar (64) The Id of the campaign pulled from the ads connection
Campaign_Name varchar (128) The name of the campaign pulled from the ads connection
Campaign_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the campaign, filter this column by “TRUE”
Ad_Group_Unique_Id varchar (3642) The Id of the ad group pulled from the ads connection
Ad_Group_Name varchar (200) The name of the ad group pulled from the ads connection
Ad_Group_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the ad group, filter this column by “TRUE”
Ad_Unique_Id varchar (64) The Id of the ad pulled from the ads connection
Ad_Name varchar (64) The name of the ad pulled from the ads connection
Ad_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the ad, filter this column by “TRUE”
Creative_Unique_Id varchar (64) The Id of the creative pulled from the ads connection
Creative_Name varchar (100) The name of the creative pulled from the ads connection
Creative_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the creative, filter this column by “TRUE”
Keyword_Unique_Id varchar (64) The Id of the keyword pulled from the ads connection
Keyword_Name varchar (100) The name of the keyword pulled from the ads connection
Keyword_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the keyword, filter this column by “TRUE”
Placement_Unique_Id varchar (64) The Id of the placement pulled from the ads connection
Placement_Name varchar (64) The name of the placement pulled from the ads connection
Placement_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the placement, filter this column by “TRUE”
Site_Unique_Id varchar (64) The Id of the site pulled from the ads connection
Site_Name varchar (64) The name of the site pulled from the ads connection
Site_Is_Aggregatable_Cost boolean If rolling up costs and summarizing by the site, filter this column by “TRUE”

Biz_Crm_Events

All CRM Events that are imported, along with custom Bizible fields.

Requirements: Enable Activities Sync: True

Column Type (length) Description Possible Relationships
Id varchar (40) CRM Event Id
Created_Date_CRM timestamp The created date of the Event from the CRM
Modified_Date_CRM timestamp The last modified date of the Event from the CRM
Lead_Id varchar (40) The Id of the related Lead that this Event is tied to Biz_Leads.Id
Lead_Email varchar (100) The email of the related Lead that this Event is tied to Biz_Leads.Email
Contact_Id varchar (40) The Id of the related Contact that this Event is tied to Biz_Contacts.Id
Contact_Email varchar (100) The email of the related Contact that this Event is tied to Biz_Contacts.Email
Bizible_Host_Id varchar (40) Bizible’s CookieId value that is used to populate from an integration partner to map an offline Event to a web session. Requirement: Enable Call Tracking: True
Event_Start_Date timestamp The start date of the Event record, one of the options used to determine the Bizible Touchpoint Date
Event_End_Date timestamp The end date of the Event record, one of the options used to determine the Bizible Touchpoint Date
Is_Deleted boolean Finds whether or not the Task that was in the system is deleted

Biz_Crm_Tasks

All CRM Tasks that are imported, along with custom Bizible fields.

Requirements: Enable Activities Sync: True OR Enable Call Tracking: True

Column Type (length) Description Possible Relationships
Id varchar (40) CRM Task Id
Created_Date_CRM timestamp The created date of the Task from the CRM
Modified_Date_CRM timestamp The last modified date of the Task from the CRM
Lead_Id varchar (40) The Id of the related Lead that this Task is tied to Biz_Leads.Id
Lead_Email varchar (100) The email of the related Lead that this Task is tied to Biz_Leads.Email
Contact_Id varchar (40) The Id of the related Contact that this Task is tied to Biz_Contacts.Id
Contact_Email varchar (100) The email of the related Contact that this Task is tied to Biz_Contacts.Email
Bizible_Host_Id varchar (40) Bizible’s CookieId value that is used to populate from an integration partner to map an offline Task to a web session. Requirement: Enable Call Tracking: True
Activity_Type varchar (40) Used to describe the type of Task, usually along the lines of Call or Email or any other custom value. For Activities, this value is used to populate the Touchpoint Type and Medium.
Activity_Date timestamp The date that the Task occurred, one of the options used to determine the Bizible Touchpoint Date
Is_Deleted boolean Finds whether or not the Task that was in the system is deleted

Biz_Customer_AB_Tests

AB Tests that have been recorded which contain a VisitorId and is related to a Biz_Session. This will contain the experiment and variation that the user was shown.

Requirement: Enable AB Tests: Yes

Column Type (length) Description Possible Relationships
Id varchar (128) A unique Id for the AB test record
Session_Id varchar (128) The recorded session Id at the time the chat was logged. Biz_Sessions.Id
Visitor_Id varchar (100) The first cookie of the related visitor Id Biz_Email_to_Visitor.Id
Cookie_Id varchar (100) The recorded cookie Id at the time the chat was logged.
Event_Date timestamp The date and time that the chat was logged.
Experiment_Id varchar (30) The Id of the experiment that’s pulled from the AB test platform.
Experiment_Name varchar (64) The name of the experiment that’s pulled from the AB test platform.
Variation_Id varchar (30) The Id of the experiment’s variation that’s pulled from the AB test platform.
Variation_Name varchar (64) The name of the experiment’s variation that’s pulled from the AB test platform.
ABTest_User_Id varchar (64) The Id of the user that was served the experiment that’s pulled from the AB test platform.
IP_Address varchar (40) The recorded IP address at the time the experiment was logged.
Is_Deleted boolean Whether or not the record was deleted, used for diagnostics and auditing.

Back to top

Biz_Customer_Events

Web events that have been recorded using custom events in the Javascript which contain a VisitorId and is related to a Biz_Session.

Requirement: Enable Bizible Events: True

Column Type (length) Description Possible Relationships
Id varchar (128) A unique Id for the custom event
Session_Id varchar (128) The recorded session Id at the time the event was triggered from the javascript. Biz_Sessions.Id
Visitor_Id varchar (100) The first cookie of the related visitor Id Biz_Email_to_Visitor.Id
Cookie_Id varchar (100) The recorded cookie Id at the time the event was triggered from the javascript.
Event_Date timestamp The date and time that the event was triggered from the custom javascript.
Key varchar (30) The name given to the event that was triggered from the custom javascript. For example, “Video View”
Value varchar (60) A value that’s given from the event that was triggered from the custom javascript. For example, “75% viewed”
IP_Address varchar (40) The recorded IP address at the time the event was triggered from the custom javascript.
Is_Deleted boolean Whether or not the record was deleted, used for diagnostics and auditing.

Biz_Email_To_Visitor_Id

Email addresses mapped to the Visitor_Id

Column Description Possible Relationships
Email A known email address that’s tied to a given visitor Id from a session
Visitor_Id The first cookie of the related visitor Id

Biz_Chats.Visitor_Id

Biz_Customer_Events.Visitor_Id

Biz_Customer_AB_Tests.Visitor_Id

Biz_Form_Submits.Visitor_Id

Biz_Impressions.Visitor_Id

Biz_Page_Views.Visitor_Id

Biz_Sessions.Visitor_Id

Biz_Impressions

All impressions that have been fired and recorded which contain a VisitorId and is related to the Biz_Touchpoints. This will contain the resolved ads information of the served impression.

Requirements: Enable View Through: True, Doubleclick by Google: True

Column Type (length) Description Possible Relationships
Id varchar (128) A unique Id for the impression
Session_Id varchar (128) The recorded session Id at the time of the impression. Biz_Session.Id
Visitor_Id varchar (100) The first cookie of the related visitor Id Biz_Email_to_Visitor.Id
Cookie_Id varchar (100) The recorded cookie Id at the time of the impression
Event_Date timestamp The date and time that the impression was served
Current_Page varchar (256) The URL where the impression was served
Referrer_Page varchar (256) Referred to in the CRM as “Referrer Page - Raw.” This is the usually the external landing page immediately before the user comes onto the website. A raw referrer page may contain query parameters in the URL.
Referrer_Name varchar (256) The title of the Referrer Page.
Web_Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Device varchar (64) The hardware device that was used during this page view, like tablet or mobile. (Intentionally left blank)
Ad_Provider varchar (30) The ad platform that Bizible was able to resolve from, typically one of our integration partners.
Account_Name varchar (32) The name of the ads account in which the ad was resolved from, such as Bizible-US.
Account_Unique_Id varchar (32) The Id of the ads account in which the ad was resolved from.
Advertiser_Name varchar (64) The name of the advertiser from the ads account in which the ad was resolved from, such as Bizible Inc.
Advertiser_Unique_Id varchar (64) The Id of the advertiser from the ads accounts in which the ad was resolved from.
Site_Name varchar (64) The site name from the ads account in which the ad was resolved from, such as Quantcast or TechCrunch. This only applies to Doubleclick Campaign Manager.
Site_Unique_Id varchar (64) The Id of the site from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Placement_Name varchar (64) The placement name from the ads account in which the ad was resolved from, such as 300x250 or Home Page Takeover. This only applies to Doubleclick Campaign Manager.
Placement_Unique_Id varchar (64) The Id placement from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Campaign_Name varchar (128) The campaign name from the ads account in which the ad was resolved from, such as Winter Promotion 2018 or Planning Your Budget Webinar.
Campaign_Unique_Id varchar (64) The Id of the campaign from the ads account in which the ad was resolved from.
Ad_Group_Name varchar (200) The ad group name from the ads account in which the ad was resolved from. This only applies to Google AdWords.
Ad_Group_Unique_Id varchar (64) The Id of the ad group from the ads account in which the ad was resolved from. This only applies to Google Adwords.
Ad_Name varchar (64) The ad name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Doubleclick Campaign Manager and Facebook (display).
Ad_Unique_Id varchar (64) The id of the ad from the ads account in which the ad was resolved from. This applies to Doubleclick Campaign Manager and Facebook (display).
Creative_Name varchar (100) The creative name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Google AdWords and Bing Ads (search).
Creative_Unique_Id varchar (64) The id of the creative from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_1 varchar (100) The first line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_2 varchar (100) The second line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Destination_URL varchar (200) The landing page that clicks through from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Display_URL varchar (200) The friendly URL name that’s shown on the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Keyword_Name varchar (100) The keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from, such as Software Discount or Annual Budget. This applies to Google AdWords and Bing Ads (search)
Keyword_Unique_Id varchar (64) The Id of the keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search
Keyword_Match_Type varchar (16) The type of match that was found between the search phrase and the purchased keyword, such as “broad” or “exact”
Campaign varchar (100) Referred to in the CRM as “Ad Campaign Name.” Used to define the ad campaign that resulted in the touchpoint. This can either be parsed out from the URL from utm_campaign or if Bizible is able to resolve an ad, it may be values such as “Winter Promotion 2018” or “Quarterly Customer Webinar.”
Medium varchar (32) Used to define the medium that resulted in the touchpoint. This can either be parsed out from the URL from utm_medium or if Bizible is able to resolve an ad, it may be values such as “cpc” or “display.”
Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Term varchar (128) Referred to in the CRM as “Keyword Text.” Used the define any targeted keyword terms from the ad platform that resulted in the touchpoint. This can be parsed out from the URL from utm_term or if Bizible is able to resolve the ad, it may be values from the user's search such as “marketing analytics” or “analytics vendors.”
Content varchar (128) Referred to in the CRM as “Ad Content.” Used to share more detail about the ad, whether it’s the size or placement location or creative name. This can be parsed out from the URL from utm_content or if Bizible is able to resolve ad ad, it can be the creative description or ad body.
IP_Address varchar (100) The recorded IP address at the time of the impression
City varchar (50) The resolved city from the IP address. For example, Seattle or Vancouver.
Region varchar (50) The resolved region from the IP address. For example, Washington or British Columbia.
Country varchar (50) The resolved country from the IP address. For example, United States or Canada.
ISP_Name varchar (64) The name of the internet service provider, used by customers with advanced Geo IP tracking.
Is_Deleted boolean Whether or not the record was deleted, used for diagnostics and auditing.

Biz_Leads

All CRM Leads that are imported, along with custom Bizible fields

Column Type (length) Description Possible Relationships
Id varchar (40) CRM Lead Id
Created_Date_CRM timestamp The created date of the Lead from the CRM
Modified_Date_CRM timestamp The last modified date of the Lead from the CRM
Email varchar (100) The email address of the Lead from the CRM
Web_Site varchar (256) Website that is entered for the Lead, used for Lead2Account mapping
Company varchar (64) The company name that is entered for the Lead, used for Lead2Account mapping
Lead_Source varchar (100) The value of the Lead Source, as entered in the CRM
Is_Converted boolean Whether or not the Lead has been converted to a Contact
Converted_Opportunity_Id varchar (40) The Id of the related Opportunity once the Lead has been converted, if applicable
Converted_Date timestamp The date that the Lead was converted to a Contact, if applicable
Converted_Contact_Id varchar (40) The Id of the related Contact once the Lead has been converted, if applicable

Biz_Opportunities

All CRM Opportunities that are imported, along with custom Bizible fields

Column Type (length) Description Possible Relationships
Id varchar (40) CRM Opportunity Id
Created_Date_CRM timestamp The created date of the Opportunity from the CRM
Modified_Date_CRM timestamp The last modified date of the Opportunity from the CRM
Account_Id varchar (40) The Id of the related Account that this Opportunity is tied to Biz_Accounts.Id
Name varchar (200) The name of the Opportunity
Is_Won boolean Whether or not the Opportunity has been moved to a stage that is considered Won
Is_Closed boolean Whether or not the Opportunity has been moved to a stage that is considered closed, which can either be Won or Lost
Close_Date timestamp The anticipated or actual Close Date set on the Opportunity
Bizible_Custom_Model_Date timestamp A custom close date, if Close_Date is not used in the CRM, set within the Bizible App to determine which custom Close Date field is being used
Amount real The deal amount that is expected or closed from the Opportunity
Converted_From_Lead_Id varchar (40) The Id of the related Lead that had converted into this Opportunity Biz_Leads.Id
Converted_From_Lead_Email varchar (100) The email of the related Lead that had converted into this Opportunity Biz_Leads.Email
Primary_Contact_Id varchar (40) If Primary Contact Role is used, the Id of the related Contact that is listed as the primary contact role Biz_Contacts.Id
Primary_Contact_Email varchar (100) If Primary Contact Role is used, the email d of the related Contact that is listed as the primary contact role Biz_Contacts.Email
Odds_Of_Conversion real Bizible’s algorithm of estimating whether an opportunity will close based on the age and stage
Bizible_Stage varchar (32) The current stage that the Contact is sitting in, recognized as a custom stage that can be created in Bizible’s application.
Bizible_Stage_Previous varchar (32) A string of all stages that the Contact had previously gone through, recognized as custom stages that can be created in Bizible’s application.
Is_Deleted boolean Finds whether or not the Contact that was in the system is deleted

Back to top

Biz_Page_Views

Page views form web visits with a VisitorId and is related to Biz_Session. Multiple page views can compose of a single session.

Column Type (length) Description Possible Relationships
Id varchar (100) A unique Id for the page view
Session_Id varchar (128) The recorded session Id at the time of the page view Biz_Session.Id
Visitor_Id varchar (100) The first cookie of the related visitor Id Biz_Email_to_Visitor.Id
Cookie_Id varchar (100) The recorded cookie Id at the time of the page view
Event_Date timestamp The date and time of the page view
Page_Title varchar (256) The title of the page view
Current_Page varchar (384) The URL of the current page view
Referrer_Page varchar (512) The URL where page view originated from
Referrer_Name varchar (32) The name of the page where the page view originated from
Search_Phrase varchar (256) The value that the user entered in the browser to search for and end up on the website. Depending on the keyword buys, this may or may not match the keywords purchased from the Paid Search platform.
Web_Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Has_Form boolean Whether or not there was a form fill conducted on this page view
Has_Chat boolean Whether or not there was a web chat conducted on this page view
Has_Email boolean Whether or not this page view is mapped to and tied to a known email address
Has_Crm_Activity boolean Whether or not this page view is mapped to and tied to an offline Activity record
Device varchar (64) The hardware device that was used during this page view, like tablet or mobile. (Intentionally left blank)
Ad_Provider varchar (30) The ad platform that Bizible was able to resolve from, typically one of our integration partners.
Account_Name varchar (64) The name of the ads account in which the ad was resolved from, such as Bizible-US.
Account_Unique_Id varchar (32) The Id of the ads account in which the ad was resolved from.
Advertiser_Name varchar (64) The name of the advertiser from the ads account in which the ad was resolved from, such as Bizible Inc.
Advertiser_Unique_Id varchar (64) The Id of the advertiser from the ads accounts in which the ad was resolved from.
Site_Name varchar (64) The site name from the ads account in which the ad was resolved from, such as Quantcast or TechCrunch. This only applies to Doubleclick Campaign Manager.
Site_Unique_Id varchar (64) The Id of the site from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Placement_Name varchar (64) The placement name from the ads account in which the ad was resolved from, such as 300x250 or Home Page Takeover. This only applies to Doubleclick Campaign Manager.
Placement_Unique_Id varchar (64) The Id placement from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Campaign_Name varchar (128) The campaign name from the ads account in which the ad was resolved from, such as Winter Promotion 2018 or Planning Your Budget Webinar.
Campaign_Unique_Id varchar (64) The Id of the campaign from the ads account in which the ad was resolved from.
Ad_Group_Name varchar (200) The ad group name from the ads account in which the ad was resolved from. This only applies to Google AdWords.
Ad_Group_Unique_Id varchar (64) The Id of the ad group from the ads account in which the ad was resolved from. This only applies to Google Adwords.
Ad_Name varchar (64) The ad name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Doubleclick Campaign Manager and Facebook (display).
Ad_Unique_Id varchar (64) The id of the ad from the ads account in which the ad was resolved from. This applies to Doubleclick Campaign Manager and Facebook (display).
Creative_Name varchar (100) The creative name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Google AdWords and Bing Ads (search).
Creative_Unique_Id varchar (64) The id of the creative from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_1 varchar (100) The first line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_2 varchar (100) The second line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Destination_URL varchar (200) The landing page that clicks through from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Display_URL varchar (200) The friendly URL name that’s shown on the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Keyword_Name varchar (100) The keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from, such as Software Discount or Annual Budget. This applies to Google AdWords and Bing Ads (search)
Keyword_Unique_Id varchar (64) The Id of the keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search
Keyword_Match_Type varchar (16) The type of match that was found between the search phrase and the purchased keyword, such as “broad” or “exact”
Campaign varchar (100) Referred to in the CRM as “Ad Campaign Name.” Used to define the ad campaign that resulted in the touchpoint. This can either be parsed out from the URL from utm_campaign or if Bizible is able to resolve an ad, it may be values such as “Winter Promotion 2018” or “Quarterly Customer Webinar.”
Medium varchar (32) Used to define the medium that resulted in the touchpoint. This can either be parsed out from the URL from utm_medium or if Bizible is able to resolve an ad, it may be values such as “cpc” or “display.”
Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Term varchar (128) Referred to in the CRM as “Keyword Text.” Used the define any targeted keyword terms from the ad platform that resulted in the touchpoint. This can be parsed out from the URL from utm_term or if Bizible is able to resolve the ad, it may be values from the user's search such as “marketing analytics” or “analytics vendors.”
Content varchar (128) Referred to in the CRM as “Ad Content.” Used to share more detail about the ad, whether it’s the size or placement location or creative name. This can be parsed out from the URL from utm_content or if Bizible is able to resolve ad ad, it can be the creative description or ad body.
IP_Address varchar (100) The recorded IP address at the time the form was submitted
City varchar (50) The resolved city from the IP address. For example, Seattle or Vancouver.
Region varchar (50) The resolved region from the IP address. For example, Washington or British Columbia.
Country varchar (50) The resolved country from the IP address. For example, United States or Canada.
ISP_Name varchar (64) The name of the internet service provider, used by customers with advanced Geo IP tracking
Is_Deleted boolean Whether or not the record was deleted, mainly used for diagnostics and audits.

Back to top

Biz_Sessions

All recorded sessions associated with a VisitorId. Multiple sessions can be associated to a single visitor.

Column

Type (length)

Description

Possible Relationships

Id varchar (100) A unique Id for the session
Visitor_Id varchar (100) The first cookie of the related visitor Id Biz_Email_to_Visitor.Id
Session_Date timestamp The date and time of the session
Modified_Date timestamp The last time the session was modified
Marketing_Touch_Type varchar (32) Referred to in the CRM as “Touchpoint Type.” This records the type of activity, whether it was a Web Visit, Web Form, Web Chat, Phone Call, [CRM] Campaign, or [CRM] Activity
Channel varchar (64) Referred to in the CRM as “Marketing Channel - Path.” This is the channel that the touchpoint falls into, as defined in the custom channel definitions within the Bizible App.
Categories varchar (256) Referred to in the CRM as “Segments.” These are the categories and segments that the touchpoint falls into, as defined in the segment definitions within the Bizible App.
Browser_Name varchar (32) From the javascript and IP address, the detected browser that the user was on during the session. For example, Chrome or Internet Explorer.
Browser_Version varchar (20) From the javascript and IP address, the detected version of the browser that the user was on during the session. For example, Chrome or Internet Explorer.
Platform_Name varchar (32) From the javascript and IP address, the detected platform that the user was on during the session. For example, Mac or Windows.
Platform_Version varchar (20) From the javascript and IP address, the detected version of the platform that the user was on during the session. For example, Mac or Windows.
Landing_Page varchar (256) Referred to in the CRM as “Landing Page - Raw.” This is the first landing page of the session that resulted in a touchpoint. A raw landing page will contain all query parameters in the URL.
Referrer_Page varchar (256) Referred to in the CRM as “Referrer Page - Raw.” This is the usually the external landing page immediately before the user comes onto the website. A raw referrer page may contain query parameters in the URL.
Form_Page varchar (256) Referred to in the CRM as “Form - URL.” This is the first form that is recorded in a session that resulted in a touchpoint. Subsequent form submissions will not show up in the Biz_Attribution_Touchpoints table, but rather in the Biz_Form_Submits table.
Form_Date timestamp The date that the form submission occurred.
City varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Seattle or Vancouver.
Region varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Washington or British Columbia.
Country varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, United States or Canada.
Medium varchar (32) Used to define the medium that resulted in the touchpoint. This can either be parsed out from the URL from utm_medium or if Bizible is able to resolve an ad, it may be values such as “cpc” or “display.”
Web_Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Search_Phrase varchar (256) The value that the user entered in the browser to search for and end up on the website. Depending on the keyword buys, this may or may not match the keywords purchased from the Paid Search platform.
Ad_Provider varchar (30) The ad platform that Bizible was able to resolve from, typically one of our integration partners.
Account_Name varchar (32) The name of the ads account in which the ad was resolved from, such as Bizible-US.
Account_Unique_Id varchar (32) The Id of the ads account in which the ad was resolved from.
Advertiser_Name varchar (64) The name of the advertiser from the ads account in which the ad was resolved from, such as Bizible Inc.
Advertiser_Unique_Id varchar (64) The Id of the advertiser from the ads accounts in which the ad was resolved from.
Site_Name varchar (64) The site name from the ads account in which the ad was resolved from, such as Quantcast or TechCrunch. This only applies to Doubleclick Campaign Manager.
Site_Unique_Id varchar (64) The Id of the site from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Placement_Name varchar (64) The placement name from the ads account in which the ad was resolved from, such as 300x250 or Home Page Takeover. This only applies to Doubleclick Campaign Manager.
Placement_Unique_Id varchar (64) The Id placement from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Campaign_Name varchar (128) The campaign name from the ads account in which the ad was resolved from, such as Winter Promotion 2018 or Planning Your Budget Webinar.
Campaign_Unique_Id varchar (64) The Id of the campaign from the ads account in which the ad was resolved from.
Ad_Group_Name varchar (200) The ad group name from the ads account in which the ad was resolved from. This only applies to Google AdWords.
Ad_Group_Unique_Id varchar (64) The Id of the ad group from the ads account in which the ad was resolved from. This only applies to Google Adwords.
Ad_Name varchar (64) The ad name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Doubleclick Campaign Manager and Facebook (display).
Ad_Unique_Id varchar (64) The id of the ad from the ads account in which the ad was resolved from. This applies to Doubleclick Campaign Manager and Facebook (display).
Creative_Name varchar (100) The creative name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Google AdWords and Bing Ads (search).
Creative_Unique_Id varchar (64) The id of the creative from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_1 varchar (100) The first line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_2 varchar (100) The second line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Destination_URL varchar (200) The landing page that clicks through from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Display_URL varchar (200) The friendly URL name that’s shown on the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Keyword_Name varchar (100) The keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from, such as Software Discount or Annual Budget. This applies to Google AdWords and Bing Ads (search)
Keyword_Unique_Id varchar (64) The Id of the keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search
Keyword_Match_Type varchar (16) The type of match that was found between the search phrase and the purchased keyword, such as “broad” or “exact”
Is_Form_Submission_Touch boolean Whether or not this touchpoint had a form fill during the session
Is_Impression_Touch boolean Whether or not this touchpoint is treated as the first impression touch of the opportunity journey
Is_Deleted boolean Did this session merge with another and should be deleted.

Biz_Touchpoints

All touchpoints that are associated with a Lead and/or Contact. This table will be empty if Lead Touchpoints or Contact Touchpoints are disabled.

Column Type (length) Description Possible Relationships
Id varchar (100) A unique Id for the touchpoint record
Email varchar (100) The known email address that’s tied to this touchpoint via a Lead or Contact
User_Touchpoint_Id varchar (256) A lookup to the Biz_User_Touchpoints table to select columns such as session_id Biz_User_Touchpoints.Id
Contact_Id varchar (40) A lookup to the Biz_Contacts table to select columns such as Created Date Biz_Contact.Id
Account_Id varchar (40) A lookup to the Biz_Accounts table to select columns such as Website or Engagement Rating Biz_Account.Id
Lead_Id varchar (40) A lookup to the Biz_Leads table to select columns such as Created Date Biz_Lead.Id
Unique_Id_Person varchar (32) The parent person record that relates to a Lead or Contact
Touchpoint_Date timestamp The date of the recorded touchpoint, which is the date that the session occurred
Marketing_Touch_Type varchar (32) Referred to in the CRM as “Touchpoint Type.” This records the type of activity, whether it was a Web Visit, Web Form, Web Chat, Phone Call, [CRM] Campaign, or [CRM] Activity
Channel varchar (64) Referred to in the CRM as “Marketing Channel - Path.” This is the channel that the touchpoint falls into, as defined in the custom channel definitions within the Bizible App.
Categories varchar (256) Referred to in the CRM as “Segments.” These are the categories and segments that the touchpoint falls into, as defined in the segment definitions within the Bizible App.
Lead_Source varchar (64) The value of the Lead Source, as entered in the CRM
Browser_Name varchar (32) From the javascript and IP address, the detected browser that the user was on during the session. For example, Chrome or Internet Explorer.
Browser_Version varchar (20) From the javascript and IP address, the detected version of the browser that the user was on during the session. For example, Chrome or Internet Explorer.
Platform_Name varchar (32) From the javascript and IP address, the detected platform that the user was on during the session. For example, Mac or Windows.
Platform_Version varchar (20) From the javascript and IP address, the detected version of the platform that the user was on during the session. For example, Mac or Windows.
Landing_Page varchar (256) Referred to in the CRM as “Landing Page - Raw.” This is the first landing page of the session that resulted in a touchpoint. A raw landing page will contain all query parameters in the URL.
Referrer_Page varchar (256) Referred to in the CRM as “Referrer Page - Raw.” This is the usually the external landing page immediately before the user comes onto the website. A raw referrer page may contain query parameters in the URL.
Form_Page varchar (256) Referred to in the CRM as “Form - URL.” This is the first form that is recorded in a session that resulted in a touchpoint. Subsequent form submissions will not show up in the Biz_Attribution_Touchpoints table, but rather in the Biz_Form_Submits table.
Form_Date timestamp The date that the form submission occurred.
City varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Seattle or Vancouver.
Region varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Washington or British Columbia.
Country varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, United States or Canada.
Medium varchar (32) Used to define the medium that resulted in the touchpoint. This can either be parsed out from the URL from utm_medium or if Bizible is able to resolve an ad, it may be values such as “cpc” or “display.”
Web_Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Search_Phrase varchar (256) The value that the user entered in the browser to search for and end up on the website. Depending on the keyword buys, this may or may not match the keywords purchased from the Paid Search platform.
Ad_Provider varchar (30) The ad platform that Bizible was able to resolve from, typically one of our integration partners.
Account_Name varchar (32) The name of the ads account in which the ad was resolved from, such as Bizible-US.
Account_Unique_Id varchar (32) The Id of the ads account in which the ad was resolved from.
Advertiser_Name varchar (64) The name of the advertiser from the ads account in which the ad was resolved from, such as Bizible Inc.
Advertiser_Unique_Id varchar (64) The Id of the advertiser from the ads accounts in which the ad was resolved from.
Site_Name varchar (64) The site name from the ads account in which the ad was resolved from, such as Quantcast or TechCrunch. This only applies to Doubleclick Campaign Manager.
Site_Unique_Id varchar (64) The Id of the site from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Placement_Name varchar (64) The placement name from the ads account in which the ad was resolved from, such as 300x250 or Home Page Takeover. This only applies to Doubleclick Campaign Manager.
Placement_Unique_Id varchar (64) The Id placement from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Campaign_Name varchar (128) The campaign name from the ads account in which the ad was resolved from, such as Winter Promotion 2018 or Planning Your Budget Webinar.
Campaign_Unique_Id varchar (64) The Id of the campaign from the ads account in which the ad was resolved from.
Ad_Group_Name varchar (200) The ad group name from the ads account in which the ad was resolved from. This only applies to Google AdWords.
Ad_Group_Unique_Id varchar (64) The Id of the ad group from the ads account in which the ad was resolved from. This only applies to Google Adwords.
Ad_Name varchar (64) The ad name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Doubleclick Campaign Manager and Facebook (display).
Ad_Unique_Id varchar (64) The id of the ad from the ads account in which the ad was resolved from. This applies to Doubleclick Campaign Manager and Facebook (display).
Creative_Name varchar (100) The creative name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Google AdWords and Bing Ads (search).
Creative_Unique_Id varchar (64) The id of the creative from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_1 varchar (100) The first line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_2 varchar (100) The second line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Destination_URL varchar (200) The landing page that clicks through from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Display_URL varchar (200) The friendly URL name that’s shown on the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Keyword_Name varchar (100) The keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from, such as Software Discount or Annual Budget. This applies to Google AdWords and Bing Ads (search)
Keyword_Unique_Id varchar (64) The Id of the keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search
Keyword_Match_Type varchar (16) The type of match that was found between the search phrase and the purchased keyword, such as “broad” or “exact”
Is_First_Touch boolean Whether or not this touchpoint is treated as the first touch of the opportunity journey
Is_Lead_Creation_Touch boolean Whether or not this touchpoint is treated as the lead creation touch of the opportunity journey
Is_Opp_Creation_Touch boolean Whether or not this touchpoint is treated as the opportunity creation touch of the opportunity journey. Because this is the lead model, this is expected to be 0.
Is_Closed_Touch boolean Whether or not this touchpoint is treated as the closed touch of the opportunity journey. Because this is the lead model, this is expected to be 0.
Stages_Touched varchar (100) Whether or not this touchpoint caused the stage transition for these stages
Is_Form_Submission_Touch boolean Whether or not this touchpoint had a form fill during the session
Is_Impression_Touch boolean Whether or not this touchpoint is treated as the first impression touch of the opportunity journey
First_Click_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s a first touch (See Is_First_Touch)
Last_Anon_Click_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s a lead creation touch (See Is_Last_Anon_Click_Percentage)
U_Shape_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s part of a u-shaped touch (See Is_First_Touch and Is_Lead_Creation_Touch)
W_Shape_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s part of a w-shaped touch (See Is_First_Touch, Is_Lead_Creation_Touch, and Is_Opp_Creation_Touch). Because this is the lead model, this is expected to be 0.
Full_Path_Percentage Real The calculated percentage that gets allocated to this touchpoint because it’s part of a full path model (See Is_First_Touch, Is_Lead_Creation_Touch, Is_Opp_Creation_Touch, Is_Closed_Touch). Because this is the lead model, this is expected to be 0.
Custom_Model_Percentage real The calculated percentage that gets allocated to this touchpoint because it’s part of a custom model (See Is_First_Touch, Is_Lead_Creation_Touch, Is_Opp_Creation_Touch, Is_Closed_Touch, Stages Touch). Because this is the lead model, this is expected to be 0.
Is_Deleted boolean Whether or not the record was deleted, used for diagnostics and auditing.

Back to top

Biz_User_Touchpoints

Any touchpoint recorded that can be tied to an email, so it can be a web visit, web form, chat, or offline event. This table only shows touchpoints and does not record attribution.

Column Type (length) Description Possible Relationships
Id varchar (256) A unique Id for the user touchpoint
Email varchar (128) The email address that this Touchpoint is created from
Session_Id varchar (128) The Id of the related Session that this Touchpoint is created from Biz_Sessions.Id
Campaign_Member_Id varchar (40) The Id of the related Campaign Member that this Touchpoint is created from Biz_Campaign_Member.Id
Crm_Event_Id varchar (40) The Id of the related Event that this Touchpoint is created from Biz_Crm_Events.Id
Crm_Task_Id varchar (40) The Id of the related Task that this Touchpoint is created from Biz_Crm_Tasks.Id
Touchpoint_Date timestamp The date of the recorded touchpoint, which is the date that the session occurred
Marketing_Touch_Type varchar (32) Referred to in the CRM as “Touchpoint Type.” This records the type of activity, whether it was a Web Visit, Web Form, Web Chat, Phone Call, [CRM] Campaign, or [CRM] Activity
Channel varchar (64) Referred to in the CRM as “Marketing Channel - Path.” This is the channel that the touchpoint falls into, as defined in the custom channel definitions within the Bizible App.
Browser_Name varchar (32) From the javascript and IP address, the detected browser that the user was on during the session. For example, Chrome or Internet Explorer.
Browser_Version varchar (20) From the javascript and IP address, the detected version of the browser that the user was on during the session. For example, Chrome or Internet Explorer.
Platform_Name varchar (32) From the javascript and IP address, the detected platform that the user was on during the session. For example, Mac or Windows.
Platform_Version varchar (20) From the javascript and IP address, the detected version of the platform that the user was on during the session. For example, Mac or Windows.
Landing_Page varchar (256) Referred to in the CRM as “Landing Page - Raw.” This is the first landing page of the session that resulted in a touchpoint. A raw landing page will contain all query parameters in the URL.
Referrer_Page varchar (256) Referred to in the CRM as “Referrer Page - Raw.” This is the usually the external landing page immediately before the user comes onto the website. A raw referrer page may contain query parameters in the URL.
Form_Page varchar (256) Referred to in the CRM as “Form - URL.” This is the first form that is recorded in a session that resulted in a touchpoint. Subsequent form submissions will not show up in the Biz_Attribution_Touchpoints table, but rather in the Biz_Form_Submits table.
Form_Date timestamp The date that the form submission occurred.
City varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Seattle or Vancouver.
Region varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, Washington or British Columbia.
Country varchar (50) From the javascript and IP address, the detected city that the user was in during the session. For example, United States or Canada.
Medium varchar (32) Used to define the medium that resulted in the touchpoint. This can either be parsed out from the URL from utm_medium or if Bizible is able to resolve an ad, it may be values such as “cpc” or “display.”
Web_Source varchar (64) Referred to in the CRM as “Touchpoint Source.” Used to define the source that resulted in the touchpoint. This can be parsed out from the URL from utm_source or generically set as “CRM Campaign” if it was synced from the CRM or if Bizible is able to resolve an ad, it may be values such as “Google AdWords” or “Facebook.”
Search_Phrase varchar (200) The value that the user entered in the browser to search for and end up on the website. Depending on the keyword buys, this may or may not match the keywords purchased from the Paid Search platform.
Ad_Provider varchar (30) The ad platform that Bizible was able to resolve from, typically one of our integration partners.
Account_Name varchar (32) The name of the ads account in which the ad was resolved from, such as Bizible-US.
Account_Unique_Id varchar (32) The Id of the ads account in which the ad was resolved from.
Advertiser_Name varchar (64) The name of the advertiser from the ads account in which the ad was resolved from, such as Bizible Inc.
Advertiser_Unique_Id varchar (64) The Id of the advertiser from the ads accounts in which the ad was resolved from.
Site_Name varchar (64) The site name from the ads account in which the ad was resolved from, such as Quantcast or TechCrunch. This only applies to Doubleclick Campaign Manager.
Site_Unique_Id varchar (64) The Id of the site from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Placement_Name varchar (64) The placement name from the ads account in which the ad was resolved from, such as 300x250 or Home Page Takeover. This only applies to Doubleclick Campaign Manager.
Placement_Unique_Id varchar (64) The Id placement from the ads account in which the ad was resolved from. This only applies to Doubleclick Campaign Manager.
Campaign_Name varchar (128) The campaign name from the ads account in which the ad was resolved from, such as Winter Promotion 2018 or Planning Your Budget Webinar.
Campaign_Unique_Id varchar (64) The Id of the campaign from the ads account in which the ad was resolved from.
Ad_Group_Name varchar (200) The ad group name from the ads account in which the ad was resolved from. This only applies to Google AdWords.
Ad_Group_Unique_Id varchar (64) The Id of the ad group from the ads account in which the ad was resolved from. This only applies to Google Adwords.
Ad_Name varchar (64) The ad name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Doubleclick Campaign Manager and Facebook (display).
Ad_Unique_Id varchar (64) The id of the ad from the ads account in which the ad was resolved from. This applies to Doubleclick Campaign Manager and Facebook (display).
Creative_Name varchar (100) The creative name from the ads account in which the ad was resolved from, such as Winter Promo - Green button or Budget Webinar - sidebar. This applies to Google AdWords and Bing Ads (search).
Creative_Unique_Id varchar (64) The id of the creative from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_1 varchar (100) The first line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Description_2 varchar (100) The second line of the creative from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Destination_URL varchar (200) The landing page that clicks through from the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Creative_Display_URL varchar (200) The friendly URL name that’s shown on the search ad, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search).
Keyword_Name varchar (100) The keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from, such as Software Discount or Annual Budget. This applies to Google AdWords and Bing Ads (search)
Keyword_Unique_Id varchar (64) The Id of the keyword that was purchased from the Paid Search buy, which is pulled from the ads account in which the ad was resolved from. This applies to Google AdWords and Bing Ads (search
Keyword_Match_Type varchar (16) The type of match that was found between the search phrase and the purchased keyword, such as “broad” or “exact”
Is_Form_Submission_Touch boolean Whether or not this touchpoint had a form fill during the session
Is_Impression_Touch boolean Whether or not this touchpoint is treated as the first impression touch of the opportunity journey
Is_Deleted boolean Whether or not the record was deleted, used for diagnostics and auditing.

Back to top

Sample queries

How many Leads had sessions from Paid Search in 1-month?

select sum(tp.first_click_percentage)

from biz_leads leads

join biz_touchpoints tp

on leads.Id = tp.Lead_Id

join biz_user_touchpoints utp

on tp.User_Touchpoint_Id = utp.Id

where utp.touchpoint_date >= ‘2017-08-01’ and utp.touchpoint_date < ‘2017-09-01’ and utp.Channel like ‘Paid Search%’

How many sessions are there per Opportunity in Custom Model?

select top 100 opps.Id, count(utp.*)

from biz_opportunities opps

inner join biz_attribution_touchpoints tp

on opps.Id = tp.Opportunity_Id

inner join biz_user_touchpoints utp

on tp.User_Touchpoint_Id = utp.Id

where tp.Custom_Model_Percentage > 0

group by opps.Id

What is the revenue per channel in Custom model per month?

select tp.Channel, extract(year from tp.Touchpoint_date) as year, extract(month from tp.Touchpoint_date) as month, sum(opps.Amount * tp.Custom_Model_percentage) as amountWithAttribution

from biz_opportunities opps

join biz_attribution_touchpoints tp

on opps.Id = tp.Opportunity_Id

group by tp.Channel, extract(year from tp.Touchpoint_date), extract(month from tp.Touchpoint_date) order by 1, 2, 3

How many anonymous sessions by channel?

select channel, count

from biz_sessions

group by channel

What is the top landing page for Leads?

select top 5 landing_page, count

from biz_sessions

group by landing_page

On this page