Data Warehouse Schema data-warehouse-schema

Data Warehouse allows you to track as much as you want, report on your attribution data wherever you want, and plug it in to other data sets.

Entity Relationship Diagrams entity-relationship-diagrams

The Data Warehouse Data Model ERD shows how data in the data warehouse is intended to flow and be linked together. This diagram does not include all tables available in the data warehouse because some of them represent mapping tables, views of other tables already present, or deprecated tables we don’t recommend using any more. See the detailed descriptions of tables and columns present in the data warehouse below. Many of these tables contain denormalized fields, however, this diagram is the recommended data model, leveraging data from dimensional tables instead.

The additional Ads Dimensional Data Model ERD presents a view of how tables for ads specific dimensions can be best linked back to the tables in the main data model. Though ads dimensions are also denormalized in other tables, this represents the recommended model for joining these dimensions.

Click an image for its full-size version

Data Warehouse Data Model

Ads Dimensional Data Model

Views views

BIZ_ACCOUNTS biz-accounts

Accounts imported from the source system.

∗ Only available in Marketo Measure Ultimate

BIZ_ACCOUNT_TO_EMAILS biz-account-to-emails

Mapping table between known Lead/Contact email addresses and Accounts. This table will be empty if ABM is disabled.

BIZ_ACTIVITIES biz-activities

Activities imported from a source system or connected Ad Account.

BIZ_ADS biz-ads

Ads imported from any connected Ad Account.

BIZ_ADVERTISERS biz-advertisers

Advertisers imported from any connected Ad Account.

BIZ_AD_ACCOUNTS biz-ad-accounts

Ad Accounts imported from any connected Ad Account.

BIZ_AD_CAMPAIGNS biz-ad-campaigns

Campaigns imported from connected Ad Accounts, source systems, utm, and self reported.

BIZ_AD_FORMS biz-ad-forms

Ad Forms imported from any connected Ad Account.

BIZ_AD_GROUPS biz-ad-groups

Ad Groups imported from any connected Ad Account.

BIZ_AD_PROVIDERS

Ad Providers from any connected Ad Account, including an entry for self reported if applicable.

BIZ_ATTRIBUTION_TOUCHPOINTS biz-attribution-touchpoints

Buyer Attribution Touchpoints, all touchpoints associated with an Opportunity.

BIZ_ATTRIBUTION_AI_TOUCHPOINTS biz-attribution-ai-touchpoints

Data generated from the Attribution AI integration. These fields are only populated for Marketo Measure Ultimate customers.

BIZ_CAMPAIGN_MEMBERS biz-campaign-members

Campaign Members imported from the source system. This table will be empty if Campaign Sync is disabled.

BIZ_CHANNELS biz-channels

Marketing Channels, as created in the Marketo Measure application.

BIZ_CONTACTS biz-contacts

Contacts imported from the source system.

∗ Only available in Marketo Measure Ultimate

BIZ_CONVERSION_RATES biz-conversion-rates

Currency conversion rates imported from the source system.

BIZ_COSTS biz-costs

Cost data imported from connected Ad Accounts or self reported marketing spend.

BIZ_CREATIVES biz-creatives

Creatives imported from any connected Ad Account.

BIZ_CRM_EVENTS biz-crm-events

Events imported from the source system. This table will be empty if Activities Sync is disabled.

BIZ_CRM_TASKS biz-crm-tasks

Tasks imported from the source system. This table will populate if Activities Sync OR Call Tracking are enabled.

BIZ_CURRENCIES biz-currencies

Table of all ISO currencies.

BIZ_CUSTOMER_AB_TESTS biz-customer-ab-tests

AB Tests recorded. This table will be empty if AB Tests are not enabled.

BIZ_CUSTOMER_EVENTS biz-customer-events

Web events that have been recorded using custom events in the Javascript. This table will be empty if Marketo Measure Events are not enabled.

BIZ_CUSTOM_LANDING_PAGES biz-custom-landing-pages

Landing Pages downloaded from any connected Ad Account.

BIZ_EMAIL_TO_VISITOR_IDS biz-email-to-visitor-ids

Mapping table for email addresses and visitor ids.

BIZ_FACTS biz-facts

Unions together Impressions, Page Views, Visits, Form Submits, User Touchpoints, Touchpoint (BT), Attribution Touchpoints (BAT), and Cost data. Used internally to support Marketo Measure reporting.

BIZ_FORM_SUBMITS biz-forms-submits

Captured Form Submissions.

BIZ_IMPRESSIONS biz-impressions

Impressions fired and recorded. This table requires a DoubleClick connection and Enable View Through set to True.

BIZ_KEYWORDS biz-keywords

Keywords imported from any connected Ad Account.

BIZ_LANDING_PAGES biz-landing-pages

Landing Pages imported from any connected Ad Account.

BIZ_LEADS biz-leads

Leads imported from the source system.

BIZ_LEAD_STAGE_TRANSITIONS biz-lead-stage-transitions

Stage transitions for Leads or Contacts.

BIZ_OPPORTUNITIES biz-opportunities

Opportunities imported from the source system.

∗ Only available in Marketo Measure Ultimate

BIZ_OPP_STAGE_TRANSITIONS biz-opp-stage-transitions

Stage transitions for Opportunities.

BIZ_PAGE_VIEWS biz-page-views

Page Views collected from web visits. Multiple page views can compose a single Session.

BIZ_PLACEMENTS biz-placements

Table that stores all placements downloaded from any connected ads accounts, an object from the Doubleclick integration.

BIZ_SEGMENTS biz-segments

