Database Schema
attribution
Description: Contain attribution calculations for each enabled attribution model.
Click to expand JSON Schema
[
{
"name": "dd_stage_id",
"type": "STRING",
"description": "Unique identifier for the stage within the Dreamdata data model. (Combined Primary Key) (foreign key referencing dd_stage_id in the stage schema) (Combined Primary Key)"
},
{
"name": "dd_session_id",
"type": "STRING",
"description": "Unique identifier for the session containing the event. If the session only contains one event dd_session_id will be equal to the dd_event_id (Combined Primary Key) (same id as events table)"
},
{
"name": "timestamp",
"type": "TIMESTAMP",
"description": "Timestamp the session started"
},
{
"name": "quantity",
"type": "INTEGER",
"description": "Number of times the event was observed. 1 for most events, but exposure based events like linkedin_ad_impression will have quantity different from 1"
},
{
"name": "dd_visitor_id",
"type": "STRING",
"description": "Anonymized identifier for a visitor within the Dreamdata system. For identified visitors the dd_visitor_id will be equal to the dd_contact_id. (same as events table)"
},
{
"name": "dd_contact_id",
"type": "STRING",
"description": "Unique identifier for the contact associated with the session (foreign key referencing dd_contact_id in the contacts schema)."
},
{
"name": "dd_company_id",
"type": "STRING",
"description": "Unique identifier for the company associated with the session (foreign key referencing dd_company_id in the company schema)."
},
{
"name": "dd_tracking_type",
"type": "STRING",
"description": "Tracking type of the event. 'activity': all standard events that describes an action. 'exposure': exposure based events like linkedin_ad_impression"
},
{
"name": "dd_is_primary_event",
"type": "BOOLEAN",
"description": "For contacts associated with multiple companies their events are repeated to each company associated with the contact. dd_is_primary_event is a flag to remove the repeated events and only include events for the primary company of the contact. The flag is also 'true' for events without contacts. So to only include each occurrence of an event once (chosen the one associated with the primary company of a contact) use WHERE dd_is_primary_event. This is useful when answering contact level questions where the possible association to multiple companies are irrelevant. "
},
{
"name": "stage",
"type": "RECORD",
"fields": [
{
"name": "name",
"type": "STRING",
"description": "Name of the stage."
},
{
"name": "timestamp",
"type": "TIMESTAMP",
"description": "Timestamp when the stage was reached."
},
{
"name": "value",
"type": "FLOAT",
"description": "Value indicating the revenue associated with reaching the stage."
}
],
"description": "Record with information about the stage (dd_stage_id) associated with the session"
},
{
"name": "session",
"type": "RECORD",
"fields": [
{
"name": "channel",
"type": "STRING",
"description": "Channel of the session. Mapped field: This is either defined at input or mapped combining info from different fields. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings"
},
{
"name": "source",
"type": "STRING",
"description": "Source of the session. Mapped field: This is either defined at input or mapped combining info from different fields. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings."
},
{
"name": "campaign",
"type": "STRING",
"description": "Campaign of the session. Mapped field: This is either defined at input or mapped combining info from different fields."
},
{
"name": "medium",
"type": "STRING",
"description": "Medium of the session. Mapped field: This is either defined at input or mapped combining info from different fields."
},
{
"name": "term",
"type": "STRING",
"description": "Term of the session, set using a match with paid UTM parameter IDs or set using utm_term."
},
{
"name": "keyword",
"type": "STRING",
"description": "Keyword of the session, set using a match with paid UTM parameter IDs."
},
{
"name": "match_type",
"type": "STRING",
"description": "Match type e.g. 'EXACT', 'PHRASE', 'BROAD' etc"
},
{
"name": "landing_page",
"type": "STRING",
"description": "Landing page URL of the session without UTM parameters."
},
{
"name": "landing_page_url",
"type": "STRING",
"description": "Raw landing page URL of the session including UTM parameters."
},
{
"name": "landing_page_content_category",
"type": "STRING",
"description": "Content category of the landing page URL of the session. The definitions of content categories are setup using the Dreamdata Data Hub UI"
},
{
"name": "host",
"type": "STRING",
"description": "Domain of the landing page URL of the session."
},
{
"name": "referrer_clean",
"type": "STRING",
"description": "Cleaned referrer URL of the session without UTM parameters."
},
{
"name": "referrer",
"type": "STRING",
"description": "Raw referrer URL of the session including UTM parameters."
},
{
"name": "browser",
"type": "STRING",
"description": "Browser used to start the session."
},
{
"name": "device",
"type": "STRING",
"description": "Device type used to start the session."
},
{
"name": "os",
"type": "STRING",
"description": "Operating system used to start the session."
},
{
"name": "first_event_name",
"type": "STRING",
"description": "Name of the first event in the session."
},
{
"name": "contain_conversion",
"type": "BOOLEAN",
"description": "Flag indicating if the session contains an event labeled as a conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI"
},
{
"name": "first_conversion_name",
"type": "STRING",
"description": "Name of the first conversion in the session. The definitions of conversions are setup using the Dreamdata Data Hub UI"
},
{
"name": "ad_hierarchy",
"type": "RECORD",
"fields": [
{
"name": "level_1",
"type": "RECORD",
"fields": [
{
"name": "network_label",
"type": "STRING",
"description": "Network-specific label for the first level i.e. 'campaign' for Google and Meta and 'campaign group' for LinkedIn."
},
{
"name": "name",
"type": "STRING",
"description": "Name of the first level i.e. the name of the Google campaign."
},
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the first level within the network."
}
],
"description": "Top level for ads on the specific network"
},
{
"name": "level_2",
"type": "RECORD",
"fields": [
{
"name": "network_label",
"type": "STRING",
"description": "Network-specific label for the second level i.e. 'ad group' for Google, 'ad set' for Meta and 'campaign' for LinkedIn."
},
{
"name": "name",
"type": "STRING",
"description": "Name of the second level i.e. the name of the Google ad group."
},
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the second level within the network."
}
],
"description": "Second level for ads on the specific network"
}
],
"description": "Record containing information about the ad hierarchy detected, with specific network labels and IDs: These are filled in using matches with UTM parameters of the session. The id's in this record can be connected to the similar record in the spend table."
}
],
"description": "Record containing session-level information. Attribution is based on session parameters."
},
{
"name": "source_system",
"type": "RECORD",
"fields": [
{
"name": "id",
"type": "STRING",
"description": "Id for the event in the source system. For analytics sources this will be the message identifier."
},
{
"name": "source",
"type": "STRING",
"description": "Name of the source system of the event."
}
],
"description": "Record containing information about the source of the data"
},
{
"name": "attribution",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "model",
"type": "STRING",
"description": "Name of attribution model used. An attribution model is uniquely defined by its name."
},
{
"name": "weight",
"type": "FLOAT",
"description": "Weight assigned using the attribution model. This will be a number between 0 and 1."
},
{
"name": "value",
"type": "FLOAT",
"description": "Value assigned using the attribution model weight. Calculated as attribution weight times the value of the stage."
}
],
"description": "Array containing attribution for the session (dd_session_id) in the particular stage journey (dd_stage_id) according to each attribution model. Attribution models and exclusions are configured in the Dreamdata Data Hub UI"
}
]
Field
Type
Description
dd_stage_id
STRING
Unique identifier for the stage within the Dreamdata data model. (Combined Primary Key) (foreign key referencing dd_stage_id in the stage schema) (Combined Primary Key)
dd_session_id
STRING
Unique identifier for the session containing the event. If the session only contains one event dd_session_id will be equal to the dd_event_id (Combined Primary Key) (same id as events table)
timestamp
TIMESTAMP
Timestamp the session started
quantity
INTEGER
Number of times the event was observed. 1 for most events, but exposure based events like linkedin_ad_impression will have quantity different from 1
dd_visitor_id
STRING
Anonymized identifier for a visitor within the Dreamdata system. For identified visitors the dd_visitor_id will be equal to the dd_contact_id. (same as events table)
dd_contact_id
STRING
Unique identifier for the contact associated with the session (foreign key referencing dd_contact_id in the contacts schema).
dd_company_id
STRING
Unique identifier for the company associated with the session (foreign key referencing dd_company_id in the company schema).
dd_tracking_type
STRING
Tracking type of the event. 'activity': all standard events that describes an action. 'exposure': exposure based events like linkedin_ad_impression
dd_is_primary_event
BOOLEAN
For contacts associated with multiple companies their events are repeated to each company associated with the contact. dd_is_primary_event is a flag to remove the repeated events and only include events for the primary company of the contact. The flag is also 'true' for events without contacts. So to only include each occurrence of an event once (chosen the one associated with the primary company of a contact) use WHERE dd_is_primary_event. This is useful when answering contact level questions where the possible association to multiple companies are irrelevant.
▼ stage
RECORD
Record with information about the stage (dd_stage_id) associated with the session
└─ name
STRING
Name of the stage.
└─ timestamp
TIMESTAMP
Timestamp when the stage was reached.
└─ value
FLOAT
Value indicating the revenue associated with reaching the stage.
▼ session
RECORD
Record containing session-level information. Attribution is based on session parameters.
└─ channel
STRING
Channel of the session. Mapped field: This is either defined at input or mapped combining info from different fields. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings
└─ source
STRING
Source of the session. Mapped field: This is either defined at input or mapped combining info from different fields. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings.
└─ campaign
STRING
Campaign of the session. Mapped field: This is either defined at input or mapped combining info from different fields.
└─ medium
STRING
Medium of the session. Mapped field: This is either defined at input or mapped combining info from different fields.
└─ term
STRING
Term of the session, set using a match with paid UTM parameter IDs or set using utm_term.
└─ keyword
STRING
Keyword of the session, set using a match with paid UTM parameter IDs.
└─ match_type
STRING
Match type e.g. 'EXACT', 'PHRASE', 'BROAD' etc
└─ landing_page
STRING
Landing page URL of the session without UTM parameters.
└─ landing_page_url
STRING
Raw landing page URL of the session including UTM parameters.
└─ landing_page_content_category
STRING
Content category of the landing page URL of the session. The definitions of content categories are setup using the Dreamdata Data Hub UI
└─ host
STRING
Domain of the landing page URL of the session.
└─ referrer_clean
STRING
Cleaned referrer URL of the session without UTM parameters.
└─ referrer
STRING
Raw referrer URL of the session including UTM parameters.
└─ browser
STRING
Browser used to start the session.
└─ device
STRING
Device type used to start the session.
└─ os
STRING
Operating system used to start the session.
└─ first_event_name
STRING
Name of the first event in the session.
└─ contain_conversion
BOOLEAN
Flag indicating if the session contains an event labeled as a conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI
└─ first_conversion_name
STRING
Name of the first conversion in the session. The definitions of conversions are setup using the Dreamdata Data Hub UI
└─ ▼ ad_hierarchy
RECORD
Record containing information about the ad hierarchy detected, with specific network labels and IDs: These are filled in using matches with UTM parameters of the session. The id's in this record can be connected to the similar record in the spend table.
└─ ▼ level_1
RECORD
Top level for ads on the specific network
└─ network_label
STRING
Network-specific label for the first level i.e. 'campaign' for Google and Meta and 'campaign group' for LinkedIn.
└─ name
STRING
Name of the first level i.e. the name of the Google campaign.
└─ id
STRING
Unique identifier for the first level within the network.
└─ ▼ level_2
RECORD
Second level for ads on the specific network
└─ network_label
STRING
Network-specific label for the second level i.e. 'ad group' for Google, 'ad set' for Meta and 'campaign' for LinkedIn.
└─ name
STRING
Name of the second level i.e. the name of the Google ad group.
└─ id
STRING
Unique identifier for the second level within the network.
▼ source_system
RECORD
Record containing information about the source of the data
└─ id
STRING
Id for the event in the source system. For analytics sources this will be the message identifier.
└─ source
STRING
Name of the source system of the event.
▼ attribution
RECORD (REPEATED)
Array containing attribution for the session (dd_session_id) in the particular stage journey (dd_stage_id) according to each attribution model. Attribution models and exclusions are configured in the Dreamdata Data Hub UI
└─ model
STRING
Name of attribution model used. An attribution model is uniquely defined by its name.
└─ weight
FLOAT
Weight assigned using the attribution model. This will be a number between 0 and 1.
└─ value
FLOAT
Value assigned using the attribution model weight. Calculated as attribution weight times the value of the stage.
companies
Description: Contain company entities for Dreamdata foundational data model. A company is uniquely identified by the id dd_company_id.
Click to expand JSON Schema
[
{
"name": "dd_company_id",
"type": "STRING",
"description": "Id for the company within the Dreamdata system (Primary key)"
},
{
"name": "domain",
"type": "STRING",
"description": "Company domain"
},
{
"name": "all_domains",
"type": "STRING",
"mode": "REPEATED",
"description": "List of all domains associated with the company. This defines which anonymous events (events with only a company domain) are associated with the company."
},
{
"name": "account_owner",
"type": "RECORD",
"fields": [
{
"name": "email",
"type": "STRING",
"description": "Account owner email address from primary CRM"
},
{
"name": "name",
"type": "STRING",
"description": " Account owner name from primary CRM"
}
],
"description": "Record containing info about the account owner in the primary CRM. Will be empty if company is not found in primary CRM."
},
{
"name": "properties",
"type": "RECORD",
"fields": [
{
"name": "created_date",
"type": "TIMESTAMP",
"description": "Date when the company record was created"
},
{
"name": "name",
"type": "STRING",
"description": "Company name"
},
{
"name": "country",
"type": "STRING",
"description": "Company country"
},
{
"name": "industry",
"type": "STRING",
"description": "Industry the company belongs to"
},
{
"name": "annual_revenue",
"type": "STRING",
"description": "Binned annual revenue of the company."
},
{
"name": "number_of_employees",
"type": "STRING",
"description": "Binned number of employees at the company."
},
{
"name": "linkedin_url",
"type": "STRING",
"description": "Company's LinkedIn profile URL."
},
{
"name": "is_from_primary_crm",
"type": "BOOLEAN",
"description": "Flag indicating if the record originated from the primary CRM system"
},
{
"name": "is_anonymous_company",
"type": "BOOLEAN",
"description": "Flag indicating if the company is anonymous. An anonymous company is one that is not from the primary CRM and does not have any contacts with a business email associated. Most company counts in the Dreamdata UI are make using is_anonymous_company = FALSE i.e. not including anonymous companies."
},
{
"name": "engagement_score",
"type": "FLOAT",
"description": "Engagement score for the company within the last 30 days (0-1). This value is automatically calculated by the Dreamdata platform based on the company’s activities within the last 30 days"
}
],
"description": "Record containing company properties. The properties are combined from the different source integrations using waterfall enrichment prioritizing 1. The primary CRM 2. Other integrations including active intent providers 3. Dreamdata's proprietary AI enrichment and labeling."
},
{
"name": "custom_properties",
"type": "JSON",
"description": "Custom company properties in JSON format. These are the selected properties from the primary CRM. Which properties are included is controlled through the Dreamdata source UI"
},
{
"name": "source_system",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "id",
"type": "STRING",
"description": "Id for the object record in the source system. This will not necessarily match the 'dd_' id, but is useful for locating records in the source systems directly"
},
{
"name": "source",
"type": "STRING",
"description": "Name of the source system like hubspot, salesforce, etc."
},
{
"name": "object",
"type": "STRING",
"description": "The name of the object in the source system like companies, accounts, etc."
}
],
"description": "Array containing the source systems in which the company was found."
},
{
"name": "audiences",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "dd_audience_id",
"type": "STRING",
"description": "Id of the audience within the Dreamdata platform. This uniquely defines the audience"
},
{
"name": "name",
"type": "STRING",
"description": "Name of the audience"
},
{
"name": "created_date",
"type": "TIMESTAMP",
"description": "Date for the creation of the audience"
},
{
"name": "description",
"type": "STRING",
"description": "Description of the audience given within the Dreamdata UI."
}
],
"description": "Array containing the audiences the company is part of. Audiences can be defined using the no-code audience builder in the Dreamdata UI and will be automatically updated."
}
]
Field
Type
Description
dd_company_id
STRING
Id for the company within the Dreamdata system (Primary key)
domain
STRING
Company domain
all_domains
STRING (REPEATED)
List of all domains associated with the company. This defines which anonymous events (events with only a company domain) are associated with the company.
▼ account_owner
RECORD
Record containing info about the account owner in the primary CRM. Will be empty if company is not found in primary CRM.
└─ email
STRING
Account owner email address from primary CRM
└─ name
STRING
Account owner name from primary CRM
▼ properties
RECORD
Record containing company properties. The properties are combined from the different source integrations using waterfall enrichment prioritizing 1. The primary CRM 2. Other integrations including active intent providers 3. Dreamdata's proprietary AI enrichment and labeling.
└─ created_date
TIMESTAMP
Date when the company record was created
└─ name
STRING
Company name
└─ country
STRING
Company country
└─ industry
STRING
Industry the company belongs to
└─ annual_revenue
STRING
Binned annual revenue of the company.
└─ number_of_employees
STRING
Binned number of employees at the company.
└─ linkedin_url
STRING
Company's LinkedIn profile URL.
└─ is_from_primary_crm
BOOLEAN
Flag indicating if the record originated from the primary CRM system
└─ is_anonymous_company
BOOLEAN
Flag indicating if the company is anonymous. An anonymous company is one that is not from the primary CRM and does not have any contacts with a business email associated. Most company counts in the Dreamdata UI are make using is_anonymous_company = FALSE i.e. not including anonymous companies.
└─ engagement_score
FLOAT
Engagement score for the company within the last 30 days (0-1). This value is automatically calculated by the Dreamdata platform based on the company’s activities within the last 30 days
custom_properties
JSON
Custom company properties in JSON format. These are the selected properties from the primary CRM. Which properties are included is controlled through the Dreamdata source UI
▼ source_system
RECORD (REPEATED)
Array containing the source systems in which the company was found.
└─ id
STRING
Id for the object record in the source system. This will not necessarily match the 'dd_' id, but is useful for locating records in the source systems directly
└─ source
STRING
Name of the source system like hubspot, salesforce, etc.
└─ object
STRING
The name of the object in the source system like companies, accounts, etc.
▼ audiences
RECORD (REPEATED)
Array containing the audiences the company is part of. Audiences can be defined using the no-code audience builder in the Dreamdata UI and will be automatically updated.
└─ dd_audience_id
STRING
Id of the audience within the Dreamdata platform. This uniquely defines the audience
└─ name
STRING
Name of the audience
└─ created_date
TIMESTAMP
Date for the creation of the audience
└─ description
STRING
Description of the audience given within the Dreamdata UI.
contacts
Description: Contain contact entities for Dreamdata foundational data model. A person identified uniquely by an email or the anonymized dd_contact_id.
Click to expand JSON Schema
[
{
"name": "dd_contact_id",
"type": "STRING",
"description": "Unique identifier for the contact within Dreamdata. An anonymized hash or the email. (Primary key)"
},
{
"name": "email",
"type": "STRING",
"description": "Contact's primary email address. (Primary key)"
},
{
"name": "properties",
"type": "RECORD",
"fields": [
{
"name": "created_date",
"type": "TIMESTAMP",
"description": "Date when the contact record was created"
},
{
"name": "name",
"type": "STRING",
"description": "Contact's full name"
},
{
"name": "first_name",
"type": "STRING",
"description": "Contact's first name"
},
{
"name": "last_name",
"type": "STRING",
"description": "Contact's last name"
},
{
"name": "phone_number",
"type": "STRING",
"description": "Contact's phone number"
},
{
"name": "title",
"type": "STRING",
"description": "Contact's job title"
},
{
"name": "role",
"type": "STRING",
"description": "Contact's professional role."
},
{
"name": "seniority",
"type": "STRING",
"description": "Contact's seniority level"
},
{
"name": "country",
"type": "STRING",
"description": "Contact's primary country"
},
{
"name": "email_domain",
"type": "STRING",
"description": "Domain name of the contact's email address"
},
{
"name": "additional_emails",
"type": "STRING",
"mode": "REPEATED",
"description": "List of additional email addresses associated with the contact. Only used if the primary CRM supports additional emails"
},
{
"name": "is_corporate_email",
"type": "BOOLEAN",
"description": "Flag indicating if the email address belongs to a corporate/business domain. False will indicate that it is a free email domain like gmail, hotmail etc."
},
{
"name": "is_from_primary_crm",
"type": "BOOLEAN",
"description": "Flag indicating if the record originated from the primary CRM system"
}
],
"description": "Record containing contact properties. The properties are combined from the different source integrations using waterfall enrichment prioritizing 1. The primary CRM 2. Other integrations including active intent providers 3. Dreamdata's proprietary AI enrichment and labeling."
},
{
"name": "source_system",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "id",
"type": "STRING",
"description": "Id for the object record in the source system. This will not necessarily match the 'dd_' id, but is useful for locating records in the source systems directly"
},
{
"name": "source",
"type": "STRING",
"description": "Name of the source system like hubspot, salesforce, etc."
},
{
"name": "object",
"type": "STRING",
"description": "The name of the object in the source system like contacts, leads, etc."
}
],
"description": "Array containing the source systems in which the contact was found."
},
{
"name": "custom_properties",
"type": "JSON",
"description": "Custom contact properties in JSON format. These are the selected properties from the primary CRM. Which properties are included is controlled through the Dreamdata source UI"
},
{
"name": "companies",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "dd_company_id",
"type": "STRING",
"description": "Id for the company (foreign key referencing dd_company_id in the company schema)"
},
{
"name": "name",
"type": "STRING",
"description": "Company name"
},
{
"name": "domain",
"type": "STRING",
"description": "Company domain"
},
{
"name": "is_primary_company",
"type": "BOOLEAN",
"description": "Flag indicating if the company is the primary company associated with the contact. The primary company is used when stages are only associated with a contact. Then it will automatically be associated with the primary company for conversion rate calculations"
}
],
"description": "Array containing all of the companies associated with the contact"
},
{
"name": "audiences",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "dd_audience_id",
"type": "STRING",
"description": "Id of the audience within the Dreamdata platform. This uniquely defines the audience"
},
{
"name": "name",
"type": "STRING",
"description": "Name of the audience"
},
{
"name": "created_date",
"type": "TIMESTAMP",
"description": "Date for the creation of the audience"
},
{
"name": "description",
"type": "STRING",
"description": "Description of the audience given within the Dreamdata UI."
}
],
"description": "Array containing the audiences the contact is part of. Audiences can be defined using the no-code audience builder in the Dreamdata UI and will be automatically updated."
}
]
Field
Type
Description
dd_contact_id
STRING
Unique identifier for the contact within Dreamdata. An anonymized hash or the email. (Primary key)
email
STRING
Contact's primary email address. (Primary key)
▼ properties
RECORD
Record containing contact properties. The properties are combined from the different source integrations using waterfall enrichment prioritizing 1. The primary CRM 2. Other integrations including active intent providers 3. Dreamdata's proprietary AI enrichment and labeling.
└─ created_date
TIMESTAMP
Date when the contact record was created
└─ name
STRING
Contact's full name
└─ first_name
STRING
Contact's first name
└─ last_name
STRING
Contact's last name
└─ phone_number
STRING
Contact's phone number
└─ title
STRING
Contact's job title
└─ role
STRING
Contact's professional role.
└─ seniority
STRING
Contact's seniority level
└─ country
STRING
Contact's primary country
└─ email_domain
STRING
Domain name of the contact's email address
└─ additional_emails
STRING (REPEATED)
List of additional email addresses associated with the contact. Only used if the primary CRM supports additional emails
└─ is_corporate_email
BOOLEAN
Flag indicating if the email address belongs to a corporate/business domain. False will indicate that it is a free email domain like gmail, hotmail etc.
└─ is_from_primary_crm
BOOLEAN
Flag indicating if the record originated from the primary CRM system
▼ source_system
RECORD (REPEATED)
Array containing the source systems in which the contact was found.
└─ id
STRING
Id for the object record in the source system. This will not necessarily match the 'dd_' id, but is useful for locating records in the source systems directly
└─ source
STRING
Name of the source system like hubspot, salesforce, etc.
└─ object
STRING
The name of the object in the source system like contacts, leads, etc.
custom_properties
JSON
Custom contact properties in JSON format. These are the selected properties from the primary CRM. Which properties are included is controlled through the Dreamdata source UI
▼ companies
RECORD (REPEATED)
Array containing all of the companies associated with the contact
└─ dd_company_id
STRING
Id for the company (foreign key referencing dd_company_id in the company schema)
└─ name
STRING
Company name
└─ domain
STRING
Company domain
└─ is_primary_company
BOOLEAN
Flag indicating if the company is the primary company associated with the contact. The primary company is used when stages are only associated with a contact. Then it will automatically be associated with the primary company for conversion rate calculations
▼ audiences
RECORD (REPEATED)
Array containing the audiences the contact is part of. Audiences can be defined using the no-code audience builder in the Dreamdata UI and will be automatically updated.
└─ dd_audience_id
STRING
Id of the audience within the Dreamdata platform. This uniquely defines the audience
└─ name
STRING
Name of the audience
└─ created_date
TIMESTAMP
Date for the creation of the audience
└─ description
STRING
Description of the audience given within the Dreamdata UI.
events
Description: Events are basic activities performed by or towards a contact or company.
Click to expand JSON Schema
[
{
"name": "dd_event_id",
"type": "STRING",
"description": "Unique identifier for the event within the Dreamdata system. Each row has a unique value (Primary key)"
},
{
"name": "dd_session_id",
"type": "STRING",
"description": "Unique identifier for the session containing the event. If the session only contains one event, dd_session_id will be equal to the dd_event_id. If more events in a session, the dd_session_id will be the dd_event_id of the first event in the session."
},
{
"name": "event_name",
"type": "STRING",
"description": "Name of the event e.g., 'page_view', 'form_submit'"
},
{
"name": "timestamp",
"type": "TIMESTAMP",
"description": "Timestamp the event occurred"
},
{
"name": "quantity",
"type": "INTEGER",
"description": "Number of times the event was observed. 1 for most events, but exposure based events like linkedin_ad_impression will have quantity different from 1. The quantity field is used in combination with with dd_tracking_type = 'exposure', where counting the distinct dd_event_id is not sufficient to get the correct number of impressions."
},
{
"name": "dd_visitor_id",
"type": "STRING",
"description": "Anonymized identifier for a visitor within the Dreamdata system. For identified visitors the dd_visitor_id will be equal to the dd_contact_id. This field is used to calculate the Visitor count as COUNT( DISTINCT dd_pseudo_visitor_id)"
},
{
"name": "dd_contact_id",
"type": "STRING",
"description": "Unique identifier for the contact associated with the event (foreign key referencing dd_contact_id in the contacts schema)"
},
{
"name": "dd_company_id",
"type": "STRING",
"description": "Unique identifier for the company associated with the event (foreign key referencing dd_company_id in the company schema)"
},
{
"name": "dd_tracking_type",
"type": "STRING",
"description": "Tracking type of the event. 'activity': all standard events that describes an action. 'exposure': exposure based events like linkedin_ad_impression. When counting and 'activity' we can both count distinct dd_event_id or sum(quantity). For counting 'exposure' then only sum(quantity) will give the correct number"
},
{
"name": "dd_event_session_order",
"type": "INTEGER",
"description": "Order of the event within the session. To obtain the first event of each session use 'WHERE dd_event_session_order = 1'"
},
{
"name": "dd_is_primary_event",
"type": "BOOLEAN",
"description": "For contacts associated with multiple companies their events are repeated to each company associated with the contact. dd_is_primary_event is a flag to remove the repeated events and only include events for the primary company of the contact (the flag is also 'true' for events without contacts). IMPORTANT: For most questions this should not be used, as it will remove part of the stage journeyes for companies that are not the primary for the contact. This should only be used when counting basic events i.e. how many people view a page or performed an event. For questions involving companies, conversion, or how events influenced funnel stages this field should not be used. "
},
{
"name": "event",
"type": "RECORD",
"fields": [
{
"name": "url_clean",
"type": "STRING",
"description": "Cleaned URL without UTM parameters."
},
{
"name": "url",
"type": "STRING",
"description": "Raw URL including UTM parameters."
},
{
"name": "referrer_clean",
"type": "STRING",
"description": "Cleaned referrer URL without UTM parameters."
},
{
"name": "referrer",
"type": "STRING",
"description": "Raw referrer URL including UTM parameters."
},
{
"name": "utm_medium",
"type": "STRING",
"description": "UTM medium parameter. This is extracted from the url. For unified mapped field see session.medium."
},
{
"name": "utm_campaign",
"type": "STRING",
"description": "UTM campaign parameter. This is extracted from the url. For unified mapped field see session.campaign."
},
{
"name": "utm_source",
"type": "STRING",
"description": "UTM source parameter. This is extracted from the url. For unified mapped field see session.source."
},
{
"name": "utm_term",
"type": "STRING",
"description": "UTM term parameter. This is extracted from the url. For unified mapped field see session.term."
},
{
"name": "host",
"type": "STRING",
"description": "Domain of the URL where the event occurred."
},
{
"name": "browser",
"type": "STRING",
"description": "Browser used."
},
{
"name": "browser_version",
"type": "STRING",
"description": "Browser version used."
},
{
"name": "device",
"type": "STRING",
"description": "Device type used."
},
{
"name": "os",
"type": "STRING",
"description": "Operating system used."
},
{
"name": "os_version",
"type": "STRING",
"description": "Operating system version used."
},
{
"name": "campaign",
"type": "STRING",
"description": "Campaign of the event. Mapped field: This is either defined at input or mapped combining info from different fields such as utm_campaign. The campaign driving the event in an attribution context is the campaign of the session i.e. the campaign responsible for starting the session containing the event."
},
{
"name": "medium",
"type": "STRING",
"description": "Medium of the event. Mapped field: This is either defined at input or mapped combining info from different fields such as utm_medium."
},
{
"name": "term",
"type": "STRING",
"description": "Term set using a match with paid UTM parameter IDs or set using utm_term."
},
{
"name": "keyword",
"type": "STRING",
"description": "Keyword set using a match with paid UTM parameter IDs."
},
{
"name": "match_type",
"type": "STRING",
"description": "Match type e.g. 'EXACT', 'PHRASE', 'BROAD' etc."
},
{
"name": "content_category",
"type": "STRING",
"description": "Content category of URL associated with the event. The definitions of content categories are setup using the Dreamdata Data Hub UI"
},
{
"name": "is_conversion",
"type": "BOOLEAN",
"description": "Flag indicating if the event is a conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI"
},
{
"name": "conversion_name",
"type": "STRING",
"description": "Name of the conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI"
},
{
"name": "is_single_session",
"type": "BOOLEAN",
"description": "Flag indicating if the event should be considered as a stand-alone session and not be grouped with other events into a session."
}
],
"description": "Record containing detailed information about the event"
},
{
"name": "session",
"type": "RECORD",
"fields": [
{
"name": "channel",
"type": "STRING",
"description": "Channel of the session. Mapped field: This is either defined at input or mapped combining info from different fields. This is the unified channel label used accross Dreamdata reports i.e. the channel responsible for starting the session containing the event. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings"
},
{
"name": "source",
"type": "STRING",
"description": "Source of the session. Mapped field: This is either defined at input or mapped combining info from different fields like utm_source. This is the unified source label used accross Dreamdata reports i.e. the source responsible for starting the session containing the event. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings."
},
{
"name": "campaign",
"type": "STRING",
"description": "Campaign of the session. Mapped field: This is either defined at input or mapped combining info from different fields like utm_campaign. This is the unified campaign label used accross Dreamdata reports i.e. the campaign responsible for starting the session containing the event."
},
{
"name": "medium",
"type": "STRING",
"description": "Medium of the session. Mapped field: This is either defined at input or mapped combining info from different fields."
},
{
"name": "term",
"type": "STRING",
"description": "Term of the session, set using a match with paid UTM parameter IDs or set using utm_term."
},
{
"name": "keyword",
"type": "STRING",
"description": "Keyword of the session, set using a match with paid UTM parameter IDs."
},
{
"name": "match_type",
"type": "STRING",
"description": "Match type e.g. 'EXACT', 'PHRASE', 'BROAD' etc"
},
{
"name": "landing_page",
"type": "STRING",
"description": "Landing page URL of the session without UTM parameters."
},
{
"name": "landing_page_url",
"type": "STRING",
"description": "Raw landing page URL of the session including UTM parameters."
},
{
"name": "landing_page_content_category",
"type": "STRING",
"description": "Content category of the landing page URL of the session. The definitions of content categories are setup using the Dreamdata Data Hub UI"
},
{
"name": "host",
"type": "STRING",
"description": "Domain of the landing page URL of the session."
},
{
"name": "referrer_clean",
"type": "STRING",
"description": "Cleaned referrer URL of the session without UTM parameters."
},
{
"name": "referrer",
"type": "STRING",
"description": "Raw referrer URL of the session including UTM parameters."
},
{
"name": "browser",
"type": "STRING",
"description": "Browser used to start the session."
},
{
"name": "device",
"type": "STRING",
"description": "Device type used to start the session."
},
{
"name": "os",
"type": "STRING",
"description": "Operating system used to start the session."
},
{
"name": "first_event_name",
"type": "STRING",
"description": "Name of the first event in the session."
},
{
"name": "contain_conversion",
"type": "BOOLEAN",
"description": "Flag indicating if the session contains an event labeled as a conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI"
},
{
"name": "first_conversion_name",
"type": "STRING",
"description": "Name of the first conversion in the session. The definitions of conversions are setup using the Dreamdata Data Hub UI"
},
{
"name": "ad_hierarchy",
"type": "RECORD",
"fields": [
{
"name": "level_1",
"type": "RECORD",
"fields": [
{
"name": "network_label",
"type": "STRING",
"description": "Network-specific label for the first level i.e. 'campaign' for Google and Meta and 'campaign group' for LinkedIn."
},
{
"name": "name",
"type": "STRING",
"description": "Name of the first level i.e. the name of the Google campaign."
},
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the first level within the network."
}
],
"description": "Top level for ads on the specific network"
},
{
"name": "level_2",
"type": "RECORD",
"fields": [
{
"name": "network_label",
"type": "STRING",
"description": "Network-specific label for the second level i.e. 'ad group' for Google, 'ad set' for Meta and 'campaign' for LinkedIn."
},
{
"name": "name",
"type": "STRING",
"description": "Name of the second level i.e. the name of the Google ad group."
},
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the second level within the network."
}
],
"description": "Second level for ads on the specific network"
}
],
"description": "Record containing information about the ad hierarchy detected, with specific network labels and IDs: These are filled in using matches with UTM parameters of the session. The id's in this record can be connected to the similar record in the spend table."
}
],
"description": "Record containing detailed information about the session which the event is part of. The session will be the same for all events in the session. Attribution is based on session parameters."
},
{
"name": "source_system",
"type": "RECORD",
"fields": [
{
"name": "id",
"type": "STRING",
"description": "Id for the event in the source system. For analytics sources this will be the message identifier."
},
{
"name": "source",
"type": "STRING",
"description": "Name of the source system of the event."
}
],
"description": "Record containing information about the source of the data"
},
{
"name": "stages",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "dd_stage_id",
"type": "STRING",
"description": "Unique identifier for the individual stage influenced by the event. (foreign key referencing dd_stage_id in the stages schema)."
},
{
"name": "name",
"type": "STRING",
"description": "Name of the stage."
},
{
"name": "timestamp",
"type": "TIMESTAMP",
"description": "Timestamp when the stage was reached."
},
{
"name": "value",
"type": "FLOAT",
"description": "Value indicating the revenue associated with reaching the stage."
}
],
"description": "Array containing the stages that are influenced by the event = where the event is part of that particular stage journey. An event cannot influence a stage reached before the timestamp of the event. This array is used for performance analysis i.e. 'what did this event lead to' or 'which funnel stage is influenced by this event'. This can be accomplished by unnesting the stages array and count distinct dd_stage_id's i.e. for how many stages (dd_stage_id) are the event part of the stage journey."
}
]
Field
Type
Description
dd_event_id
STRING
Unique identifier for the event within the Dreamdata system. Each row has a unique value (Primary key)
dd_session_id
STRING
Unique identifier for the session containing the event. If the session only contains one event, dd_session_id will be equal to the dd_event_id. If more events in a session, the dd_session_id will be the dd_event_id of the first event in the session.
event_name
STRING
Name of the event e.g., 'page_view', 'form_submit'
timestamp
TIMESTAMP
Timestamp the event occurred
quantity
INTEGER
Number of times the event was observed. 1 for most events, but exposure based events like linkedin_ad_impression will have quantity different from 1. The quantity field is used in combination with with dd_tracking_type = 'exposure', where counting the distinct dd_event_id is not sufficient to get the correct number of impressions.
dd_visitor_id
STRING
Anonymized identifier for a visitor within the Dreamdata system. For identified visitors the dd_visitor_id will be equal to the dd_contact_id. This field is used to calculate the Visitor count as COUNT( DISTINCT dd_pseudo_visitor_id)
dd_contact_id
STRING
Unique identifier for the contact associated with the event (foreign key referencing dd_contact_id in the contacts schema)
dd_company_id
STRING
Unique identifier for the company associated with the event (foreign key referencing dd_company_id in the company schema)
dd_tracking_type
STRING
Tracking type of the event. 'activity': all standard events that describes an action. 'exposure': exposure based events like linkedin_ad_impression. When counting and 'activity' we can both count distinct dd_event_id or sum(quantity). For counting 'exposure' then only sum(quantity) will give the correct number
dd_event_session_order
INTEGER
Order of the event within the session. To obtain the first event of each session use 'WHERE dd_event_session_order = 1'
dd_is_primary_event
BOOLEAN
For contacts associated with multiple companies their events are repeated to each company associated with the contact. dd_is_primary_event is a flag to remove the repeated events and only include events for the primary company of the contact (the flag is also 'true' for events without contacts). IMPORTANT: For most questions this should not be used, as it will remove part of the stage journeyes for companies that are not the primary for the contact. This should only be used when counting basic events i.e. how many people view a page or performed an event. For questions involving companies, conversion, or how events influenced funnel stages this field should not be used.
▼ event
RECORD
Record containing detailed information about the event
└─ url_clean
STRING
Cleaned URL without UTM parameters.
└─ url
STRING
Raw URL including UTM parameters.
└─ referrer_clean
STRING
Cleaned referrer URL without UTM parameters.
└─ referrer
STRING
Raw referrer URL including UTM parameters.
└─ utm_medium
STRING
UTM medium parameter. This is extracted from the url. For unified mapped field see session.medium.
└─ utm_campaign
STRING
UTM campaign parameter. This is extracted from the url. For unified mapped field see session.campaign.
└─ utm_source
STRING
UTM source parameter. This is extracted from the url. For unified mapped field see session.source.
└─ utm_term
STRING
UTM term parameter. This is extracted from the url. For unified mapped field see session.term.
└─ host
STRING
Domain of the URL where the event occurred.
└─ browser
STRING
Browser used.
└─ browser_version
STRING
Browser version used.
└─ device
STRING
Device type used.
└─ os
STRING
Operating system used.
└─ os_version
STRING
Operating system version used.
└─ campaign
STRING
Campaign of the event. Mapped field: This is either defined at input or mapped combining info from different fields such as utm_campaign. The campaign driving the event in an attribution context is the campaign of the session i.e. the campaign responsible for starting the session containing the event.
└─ medium
STRING
Medium of the event. Mapped field: This is either defined at input or mapped combining info from different fields such as utm_medium.
└─ term
STRING
Term set using a match with paid UTM parameter IDs or set using utm_term.
└─ keyword
STRING
Keyword set using a match with paid UTM parameter IDs.
└─ match_type
STRING
Match type e.g. 'EXACT', 'PHRASE', 'BROAD' etc.
└─ content_category
STRING
Content category of URL associated with the event. The definitions of content categories are setup using the Dreamdata Data Hub UI
└─ is_conversion
BOOLEAN
Flag indicating if the event is a conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI
└─ conversion_name
STRING
Name of the conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI
└─ is_single_session
BOOLEAN
Flag indicating if the event should be considered as a stand-alone session and not be grouped with other events into a session.
▼ session
RECORD
Record containing detailed information about the session which the event is part of. The session will be the same for all events in the session. Attribution is based on session parameters.
└─ channel
STRING
Channel of the session. Mapped field: This is either defined at input or mapped combining info from different fields. This is the unified channel label used accross Dreamdata reports i.e. the channel responsible for starting the session containing the event. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings
└─ source
STRING
Source of the session. Mapped field: This is either defined at input or mapped combining info from different fields like utm_source. This is the unified source label used accross Dreamdata reports i.e. the source responsible for starting the session containing the event. See Dreamdata Data Hub UI to customize mapping outside of the Dreamdata default mappings.
└─ campaign
STRING
Campaign of the session. Mapped field: This is either defined at input or mapped combining info from different fields like utm_campaign. This is the unified campaign label used accross Dreamdata reports i.e. the campaign responsible for starting the session containing the event.
└─ medium
STRING
Medium of the session. Mapped field: This is either defined at input or mapped combining info from different fields.
└─ term
STRING
Term of the session, set using a match with paid UTM parameter IDs or set using utm_term.
└─ keyword
STRING
Keyword of the session, set using a match with paid UTM parameter IDs.
└─ match_type
STRING
Match type e.g. 'EXACT', 'PHRASE', 'BROAD' etc
└─ landing_page
STRING
Landing page URL of the session without UTM parameters.
└─ landing_page_url
STRING
Raw landing page URL of the session including UTM parameters.
└─ landing_page_content_category
STRING
Content category of the landing page URL of the session. The definitions of content categories are setup using the Dreamdata Data Hub UI
└─ host
STRING
Domain of the landing page URL of the session.
└─ referrer_clean
STRING
Cleaned referrer URL of the session without UTM parameters.
└─ referrer
STRING
Raw referrer URL of the session including UTM parameters.
└─ browser
STRING
Browser used to start the session.
└─ device
STRING
Device type used to start the session.
└─ os
STRING
Operating system used to start the session.
└─ first_event_name
STRING
Name of the first event in the session.
└─ contain_conversion
BOOLEAN
Flag indicating if the session contains an event labeled as a conversion. The definitions of conversions are setup using the Dreamdata Data Hub UI
└─ first_conversion_name
STRING
Name of the first conversion in the session. The definitions of conversions are setup using the Dreamdata Data Hub UI
└─ ▼ ad_hierarchy
RECORD
Record containing information about the ad hierarchy detected, with specific network labels and IDs: These are filled in using matches with UTM parameters of the session. The id's in this record can be connected to the similar record in the spend table.
└─ ▼ level_1
RECORD
Top level for ads on the specific network
└─ network_label
STRING
Network-specific label for the first level i.e. 'campaign' for Google and Meta and 'campaign group' for LinkedIn.
└─ name
STRING
Name of the first level i.e. the name of the Google campaign.
└─ id
STRING
Unique identifier for the first level within the network.
└─ ▼ level_2
RECORD
Second level for ads on the specific network
└─ network_label
STRING
Network-specific label for the second level i.e. 'ad group' for Google, 'ad set' for Meta and 'campaign' for LinkedIn.
└─ name
STRING
Name of the second level i.e. the name of the Google ad group.
└─ id
STRING
Unique identifier for the second level within the network.
▼ source_system
RECORD
Record containing information about the source of the data
└─ id
STRING
Id for the event in the source system. For analytics sources this will be the message identifier.
└─ source
STRING
Name of the source system of the event.
▼ stages
RECORD (REPEATED)
Array containing the stages that are influenced by the event = where the event is part of that particular stage journey. An event cannot influence a stage reached before the timestamp of the event. This array is used for performance analysis i.e. 'what did this event lead to' or 'which funnel stage is influenced by this event'. This can be accomplished by unnesting the stages array and count distinct dd_stage_id's i.e. for how many stages (dd_stage_id) are the event part of the stage journey.
└─ dd_stage_id
STRING
Unique identifier for the individual stage influenced by the event. (foreign key referencing dd_stage_id in the stages schema).
└─ name
STRING
Name of the stage.
└─ timestamp
TIMESTAMP
Timestamp when the stage was reached.
└─ value
FLOAT
Value indicating the revenue associated with reaching the stage.
spend
Description: Contains spend data imported through native integrations and direct uploads.
Click to expand JSON Schema
[
{
"name": "timestamp",
"type": "TIMESTAMP",
"description": "Date and time the cost was incurred"
},
{
"name": "cost",
"type": "FLOAT",
"description": "Amount recorded in the currency set in the Dreamdata platform."
},
{
"name": "impressions",
"type": "FLOAT",
"description": "Number of ad impressions."
},
{
"name": "clicks",
"type": "FLOAT",
"description": "Number of ad clicks."
},
{
"name": "adNetwork",
"type": "STRING",
"description": "Name of the advertising network used like 'Google', 'LinkedIn' etc"
},
{
"name": "channel",
"type": "STRING",
"description": "Channel referencing the session channel in events table. This can be used to match spend to activity."
},
{
"name": "source",
"type": "STRING",
"description": "Source referencing the session source in events table. This can be used to match spend to activity."
},
{
"name": "ad_account",
"type": "RECORD",
"fields": [
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the ad account within the network"
},
{
"name": "name",
"type": "STRING",
"description": "Name of the ad account"
}
],
"description": "Record containing information about the ad account used for the spend"
},
{
"name": "ad_hierarchy",
"type": "RECORD",
"fields": [
{
"name": "level_1",
"type": "RECORD",
"fields": [
{
"name": "network_label",
"type": "STRING",
"description": "Network-specific label for the first level i.e. 'campaign' for Google and Meta and 'campaign group' for LinkedIn."
},
{
"name": "name",
"type": "STRING",
"description": "Name of the first level i.e. the name of the Google campaign."
},
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the first level within the network."
}
],
"description": "Top level for ads on the specific network"
},
{
"name": "level_2",
"type": "RECORD",
"fields": [
{
"name": "network_label",
"type": "STRING",
"description": "Network-specific label for the second level i.e. 'ad group' for Google, 'ad set' for Meta and 'campaign' for LinkedIn."
},
{
"name": "name",
"type": "STRING",
"description": "Name of the second level i.e. the name of the Google ad group."
},
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the second level within the network."
}
],
"description": "Second level for ads on the specific network"
}
],
"description": "Record containing information about the ad hierarchy, with specific network labels and IDs: The IDs are used to match activities and ad, through things like UTM parameters of the activities"
},
{
"name": "context",
"type": "RECORD",
"fields": [
{
"name": "campaign",
"type": "STRING",
"description": "Name of the campaign"
},
{
"name": "campaign_id",
"type": "STRING",
"description": "Unique identifier for the campaign within the network"
},
{
"name": "keyword",
"type": "STRING",
"description": "Keyword used in the ad if applicable"
},
{
"name": "keyword_id",
"type": "STRING",
"description": "Unique identifier for the keyword within the network."
},
{
"name": "match_type",
"type": "STRING",
"description": "Matching type used for the keyword if applicable"
}
],
"description": "Record containing campaign and keyword parameters of the spend"
},
{
"name": "source_system",
"type": "RECORD",
"fields": [
{
"name": "type",
"type": "STRING",
"description": "Type of data source like 'advertisement'."
},
{
"name": "source",
"type": "STRING",
"description": "Name of the data source."
}
],
"description": "Record containing information about the source of the spend data"
}
]
Field
Type
Description
timestamp
TIMESTAMP
Date and time the cost was incurred
cost
FLOAT
Amount recorded in the currency set in the Dreamdata platform.
impressions
FLOAT
Number of ad impressions.
clicks
FLOAT
Number of ad clicks.
adNetwork
STRING
Name of the advertising network used like 'Google', 'LinkedIn' etc
channel
STRING
Channel referencing the session channel in events table. This can be used to match spend to activity.
source
STRING
Source referencing the session source in events table. This can be used to match spend to activity.
▼ ad_account
RECORD
Record containing information about the ad account used for the spend
└─ id
STRING
Unique identifier for the ad account within the network
└─ name
STRING
Name of the ad account
▼ ad_hierarchy
RECORD
Record containing information about the ad hierarchy, with specific network labels and IDs: The IDs are used to match activities and ad, through things like UTM parameters of the activities
└─ ▼ level_1
RECORD
Top level for ads on the specific network
└─ network_label
STRING
Network-specific label for the first level i.e. 'campaign' for Google and Meta and 'campaign group' for LinkedIn.
└─ name
STRING
Name of the first level i.e. the name of the Google campaign.
└─ id
STRING
Unique identifier for the first level within the network.
└─ ▼ level_2
RECORD
Second level for ads on the specific network
└─ network_label
STRING
Network-specific label for the second level i.e. 'ad group' for Google, 'ad set' for Meta and 'campaign' for LinkedIn.
└─ name
STRING
Name of the second level i.e. the name of the Google ad group.
└─ id
STRING
Unique identifier for the second level within the network.
▼ context
RECORD
Record containing campaign and keyword parameters of the spend
└─ campaign
STRING
Name of the campaign
└─ campaign_id
STRING
Unique identifier for the campaign within the network
└─ keyword
STRING
Keyword used in the ad if applicable
└─ keyword_id
STRING
Unique identifier for the keyword within the network.
└─ match_type
STRING
Matching type used for the keyword if applicable
▼ source_system
RECORD
Record containing information about the source of the spend data
└─ type
STRING
Type of data source like 'advertisement'.
└─ source
STRING
Name of the data source.
stages
Description: Stages are your main funnel KPIs. A stage is identified by a timestamp and the contact or company reaching the stage.
Click to expand JSON Schema
[
{
"name": "dd_stage_id",
"type": "STRING",
"description": "Unique identifier for each stage entry within the Dreamdata data model. (Primary key)"
},
{
"name": "stage_name",
"type": "STRING",
"description": "Name of the stage"
},
{
"name": "dd_object_id",
"type": "STRING",
"description": " Identifier for the object the stage is built on. This could be the id of the deal or opportunity the stage is built on. The dd_object_id is unique within each stage, but is not globally unique. Relationship with dd_stage_id: dd_stage_id is the combination of stage_name and dd_object_id"
},
{
"name": "dd_company_id",
"type": "STRING",
"description": "Unique identifier for the company associated with the stage (foreign key referencing dd_company_id in the company schema). Can be null if no company is associated with the stage."
},
{
"name": "timestamp",
"type": "TIMESTAMP",
"description": "Timestamp when the stage was reached - not nullable"
},
{
"name": "value",
"type": "FLOAT",
"description": "Value indicating the revenue or similar value associated with reaching the stage."
},
{
"name": "primary_owner",
"type": "RECORD",
"fields": [
{
"name": "id",
"type": "STRING",
"description": "Unique identifier for the owner"
},
{
"name": "name",
"type": "STRING",
"description": "Owner's name"
},
{
"name": "email",
"type": "STRING",
"description": "Owner's email address."
}
],
"description": "Record containing details about the owner in the source integration"
},
{
"name": "journey",
"type": "RECORD",
"fields": [
{
"name": "type",
"type": "STRING",
"description": "Type of the journey. This determines which events are part of the stage journey. The type is set using the Dreamdata Data Hub UI. 'contact level': all activity associated with the contact (see dd_primary_contact_id) of the stage. 'company level': all activity associated with the company (see dd_company_id) of the stage. 'opportunity level': contacts on the opportunity (see object_contacts)"
},
{
"name": "start_date",
"type": "TIMESTAMP",
"description": "Start date of the stage journey. If NULL the journey includes all tracked data e.g. start date is the first tracked data."
},
{
"name": "end_date",
"type": "TIMESTAMP",
"description": "End date of the stage journey. If this is NULL the stage timestamp is the end date of the stage journey."
}
],
"description": "Record containing details about the journey settings for the stage. The settings are controled from the Dreamdata Data Hub UI."
},
{
"name": "dd_primary_contact_id",
"type": "STRING",
"description": "Unique identifier for a single contact associated with the stage. This is only available for stages built on objects that have a single email associated with it like contacts or leads. (foreign key referencing dd_contact_id in the contacts schema)"
},
{
"name": "object_contacts",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "dd_contact_id",
"type": "STRING",
"description": "Unique identifier for the contact associated with the stage (foreign key referencing dd_contact_id in the contacts schema)"
}
],
"description": "Array containing details about all contacts on the object the stage is built on (can be empty). This is only available for stages built on objects that can have multiple email associated with it, like salesforce opportunities"
},
{
"name": "custom_properties",
"type": "JSON",
"description": "Custom stage properties in JSON format. These are selected properties from the object the stage is built on. If the stage is built on opportunities, it will contain properties selected from the opportunity object and if it is built on contacts it will contain properties selected from the contacts object. Which properties are included for each object is controlled through the Dreamdata source UI for the source of the object."
},
{
"name": "source_system",
"type": "RECORD",
"fields": [
{
"name": "id",
"type": "STRING",
"description": "Id for the object record in the source system."
},
{
"name": "source",
"type": "STRING",
"description": "Name of the source system like hubspot, salesforce, etc."
},
{
"name": "object",
"type": "STRING",
"description": "The name of the object in the source system like contacts, leads, deals, opportunities etc."
}
],
"description": "Array containing information about the source systems for the object the stage is built on"
},
{
"name": "stage_transitions",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "dd_stage_id",
"type": "STRING",
"description": "Unique identifier for the stage transitioned to"
},
{
"name": "dd_object_id",
"type": "STRING",
"description": "Identifier for the object for the stage transitioned to"
},
{
"name": "name",
"type": "STRING",
"description": "Name of the stage transitioned to"
},
{
"name": "timestamp",
"type": "TIMESTAMP",
"description": "Time of the stage transitioned to"
}
],
"description": "Array containing transitions between this stage and other stages. All stages in the array are reached after the this stage, by the same company or contact (depending on the type of stage). This is used for conversion rate calculations as all connected stages are pre-calculated depending on their specific types and can be accessed simply using the stage_transitions array"
}
]
Field
Type
Description
dd_stage_id
STRING
Unique identifier for each stage entry within the Dreamdata data model. (Primary key)
stage_name
STRING
Name of the stage
dd_object_id
STRING
Identifier for the object the stage is built on. This could be the id of the deal or opportunity the stage is built on. The dd_object_id is unique within each stage, but is not globally unique. Relationship with dd_stage_id: dd_stage_id is the combination of stage_name and dd_object_id
dd_company_id
STRING
Unique identifier for the company associated with the stage (foreign key referencing dd_company_id in the company schema). Can be null if no company is associated with the stage.
timestamp
TIMESTAMP
Timestamp when the stage was reached - not nullable
value
FLOAT
Value indicating the revenue or similar value associated with reaching the stage.
▼ primary_owner
RECORD
Record containing details about the owner in the source integration
└─ id
STRING
Unique identifier for the owner
└─ name
STRING
Owner's name
└─ email
STRING
Owner's email address.
▼ journey
RECORD
Record containing details about the journey settings for the stage. The settings are controled from the Dreamdata Data Hub UI.
└─ type
STRING
Type of the journey. This determines which events are part of the stage journey. The type is set using the Dreamdata Data Hub UI. 'contact level': all activity associated with the contact (see dd_primary_contact_id) of the stage. 'company level': all activity associated with the company (see dd_company_id) of the stage. 'opportunity level': contacts on the opportunity (see object_contacts)
└─ start_date
TIMESTAMP
Start date of the stage journey. If NULL the journey includes all tracked data e.g. start date is the first tracked data.
└─ end_date
TIMESTAMP
End date of the stage journey. If this is NULL the stage timestamp is the end date of the stage journey.
dd_primary_contact_id
STRING
Unique identifier for a single contact associated with the stage. This is only available for stages built on objects that have a single email associated with it like contacts or leads. (foreign key referencing dd_contact_id in the contacts schema)
▼ object_contacts
RECORD (REPEATED)
Array containing details about all contacts on the object the stage is built on (can be empty). This is only available for stages built on objects that can have multiple email associated with it, like salesforce opportunities
└─ dd_contact_id
STRING
Unique identifier for the contact associated with the stage (foreign key referencing dd_contact_id in the contacts schema)
custom_properties
JSON
Custom stage properties in JSON format. These are selected properties from the object the stage is built on. If the stage is built on opportunities, it will contain properties selected from the opportunity object and if it is built on contacts it will contain properties selected from the contacts object. Which properties are included for each object is controlled through the Dreamdata source UI for the source of the object.
▼ source_system
RECORD
Array containing information about the source systems for the object the stage is built on
└─ id
STRING
Id for the object record in the source system.
└─ source
STRING
Name of the source system like hubspot, salesforce, etc.
└─ object
STRING
The name of the object in the source system like contacts, leads, deals, opportunities etc.
▼ stage_transitions
RECORD (REPEATED)
Array containing transitions between this stage and other stages. All stages in the array are reached after the this stage, by the same company or contact (depending on the type of stage). This is used for conversion rate calculations as all connected stages are pre-calculated depending on their specific types and can be accessed simply using the stage_transitions array
└─ dd_stage_id
STRING
Unique identifier for the stage transitioned to
└─ dd_object_id
STRING
Identifier for the object for the stage transitioned to
└─ name
STRING
Name of the stage transitioned to
└─ timestamp
TIMESTAMP
Time of the stage transitioned to