Segment values as defined in the Marketo Measure application.

BIZ_SEGMENT_NAMES biz-segment-names

Maps the name of the custom segment to it’s category value. (This maps the column names to the Category1 - 15 column headers found in the touchpoint tables.)

BIZ_SESSIONS biz-sessions

Sessions as processed from Page Views. Multiple Page Views can make up one Session, and a single visitor id can be associated to multiple Sessions.

BIZ_SITES biz-sites

Sites imported from any connected Ad Account.

BIZ_SITE_LINKS biz-site-links

Sites Links from any connected Ads Account.

BIZ_STAGE_DEFINITIONS biz-stage-definitions

List of stages as imported or defined in the Marketo Measure application.

BIZ_TOUCHPOINTS biz-touchpoints

Buyer Touchpoints, all touchpoints associated with a Lead or Contact. This table will be empty if Lead Touchpoints or Contact Touchpoints are disabled.

BIZ_URLS biz-urls

Aggregation of URLs from landing pages, referrer pages, and page views.

BIZ_USER_TOUCHPOINTS biz-user-touchpoints

All Touchpoints created from any event tied to an email.

BIZ_WEB_HOST_MAPPINGS biz-web-host-mappings

Mapping table to map Marketo Measure Session Id to Adobe ECID and Munckin Id.

Sample Queries sample-queries

How many Buyer Touchpoints (BTs) were there for each channel/subchannel last month?

--Note: This query can quickly be modified to show Buyer Attribution Touchpoint (BAT) counts by switching the biz_touchpoints table to the biz_attribution_touchpoints table.

select trim(split(ch.name,'.')[0])  as channel
      ,trim(split(ch.name,'.')[1])  as subchannel
      ,count(bt.id)                 as buyer_touchpoint_count
  from biz_user_touchpoints     ut
       left outer join
       biz_touchpoints          bt
        on bt.user_touchpoint_id    = ut.id
       and bt._deleted_date         is null
       left outer join
       biz_channels             ch
        on ut.channel               = ch.id
       and ch._deleted_date         is null
 where ut._deleted_date is null
   and ut.touchpoint_date between add_months(date_trunc(month,current_date),-1) and last_day(dateadd(month,-1,current_date))
group by 1,2

How much Attributed Revenue for each channel was closed in the past month, for the full path attribution model?

--Note: This query does not perform any currency conversion.  If your data contains multiple currencies, you will need to add in logic to perform the conversion to the desired currency using the biz_conversion_rates table.

select trim(split(ch.name,'.')[0])  as channel
      ,sum(opp.amount*(bat.full_path_percentage/100))   as attributed_revenue
  from biz_user_touchpoints         ut
       inner join
       biz_attribution_touchpoints  bat
        on bat.user_touchpoint_id   = ut.id
       and bat._deleted_date        is null
       inner join
       biz_opportunities            opp
        on bat.opportunity_id       = opp.id
       and opp._deleted_date        is null
       and opp.is_closed            = true
       and opp.is_won               = true
       and opp.close_date between add_months(date_trunc(month,current_date),-1) and last_day(dateadd(month,-1,current_date))
       left outer join
       biz_channels                 ch
        on ut.channel               = ch.id
       and ch._deleted_date         is null
 where ut._deleted_date is null
group by 1

What is the entire journey for one person? (Show all Touchpoints for a single email address.)

select ut.touchpoint_date
      ,ut.marketing_touch_type
      ,listagg(distinct ifnull(sdl.stage_name,sdo.stage_name),',')           as touchpoint_position
  from biz_user_touchpoints         ut
       left outer join
       biz_touchpoints              bt
        on bt.user_touchpoint_id    = ut.id
       and bt._deleted_date         is null
       left outer join
       biz_attribution_touchpoints  bat
        on bat.user_touchpoint_id   = ut.id
       and bat._deleted_date        is null
       left outer join
       biz_lead_stage_transitions   lst
        on lst.touchpoint_id        = bt.id
       and lst._deleted_date        is null
       and lst.is_pending           = false
       and lst.is_non_transitional  = false
       left outer join
       biz_stage_definitions        sdl
        on lst.stage_id             = sdl.id
       and sdl._deleted_date        is null
       left outer join
       biz_opp_stage_transitions    ost
        on ost.touchpoint_id        = bat.id
       and ost._deleted_date        is null
       and ost.is_pending           = false
       and ost.is_non_transitional  = false
       left outer join
       biz_stage_definitions        sdo
        on ost.stage_id             = sdo.id
       and sdo._deleted_date        is null
 where ut._deleted_date     is null
   and ut.email             = [email address]
group by 1,2
order by 1

Show all Buyer Attribution Touchpoints (BATs) and their Attributed Revenue for a single opportunity.

select bat.id
      ,bat.touchpoint_date
      ,bat.email
      ,opp.amount*(bat.w_shape_percentage/100)             as attributed_revenue
      ,listagg(osd.stage_name,', ')                        as touchpoint_position
  from biz_opportunities               opp
       inner join
       biz_attribution_touchpoints     bat
        on bat.opportunity_id      = opp.id
       and bat._deleted_date       is null
       left outer join
       biz_opp_stage_transitions       ost
        on ost.touchpoint_id       = bat.id
       and ost._deleted_date       is null
       and ost.is_pending          = false
       and ost.is_non_transitional = false
       left outer join
       biz_stage_definitions            osd
        on ost.stage_id             = osd.id
       and osd._deleted_date        is null
 where opp._deleted_date    is null
   and opp.id               = [opportunity id]
group by 1,2,3,4
order by touchpoint_date

Back to top