`analytics.conf_namespace`

NAMESPACE

Configuration settings related to Namespaces.

Layer: Core Dataset
Category: Configuration
Status: Published

Primary Key: env, env_namespace
Other Unique Key(s): env_namespace
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = conf_namespace.env_namespace;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`geo_region`	`STRING`	Geographical region of the Namespace (as defined by CitrusAd).
`local_timezone`	`STRING`	Not in use.

`analytics.conf_retailer`

NAMESPACE
RETAILER

Configuration settings related to Retailers.

Layer: Core Dataset
Category: Configuration
Status: Published

Primary Key: env_namespace, retailer_id
Other Unique Key(s): None
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = conf_retailer.env_namespace;
dim_retailer on dim_retailer.env_namespace = conf_retailer.env_namespace and dim_retailer.retailer_id = conf_retailer.retailer_id and dim_retailer.is_current;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`global_suppress_flag`	`STRING`	Flag to indicate Retailer is to be excluded from reporting (eg test data).
`ads_requested_cap`	`NUMERIC`	Max number of Product Ads that will be considered for reporting purposes per Ad Request (set per Retailer team, no setting means no cap will be imposed).
`reserved_search_terms`	`STRING`	Not in use.
`period_start_date`	`STRING`	Not in use.
`banner_ads_requested_cap`	`NUMERIC`	Max number of Banner Ads that will be considered for reporting purposes per Ad Request (set per Retailer team, no setting means no cap will be imposed).
`view_attribution_window`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for View Thru Attribution.
`click_attribution_window`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for Halo Click Attribution (1st model).
`retailer_taxonomy_halo`	`STRING`	Comma separated list of what halo levels will be computed for model 1.
`enhanced_attribution_flag`	`STRING`	If true, the first enhanced attribution model will be calculated.
`enhanced_attribution_flag_2`	`STRING`	If true, the second enhanced attribution model will be calculated.
`view_attribution_window_2`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for View Thru Attribution (2nd model).
`click_attribution_window_2`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for Halo Click Attribution (2nd model).
`retailer_taxonomy_halo_2`	`STRING`	Comma separated list of what halo levels will be computed for model 2.
`attribution_time_frame`	`NUMERIC`	Determines how many days of attribution results should be stored for the first model.
`attribution_time_frame_2`	`NUMERIC`	Determines how many days of attribution results should be stored for the second model.
`retailer_group_1`	`STRING`	Retailer team group membership #1.
`retailer_group_2`	`STRING`	Retailer team group membership #2.

`analytics.conf_supplier`

NAMESPACE
SUPPLIER

Configuration settings related to Suppliers.

Layer: Core Dataset
Category: Configuration
Status: Published

Primary Key: env_namespace, supplier_id
Other Unique Key(s): None
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = conf_supplier.env_namespace;
dim_supplier on dim_supplier.env_namespace = conf_supplier.env_namespace and dim_supplier.supplier_id = conf_supplier.supplier_id and dim_supplier.is_current;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`supplier_group_1`	`STRING`	Supplier team group membership #1.
`supplier_group_2`	`STRING`	Supplier team group membership #2.
`supplier_group_3`	`STRING`	Supplier team group membership #3.
`supplier_group_4`	`STRING`	Supplier team group membership #4.

`analytics.currency_aud`

Current conversion rates between the base currency (AUD) to other common currencies. ISO 4217 currency codes used.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: currency
Other Unique Key(s): None
Partition: None

Foreign Key(s):
None

Column Name	Data Type	Description
`currency`	`STRING`	Currency being converted (ISO 4217 currency code).
`value`	`FLOAT`	Conversion rate to apply.
`base`	`STRING`	Currency being converted into (AUD).

`analytics.currency_usd`

Current conversion rates between the base currency (USD) to other common currencies. ISO 4217 currency codes used.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: currency
Other Unique Key(s): None
Partition: None

Foreign Key(s):
None

Column Name	Data Type	Description
`currency`	`STRING`	Currency being converted (ISO 4217 currency code).
`value`	`FLOAT`	Conversion rate to apply.
`base`	`STRING`	Currency being converted into (USD).

`analytics.dim_campaign`

NAMESPACE

Campaign master data dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: campaign_id, row_version
Other Unique Key(s): campaign_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_campaign.env_namespace;
dim_placement on dim_placement.env_namespace = dim_campaign.env_namespace and dim_placement.placement_id = dim_campaign.placement_id;
dim_team on dim_team.env_namespace = dim_campaign.env_namespace and dim_team.team_id = dim_campaign.team_id and dim_team.is_current;
dim_wallet on dim_wallet.env_namespace = dim_campaign.env_namespace and dim_wallet.wallet_id = dim_campaign.wallet_id;
dim_catalog on dim_catalog.env_namespace = dim_campaign.env_namespace and dim_catalog.catalog_id = dim_campaign.first_catalog_id and dim_catalog.is_current;

Column Name	Data Type	Description
`campaign_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).
`first_catalog_id`	`STRING`	First Catalog id.
`campaign_name`	`STRING`	Name of the Campaign.
`campaign_type`	`STRING`	Type of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
`campaign_subtype`	`STRING`	Legacy, superseded by placement. Subtype of the Campaign (SEARCH_ONLY, CATEGORY_ONLY, CATEGORY_AND_SEARCH, BROAD_DISPLAY, CROSS_SELL, UPSELL, RUN_OF_SITE).
`campaign_start_date`	`TIMESTAMP`	Date the Campaign will begin.
`campaign_start_date_utc`	`TIMESTAMP`	Currently the same as campaign_start_date since all data is in UTC.
`campaign_end_date`	`TIMESTAMP`	Date when the Campaign will end.
`campaign_end_date_utc`	`TIMESTAMP`	Currently the same as campaign_end_date since all data is in UTC.
`valid_state`	`STRING`	The Campaign's valid state. (APPROVED, PENDING, REJECTED)
`active_state`	`STRING`	The Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
`is_fta`	`STRING`	Flag indicating if the Campaign is Fixed Tenancy Agreement (FTA).
`catalog_ids`	`STRING`	Catalog ids.
`search_terms`	`STRING`	Comma separated list of SearchTerms targeted by the Campaign.
`has_search_terms`	`BOOLEAN`	Flag to indicate if the Campaign is targeting SearchTerms.
`catalog_products`	`STRING`	The Catalog's Products within the Campaign.
`placements`	`STRING`	The Campaign's selected Placement.
`currency_code`	`STRING`	ISO 4217 currency code
`fta_cost`	`NUMERIC`	Total amount being spent by the given Fixed Tenancy Agreement (FTA) Campaign.
`max_cost_per_click`	`NUMERIC`	The maximum cost per click set for the Campaign.
`max_total_spend`	`NUMERIC`	The maximum total spend set for the Campaign.
`max_daily_spend`	`NUMERIC`	The maximum daily spend set for the Campaign.
`impression_goal`	`NUMERIC`	The maximum number of impressions a Campaign is aiming for. Only relevant for campaigns with a spend type of Fixed Spend.
`total_spend`	`NUMERIC`	Total AdSpend to date for the Campaign.
`retailer_ad_spend_type`	`STRING`	Type of spending if Retailer Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
`supplier_ad_spend_type`	`STRING`	Type of spending if Supplier Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
`budget`	`NUMERIC`	The Campaign's budget as set by the Campaign's manager.
`targeted_filters`	`STRING`	The filters selected by an advertiser that their campaign will appear for. Used for fixed tenancy campaigns.
`location_filters`	`STRING`	Filters on the campaign specific to a location if a location filter class is created.
`filter_whitelists`	`STRING`	The filters selected by an advertiser that the campaign can appear for. The campaign will only appear where eligible regardless of the advertiser's selection.
`cross_sell_settings`	`STRING`	The cross-sell targeting selection in the campaign.
`upsell_settings`	`STRING`	The Upsell targeting selection in the campaign if applicable.
`categories`	`STRING`	Comma separated list of Categories targeted by the Campaign.
`catalog_count`	`INTEGER`	Number of Catalogs being targeted by the Campaign.
`last_spend_date`	`DATE`	Most recent date that AdSpend was incurred by the Supplier.
`product_type_code`	`STRING`	Product type code.
`namespace`	`STRING`	Name of the Namespace.
`user_id`	`STRING`	Internal identifier of the User (from the source system).
`user_email`	`STRING`	User's email address.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`created_at`	`TIMESTAMP`	Date the record was created in the source system.
`created_at_utc`	`TIMESTAMP`	Currently the same as created_at since all data is in UTC.
`updated_at`	`TIMESTAMP`	Date the record was updated in the source system.
`updated_at_utc`	`TIMESTAMP`	Currently the same as updated_at since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`cta_text`	`STRING`	CTA text.
`suggested_search_terms`	`STRING`	Suggested search terms.
`has_suggested_search_terms`	`BOOLEAN`	Flag to indicate that any suggested search terms exist.

`analytics.dim_campaign_attr`

NAMESPACE

Campaign custom attributes.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: campaign_id, row_version
Other Unique Key(s): campaign_attr_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_campaign_attr.env_namespace;
dim_campaign on dim_campaign.env_namespace = dim_campaign_attr.env_namespace and dim_campaign.campaign_id = dim_campaign_attr.campaign_id and dim_campaign.row_version = dim_campaign_attr.row_version;

Column Name	Data Type	Description
`campaign_attr_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`attr1`	`STRING`	Campaign user defined attribute value 1.
`attr2`	`STRING`	Campaign user defined attribute value 2.
`attr3`	`STRING`	Campaign user defined attribute value 3.
`attr4`	`STRING`	Campaign user defined attribute value 4.
`attr5`	`STRING`	Campaign user defined attribute value 5.
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.dim_catalog`

NAMESPACE

Catalog master data dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: catalog_id, row_version
Other Unique Key(s): catalog_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_catalog.env_namespace;
dim_retailer on dim_retailer.env_namespace = dim_catalog.env_namespace and dim_retailer.retailer_id = dim_catalog.retailer_id and dim_retailer.is_current;
currency_aud on currency_aud.currency = dim_catalog.currency_code;
currency_usd on currency_usd.currency = dim_catalog.currency_code;

Column Name	Data Type	Description
`catalog_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`catalog_name`	`STRING`	Name of the Catalog.
`currency_code`	`STRING`	ISO 4217 currency code
`retailer_name`	`STRING`	Name of the Retailer.
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`is_visible`	`BOOLEAN`	Flag to indicate if the catalog is visible in the source system.
`grace_window_days`	`NUMERIC`	Configured grace window lookback period value in days or part thereof.

`analytics.dim_category`

NAMESPACE

Category dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: env_namespace, retailer_id, category_id
Other Unique Key(s): category_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_category.env_namespace;
dim_retailer on dim_retailer.env_namespace = dim_category.env_namespace and dim_retailer.retailer_id = dim_category.retailer_id and dim_retailer.is_current;

Column Name	Data Type	Description
`category_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`category_id`	`STRING`	Category being targeted.
`namespace`	`STRING`	Name of the Namespace.
`category_label`	`STRING`	Label for the Category (more descriptive than the typical id provided).
`cat_level_1`	`STRING`	Level 1 component of the category_label (delimited by >).
`cat_level_2`	`STRING`	Level 2 component of the category_label (delimited by >).
`cat_level_3`	`STRING`	Level 3 component of the category_label (delimited by >).
`cat_level_4`	`STRING`	Level 4 component of the category_label (delimited by >).
`cat_level_5`	`STRING`	Level 5 component of the category_label (delimited by >).
`cat_level_6`	`STRING`	Level 6 component of the category_label (delimited by >).
`cat_level_7`	`STRING`	Level 7 component of the category_label (delimited by >).
`category_label_count`	`INTEGER`	Count(category_label) across env_namespace, retailer_id, category_id.
`source_name`	`STRING`	Source name.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.dim_env_namespace`

NAMESPACE

Namespace dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: env, env_namespace
Other Unique Key(s): env_namespace_key
Partition: None

Foreign Key(s):
None

Column Name	Data Type	Description
`env_namespace_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`geo_region`	`STRING`	Geographical region of the Namespace (as defined by CitrusAd).
`local_timezone`	`STRING`	Not in use.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`zone_id`	`STRING`	Timezone (TZ) identifier standardised by IANA time zone database.
`local_hour`	`NUMERIC`	Local hour.
`enable_okta_login`	`BOOLEAN`	Flag to indicate if okta login is enabled.
`enable_credit_card`	`BOOLEAN`	Flag to indicate if credit card transactions are enabled.
`enabled_spend_types`	`STRING`	Spend types enabled in the system.
`banner_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on banner campaigns.
`banner_enable_tracking_tags`	`BOOLEAN`	Flag to indicate if tracking tags are enabled on banner campaigns.
`banner_enable_filter_targeting`	`BOOLEAN`	Flag to indicate if filter targeting is enabled on banner campaigns.
`banner_enable_filter_whitelisting`	`BOOLEAN`	Flag to indicate if filter whitelisting is enabled on banner campaigns.
`display_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on display campaigns.
`display_enable_tracking_tags`	`BOOLEAN`	Flag to indicate if tracking tags are enabled on display campaigns.
`display_enable_filter_targeting`	`BOOLEAN`	Flag to indicate if filter targeting is enabled on display campaigns.
`display_enable_filter_whitelisting`	`BOOLEAN`	Flag to indicate if filter whitelisting is enabled on display campaigns.
`wildcard_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on wildcard campaigns.
`wildcard_enable_tracking_tags`	`BOOLEAN`	Flag to indicate if tracking tags are enabled on wildcard campaigns.
`wildcard_enable_filter_targeting`	`BOOLEAN`	Flag to indicate if filter targeting is enabled on wildcard campaigns.
`wildcard_enable_filter_whitelisting`	`BOOLEAN`	Flag to indicate if filter whitelisting is enabled on wildcard campaigns.
`brand_page_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on brand page campaigns.
`brand_page_enable_tracking_tags`	`BOOLEAN`	Flag to indicate if tracking tags are enabled on brand page campaigns.
`brand_page_enable_filter_targeting`	`BOOLEAN`	Flag to indicate if filter targeting is enabled on brand page campaigns.
`brand_page_enable_filter_whitelisting`	`BOOLEAN`	Flag to indicate if filter whitelisting is enabled on brand page campaigns.
`shotgun_banner_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on shotgun banner campaigns.
`shotgun_banner_enable_tracking_tags`	`BOOLEAN`	Flag to indicate if tracking tags are enabled on shotgun banner campaigns.
`shotgun_banner_enable_filter_targeting`	`BOOLEAN`	Flag to indicate if filter targeting is enabled on shotgun banner campaigns.
`shotgun_banner_enable_filter_whitelisting`	`BOOLEAN`	Flag to indicate if filter whitelisting is enabled on shotgun banner campaigns.
`disabled_demographics`	`STRING`	Disabled Demographics.
`disabled_economy_level`	`BOOLEAN`	Flag to indicate if economy level is disabled.
`enable_category_min_bid`	`BOOLEAN`	Flag to indicate if category minimum bid is enabled.
`include_retailer_t_and_c`	`BOOLEAN`	Flag to indicate if Retailer Terms and Conditions is included.
`disabled_strategy_types`	`STRING`	Disabled Strategy Types.
`enable_advanced_reports`	`BOOLEAN`	Flag to indicate if advanced reports are enabled.
`enable_reporting_upc_fix`	`BOOLEAN`	Flag to indicate if reporting UPC fix is enabled.
`can_group_products_by_code`	`BOOLEAN`	Flag to indicate if products can be grouped by code.
`default_reporting_period`	`STRING`	Default reporting period.
`enable_offsite_campaigns`	`BOOLEAN`	Flag to indicate if offsite Campaigns are enabled.
`enable_search_term_min_bid`	`BOOLEAN`	Flag to indicate if search term minimum bid is enabled.
`enable_campaign_templates`	`BOOLEAN`	Flag to indicate if Campaign templates is enabled.
`enable_language_selection`	`BOOLEAN`	Flag to indicate if language selection is enabled.
`enable_product_tag_manager`	`BOOLEAN`	Flag to indicate if product tag manager is enabled.
`enable_daily_spend_rollover`	`BOOLEAN`	Flag to indicate if daily spend rollover is enabled.
`product_metadata_selection`	`BOOLEAN`	Flag to indicate if metadata selection is enabled.
`catalog_selection_reporting`	`BOOLEAN`	Flag to indicate if Catalog selection reporting is enabled.
`enable_campaign_duplication`	`BOOLEAN`	Flag to indicate if campaign duplication is enabled.
`enable_powered_by_citrus_icon`	`BOOLEAN`	Flag to indicate if Powered by Citrus is enabled.
`enable_retailer_team_manager`	`BOOLEAN`	Flag to indicate if Retailer Team Manager is enabled.
`enabled_search_term_measures`	`STRING`	Flag to indicate if search term measures is enabled.
`max_banner_slots_per_campaign`	`INTEGER`	Maximum banner slots per Campaign.
`enable_report_table_filtering`	`BOOLEAN`	Flag to indicate if report table filtering enabled.
`enable_supplier_edit_team_info`	`BOOLEAN`	Flag to indicate if the platform enable Suppliers to edit team info.
`disabled_search_term_match_type`	`BOOLEAN`	Flag to indicate if search term match type is disabled.
`enable_search_term_collections`	`BOOLEAN`	Flag to indicate if search term collections is enabled.
`enable_supplier_wallet_editing`	`BOOLEAN`	Flag to indicate if the platform enables Supplier to edit the wallets.
`enable_external_user_management`	`BOOLEAN`	Flag to indicate if external user management is enabled.
`enable_retailer_user_management`	`BOOLEAN`	Flag to indicate if Retailer user management is enabled.
`enable_retailer_wallet_creation`	`BOOLEAN`	Flag to indicate if the platform enables Retailers to create wallets.
`enable_supplier_wallet_creation`	`BOOLEAN`	Flag to indicate if the platform enables Supplier to create wallets.
`enable_retailer_impression_share`	`BOOLEAN`	Flag to indicate if Retailer impression share is enabled.
`enable_single_campaign_reporting`	`BOOLEAN`	Flag to indicate if single Campaign reporting is enabled.
`enable_supplier_impression_share`	`BOOLEAN`	Flag to indicate if Supplier impression share is enabled.
`enable_external_assets_management`	`BOOLEAN`	Flag to indicate if external assets management is enabled.
`enable_retailer_wallet_management`	`BOOLEAN`	Flag to indicate if Retailer wallet management is enabled.
`enable_supplier_wallet_daily_budget`	`BOOLEAN`	Flag to indicate if Supplier wallet daily budget is enabled.
`enable_campaign_media_spend_document`	`BOOLEAN`	Flag to indicate if Campaign media spend document is enabled.
`enable_retailer_campaign_management`	`BOOLEAN`	Flag to indicate if Retailer Campaign management is enabled.
`enable_retailer_wallet_external_id_field`	`BOOLEAN`	Flag to indicate if Retailer wallet external ID is enabled.
`enable_supplier_wallet_external_id_field`	`BOOLEAN`	Flag to indicate if Supplier wallet external ID is enabled.
`enable_retailer_restricted_team_creation`	`BOOLEAN`	Flag to indicate if Retailer restricted team creation is enabled.
`enable_report_table_filtering_by_product_tag`	`BOOLEAN`	Flag to indicate if report table filtering by product tag is enabled.
`enable_retailer_wallet_credit_limit_management`	`BOOLEAN`	Flag to indicate if Retailer wallet credit limit management is enabled.
`enable_retailer_to_view_supplier_campaign_reports`	`BOOLEAN`	Flag to indicate if the platform enables Retailer to view the Campaign report of Suppliers.

`analytics.dim_external_order_config`

NAMESPACE

External Order Config dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: external_order_config_id, row_version
Other Unique Key(s): external_order_config_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_wallet.env_namespace;
dim_wallet on dim_wallet.wallet_id = dim_externalOrder_config.wallet_id;

Column Name	Data Type	Description
`external_order_config_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`external_order_config_id`	`STRING`	Internal identifier of the External Order Config (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`wallet_id`	`STRING`	ID of the wallet associated with the external order config.
`external_order_line_item_id`	`STRING`	External Order Line Item Id for that wallet.
`user_id`	`STRING`	Internal identifier of the User (from the source system).
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`created_at`	`TIMESTAMP`	Date the record was created in the source system.
`created_at_utc`	`TIMESTAMP`	Currently the same as created_at since all data is in UTC.
`updated_at`	`TIMESTAMP`	Date the record was updated in the source system.
`updated_at_utc`	`TIMESTAMP`	Currently the same as updated_at since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.dim_placement`

NAMESPACE

Placement dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: env_namespace, placement_id
Other Unique Key(s): placement_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_placement.env_namespace;

Column Name	Data Type	Description
`placement_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).
`ad_gen_id`	`STRING`	Ad gen id.
`archived`	`STRING`	Archived status of the Placement.
`display_name`	`STRING`	Display name of the Placement.
`campaign_target`	`STRING`	Campaign target.
`catalog_ids`	`STRING`	Catalog ids.
`campaign_types`	`STRING`	List of applicable campaign types.
`namespace`	`STRING`	Name of the Namespace.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`enable_search_term_suggestions`	`BOOLEAN`	Enable search term suggestions feature flag.
`allow_custom_search_terms`	`BOOLEAN`	Allow custom search term term feature flag.
`search_term_type`	`STRING`	Custom search term type (if applicable).
`search_term_type_label`	`STRING`	Custom search term type label (if applicable).
`allow_targeted_categories_modified_by_user`	`BOOLEAN`	Feature flag for whether targeted categories are allowed to be modified by user.
`allow_targeted_locations_modified_by_user`	`BOOLEAN`	Feature flag for whether targeted locations are allowed to be modified by user.

`analytics.dim_product`

NAMESPACE

Product dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: normalised_product_code, product_code,catalog_id
Other Unique Key(s): product_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_product.env_namespace;
dim_retailer on dim_retailer.env_namespace = dim_product.env_namespace and dim_retailer.retailer_id = dim_product.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = dim_product.env_namespace and dim_catalog.catalog_id = dim_product.catalog_id and dim_catalog.is_current;
currency_aud on currency_aud.currency = dim_product.currency_code;
currency_usd on currency_usd.currency = dim_product.currency_code;

Column Name	Data Type	Description
`product_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`product_name`	`STRING`	Name of the Product.
`product_type`	`STRING`	Type of the Product.
`product_image_url`	`STRING`	URL to an image of the Product for the Ad.
`retailer_name`	`STRING`	Name of the Retailer.
`currency_code`	`STRING`	ISO 4217 currency code
`product_tags`	`STRING`	Tags associated with the Product.
`brand`	`STRING`	Brand of the Product.
`google_product_taxonomy`	`STRING`	Google taxonomy (product hierarchy) of the Product. Levels of the hierarchy delimited using > symbol.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`seller_id`	`STRING`	Seller that the Product is applicable for.
`seller_name`	`STRING`	Name of the Seller id.
`upc`	`STRING`	UPC of the Product.
`inventory`	`STRING`	How much of the Product is in stock.
`namespace`	`STRING`	Name of the Namespace.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`product_collection`	`STRING`	Product collections represent the parent product of a given product code.
`is_product_collection`	`BOOLEAN`	Flag to indicate if product collection.

`analytics.dim_retailer`

NAMESPACE
RETAILER

Retailer dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: retailer_id, row_version
Other Unique Key(s): retailer_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_retailer.env_namespace;
currency_aud on currency_aud.currency = dim_retailer.currency_code;
currency_usd on currency_usd.currency = dim_retailer.currency_code;

Column Name	Data Type	Description
`retailer_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`retailer_name`	`STRING`	Name of the Retailer.
`team_type`	`STRING`	Type of Team (RETAILER).
`company_name`	`STRING`	Company name of the Retailer.
`business_identifier`	`STRING`	Implementation specific generic label.
`industry`	`STRING`	Industry of the Retailer.
`currency_code`	`STRING`	ISO 4217 currency code
`ads_requested_cap`	`NUMERIC`	Max number of Product Ads that will be considered for reporting purposes per Ad Request (set per Retailer team, no setting means no cap will be imposed).
`banner_ads_requested_cap`	`NUMERIC`	Max number of Banner Ads that will be considered for reporting purposes per Ad Request (set per Retailer team, no setting means no cap will be imposed).
`reserved_search_terms`	`STRING`	Not in use.
`global_suppress_flag`	`STRING`	Flag to indicate Retailer is to be excluded from reporting (eg test data).
`view_attribution_window`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for View Thru Attribution.
`click_attribution_window`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for Halo Click Attribution (1st model).
`retailer_taxonomy_halo`	`STRING`	Comma separated list of what halo levels will be computed for model 1.
`enhanced_attribution_flag`	`STRING`	If true, the first enhanced attribution model will be calculated.
`enhanced_attribution_flag_2`	`STRING`	If true, the second enhanced attribution model will be calculated.
`view_attribution_window_2`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for View Thru Attribution (2nd model).
`click_attribution_window_2`	`NUMERIC`	Max number of days allowed between the Order and Realised Ad for Halo Click Attribution (2nd model).
`retailer_taxonomy_halo_2`	`STRING`	Comma separated list of what halo levels will be computed for model 2.
`attribution_time_frame`	`NUMERIC`	Determines how many days of attribution results should be stored for the first model.
`attribution_time_frame_2`	`NUMERIC`	Determines how many days of attribution results should be stored for the second model.
`retailer_group_1`	`STRING`	Retailer team group membership #1.
`retailer_group_2`	`STRING`	Retailer team group membership #2.
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`created_at`	`TIMESTAMP`	Date the record was created in the source system.
`created_at_utc`	`TIMESTAMP`	Currently the same as created_at since all data is in UTC.
`updated_at`	`TIMESTAMP`	Date the record was updated in the source system.
`updated_at_utc`	`TIMESTAMP`	Currently the same as updated_at since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.dim_search_term`

NAMESPACE

Search Term dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: env_namespace, retailer_id, search_term
Other Unique Key(s): search_term_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_search_term.env_namespace;
dim_retailer on dim_retailer.env_namespace = dim_search_term.env_namespace and dim_retailer.retailer_id = dim_search_term.retailer_id and dim_retailer.is_current;
currency_aud on currency_aud.currency = dim_search_term.currency_code;
currency_usd on currency_usd.currency = dim_search_term.currency_code;

Column Name	Data Type	Description
`search_term_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`num_ad_requests`	`INTEGER`	Total number of Requests.
`num_realised_ads`	`INTEGER`	Total number of Ads realised.
`ad_request_rank`	`INTEGER`	Rank of the Search Term by total Requests.
`ad_stat_rank`	`INTEGER`	Rank of the Search Term by total Ads realised.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.dim_supplier`

NAMESPACE
SUPPLIER

Supplier dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: supplier_id, row_version
Other Unique Key(s): supplier_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_supplier.env_namespace;

Column Name	Data Type	Description
`supplier_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`supplier_name`	`STRING`	Name of the Supplier.
`team_type`	`STRING`	Type of Team (SUPPLIER).
`company_name`	`STRING`	Company name of the Supplier.
`business_identifier`	`STRING`	Implementation specific generic label.
`industry`	`STRING`	Industry of the Supplier.
`supplier_group_1`	`STRING`	Supplier team group membership #1.
`supplier_group_2`	`STRING`	Supplier team group membership #2.
`supplier_group_3`	`STRING`	Supplier team group membership #3.
`supplier_group_4`	`STRING`	Supplier team group membership #4.
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`created_at`	`TIMESTAMP`	Date the record was created in the source system.
`created_at_utc`	`TIMESTAMP`	Currently the same as created_at since all data is in UTC.
`updated_at`	`TIMESTAMP`	Date the record was updated in the source system.
`updated_at_utc`	`TIMESTAMP`	Currently the same as updated_at since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.dim_team`

NAMESPACE

Team dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: team_id, row_version
Other Unique Key(s): team_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_team.env_namespace;

Column Name	Data Type	Description
`team_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`team_name`	`STRING`	Name of the Team.
`team_type`	`STRING`	Type of Team (RETAILER or SUPPLIER).
`company_name`	`STRING`	Company name of the Team.
`business_identifier`	`STRING`	Implementation specific generic label.
`industry`	`STRING`	Industry of the Team.
`user_id`	`STRING`	Internal identifier of the User (from the source system).
`last_campaign_created_id`	`STRING`	Internal identifier of the Campaign last created by the Team (from the source system).
`last_campaign_created_date`	`TIMESTAMP`	Date the Team created the most recent Campaign.
`last_campaign_updated_id`	`STRING`	Internal identifier of the Campaign last modified by the Team (from the source system).
`last_campaign_updated_date`	`TIMESTAMP`	Date the Team updated the most recent Campaign.
`total_campaigns`	`INTEGER`	Total number of Campaigns associated with Team (including inactive).
`total_active_campaigns`	`INTEGER`	Total active Campaigns associated with the Team.
`total_spend`	`NUMERIC`	Total AdSpend to date for the Team across all Campaigns.
`last_spend_date`	`DATE`	Most recent date that AdSpend was incurred by the Supplier.
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`created_at`	`TIMESTAMP`	Date the record was created in the source system.
`created_at_utc`	`TIMESTAMP`	Currently the same as created_at since all data is in UTC.
`updated_at`	`TIMESTAMP`	Date the record was updated in the source system.
`updated_at_utc`	`TIMESTAMP`	Currently the same as updated_at since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`phone`	`STRING`	The company phone number
`company_website`	`STRING`	The company website
`company_address_line_1`	`STRING`	The company address
`company_address_post_code`	`STRING`	Post code of the company address
`contact_email`	`STRING`	Email address of the company contact

`analytics.dim_user`

NAMESPACE

User dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: user_id, row_version
Other Unique Key(s): user_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_user.env_namespace;

Column Name	Data Type	Description
`user_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`user_id`	`STRING`	Internal identifier of the User (from the source system).
`row_version`	`INTEGER`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`admin`	`STRING`	Flag if the User is an administrator.
`email`	`STRING`	User's email address.
`user_name`	`STRING`	Name of the User.
`activated`	`BOOLEAN`	Flag indicating that a User has activated their account.
`system_permissions`	`STRING`	User system permissions.
`action_permission`	`STRING`	User action permissions.
`subject_permission`	`STRING`	User subject permissions.
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`created_at`	`TIMESTAMP`	Date the record was created in the source system.
`created_at_utc`	`TIMESTAMP`	Currently the same as created_at since all data is in UTC.
`updated_at`	`TIMESTAMP`	Date the record was updated in the source system.
`updated_at_utc`	`TIMESTAMP`	Currently the same as updated_at since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.dim_wallet`

NAMESPACE

Wallet dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

Primary Key: wallet_id
Other Unique Key(s): wallet_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = dim_wallet.env_namespace;
dim_team on dim_team.env_namespace = dim_wallet.env_namespace and dim_team.team_id = dim_wallet.wallet_team_id and dim_team.is_current;
currency_aud on currency_aud.currency = dim_wallet.currency_code;
currency_usd on currency_usd.currency = dim_wallet.currency_code;

Column Name	Data Type	Description
`wallet_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`wallet_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`external_id`	`STRING`	Implementation specific generic label.
`wallet_name`	`STRING`	Name of the Wallet.
`team_name`	`STRING`	Name of the Team.
`archived`	`BOOLEAN`	Archived status of the Wallet.
`credit_limit`	`NUMERIC`	Amount the Wallet may go into credit.
`currency_code`	`STRING`	ISO 4217 currency code
`default_wallet`	`BOOLEAN`	Flag indicating if this is the default Wallet.
`prepaid_balance`	`NUMERIC`	Prepaid balance of the Wallet.
`available_balance`	`NUMERIC`	Current available balance of the Wallet.
`total_purchased_credits`	`NUMERIC`	Total credits made to the Wallet.
`latest_top_up_amount`	`NUMERIC`	Amount of the most recent credit applied to the Wallet.
`earliest_top_up_amount`	`NUMERIC`	Earliest credit amount made against the Wallet.
`largest_top_up_amount`	`NUMERIC`	Largest credit amount made against the Wallet.
`smallest_top_up_amount`	`NUMERIC`	Smallest credit amount made against the Wallet.
`average_top_up_amount`	`NUMERIC`	Average of credits applied to the Wallet.
`credit_limit_usd`	`NUMERIC`	credit_limit in USD (current exchange rate only).
`total_purchased_credits_usd`	`NUMERIC`	total_purchased_credits in USD (current exchange rate only).
`available_balance_usd`	`NUMERIC`	available_balance in USD (current exchange rate only).
`prepaid_balance_usd`	`NUMERIC`	prepaid_balance in USD (current exchange rate only).
`latest_top_up_amount_usd`	`NUMERIC`	latest_top_up_amount in USD (current exchange rate only).
`earliest_top_up_amount_usd`	`NUMERIC`	earliest_top_up_amount in USD (current exchange rate only).
`largest_top_up_amount_usd`	`NUMERIC`	largest_top_up_amount in USD (current exchange rate only).
`smallest_top_up_amount_usd`	`NUMERIC`	smallest_top_up_amount in USD (current exchange rate only).
`average_top_up_amount_usd`	`NUMERIC`	average_top_up_amount in USD (current exchange rate only).
`active_campaign_count`	`NUMERIC`	Number of currently active Campaigns associated to the Wallet.
`active_campaign_max_total_spend`	`NUMERIC`	Aggregate CampaignMaxTotalSpend for all currently active Campaigns associated to the Wallet.
`namespace`	`STRING`	Name of the Namespace.
`earliest_credit_date`	`TIMESTAMP`	Most recent date a credit was made to the Wallet.
`earliest_credit_date_utc`	`TIMESTAMP`	Currently the same as earliest_credit_date since all data is in UTC.
`latest_credit_date`	`TIMESTAMP`	Date of the most recent credit.
`latest_credit_date_utc`	`TIMESTAMP`	Currently the same as latest_credit_date since all data is in UTC.
`wallet_capture_date`	`TIMESTAMP`	Date the Wallet details were captured on.
`wallet_capture_date_utc`	`TIMESTAMP`	Currently the same as wallet_capture_date since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`daily_limit`	`NUMERIC`	Amount the Wallet may go into credit in a day.
`capped_available_balance`	`NUMERIC`	Current capped available balance of the Wallet.

`analytics.fact_ad_request`

NAMESPACE

Request fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: request_trace_id, request_type
Other Unique Key(s): ad_request_key
Partition: ingressed_at (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_ad_request.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_ad_request.env_namespace and dim_retailer.retailer_id = fact_ad_request.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_ad_request.env_namespace and dim_catalog.catalog_id = fact_ad_request.catalog_id and dim_catalog.is_current;
dim_placement on dim_placement.env_namespace = fact_ad_request.env_namespace and dim_placement.placement_id = fact_ad_request.placement_id;
dim_search_term on dim_search_term.env_namespace = fact_ad_request.env_namespace and dim_search_term.retailer_id = fact_ad_request.retailer_id and dim_search_term.search_term = fact_ad_request.search_term;
dim_category on dim_category.env_namespace=fact_ad_request.env_namespace and dim_category.category_id=fact_ad_request.category and dim_category.retailer_id = fact_ad_request.retailer_id;

Column Name	Data Type	Description
`ad_request_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`request_trace_id`	`STRING`	Request trace id.
`request_type`	`INTEGER`	Request type.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`customer_id`	`STRING`	Customer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
`session_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser session, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).
`realised_ad_ids`	`STRING`	Internal identifiers of the Realised Ads associated to the Ad Request (ids from the source system).
`ingressed_at`	`TIMESTAMP`	Timestamp the Requests were received.
`ingressed_at_utc`	`TIMESTAMP`	Currently the same as ingressed_at since all data is in UTC.
`search_term`	`STRING`	Search Term being targeted.
`reserved_search_term_flag`	`STRING`	Not in use.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`filter_mode`	`INTEGER`	The filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
`slot_ids`	`STRING`	Slot ids.
`category`	`STRING`	Category being targeted.
`product_type_code`	`STRING`	Product type code.
`location`	`STRING`	Location.
`namespace`	`STRING`	Name of the Namespace.
`experiment_id`	`STRING`	Not in use.
`num_ads_considered`	`NUMERIC`	Total number of Ad candidates that were considered by relevancy algorithms.
`num_ads_served`	`NUMERIC`	Total number of Ads that were served back.
`num_ads_requested`	`NUMERIC`	Total number of Ads that were requested by the Requests.
`capped_num_ads_served`	`NUMERIC`	Total number of Ads that were served back with Cap applied per Request.
`capped_num_ads_requested`	`NUMERIC`	Total number of Ads that were requested with Cap applied per Request.
`capped_num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the Capped number of Ads requested was served in full.
`num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the number of Ads requested was served in full.
`num_ad_requests_serving_no_ads`	`NUMERIC`	Total number of Requests where no Ads were served.
`num_ad_requests_serving_some_ads`	`NUMERIC`	Total number of Requests where at least 1 Ad was served.
`num_ad_requests`	`NUMERIC`	Total number of Requests.
`num_ad_requests_with_saturation`	`NUMERIC`	Total number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`dtm_cookie_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser cookie, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).

`analytics.fact_ad_request_agg`

NAMESPACE

Request fact (aggregated).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: ingressed_at, env_namespace, catalog_id, retailer_id, request_type, search_term, reserved_search_term_flag, generic_search_term, placement, platform, page_type, filter_mode, slot_ids, category
Other Unique Key(s): ad_request_agg_key
Partition: ingressed_at (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_ad_request_agg.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_ad_request_agg.env_namespace and dim_retailer.retailer_id = fact_ad_request_agg.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_ad_request_agg.env_namespace and dim_catalog.catalog_id = fact_ad_request_agg.catalog_id and dim_catalog.is_current;
dim_placement on dim_placement.env_namespace = fact_ad_request_agg.env_namespace and dim_placement.placement_id = fact_ad_request_agg.placement_id;
dim_search_term on dim_search_term.env_namespace = fact_ad_request_agg.env_namespace and dim_search_term.retailer_id = fact_ad_request_agg.retailer_id and dim_search_term.search_term = fact_ad_request_agg.search_term;
dim_category on dim_category.env_namespace = fact_ad_request_agg.env_namespace and dim_category.category_id = fact_ad_request_agg.category and dim_category.retailer_id = fact_ad_request_agg.retailer_id

Column Name	Data Type	Description
`ad_request_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`ingressed_at`	`DATE`	Date the Requests were received.
`request_type`	`INTEGER`	Request type.
`search_term`	`STRING`	Search Term being targeted.
`reserved_search_term_flag`	`STRING`	Not in use.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`filter_mode`	`INTEGER`	The filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
`slot_ids`	`STRING`	Slot ids.
`category`	`STRING`	Category being targeted.
`namespace`	`STRING`	Name of the Namespace.
`num_ads_considered`	`NUMERIC`	Total number of Ad candidates that were considered by relevancy algorithms.
`num_ads_served`	`NUMERIC`	Total number of Ads that were served back.
`num_ads_requested`	`NUMERIC`	Total number of Ads that were requested by the Requests.
`capped_num_ads_served`	`NUMERIC`	Total number of Ads that were served back with Cap applied per Request.
`capped_num_ads_requested`	`NUMERIC`	Total number of Ads that were requested with Cap applied per Request.
`capped_num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the number of Ads requested was served in full.
`num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the number of Ads requested was served in full.
`num_ad_requests_serving_no_ads`	`NUMERIC`	Total number of Requests where no Ads were served.
`num_ad_requests_serving_some_ads`	`NUMERIC`	Total number of Requests where at least 1 Ad was served.
`num_ad_requests`	`NUMERIC`	Total number of Requests.
`num_ad_requests_with_saturation`	`NUMERIC`	Total number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).

`analytics.fact_ad_request_agg_ltz`

NAMESPACE

Request fact (aggregated) in local timezone.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`ad_request_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`ingressed_at`	`DATE`	Date the Requests were received in local timezone.
`request_type`	`INTEGER`	Request type.
`search_term`	`STRING`	Search Term being targeted.
`reserved_search_term_flag`	`STRING`	Not in use.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`filter_mode`	`INTEGER`	The filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
`slot_ids`	`STRING`	Slot ids.
`category`	`STRING`	Category being targeted.
`namespace`	`STRING`	Name of the Namespace.
`num_ads_considered`	`NUMERIC`	Total number of Ad candidates that were considered by relevancy algorithms.
`num_ads_served`	`NUMERIC`	Total number of Ads that were served back.
`num_ads_requested`	`NUMERIC`	Total number of Ads that were requested by the Requests.
`capped_num_ads_served`	`NUMERIC`	Total number of Ads that were served back with Cap applied per Request.
`capped_num_ads_requested`	`NUMERIC`	Total number of Ads that were requested with Cap applied per Request.
`capped_num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the number of Ads requested was served in full.
`num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the number of Ads requested was served in full.
`num_ad_requests_serving_no_ads`	`NUMERIC`	Total number of Requests where no Ads were served.
`num_ad_requests_serving_some_ads`	`NUMERIC`	Total number of Requests where at least 1 Ad was served.
`num_ad_requests`	`NUMERIC`	Total number of Requests.
`num_ad_requests_with_saturation`	`NUMERIC`	Total number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).

`analytics.fact_enhanced_attribution`

NAMESPACE

Enhanced Attribution (full detail, first model only).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: env_namespace, realised_ad_id, retailer_order_id, order_item_id, session_id, campaign_id, category_id, normalised_product_code, product_code, retailer_taxonomy, halo_taxonomy, halo_taxonomy_level
Other Unique Key(s): enhanced_attribution_key
Partition: ingressed_at (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_enhanced_attribution.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_enhanced_attribution.env_namespace and dim_retailer.retailer_id = fact_enhanced_attribution.retailer_id and dim_retailer.is_current;
dim_campaign on dim_campaign.env_namespace = fact_enhanced_attribution.env_namespace and dim_campaign.campaign_id = fact_enhanced_attribution.campaign_id and dim_campaign.is_current;
dim_product on dim_product.env_namespace = fact_enhanced_attribution.env_namespace and dim_product.product_code = fact_enhanced_attribution.product_code ;
dim_search_term on dim_search_term.env_namespace = fact_enhanced_attribution.env_namespace and dim_search_term.retailer_id = fact_enhanced_attribution.retailer_id and dim_search_term.search_term = fact_enhanced_attribution.search_term;
dim_category on dim_category.env_namespace = fact_enhanced_attribution.env_namespace and dim_category.category_id = fact_enhanced_attribution.category and dim_category.retailer_id = fact_enhanced_attribution.retailer_id;
dim_placement on dim_placement.env_namespace = fact_enhanced_attribution.env_namespace and dim_placement.placement = fact_enhanced_attribution.placement;
fact_realised_ad on fact_realised_ad.env_namespace = fact_enhanced_attribution.env_namespace and fact_realised_ad.realised_ad_id = fact_enhanced_attribution.realised_ad_id;
fact_order on on fact_order.env_namespace = fact_enhanced_attribution.env_namespace and fact_order.retailer_order_id = fact_enhanced_attribution.retailer_order_id and fact_order.order_item_id = fact_enhanced_attribution.retailer_order_id ;

Column Name	Data Type	Description
`enhanced_attribution_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`realised_ad_id`	`STRING`	Internal identifier of the Realised Ad the Conversion is Attributed to.
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`session_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser session, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`product_name`	`STRING`	Name of the Product.
`brand`	`STRING`	Brand of the Product.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`halo_taxonomy`	`STRING`	The portion of the retailer_taxonomy relevant for the given halo_taxonomy_level. Note for halo_taxonomy_level=1 it is null since the Brand is level 1.
`halo_taxonomy_level`	`INTEGER`	Halo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
`ingressed_at`	`TIMESTAMP`	Timestamp the Requests were received.
`impressioned_at`	`TIMESTAMP`	Timestamp the impression occurred.
`clicked_at`	`TIMESTAMP`	Timestamp of the Click.
`impressioned`	`BOOLEAN`	Flag indicating an impression.
`clicked`	`BOOLEAN`	Flag indicating if clicked.
`purchased`	`BOOLEAN`	Total number of purchases.
`search_term`	`STRING`	Search Term being targeted.
`category_id`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`order_date`	`DATE`	Date of the Orders.
`retailer_order_id`	`STRING`	Identifier of the Order the Conversion is Attributed to.
`order_item_id`	`STRING`	Line item of the Order the Conversion is Attributed to.
`order_product_code`	`STRING`	Order Product code.
`order_normalised_product`	`STRING`	Normalised Product code of the Order.
`customer_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser session, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).
`order_type`	`STRING`	Type of the Order.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`direct_unit_sales`	`NUMERIC`	Unit sales from CitrusAd platform Attributions.
`direct_sales_value`	`NUMERIC`	Revenue from CitrusAd platform Attributions.
`direct_conversions`	`NUMERIC`	Conversions from CitrusAd platform Attributions.
`view_through_unit_sales`	`NUMERIC`	Unit sales from Impression View Thru Attributions.
`view_through_sales_value`	`NUMERIC`	Revenue from Impression View Thru Attributions.
`view_through_conversions`	`NUMERIC`	Conversions from Impression View Thru Attributions.
`halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`row_type`	`STRING`	Row Type.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo impression Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo impression Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo impression Attributions.

`analytics.fact_enhanced_attribution_2`

NAMESPACE

Enhanced Attribution (full detail, second model only).

Layer: Core Dataset
Category: Fact
Status: Published

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_enhanced_attribution_2.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_enhanced_attribution_2.env_namespace and dim_retailer.retailer_id = fact_enhanced_attribution_2.retailer_id and dim_retailer.is_current;
dim_campaign on dim_campaign.env_namespace = fact_enhanced_attribution_2.env_namespace and dim_campaign.campaign_id = fact_enhanced_attribution_2.campaign_id and dim_campaign.is_current;
dim_product on dim_product.env_namespace = fact_enhanced_attribution_2.env_namespace and dim_product.product_code = fact_enhanced_attribution_2.product_code ;
dim_search_term on dim_search_term.env_namespace = fact_enhanced_attribution_2.env_namespace and dim_search_term.retailer_id = fact_enhanced_attribution_2.retailer_id and dim_search_term.search_term = fact_enhanced_attribution_2.search_term;
dim_category on dim_category.env_namespace = fact_enhanced_attribution_2.env_namespace and dim_category.category_id = fact_enhanced_attribution_2.category and dim_category.retailer_id = fact_enhanced_attribution_2.retailer_id;
dim_placement on dim_placement.env_namespace = fact_enhanced_attribution_2.env_namespace and dim_placement.placement = fact_enhanced_attribution_2.placement;
fact_realised_ad on fact_realised_ad.env_namespace = fact_enhanced_attribution_2.env_namespace and fact_realised_ad.realised_ad_id = fact_enhanced_attribution_2.realised_ad_id;
fact_order on on fact_order.env_namespace = fact_enhanced_attribution_2.env_namespace and fact_order.retailer_order_id = fact_enhanced_attribution_2.retailer_order_id and fact_order.order_item_id = fact_enhanced_attribution_2.retailer_order_id;

Column Name	Data Type	Description
`enhanced_attribution_2_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`realised_ad_id`	`STRING`	Internal identifier of the Realised Ad the Conversion is Attributed to.
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`session_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser session, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`product_name`	`STRING`	Name of the Product.
`brand`	`STRING`	Brand of the Product.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`halo_taxonomy`	`STRING`	The portion of the retailer_taxonomy relevant for the given halo_taxonomy_level. Note for halo_taxonomy_level=1 it is null since the Brand is level 1.
`halo_taxonomy_level`	`INTEGER`	Halo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
`ingressed_at`	`TIMESTAMP`	Timestamp the Requests were received.
`impressioned_at`	`TIMESTAMP`	Timestamp the impression occurred.
`clicked_at`	`TIMESTAMP`	Timestamp of the Click.
`impressioned`	`BOOLEAN`	Flag indicating an impression.
`clicked`	`BOOLEAN`	Flag indicating if clicked.
`purchased`	`BOOLEAN`	Total number of purchases.
`search_term`	`STRING`	Search Term being targeted.
`category_id`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`order_date`	`DATE`	Date of the Orders.
`retailer_order_id`	`STRING`	Identifier of the Order the Conversion is Attributed to.
`order_item_id`	`STRING`	Line item of the Order the Conversion is Attributed to.
`order_product_code`	`STRING`	Order Product code.
`order_normalised_product`	`STRING`	Normalised Product code of the Order.
`customer_id`	`STRING`	Customer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
`order_type`	`STRING`	Type of the Order.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`direct_unit_sales`	`NUMERIC`	Unit sales from CitrusAd platform Attributions.
`direct_sales_value`	`NUMERIC`	Revenue from CitrusAd platform Attributions.
`direct_conversions`	`NUMERIC`	Conversions from CitrusAd platform Attributions.
`view_through_unit_sales`	`NUMERIC`	Unit sales from Impression View Thru Attributions.
`view_through_sales_value`	`NUMERIC`	Revenue from Impression View Thru Attributions.
`view_through_conversions`	`NUMERIC`	Conversions from Impression View Thru Attributions.
`halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`row_type`	`STRING`	Row Type.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo impression Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo impression Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo impression Attributions.

`analytics.fact_enhanced_attribution_agg`

NAMESPACE

Enhanced Attribution (aggregated).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: env_namespace, campaign_id, category_id, normalised_product_code, product_code, product_name, brand,retailer_taxonomy, halo_taxonomy, halo_taxonomy_level, order_date, ingressed_at, search_term, placement, platform, page_type, attribution_model, retailer_id, order_type
Other Unique Key(s): enhanced_attribution_agg_key
Partition: ingressed_at (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_enhanced_attribution_agg.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_enhanced_attribution_agg.env_namespace and dim_retailer.retailer_id = fact_enhanced_attribution_agg.retailer_id and dim_retailer.is_current;
dim_campaign on dim_campaign.env_namespace = fact_enhanced_attribution_agg.env_namespace and dim_campaign.campaign_id = fact_enhanced_attribution_agg.campaign_id and dim_campaign.is_current;
dim_product on dim_product.env_namespace = fact_enhanced_attribution_agg.env_namespace and dim_product.product_code = fact_enhanced_attribution_agg.product_code ;
dim_search_term on dim_search_term.env_namespace = fact_enhanced_attribution_agg.env_namespace and dim_search_term.retailer_id = fact_enhanced_attribution_agg.retailer_id and dim_search_term.search_term = fact_enhanced_attribution_agg.search_term;
dim_category on dim_category.env_namespace = fact_enhanced_attribution_agg.env_namespace and dim_category.category_id = fact_enhanced_attribution_agg.category and dim_category.retailer_id = fact_enhanced_attribution_agg.retailer_id;
dim_placement on dim_placement.env_namespace = fact_enhanced_attribution_agg.env_namespace and dim_placement.placement = fact_enhanced_attribution_agg.placement;

Column Name	Data Type	Description
`enhanced_attribution_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`category_id`	`STRING`	Category being targeted.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`product_name`	`STRING`	Name of the Product.
`brand`	`STRING`	Brand of the Product.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`halo_taxonomy`	`STRING`	The portion of the retailer_taxonomy relevant for the given halo_taxonomy_level. Note for halo_taxonomy_level=1 it is null since the Brand is level 1.
`halo_taxonomy_level`	`INTEGER`	Halo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
`order_date`	`DATE`	Date of the Orders.
`ingressed_at`	`DATE`	Date the Ads were realised.
`search_term`	`STRING`	Search Term being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`namespace`	`STRING`	Name of the Namespace.
`order_type`	`STRING`	Type of the Order.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`direct_unit_sales`	`NUMERIC`	Unit sales from CitrusAd platform Attributions.
`direct_sales_value`	`NUMERIC`	Revenue from CitrusAd platform Attributions.
`direct_conversions`	`NUMERIC`	Conversions from CitrusAd platform Attributions.
`view_through_unit_sales`	`NUMERIC`	Unit sales from Impression View Thru Attributions.
`view_through_sales_value`	`NUMERIC`	Revenue from Impression View Thru Attributions.
`view_through_conversions`	`NUMERIC`	Conversions from Impression View Thru Attributions.
`halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`attribution_model`	`INTEGER`	Indicate if data is from first or second model.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo impression Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo impression Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo impression Attributions.

`analytics.fact_enhanced_attribution_agg_ltz`

NAMESPACE

Enhanced Attribution (aggregated) in local timezone.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`enhanced_attribution_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`category_id`	`STRING`	Category being targeted.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`product_name`	`STRING`	Name of the Product.
`brand`	`STRING`	Brand of the Product.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`halo_taxonomy`	`STRING`	The portion of the retailer_taxonomy relevant for the given halo_taxonomy_level. Note for halo_taxonomy_level=1 it is null since the Brand is level 1.
`halo_taxonomy_level`	`INTEGER`	Halo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
`order_date`	`DATE`	Date of the Orders.
`ingressed_at`	`DATE`	Date the Ads were realised in local timezone.
`search_term`	`STRING`	Search Term being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`namespace`	`STRING`	Name of the Namespace.
`order_type`	`STRING`	Type of the Order.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`direct_unit_sales`	`NUMERIC`	Unit sales from CitrusAd platform Attributions.
`direct_sales_value`	`NUMERIC`	Revenue from CitrusAd platform Attributions.
`direct_conversions`	`NUMERIC`	Conversions from CitrusAd platform Attributions.
`view_through_unit_sales`	`NUMERIC`	Unit sales from Impression View Thru Attributions.
`view_through_sales_value`	`NUMERIC`	Revenue from Impression View Thru Attributions.
`view_through_conversions`	`NUMERIC`	Conversions from Impression View Thru Attributions.
`halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`attribution_model`	`INTEGER`	Indicate if data is from first or second model.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo impression Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo impression Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo impression Attributions.

`analytics.fact_fta_campaign_spend`

NAMESPACE

FTA Campaign Spend fact.

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: env_namespace, campaign_id, team_id, wallet_id, search_term, catalog_id, product_code, bucket_date, bucket_date_key
Other Unique Key(s): fta_campaign_spend_key
Partition: bucket_date (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_fta_campaign_spend.env_namespace;
dim_campaign on dim_campaign.env_namespace = fact_fta_campaign_spend.env_namespace and dim_campaign.campaign_id = fact_fta_campaign_spend.campaign_id and dim_campaign.is_current;
dim_team on dim_team.env_namespace = fact_fta_campaign_spend.env_namespace and dim_team.team_id = fact_fta_campaign_spend.team_id and dim_team.is_current;
dim_wallet on dim_wallet.env_namespace = fact_fta_campaign_spend.env_namespace and dim_wallet.wallet_id = fact_fta_campaign_spend.wallet_id;
dim_catalog on dim_catalog.env_namespace = fact_fta_campaign_spend.env_namespace and dim_catalog.catalog_id = fact_fta_campaign_spend.catalog_id and dim_catalog.is_current;
dim_search_term on dim_search_term.env_namespace = fact_fta_campaign_spend.env_namespace and dim_search_term.retailer_id = fact_fta_campaign_spend.retailer_id and dim_search_term.search_term = fact_fta_campaign_spend.search_term;
dim_product on dim_product.env_namespace = fact_fta_campaign_spend.env_namespace and dim_product.product_code = fact_fta_campaign_spend.product_code;

Column Name	Data Type	Description
`fta_campaign_spend_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`product_code`	`STRING`	Product code.
`bucket_date`	`DATE`	Bucket date.
`bucket_date_key`	`INTEGER`	Unique identifier of the row in this table (surrogate key).
`namespace`	`STRING`	Name of the Namespace.
`bucketed_cost`	`NUMERIC`	Bucketed cost.
`fta_cost`	`NUMERIC`	Total amount being spent by the given Fixed Tenancy Agreement (FTA) Campaign.
`catalog_cost_percentage`	`NUMERIC`	Catalog cost percentage.
`num_of_search_terms`	`INTEGER`	Number of Search Terms.
`num_of_products`	`INTEGER`	Number of Products.
`num_of_days_in_campaign`	`INTEGER`	Total number of days in the Campaign.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.fact_ledger`

NAMESPACE

Ledger fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: ledger_id
Other Unique Key(s): ledger_key
Partition: transaction_date (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_ledger.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_ledger.env_namespace and dim_retailer.retailer_id = fact_ledger.retailer_id and dim_retailer.is_current;
dim_campaign on dim_campaign.env_namespace = fact_ledger.env_namespace and dim_campaign.campaign_id = fact_ledger.campaign_id and dim_campaign.is_current;
dim_team on dim_team.env_namespace = fact_ledger.env_namespace and dim_team.team_id = fact_ledger.team_id and dim_team.is_current;
dim_wallet on dim_wallet.env_namespace = fact_ledger.env_namespace and dim_wallet.wallet_id = fact_ledger.wallet_id;

Column Name	Data Type	Description
`ledger_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`ledger_id`	`STRING`	Internal identifier of the Ledger transaction (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`transaction_date`	`TIMESTAMP`	Date of the transactions.
`transaction_date_utc`	`TIMESTAMP`	Currently the same as transaction_date since all data is in UTC.
`ledger_type`	`STRING`	Debit or Credit transaction type.
`product_type`	`STRING`	Type of the Product.
`reason`	`STRING`	Reason given for the transaction type.
`namespace`	`STRING`	Name of the Namespace.
`amount`	`NUMERIC`	Total of transaction amounts.
`citrus_gross_margin`	`NUMERIC`	Citrus gross margin.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`reason_linkback`	`STRING`	Link back to what the transaction was for. E.g- Ids of the ads that were impressioned/clicked.

`analytics.fact_ledger_agg`

NAMESPACE

Ledger fact (aggregated into UTC+0 day buckets).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: transaction_date, env_namespace, team_id, wallet_id, campaign_id, ledger_type, product_ledger, reason, retailer_id
Other Unique Key(s): ledger_agg_key
Partition: transaction_date (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_ledger_agg.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_ledger_agg.env_namespace and dim_retailer.retailer_id = fact_ledger_agg.retailer_id and dim_retailer.is_current;
dim_campaign on dim_campaign.env_namespace = fact_ledger_agg.env_namespace and dim_campaign.campaign_id = fact_ledger_agg.campaign_id and dim_campaign.is_current;
dim_team on dim_team.env_namespace = fact_ledger_agg.env_namespace and dim_team.team_id = fact_ledger_agg.team_id and dim_team.is_current;
dim_wallet on dim_wallet.env_namespace = fact_ledger_agg.env_namespace and dim_wallet.wallet_id = fact_ledger_agg.wallet_id;

Column Name	Data Type	Description
`ledger_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`transaction_date`	`DATE`	Date of the transactions.
`date_key`	`INTEGER`	Unique identifier of the row in this table (surrogate key).
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`ledger_type`	`STRING`	Debit or Credit transaction type.
`product_ledger`	`STRING`	More detailed transaction type.
`namespace`	`STRING`	Name of the Namespace.
`reason`	`STRING`	Reason given for the transaction type.
`amount`	`NUMERIC`	Total of transaction amounts.
`transaction_count`	`NUMERIC`	Total transaction count.
`citrus_gross_margin`	`NUMERIC`	Citrus gross margin.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.fact_ledger_agg_2`

NAMESPACE

Ledger fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: transaction_timestamp, env_namespace, team_id, wallet_id, campaign_id, ledger_type, product_ledger, reason, retailer_id
Other Unique Key(s): None
Partition: transaction_timestamp (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_ledger_agg_2.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_ledger_agg_2.env_namespace and dim_retailer.retailer_id = fact_ledger_agg_2.retailer_id and dim_retailer.is_current;
dim_campaign on dim_campaign.env_namespace = fact_ledger_agg_2.env_namespace and dim_campaign.campaign_id = fact_ledger_agg_2.campaign_id and dim_campaign.is_current;
dim_team on dim_team.env_namespace = fact_ledger_agg_2.env_namespace and dim_team.team_id = fact_ledger_agg_2.team_id and dim_team.is_current;
dim_wallet on dim_wallet.env_namespace = fact_ledger_agg_2.env_namespace and dim_wallet.wallet_id = fact_ledger_agg_2.wallet_id;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`transaction_timestamp`	`TIMESTAMP`	Timestamp of the transactions rounded up to the hour
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`ledger_type`	`STRING`	Debit or Credit transaction type.
`product_ledger`	`STRING`	Type of the Product.
`reason`	`STRING`	Reason given for the transaction type.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`citrus_gross_margin`	`NUMERIC`	Total of Citrus gross margin
`amount`	`NUMERIC`	Total of transaction amounts.
`transaction_count`	`INTEGER`	Total number of transactions.

`analytics.fact_ledger_agg_2_ltz`

NAMESPACE

Ledger fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`transaction_timestamp`	`DATETIME`	Datetime of the transactions rounded up to the hour in local timezone
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`ledger_type`	`STRING`	Debit or Credit transaction type.
`product_ledger`	`STRING`	Type of the Product.
`reason`	`STRING`	Reason given for the transaction type.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`citrus_gross_margin`	`NUMERIC`	Total of Citrus gross margin
`amount`	`NUMERIC`	Total of transaction amounts.
`transaction_count`	`INTEGER`	Total number of transactions.

`analytics.fact_ledger_agg_ltz`

NAMESPACE

Ledger fact (aggregated into local timezone day buckets).

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`ledger_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`transaction_date`	`DATE`	Date of the transactions in local timezone.
`date_key`	`INTEGER`	Unique identifier of the row in this table (surrogate key).
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`ledger_type`	`STRING`	Debit or Credit transaction type.
`product_ledger`	`STRING`	More detailed transaction type.
`namespace`	`STRING`	Name of the Namespace.
`reason`	`STRING`	Reason given for the transaction type.
`amount`	`NUMERIC`	Total of transaction amounts.
`transaction_count`	`NUMERIC`	Total transaction count.
`citrus_gross_margin`	`NUMERIC`	Citrus gross margin.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.fact_order`

NAMESPACE

Order fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: retailer_order_id, sequence_id, retailer_id, row_version, order_item_id, order_type
Other Unique Key(s): order_key
Partition: order_timestamp (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_order.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_order.env_namespace and dim_retailer.retailer_id = fact_order.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_order.env_namespace and dim_catalog.catalog_id = fact_order.catalog_id and dim_catalog.is_current;
dim_product on dim_product.env_namespace = fact_order.env_namespace and dim_product.catalog_id = fact_order.catalog_id and dim_product.product_code = fact_order.product_code;

Column Name	Data Type	Description
`order_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`sequence_id`	`NUMERIC`	Sequence id.
`retailer_order_id`	`STRING`	Identifier for the Order from the Retailer.
`row_version`	`NUMERIC`	Track changes with new row versions (sequentially increasing number to indicate chronology).
`order_item_id`	`STRING`	Line item of the Order.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`issuer_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`namespace`	`STRING`	Name of the Namespace.
`order_timestamp`	`TIMESTAMP`	Timestamp of the Order.
`order_timestamp_utc`	`TIMESTAMP`	Currently the same as order_timestamp since all data is in UTC.
`order_date`	`DATE`	Date of the Orders.
`order_date_utc`	`DATE`	Currently the same as order_date since all data is in UTC.
`order_date_key`	`INTEGER`	Unique identifier of the row in this table (surrogate key).
`realised_ad_id`	`STRING`	Realised Ad the Order is Attributed to (as set by external processes).
`product_code`	`STRING`	Product code.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`session_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser session, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).
`customer_id`	`STRING`	Customer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
`currency_code`	`STRING`	ISO 4217 currency code
`order_type`	`STRING`	Type of the Order.
`quantity`	`NUMERIC`	Quantity of the Product ordered.
`total_price`	`NUMERIC`	Total paid for the products ordered.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`dtm_cookie_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser cookie, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).

`analytics.fact_order_agg`

NAMESPACE

Order fact (aggregated).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: env_namespace, order_date, issuer_team_id, retailer_id, catalog_id, product_code, normalised_product_code, order_type
Other Unique Key(s): order_agg_key
Partition: order_date (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_order_agg.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_order_agg.env_namespace and dim_retailer.retailer_id = fact_order_agg.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_order_agg.env_namespace and dim_catalog.catalog_id = fact_order_agg.catalog_id and dim_catalog.is_current;
dim_product on dim_product.env_namespace = fact_order_agg.env_namespace and dim_product.catalog_id = fact_order_agg.catalog_id and dim_product.product_code = fact_order_agg.product_code;

Column Name	Data Type	Description
`order_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`issuer_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`order_date`	`DATE`	Date of the Orders.
`order_date_key`	`INTEGER`	Unique identifier of the row in this table (surrogate key).
`product_code`	`STRING`	Product code.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`currency_code`	`STRING`	ISO 4217 currency code
`namespace`	`STRING`	Name of the Namespace.
`order_type`	`STRING`	Type of the Order.
`quantity`	`NUMERIC`	Quantity of the Product ordered.
`total_price`	`NUMERIC`	Total paid for the products ordered.
`total_price_usd`	`FLOAT`	total_price in USD (current exchange rate only).
`order_items`	`NUMERIC`	Total number of items ordered.
`orders`	`NUMERIC`	Number of Orders made.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.fact_order_agg_ltz`

NAMESPACE

Order fact (aggregated in local timezone dates).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: env_namespace, order_date, issuer_team_id, retailer_id, catalog_id, product_code, normalised_product_code, order_type
Other Unique Key(s): order_agg_key
Partition: order_date (by DAY)

Column Name	Data Type	Description
`order_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`issuer_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`order_date`	`DATE`	Date of the Orders in local timezone.
`order_date_key`	`INTEGER`	Unique identifier of the row in this table (surrogate key).
`product_code`	`STRING`	Product code.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`currency_code`	`STRING`	ISO 4217 currency code
`namespace`	`STRING`	Name of the Namespace.
`order_type`	`STRING`	Type of the Order.
`quantity`	`NUMERIC`	Quantity of the Product ordered.
`total_price`	`NUMERIC`	Total paid for the products ordered.
`total_price_usd`	`FLOAT`	total_price in USD (current exchange rate only).
`order_items`	`NUMERIC`	Total number of items ordered.
`orders`	`NUMERIC`	Number of Orders made.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.fact_realised_ad`

NAMESPACE

Realised Ad fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: realised_ad_id
Other Unique Key(s): realised_ad_key
Partition: ingressed_at (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_realised_ad.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_realised_ad.env_namespace and dim_retailer.retailer_id = fact_realised_ad.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_realised_ad.env_namespace and dim_catalog.catalog_id = fact_realised_ad.catalog_id and dim_catalog.is_current;
dim_product on dim_product.env_namespace = fact_realised_ad.env_namespace and dim_product.catalog_id = fact_realised_ad.catalog_id and dim_product.normalised_product_code = fact_realised_ad.product_code;
dim_supplier on dim_supplier.env_namespace = fact_realised_ad.env_namespace and dim_supplier.supplier_id = fact_realised_ad.supplier_id and dim_supplier.is_current;
dim_campaign on dim_campaign.env_namespace = fact_realised_ad.env_namespace and dim_campaign.campaign_id = fact_realised_ad.campaign_id and dim_campaign.is_current;
dim_search_term on dim_search_term.env_namespace = fact_realised_ad.env_namespace and dim_search_term.retailer_id = fact_realised_ad.retailer_id and dim_search_term.search_term = fact_realised_ad.search_term;
dim_category on dim_category.env_namespace = fact_realised_ad.env_namespace and dim_category.category_id = fact_realised_ad.category and dim_category.retailer_id = fact_realised_ad.retailer_id;
dim_placement on dim_placement.env_namespace = fact_realised_ad.env_namespace and dim_placement.placement = fact_realised_ad.placement;
fact_order on fact_order.env_namespace = fact_realised_ad.env_namespace and fact_order.order_item_id = fact_realised_ad.order_item_id;

Column Name	Data Type	Description
`realised_ad_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`realised_ad_id`	`STRING`	Internal identifier of the Realised Ad (from the source system)..
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`ingressed_at`	`TIMESTAMP`	Timestamp the Requests were received.
`ingressed_at_utc`	`TIMESTAMP`	Currently the same as ingressed_at since all data is in UTC.
`purchased_at`	`TIMESTAMP`	Timestamp of the purchase.
`purchased_at_utc`	`TIMESTAMP`	Currently the same as purchased_at since all data is in UTC.
`impressioned_at`	`STRING`	String representation of the timestamp when the impression occurred.
`impressioned_at_utc`	`TIMESTAMP`	Currently the same as impressioned_at since all data is in UTC.
`clicked_at`	`TIMESTAMP`	Timestamp of the Click.
`clicked_at_utc`	`TIMESTAMP`	Currently the same as clicked_at since all data is in UTC.
`served_at`	`TIMESTAMP`	Timestamp when Ads served.
`served_at_utc`	`TIMESTAMP`	Currently the same as served_at since all data is in UTC.
`namespace`	`STRING`	Name of the Namespace.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`search_term`	`STRING`	Search Term being targeted.
`product_code`	`STRING`	Product code.
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`session_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser session, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).
`customer_id`	`STRING`	Customer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
`target_product_code`	`STRING`	Target Product code.
`reserved_search_term_flag`	`STRING`	Not in use.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`impressioned`	`BOOLEAN`	Flag indicating an impression.
`clicked`	`BOOLEAN`	Flag indicating if clicked.
`purchased`	`BOOLEAN`	Total number of purchases.
`served`	`BOOLEAN`	Flag indicating Ads were served.
`click_invalid`	`BOOLEAN`	Flag indicating if the click was invalid.
`impression_invalid`	`BOOLEAN`	Flag indicating if the impression was invalid.
`campaign_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`filter_mode`	`INTEGER`	The filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`serve_spend`	`NUMERIC`	Serve spend.
`serve_price`	`NUMERIC`	Serve price.
`impression_price`	`NUMERIC`	Price of an impression.
`click_price`	`NUMERIC`	Click Price.
`amount_refunded_from_invalid_click`	`NUMERIC`	Refund amount from invalid clicks.
`amount_refunded_from_invalid_impression`	`NUMERIC`	Refund amount from invalid impressions.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).
`product_type_code`	`STRING`	Product type code.
`location`	`STRING`	Location.
`order_item_id`	`STRING`	Line item of the Order.
`position`	`INTEGER`	Position of the Ad.
`experiment_id`	`STRING`	Not in use.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`ad_requester_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`email_serve_revenue`	`NUMERIC`	Email serve revenue.
`invalid_ad_revenue`	`NUMERIC`	Total invalid revenue.
`campaign_type`	`INTEGER`	Type of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
`product_collection`	`STRING`	Product collection (parent child sku).
`cached_ad_id`	`STRING`	Internal identifier of the Cached Ad (from the source system).
`seller_id`	`STRING`	Internal identifier of the Seller (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`is_fta`	`BOOLEAN`	Flag indicating if the Campaign is Fixed Tenancy Agreement (FTA).
`last_event_loaded_at`	`TIMESTAMP`	Date the row was inserted into the source system.
`keyword`	`STRING`	The selected Keyword which matched the user search term at ad generation
`keyword_match_type`	`STRING`	Method used in matching the keyword to the user search term i.e. EXACT, PHRASE, etc.
`impression_at`	`TIMESTAMP`	Timestamp the impression occurred.
`generic_search_term`	`STRING`	Search term with basic data cleaning applied.
`dtm_cookie_id`	`STRING`	Customer identifier as provided by the Retailer (often tied to a browser cookie, but is implementation specific). Normally used for Attribution (so must be persistent across the attribution window).

`analytics.fact_realised_ad_agg`

NAMESPACE

Realised Ad fact (aggregated into UTC+0 day buckets).

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: ingressed_at, env_namespace, catalog_id, retailer_id, supplier_id, campaign_id, ad_type, search_term, generic_search_term, product_code, content_standard_id, slot_id, category, placement, platform, target_product_code, ad_requester_team_id
Other Unique Key(s): realised_ad_agg_key
Partition: ingressed_at (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_realised_ad_agg.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_realised_ad_agg.env_namespace and dim_retailer.retailer_id = fact_realised_ad_agg.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_realised_ad_agg.env_namespace and dim_catalog.catalog_id = fact_realised_ad_agg.catalog_id and dim_catalog.is_current;
dim_product on dim_product.env_namespace = fact_realised_ad_agg.env_namespace and dim_product.catalog_id = fact_realised_ad_agg.catalog_id and dim_product.normalised_product_code = fact_realised_ad_agg.product_code;
dim_supplier on dim_supplier.env_namespace = fact_realised_ad_agg.env_namespace and dim_supplier.supplier_id = fact_realised_ad_agg.supplier_id and dim_supplier.is_current;
dim_campaign on dim_campaign.env_namespace = fact_realised_ad_agg.env_namespace and dim_campaign.campaign_id = fact_realised_ad_agg.campaign_id and dim_campaign.is_current;
dim_search_term on dim_search_term.env_namespace = fact_realised_ad_agg.env_namespace and dim_search_term.retailer_id = fact_realised_ad_agg.retailer_id and dim_search_term.search_term = fact_realised_ad_agg.search_term;
dim_category on dim_category.env_namespace = fact_realised_ad_agg.env_namespace and dim_category.category_id = fact_realised_ad_agg.category and dim_category.retailer_id = fact_realised_ad_agg.retailer_id;
dim_placement on dim_placement.env_namespace = fact_realised_ad_agg.env_namespace and dim_placement.placement = fact_realised_ad_agg.placement;

Column Name	Data Type	Description
`realised_ad_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`ingressed_at`	`DATE`	Date the Ads were realised.
`namespace`	`STRING`	Name of the Namespace.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`product_code`	`STRING`	Product code.
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`target_product_code`	`STRING`	Target Product code.
`position_sum`	`NUMERIC`	Sum of all postions for the Ads. Used to calculate average ad position (avg position = position_sum / ads_with_position_count).
`ads_with_position_count`	`NUMERIC`	Total number of Ads that have a position set. avg position = position_sum / ads_with_position_count.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`ad_requester_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`email_serve_revenue`	`NUMERIC`	Email serve revenue.
`invalid_ad_revenue`	`NUMERIC`	Total invalid revenue.
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`TIMESTAMP`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_2`

NAMESPACE

Realised Ad fact (aggregated, customer centric). Includes customer_id, but no breakdown by Campaign, Placement and Product.

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: ingressed_at, env_namespace, retailer_id, supplier_id, customer_id, search_term,category
Other Unique Key(s): realised_ad_agg_2_key
Partition: ingressed_at (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_realised_ad_agg_2.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_realised_ad_agg_2.env_namespace and dim_retailer.retailer_id = fact_realised_ad_agg_2.retailer_id and dim_retailer.is_current;
dim_supplier on dim_supplier.env_namespace = fact_realised_ad_agg_2.env_namespace and dim_supplier.supplier_id = fact_realised_ad_agg_2.supplier_id and dim_supplier.is_current;
dim_search_term on dim_search_term.env_namespace = fact_realised_ad_agg_2.env_namespace and dim_search_term.retailer_id = fact_realised_ad_agg_2.retailer_id and dim_search_term.search_term = fact_realised_ad_agg_2.search_term;
dim_category on dim_category.env_namespace = fact_realised_ad_agg_2.env_namespace and dim_category.category_id = fact_realised_ad_agg_2.category and dim_category.retailer_id = fact_realised_ad_agg_2.retailer_id;

Column Name	Data Type	Description
`realised_ad_agg_2_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`customer_id`	`STRING`	Customer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
`ingressed_at`	`DATE`	Date the Ads were realised.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`category`	`STRING`	Category being targeted.
`namespace`	`STRING`	Name of the Namespace.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`last_event_loaded_at`	`TIMESTAMP`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_3`

NAMESPACE

Realised Ad fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.

Layer: Core Dataset
Category: Fact
Status: Published

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_realised_ad_agg_3.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_retailer.retailer_id = fact_realised_ad_agg_3.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_catalog.catalog_id = fact_realised_ad_agg_3.catalog_id and dim_catalog.is_current;
dim_product on dim_product.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_product.catalog_id = fact_realised_ad_agg_3.catalog_id and dim_product.normalised_product_code = fact_realised_ad_agg_3.product_code;
dim_supplier on dim_supplier.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_supplier.supplier_id = fact_realised_ad_agg_3.supplier_id and dim_supplier.is_current;
dim_campaign on dim_campaign.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_campaign.campaign_id = fact_realised_ad_agg_3.campaign_id and dim_campaign.is_current;
dim_search_term on dim_search_term.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_search_term.retailer_id = fact_realised_ad_agg_3.retailer_id and dim_search_term.search_term = fact_realised_ad_agg_3.search_term;
dim_category on dim_category.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_category.category_id = fact_realised_ad_agg_3.category and dim_category.retailer_id = fact_realised_ad_agg_3.retailer_id;
dim_placement on dim_placement.env_namespace = fact_realised_ad_agg_3.env_namespace and dim_placement.placement = fact_realised_ad_agg_3.placement;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`TIMESTAMP`	Timestamp the Ads were realised.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`has_search_term`	`INTEGER`	Flag to indicate if the Ad is targeting a Search Term.
`has_category`	`INTEGER`	Flag to indicate if the Ad is targeting a Category.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`TIMESTAMP`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_3_live`

NAMESPACE

Realised Ad fact (aggregated into UTC+0 hourly buckets). Only for current + previous 2 Days.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`TIMESTAMP`	Timestamp the Ads were realised.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`has_search_term`	`INTEGER`	Flag to indicate if the Ad is targeting a Search Term.
`has_category`	`INTEGER`	Flag to indicate if the Ad is targeting a Category.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`TIMESTAMP`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_3_ltz`

NAMESPACE

Realised Ad fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`DATETIME`	Datetime the Ads were realised in local timezone.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`has_search_term`	`INTEGER`	Flag to indicate if the Ad is targeting a Search Term.
`has_category`	`INTEGER`	Flag to indicate if the Ad is targeting a Category.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`DATETIME`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_3_ltz_live`

NAMESPACE

Realised Ad fact (aggregated into local timezone hourly buckets). Only for current + previous 2 days.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`DATETIME`	Datetime the Ads were realised in local timezone.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`has_search_term`	`INTEGER`	Flag to indicate if the Ad is targeting a Search Term.
`has_category`	`INTEGER`	Flag to indicate if the Ad is targeting a Category.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`DATETIME`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_4`

NAMESPACE

Realised Ad fact (aggregated by month). Based on fact_realised_ad with keyword and keyword_matchtype.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`DATE`	Date the Ads were realised.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`keyword`	`STRING`	The selected Keyword which matched the user search term at ad generation
`keyword_match_type`	`STRING`	Method used in matching the keyword to the user search term i.e. EXACT, PHRASE, etc.
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`has_search_term`	`INTEGER`	Flag to indicate if the Ad is targeting a Search Term.
`has_category`	`INTEGER`	Flag to indicate if the Ad is targeting a Category.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`TIMESTAMP`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_4_ltz`

NAMESPACE

Based on fact_realised_ad with keyword and keyword_matchtype grouped in retailer specified local timezone.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`DATE`	Date (local time) the Ads were realised.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`keyword`	`STRING`	The selected Keyword which matched the user search term at ad generation
`keyword_match_type`	`STRING`	Method used in matching the keyword to the user search term i.e. EXACT, PHRASE, etc.
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`has_search_term`	`INTEGER`	Flag to indicate if the Ad is targeting a Search Term.
`has_category`	`INTEGER`	Flag to indicate if the Ad is targeting a Category.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`DATETIME`	Date the row was inserted into the source system.

`analytics.fact_realised_ad_agg_4a`

NAMESPACE

Realised Ad fact (aggregated by month). Based on fact_realised_ad with keyword and keyword_matchtype. Includes metrics aggregated at the search term level.

Layer: Core Dataset
Category: Fact
Status: Published

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_realised_ad_agg_4a.env_namespace;
dim_retailer on dim_retailer.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_retailer.retailer_id = fact_realised_ad_agg_4a.retailer_id and dim_retailer.is_current;
dim_catalog on dim_catalog.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_catalog.catalog_id = fact_realised_ad_agg_4a.catalog_id and dim_catalog.is_current;
dim_product on dim_product.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_product.catalog_id = fact_realised_ad_agg_4a.catalog_id and dim_product.normalised_product_code = fact_realised_ad_agg_4a.product_code;
dim_supplier on dim_supplier.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_supplier.supplier_id = fact_realised_ad_agg_4a.supplier_id and dim_supplier.is_current;
dim_campaign on dim_campaign.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_campaign.campaign_id = fact_realised_ad_agg_4a.campaign_id and dim_campaign.is_current;
dim_search_term on dim_search_term.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_search_term.retailer_id = fact_realised_ad_agg_4a.retailer_id and dim_search_term.search_term = fact_realised_ad_agg_4a.search_term;
dim_category on dim_category.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_category.category_id = fact_realised_ad_agg_4a.category and dim_category.retailer_id = fact_realised_ad_agg_4a.retailer_id;
dim_placement on dim_placement.env_namespace = fact_realised_ad_agg_4a.env_namespace and dim_placement.placement = fact_realised_ad_agg_4a.placement;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`DATE`	Date the Ads were realised.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`keyword`	`STRING`	The selected Keyword which matched the user search term at ad generation
`keyword_match_type`	`STRING`	Method used in matching the keyword to the user search term i.e. EXACT, PHRASE, etc.
`search_term_metrics`	`RECORD`	Metrics for each search term

`analytics.fact_realised_ad_agg_4a_ltz`

NAMESPACE

Realised Ad fact (aggregated by month). Based on fact_realised_ad with keyword and keyword_matchtype. Includes metrics aggregated at the search term level.
daily groups by retailer specified local timezone.

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`ingressed_at`	`DATE`	Date the Ads were realised.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`product_code`	`STRING`	Product code.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`target_product_code`	`STRING`	Target Product code.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`keyword`	`STRING`	The selected Keyword which matched the user search term at ad generation
`keyword_match_type`	`STRING`	Method used in matching the keyword to the user search term i.e. EXACT, PHRASE, etc.
`search_term_metrics`	`RECORD`	Metrics for each search term

`analytics.fact_realised_ad_agg_ltz`

NAMESPACE

Realised Ad fact (aggregated into local timezone day buckets).

Layer: Core Dataset
Category: Fact
Status: Published

Column Name	Data Type	Description
`realised_ad_agg_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`ingressed_at`	`DATE`	Date the Ads were realised in local timezone.
`namespace`	`STRING`	Name of the Namespace.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`search_term`	`STRING`	Search Term being targeted.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`product_code`	`STRING`	Product code.
`content_standard_id`	`STRING`	ContentStandardId as specified in the Ad Request.
`slot_id`	`STRING`	Banner SlotId as specified in the Ad Request.
`category`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`conversions`	`NUMERIC`	Total standard Conversions (purchases of the exact Product in the Ad).
`unit_sales`	`NUMERIC`	Total number of Products purchased from Conversions.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`impression_spend`	`NUMERIC`	AdSpend generated by impressions.
`click_spend`	`NUMERIC`	AdSpend generated by clicks.
`ad_revenue`	`NUMERIC`	Portion of Ad spend retained by the Retailer.
`sales_revenue`	`NUMERIC`	Total sales revenue generated by Conversions.
`target_product_code`	`STRING`	Target Product code.
`position_sum`	`NUMERIC`	Sum of all postions for the Ads. Used to calculate average ad position (avg position = position_sum / ads_with_position_count).
`ads_with_position_count`	`NUMERIC`	Total number of Ads that have a position set. avg position = position_sum / ads_with_position_count.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).
`ad_requester_team_id`	`STRING`	Internal identifier of the Team (from the source system).
`email_serve_revenue`	`NUMERIC`	Email serve revenue.
`invalid_ad_revenue`	`NUMERIC`	Total invalid revenue.
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`last_event_loaded_at`	`DATETIME`	Date the row was inserted into the source system.

`analytics.fact_report_request_tracking`

NAMESPACE

CitrusAd client reporting usage fact.

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: report_request_tracking_id
Other Unique Key(s): report_request_tracking_key
Partition: request_date (by DAY)

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_report_request_tracking.env_namespace;
dim_team on dim_team.env_namespace = fact_report_request_tracking.env_namespace and dim_team.team_id = fact_report_request_tracking.team_id and dim_team.is_current;
dim_user on dim_user.env_namespace = fact_report_request_tracking.env_namespace and dim_user.user_id = fact_report_request_tracking.user_id and dim_user.is_current;

Column Name	Data Type	Description
`report_request_tracking_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`report_request_tracking_id`	`STRING`	Internal identifier of the Report Request (from the source system).
`user_id`	`STRING`	Internal identifier of the User (from the source system).
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`report_type`	`STRING`	Type of report.
`cache_level`	`STRING`	Cache level.
`report_span`	`STRING`	Span of report.
`request_date`	`DATE`	Date of the report request.
`request_date_key`	`INTEGER`	Unique identifier of the row in this table (surrogate key).
`request_time`	`TIMESTAMP`	Request time.
`namespace`	`STRING`	Name of the Namespace.
`counts_towards_quota`	`BOOLEAN`	Flag indicating of usage counts towards quota.
`big_query_billed_bytes`	`NUMERIC`	Total bytes billed.
`big_query_execution_duration_seconds`	`NUMERIC`	Total duration of all queries.
`big_query_finished_polling_duration_seconds`	`NUMERIC`	Total polling duration of all queries.
`total_execution_duration_seconds`	`NUMERIC`	Total execution duration of the report.
`max_billed_bytes_per_day`	`NUMERIC`	Max billed bytes per day.
`max_billed_bytes_per_month`	`NUMERIC`	Max billed bytes per month.
`max_uncached_requests_per_day`	`NUMERIC`	Max uncached requests per day.
`max_uncached_requests_per_month`	`NUMERIC`	Max uncached requests per month.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.fact_user_team`

NAMESPACE

List of all Team Users.

Layer: Core Dataset
Category: Fact
Status: Published

Primary Key: user_id, team_id, env_namespace
Other Unique Key(s): user_team_key
Partition: None

Foreign Key(s):
dim_env_namespace on dim_env_namespace.env_namespace = fact_user_team.env_namespace;
dim_team on dim_team.env_namespace = fact_user_team.env_namespace and dim_team.team_id = fact_user_team.team_id and dim_team.is_current;
dim_user on dim_user.env_namespace = fact_user_team.env_namespace and dim_user.user_id = fact_user_team.user_id and dim_user.is_current;

Column Name	Data Type	Description
`user_team_key`	`STRING`	Unique identifier of the row in this table (surrogate key).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`user_id`	`STRING`	Internal identifier of the User (from the source system).
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`role_ids`	`STRING`	Role ids.
`last_edit_date`	`TIMESTAMP`	Date of the last edit.
`last_edit_date_utc`	`TIMESTAMP`	Currently the same as last_edit_date since all data is in UTC.
`etl_created_at`	`TIMESTAMP`	Date the row was inserted into this table (not the date the record was created in the source system).
`etl_modified_at`	`TIMESTAMP`	Date this row was last updated (not the date the record was updated in the source system).

`analytics.log_status`

Logging table. Records when this dataset has been updated (both start time and end time with status).

Layer: Core Dataset
Category: Logging
Status: Published

Primary Key: jobid, logging_datetime_utc, status
Other Unique Key(s): jobid
Partition: None

Foreign Key(s):
None

Column Name	Data Type	Description
`jobid`	`STRING`	Id for the Job being executed.
`logging_datetime_utc`	`DATETIME`	Date and time the Job log entry was made.
`job_name`	`STRING`	Label indicating what core dataset the Job is for.
`status`	`STRING`	Indicate if the Job is running or completed.
`return_code`	`STRING`	Indicate if the completed status was successful (0) or failed (1).

`reporting.ad_request__realised_ad_ltz_view`

NAMESPACE
RETAILER
SUPPLIER

Union of ad_request_agg_view and realised_ad_agg_view (local timezone). Allows for convenient calculation of measures that require both Request and Realised Ad statistics (for example a ratio of impressions vs ads requested).

Layer: Reporting Datamart
Category: Fact Decorated View
Status: Published

Primary Key: Refer to realised_ad_agg_view
Other Unique Key(s):
Partition: Refer to realised_ad_agg_view

Foreign Key(s):
Refer to view code

Column Name	Data Type	Description
`env`	`STRING`
`env_namespace`	`STRING`
`ingressed_at`	`DATE`
`ad_type`	`INTEGER`
`search_term`	`STRING`
`generic_search_term`	`STRING`
`reserved_search_term_flag`	`STRING`
`placement`	`STRING`
`platform`	`STRING`
`page_type`	`INTEGER`
`filter_mode`	`INTEGER`
`slot_id`	`STRING`
`category`	`STRING`
`catalog_id`	`STRING`
`catalog_name`	`STRING`
`retailer_id`	`STRING`
`retailer_name`	`STRING`
`retailer_industry`	`STRING`
`retailer_business_identifier`	`STRING`
`retailer_company_name`	`STRING`
`retailer_group_1`	`STRING`
`retailer_group_2`	`STRING`
`geo_region`	`STRING`
`local_timezone`	`STRING`
`has_search_term`	`INTEGER`
`has_category`	`INTEGER`
`num_ads_considered`	`NUMERIC`
`num_ads_served`	`NUMERIC`
`num_ads_requested`	`NUMERIC`
`capped_num_ads_served`	`NUMERIC`
`capped_num_ads_requested`	`NUMERIC`
`capped_num_requests_serving_max_ads`	`NUMERIC`
`num_requests_serving_max_ads`	`NUMERIC`
`num_ad_requests_serving_no_ads`	`NUMERIC`
`num_ad_requests_serving_some_ads`	`NUMERIC`
`num_ad_requests`	`NUMERIC`
`num_ad_requests_with_saturation`	`NUMERIC`
`campaign_id`	`STRING`
`campaign_name`	`STRING`
`supplier_id`	`STRING`
`supplier_name`	`STRING`
`supplier_industry`	`STRING`
`supplier_business_identifier`	`STRING`
`supplier_company_name`	`STRING`
`product_code`	`STRING`
`content_standard_id`	`STRING`
`target_product_code`	`STRING`
`supplier_group_1`	`STRING`
`supplier_group_2`	`STRING`
`supplier_group_3`	`STRING`
`supplier_group_4`	`STRING`
`sales_revenue`	`NUMERIC`
`ad_revenue`	`NUMERIC`
`ad_spend`	`NUMERIC`
`impressions`	`NUMERIC`
`clicks`	`NUMERIC`
`conversions`	`NUMERIC`
`unit_sales`	`NUMERIC`
`click_spend`	`NUMERIC`
`impression_spend`	`NUMERIC`
`position_sum`	`NUMERIC`
`sales_revenue_usd`	`FLOAT`
`ad_revenue_usd`	`FLOAT`
`ad_spend_usd`	`FLOAT`
`placement_id`	`STRING`
`wallet_id`	`STRING`

`reporting.ad_request__realised_ad_view`

NAMESPACE
RETAILER
SUPPLIER

Union of ad_request_agg_view and realised_ad_agg_view. Allows for convenient calculation of measures that require both Request and Realised Ad statistics (for example a ratio of impressions vs ads requested).

Layer: Reporting Datamart
Category: Fact Decorated View
Status: Published

Primary Key: Refer to realised_ad_agg_view
Other Unique Key(s):
Partition: Refer to realised_ad_agg_view

Foreign Key(s):
Refer to view code

Column Name	Data Type	Description
`env`	`STRING`
`env_namespace`	`STRING`
`ingressed_at`	`DATE`
`ad_type`	`INTEGER`
`search_term`	`STRING`
`generic_search_term`	`STRING`
`reserved_search_term_flag`	`STRING`
`placement`	`STRING`
`platform`	`STRING`
`page_type`	`INTEGER`
`filter_mode`	`INTEGER`
`slot_id`	`STRING`
`category`	`STRING`
`catalog_id`	`STRING`
`catalog_name`	`STRING`
`retailer_id`	`STRING`
`retailer_name`	`STRING`
`retailer_industry`	`STRING`
`retailer_business_identifier`	`STRING`
`retailer_company_name`	`STRING`
`retailer_group_1`	`STRING`
`retailer_group_2`	`STRING`
`geo_region`	`STRING`
`local_timezone`	`STRING`
`has_search_term`	`INTEGER`
`has_category`	`INTEGER`
`num_ads_considered`	`NUMERIC`
`num_ads_served`	`NUMERIC`
`num_ads_requested`	`NUMERIC`
`capped_num_ads_served`	`NUMERIC`
`capped_num_ads_requested`	`NUMERIC`
`capped_num_requests_serving_max_ads`	`NUMERIC`
`num_requests_serving_max_ads`	`NUMERIC`
`num_ad_requests_serving_no_ads`	`NUMERIC`
`num_ad_requests_serving_some_ads`	`NUMERIC`
`num_ad_requests`	`NUMERIC`
`num_ad_requests_with_saturation`	`NUMERIC`
`campaign_id`	`STRING`
`campaign_name`	`STRING`
`supplier_id`	`STRING`
`supplier_name`	`STRING`
`supplier_industry`	`STRING`
`supplier_business_identifier`	`STRING`
`supplier_company_name`	`STRING`
`product_code`	`STRING`
`content_standard_id`	`STRING`
`target_product_code`	`STRING`
`supplier_group_1`	`STRING`
`supplier_group_2`	`STRING`
`supplier_group_3`	`STRING`
`supplier_group_4`	`STRING`
`sales_revenue`	`NUMERIC`
`ad_revenue`	`NUMERIC`
`ad_spend`	`NUMERIC`
`impressions`	`NUMERIC`
`clicks`	`NUMERIC`
`conversions`	`NUMERIC`
`unit_sales`	`NUMERIC`
`click_spend`	`NUMERIC`
`impression_spend`	`NUMERIC`
`position_sum`	`NUMERIC`
`sales_revenue_usd`	`FLOAT`
`ad_revenue_usd`	`FLOAT`
`ad_spend_usd`	`FLOAT`
`placement_id`	`STRING`
`wallet_id`	`STRING`

`reporting.ad_request_agg`

NAMESPACE
RETAILER

Request data (aggregated).

Layer: Reporting Datamart
Category: Fact
Status: Published

Primary Key: env_namespace, ingressed_at, retailer_id, catalog_id, search_term, placement, platform, reserved_search_term_flag, generic_search_term, ad_type, page_type, filter_mode, slot_ids, category, has_search_term, has_category
Other Unique Key(s):
Partition: ingressed_at (by DAY)

Foreign Key(s):
env_namespace on env_namespace.env_namespace = ad_request_agg.env_namespace;
retailer on retailer.env_namespace = ad_request_agg.env_namespace and retailer.retailer_id = ad_request_agg.retailer_id;
catalog on catalog.env_namespace = ad_request_agg.env_namespace and catalog.catalog_id = ad_request_agg.catalog_id;
placement on placement.env_namespace = ad_request_agg.env_namespace and placement.placement_id = ad_request_agg.placement_id;
search_term on search_term.env_namespace = ad_request_agg.env_namespace and search_term.retailer_id = ad_request_agg.retailer_id and search_term.search_term = ad_request_agg.search_term;
category on category.env_namespace = ad_request_agg.env_namespace and category.category_id = ad_request_agg.category and category.retailer_id = ad_request_agg.retailer_id;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`ingressed_at`	`DATE`	Date the Requests were received.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`reserved_search_term_flag`	`STRING`	Not in use.
`generic_search_term`	`STRING`	Same as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
`ad_type`	`INTEGER`	Type of Ad (0 = Product, 1 = Banner, 4 = BannerX).
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`filter_mode`	`INTEGER`	The filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
`slot_ids`	`STRING`	Slot ids.
`category`	`STRING`	Category being targeted.
`has_search_term`	`INTEGER`	Flag to indicate if the Request is targeting a Search Term.
`has_category`	`INTEGER`	Flag to indicate if the Request is targeting a Category.
`retailer_group_1`	`STRING`	Retailer group membership #1.
`retailer_group_2`	`STRING`	Retailer group membership #2.
`num_ads_considered`	`NUMERIC`	Total number of Ad candidates that were considered by relevancy algorithms.
`num_ads_served`	`NUMERIC`	Total number of Ads that were served back.
`num_ads_requested`	`NUMERIC`	Total number of Ads that were requested by the Requests.
`capped_num_ads_served`	`NUMERIC`	Total number of Ads that were served back with Cap applied per Request.
`capped_num_ads_requested`	`NUMERIC`	Total number of Ads that were requested with Cap applied per Request.
`capped_num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the number of Ads requested was served in full.
`num_requests_serving_max_ads`	`NUMERIC`	Total number of Requests where the number of Ads requested was served in full.
`num_ad_requests_serving_no_ads`	`NUMERIC`	Total number of Requests where no Ads were served.
`num_ad_requests_serving_some_ads`	`NUMERIC`	Total number of Requests where at least 1 Ad was served.
`num_ad_requests`	`NUMERIC`	Total number of Requests.
`num_ad_requests_with_saturation`	`NUMERIC`	Total number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
`placement_id`	`STRING`	Internal identifier of the Placement (from the source system).
`namespace`	`STRING`	Name of the Namespace.

`reporting.ad_request_agg_ltz_view`

NAMESPACE
RETAILER

Request view (aggregated in local timezone). Decorated version of ad_request_agg.

Layer: Reporting Datamart
Category: Fact Decorated View
Status: Published

Primary Key: Refer to ad_request_agg
Other Unique Key(s):
Partition: Refer to ad_request_agg

Foreign Key(s):
Refer to view code

Column Name	Data Type	Description
`env`	`STRING`
`env_namespace`	`STRING`
`ingressed_at`	`DATE`
`ad_type`	`INTEGER`
`search_term`	`STRING`
`generic_search_term`	`STRING`
`reserved_search_term_flag`	`STRING`
`placement`	`STRING`
`platform`	`STRING`
`page_type`	`INTEGER`
`filter_mode`	`INTEGER`
`slot_id`	`STRING`
`category`	`STRING`
`catalog_id`	`STRING`
`catalog_name`	`STRING`
`retailer_id`	`STRING`
`retailer_name`	`STRING`
`retailer_industry`	`STRING`
`retailer_business_identifier`	`STRING`
`retailer_company_name`	`STRING`
`retailer_group_1`	`STRING`
`retailer_group_2`	`STRING`
`geo_region`	`STRING`
`local_timezone`	`STRING`
`has_search_term`	`INTEGER`
`has_category`	`INTEGER`
`namespace`	`STRING`
`ad_type_name`	`STRING`
`num_ads_considered`	`NUMERIC`
`num_ads_served`	`NUMERIC`
`num_ads_requested`	`NUMERIC`
`capped_num_ads_served`	`NUMERIC`
`capped_num_ads_requested`	`NUMERIC`
`capped_num_requests_serving_max_ads`	`NUMERIC`
`num_requests_serving_max_ads`	`NUMERIC`
`num_ad_requests_serving_no_ads`	`NUMERIC`
`num_ad_requests_serving_some_ads`	`NUMERIC`
`num_ad_requests`	`NUMERIC`
`num_ad_requests_with_saturation`	`NUMERIC`
`placement_id`	`STRING`

`reporting.ad_request_agg_view`

NAMESPACE
RETAILER

Request view (aggregated). Decorated version of ad_request_agg.

Layer: Reporting Datamart
Category: Fact Decorated View
Status: Published

Primary Key: Refer to ad_request_agg
Other Unique Key(s):
Partition: Refer to ad_request_agg

Foreign Key(s):
Refer to view code

Column Name	Data Type	Description
`env`	`STRING`
`env_namespace`	`STRING`
`ingressed_at`	`DATE`
`ad_type`	`INTEGER`
`search_term`	`STRING`
`generic_search_term`	`STRING`
`reserved_search_term_flag`	`STRING`
`placement`	`STRING`
`platform`	`STRING`
`page_type`	`INTEGER`
`filter_mode`	`INTEGER`
`slot_id`	`STRING`
`category`	`STRING`
`catalog_id`	`STRING`
`catalog_name`	`STRING`
`retailer_id`	`STRING`
`retailer_name`	`STRING`
`retailer_industry`	`STRING`
`retailer_business_identifier`	`STRING`
`retailer_company_name`	`STRING`
`retailer_group_1`	`STRING`
`retailer_group_2`	`STRING`
`geo_region`	`STRING`
`local_timezone`	`STRING`
`has_search_term`	`INTEGER`
`has_category`	`INTEGER`
`namespace`	`STRING`
`ad_type_name`	`STRING`
`num_ads_considered`	`NUMERIC`
`num_ads_served`	`NUMERIC`
`num_ads_requested`	`NUMERIC`
`capped_num_ads_served`	`NUMERIC`
`capped_num_ads_requested`	`NUMERIC`
`capped_num_requests_serving_max_ads`	`NUMERIC`
`num_requests_serving_max_ads`	`NUMERIC`
`num_ad_requests_serving_no_ads`	`NUMERIC`
`num_ad_requests_serving_some_ads`	`NUMERIC`
`num_ad_requests`	`NUMERIC`
`num_ad_requests_with_saturation`	`NUMERIC`
`placement_id`	`STRING`

`reporting.campaign`

NAMESPACE
RETAILER
SUPPLIER

Campaign data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

Primary Key: campaign_id, env_namespace
Other Unique Key(s):
Partition: None

Foreign Key(s):
env_namespace on env_namespace.env_namespace = campaign.env_namespace;
retailer on retailer.env_namespace = campaign.env_namespace and retailer.retailer_id = campaign.retailer_id;
supplier on supplier.env_namespace = campaign.env_namespace and supplier.supplier_id = campaign.supplier_id;
wallet on wallet.env_namespace = campaign.env_namespace and wallet.wallet_id = campaign.wallet_id;

Column Name	Data Type	Description
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`campaign_name`	`STRING`	Name of the Campaign.
`campaign_type`	`STRING`	Type of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
`campaign_subtype`	`STRING`	Legacy, superseded by placement. Subtype of the Campaign (SEARCH_ONLY, CATEGORY_ONLY, CATEGORY_AND_SEARCH, BROAD_DISPLAY, CROSS_SELL, UPSELL, RUN_OF_SITE).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`retailer_name`	`STRING`	Name of the Retailer.
`supplier_name`	`STRING`	Name of the Supplier.
`wallet_id`	`STRING`	Internal identifier of the Wallet (from the source system).
`is_fta`	`STRING`	Flag indicating if the Campaign is Fixed Tenancy Agreement (FTA).
`fta_cost`	`NUMERIC`	Total amount being spent by the given Fixed Tenancy Agreement (FTA) Campaign.
`wallet_name`	`STRING`	Name of the Wallet.
`wallet_external_id`	`STRING`	Implementation specific generic label.
`wallet_archived`	`BOOLEAN`	Archived status of the Wallet.
`wallet_credit_limit`	`NUMERIC`	Amount the Wallet may go into credit.
`wallet_currency_code`	`STRING`	ISO 4217 currency code
`wallet_total_purchased_credits`	`NUMERIC`	Total credits made to the Wallet.
`wallet_available_balance`	`NUMERIC`	Current available balance of the Wallet.
`wallet_credit_limit_usd`	`NUMERIC`	wallet_credit_limit in USD (current exchange rate only).
`wallet_available_balance_usd`	`NUMERIC`	wallet_available_balance in USD (current exchange rate only).
`wallet_total_purchased_credits_usd`	`NUMERIC`	wallet_total_purchased_credits in USD (current exchange rate only).
`campaign_start_date`	`TIMESTAMP`	Date the Campaign will begin.
`campaign_end_date`	`TIMESTAMP`	Date when the Campaign will end.
`campaign_valid_state`	`STRING`	The Campaign's valid state. (APPROVED, PENDING, REJECTED)
`campaign_active_state`	`STRING`	The Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
`campaign_created_at`	`TIMESTAMP`	Date the record was created in the source system.
`campaign_updated_at`	`TIMESTAMP`	Date the record was updated in the source system.
`campaign_placements`	`STRING`	The Campaign's selected Placement.
`campaign_total_spend`	`NUMERIC`	Total AdSpend to date for the Campaign.
`campaign_total_spend_usd`	`NUMERIC`	campaign_total_spend in USD (current exchange rate only).
`campaign_max_cost_per_click`	`NUMERIC`	The maximum cost per click set for the Campaign.
`campaign_max_total_spend`	`NUMERIC`	The maximum total spend set for the Campaign.
`campaign_max_daily_spend`	`NUMERIC`	The maximum daily spend set for the Campaign.
`campaign_impression_goal`	`NUMERIC`	The maximum number of impressions a Campaign is aiming for. Only relevant for campaigns with a spend type of Fixed Spend.
`campaign_catalog_products`	`STRING`	The Catalog's Products within the Campaign.
`campaign_attr_1`	`STRING`	Campaign user defined attribute 1.
`retailer_group_1`	`STRING`	Retailer group membership #1.
`retailer_group_2`	`STRING`	Retailer group membership #2.
`supplier_group_1`	`STRING`	Supplier team group membership #1.
`supplier_group_2`	`STRING`	Supplier team group membership #2.
`supplier_group_3`	`STRING`	Supplier team group membership #3.
`supplier_group_4`	`STRING`	Supplier team group membership #4.
`team_id`	`STRING`	Internal identifier of the Team (from the source system).
`retailer_ad_spend_type`	`STRING`	Type of spending if Retailer Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
`supplier_ad_spend_type`	`STRING`	Type of spending if Supplier Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
`budget`	`NUMERIC`	The Campaign's budget as set by the Campaign's manager.
`targeted_filters`	`STRING`	The filters selected by an advertiser that their campaign will appear for. Used for fixed tenancy campaigns.
`location_filters`	`STRING`	Filters on the campaign specific to a location if a location filter class is created.
`filter_whitelists`	`STRING`	The filters selected by an advertiser that the campaign can appear for. The campaign will only appear where eligible regardless of the advertiser's selection.
`cross_sell_settings`	`STRING`	The cross-sell targeting selection in the campaign.
`upsell_settings`	`STRING`	The Upsell targeting selection in the campaign if applicable.
`categories`	`STRING`	Comma separated list of Categories targeted by the Campaign.
`catalog_count`	`INTEGER`	Number of Catalogs being targeted by the Campaign.
`catalog_ids`	`STRING`	Catalog ids.
`search_terms`	`STRING`	Comma separated list of SearchTerms targeted by the Campaign.
`has_search_terms`	`BOOLEAN`	Flag to indicate if the Campaign is targeting SearchTerms.
`last_spend_date`	`DATE`	Most recent date that AdSpend was incurred by the Supplier.
`geo_region`	`STRING`	Geographical region of the Namespace (as defined by CitrusAd).
`local_timezone`	`STRING`	Not in use.
`product_type_code`	`STRING`	Product type code.
`cta_text`	`STRING`	CTA text.
`suggested_search_terms`	`STRING`	Suggested search terms.
`has_suggested_search_terms`	`BOOLEAN`	Flag to indicate that any suggested search terms exist.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.
`active_state`	`STRING`	The Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
`valid_state`	`STRING`	The Campaign's valid state. (APPROVED, PENDING, REJECTED)
`namespace`	`STRING`	Name of the Namespace.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`currency_code`	`STRING`	ISO 4217 currency code

`reporting.catalog`

NAMESPACE
RETAILER

Catalog data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

Primary Key: catalog_id, env_namespace
Other Unique Key(s):
Partition: None

Foreign Key(s):
env_namespace on env_namespace.env_namespace = catalog.env_namespace;
retailer on retailer.env_namespace = catalog.env_namespace and retailer.retailer_id = catalog.retailer_id;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`catalog_name`	`STRING`	Name of the Catalog.
`retailer_name`	`STRING`	Name of the Retailer.
`currency_code`	`STRING`	ISO 4217 currency code
`namespace`	`STRING`	Name of the Namespace.
`retailer_group_1`	`STRING`	Retailer team group membership #1.
`retailer_group_2`	`STRING`	Retailer team group membership #2.
`geo_region`	`STRING`	Geographical region of the Namespace (as defined by CitrusAd).
`local_timezone`	`STRING`	Not in use.
`is_active`	`BOOLEAN`	Flag to indicate that the record is active in the source system.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`is_current`	`BOOLEAN`	Flag if the row is the latest version in the source system.

`reporting.category`

NAMESPACE
RETAILER

Category data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

Primary Key: env_namespace, retailer_id, category_id
Other Unique Key(s):
Partition: None

Foreign Key(s):
env_namespace on env_namespace.env_namespace = category.env_namespace;
retailer on retailer.env_namespace = category.env_namespace and retailer.retailer_id = category.retailer_id;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`category_id`	`STRING`	Category being targeted.
`namespace`	`STRING`	Name of the Namespace.
`category_label`	`STRING`	Label for the Category (more descriptive than the typical id provided).
`cat_level_1`	`STRING`	Level 1 component of the category_label (delimited by >).
`cat_level_2`	`STRING`	Level 2 component of the category_label (delimited by >).
`cat_level_3`	`STRING`	Level 3 component of the category_label (delimited by >).
`cat_level_4`	`STRING`	Level 4 component of the category_label (delimited by >).
`cat_level_5`	`STRING`	Level 5 component of the category_label (delimited by >).
`cat_level_6`	`STRING`	Level 6 component of the category_label (delimited by >).
`cat_level_7`	`STRING`	Level 7 component of the category_label (delimited by >).
`category_label_count`	`INTEGER`	Count(category_label) across env_namespace, retailer_id, category_id.
`source_name`	`STRING`	Source name.
`retailer_group_1`	`STRING`	Retailer group membership #1.
`retailer_group_2`	`STRING`	Retailer group membership #2.
`retailer_name`	`STRING`	Name of the Retailer.

`reporting.currency`

Current conversion rates between the base currency (AUD and USD) to other common currencies. ISO 4217 currency codes used.

Layer: Reporting Datamart
Category: Dimension
Status: Published

Primary Key: currency,base
Other Unique Key(s):
Partition: None

Foreign Key(s):
None

Column Name	Data Type	Description
`currency`	`STRING`	Currency being converted (ISO 4217 currency code).
`value`	`FLOAT`	Conversion rate to apply.
`base`	`STRING`	Currency being converted into.

`reporting.deduped_product`

NAMESPACE
RETAILER

Deduped product data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

Primary Key: env_namespace, normalised_product_code
Other Unique Key(s):
Partition: None

Foreign Key(s):
env_namespace on env_namespace.env_namespace = deduped_product.env_namespace;
catalog on catalog.env_namespace = deduped_product.env_namespace and catalog.catalog_id = deduped_product.catalog_id;
retailer on retailer.env_namespace = deduped_product.env_namespace and retailer.retailer_id = deduped_product.retailer_id;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`catalog_id`	`STRING`	Internal identifier of the Catalog (from the source system).
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`product_name`	`STRING`	Name of the Product.
`product_type`	`STRING`	Type of the Product.
`retailer_name`	`STRING`	Name of the Retailer.
`currency_code`	`STRING`	ISO 4217 currency code
`product_tags`	`STRING`	Tags associated with the Product.
`product_image_url`	`STRING`	URL to an image of the Product for the Ad.
`brand`	`STRING`	Brand of the Product.
`google_product_taxonomy`	`STRING`	Google taxonomy (product hierarchy) of the Product. Levels of the hierarchy delimited using > symbol.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`namespace`	`STRING`	Name of the Namespace.
`is_deleted`	`BOOLEAN`	Flag to indicate that the record has been soft deleted in the source system.
`retailer_group_1`	`STRING`	Retailer group membership #1.
`retailer_group_2`	`STRING`	Retailer group membership #2.

`reporting.enhanced_attribution_agg`

NAMESPACE
RETAILER
SUPPLIER

Enhanced Attribution data (aggregated, both models).

Layer: Reporting Datamart
Category: Fact
Status: Published

Primary Key: env_namespace, campaign_id, category_id, normalised_product_code, product_code, product_name, brand, retailer_taxonomy, halo_taxonomy, halo_taxonomy_level, order_date, ingressed_at, search_term, placement, platform, page_type, attribution_model, retailer_id, order_type
Other Unique Key(s):
Partition: ingressed_at (by DAY)

Foreign Key(s):
env_namespace on env_namespace.env_namespace = enhanced_attribution_agg.env_namespace;
retailer on retailer.env_namespace = enhanced_attribution_agg.env_namespace and retailer.retailer_id = enhanced_attribution_agg.retailer_id;
campaign on campaign.env_namespace = enhanced_attribution_agg.env_namespace and campaign.campaign_id = enhanced_attribution_agg.campaign_id;
product on product.env_namespace = enhanced_attribution_agg.env_namespace and product.product_code = enhanced_attribution_agg.product_code;
search_term on search_term.env_namespace = enhanced_attribution_agg.env_namespace and search_term.retailer_id = enhanced_attribution_agg.retailer_id and search_term.search_term = enhanced_attribution_agg.search_term;
category on category.env_namespace = enhanced_attribution_agg.env_namespace and category.category_id = enhanced_attribution_agg.category and category.retailer_id = enhanced_attribution_agg.retailer_id;
placement on placement.env_namespace = enhanced_attribution_agg.env_namespace and placement.placement = enhanced_attribution_agg.placement;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`ingressed_at`	`DATE`	Date the Ads were realised.
`yyyymmdd`	`STRING`	Year+month+day of ingressed_at.
`yyyymm`	`STRING`	Year+month of ingressed_at.
`yyyy`	`STRING`	Year of ingressed_at.
`week_start_date`	`DATE`	First day of the week of the ingressed_at date.
`order_date`	`DATE`	Date of the Orders.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`product_name`	`STRING`	Name of the Product.
`brand`	`STRING`	Brand of the Product.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`halo_taxonomy`	`STRING`	The portion of the retailer_taxonomy relevant for the given halo_taxonomy_level. Note for halo_taxonomy_level=1 it is null since the Brand is level 1.
`halo_taxonomy_level`	`INTEGER`	Halo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`category_id`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`attribution_model`	`INTEGER`	Indicate if data is from first or second model.
`order_type`	`STRING`	Type of the Order.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`direct_unit_sales`	`NUMERIC`	Unit sales from CitrusAd platform Attributions.
`direct_sales_value`	`NUMERIC`	Revenue from CitrusAd platform Attributions.
`direct_conversions`	`NUMERIC`	Conversions from CitrusAd platform Attributions.
`view_through_unit_sales`	`NUMERIC`	Unit sales from Impression View Thru Attributions.
`view_through_sales_value`	`NUMERIC`	Revenue from Impression View Thru Attributions.
`view_through_conversions`	`NUMERIC`	Conversions from Impression View Thru Attributions.
`halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`total_click_sales`	`NUMERIC`	Direct unit sales + halo unit sales.
`total_click_sales_value`	`NUMERIC`	Direct sales value + halo sales value.
`total_unit_sales`	`NUMERIC`	Total click sales + view through unit sales.
`total_sales_value`	`NUMERIC`	Total click sales value + view through sales value.
`total_conversions`	`NUMERIC`	Total click conversions + view through conversions.
`retailer_group_1`	`STRING`	Retailer team group membership #1.
`retailer_group_2`	`STRING`	Retailer team group membership #2.
`supplier_group_1`	`STRING`	Supplier team group membership #1.
`supplier_group_2`	`STRING`	Supplier team group membership #2.
`supplier_group_3`	`STRING`	Supplier team group membership #3.
`supplier_group_4`	`STRING`	Supplier team group membership #4.
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`enable_sales_metrics`	`BOOLEAN`	Used to set sales metrics to 0 for certain namespace/campaign types if required for reporting
`catalog_id`	`STRING`
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo impression Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo impression Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo impression Attributions.

`reporting.enhanced_attribution_agg_ltz_view`

NAMESPACE
RETAILER
SUPPLIER

Enhanced Attribution view (aggregated, both models in local timezone). Decorated version of enhanced_attribution_agg.

Layer: Reporting Datamart
Category: Fact Decorated View
Status: Published

Primary Key: Refer to enhanced_attribution_agg
Other Unique Key(s):
Partition: Refer to enhanced_attribution_agg

Foreign Key(s):
Refer to view code

Column Name	Data Type	Description
`env`	`STRING`
`namespace`	`STRING`
`env_namespace`	`STRING`
`ingressed_at`	`DATE`
`yyyymmdd`	`STRING`
`yyyymm`	`STRING`
`yyyy`	`STRING`
`week_start_date`	`DATE`
`order_date`	`DATE`
`normalised_product_code`	`STRING`
`product_code`	`STRING`
`product_name`	`STRING`
`brand`	`STRING`
`retailer_taxonomy`	`STRING`
`halo_taxonomy`	`STRING`
`halo_taxonomy_level`	`INTEGER`
`campaign_id`	`STRING`
`search_term`	`STRING`
`placement`	`STRING`
`platform`	`STRING`
`page_type`	`INTEGER`
`attribution_model`	`INTEGER`
`order_type`	`STRING`
`impressions`	`NUMERIC`
`clicks`	`NUMERIC`
`ad_spend`	`NUMERIC`
`direct_unit_sales`	`NUMERIC`
`direct_sales_value`	`NUMERIC`
`direct_conversions`	`NUMERIC`
`view_through_unit_sales`	`NUMERIC`
`view_through_sales_value`	`NUMERIC`
`view_through_conversions`	`NUMERIC`
`halo_unit_sales`	`NUMERIC`
`halo_sales_value`	`NUMERIC`
`halo_conversions`	`NUMERIC`
`total_click_sales`	`NUMERIC`
`total_click_sales_value`	`NUMERIC`
`total_unit_sales`	`NUMERIC`
`total_sales_value`	`NUMERIC`
`total_conversions`	`NUMERIC`
`retailer_group_1`	`STRING`
`retailer_group_2`	`STRING`
`supplier_group_1`	`STRING`
`supplier_group_2`	`STRING`
`supplier_group_3`	`STRING`
`supplier_group_4`	`STRING`
`enable_sales_metrics`	`BOOLEAN`
`category_id`	`STRING`
`campaign_name`	`STRING`
`campaign_start_date`	`TIMESTAMP`
`campaign_end_date`	`TIMESTAMP`
`campaign_type`	`STRING`
`campaign_subtype`	`STRING`
`wallet_id`	`STRING`
`active_state`	`STRING`
`valid_state`	`STRING`
`cta_text`	`STRING`
`supplier_id`	`STRING`
`retailer_id`	`STRING`
`retailer_name`	`STRING`
`team_type`	`STRING`
`company_name`	`STRING`
`supplier_name`	`STRING`
`category_name`	`STRING`
`wallet_name`	`STRING`
`wallet_available_balance`	`NUMERIC`
`supplier_company_name`	`STRING`
`wallet_capture_date`	`DATE`
`geo_region`	`STRING`
`local_timezone`	`STRING`
`has_search_term`	`INTEGER`
`has_category`	`INTEGER`
`catalog_id`	`STRING`
`catalog_name`	`STRING`
`enhanced_attribution_flag`	`STRING`
`enhanced_attribution_flag_2`	`STRING`
`retailer_taxonomy_halo`	`STRING`
`retailer_taxonomy_halo_2`	`STRING`
`view_attribution_window`	`NUMERIC`
`view_attribution_window_2`	`NUMERIC`
`click_attribution_window`	`NUMERIC`
`click_attribution_window_2`	`NUMERIC`
`attribution_time_frame`	`NUMERIC`
`attribution_time_frame_2`	`NUMERIC`
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo impression Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo impression Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo impression Attributions.

`reporting.enhanced_attribution_agg_view`

NAMESPACE
RETAILER
SUPPLIER

Enhanced Attribution view (aggregated, both models). Decorated version of enhanced_attribution_agg.

Layer: Reporting Datamart
Category: Fact Decorated View
Status: Published

Primary Key: Refer to enhanced_attribution_agg
Other Unique Key(s):
Partition: Refer to enhanced_attribution_agg

Foreign Key(s):
Refer to view code

Column Name	Data Type	Description
`env`	`STRING`
`namespace`	`STRING`
`env_namespace`	`STRING`
`ingressed_at`	`DATE`
`yyyymmdd`	`STRING`
`yyyymm`	`STRING`
`yyyy`	`STRING`
`week_start_date`	`DATE`
`order_date`	`DATE`
`normalised_product_code`	`STRING`
`product_code`	`STRING`
`product_name`	`STRING`
`brand`	`STRING`
`retailer_taxonomy`	`STRING`
`halo_taxonomy`	`STRING`
`halo_taxonomy_level`	`INTEGER`
`campaign_id`	`STRING`
`search_term`	`STRING`
`placement`	`STRING`
`platform`	`STRING`
`page_type`	`INTEGER`
`attribution_model`	`INTEGER`
`order_type`	`STRING`
`impressions`	`NUMERIC`
`clicks`	`NUMERIC`
`ad_spend`	`NUMERIC`
`direct_unit_sales`	`NUMERIC`
`direct_sales_value`	`NUMERIC`
`direct_conversions`	`NUMERIC`
`view_through_unit_sales`	`NUMERIC`
`view_through_sales_value`	`NUMERIC`
`view_through_conversions`	`NUMERIC`
`halo_unit_sales`	`NUMERIC`
`halo_sales_value`	`NUMERIC`
`halo_conversions`	`NUMERIC`
`total_click_sales`	`NUMERIC`
`total_click_sales_value`	`NUMERIC`
`total_unit_sales`	`NUMERIC`
`total_sales_value`	`NUMERIC`
`total_conversions`	`NUMERIC`
`retailer_group_1`	`STRING`
`retailer_group_2`	`STRING`
`supplier_group_1`	`STRING`
`supplier_group_2`	`STRING`
`supplier_group_3`	`STRING`
`supplier_group_4`	`STRING`
`enable_sales_metrics`	`BOOLEAN`
`category_id`	`STRING`
`campaign_name`	`STRING`
`campaign_start_date`	`TIMESTAMP`
`campaign_end_date`	`TIMESTAMP`
`campaign_type`	`STRING`
`campaign_subtype`	`STRING`
`wallet_id`	`STRING`
`active_state`	`STRING`
`valid_state`	`STRING`
`cta_text`	`STRING`
`supplier_id`	`STRING`
`retailer_id`	`STRING`
`retailer_name`	`STRING`
`team_type`	`STRING`
`company_name`	`STRING`
`supplier_name`	`STRING`
`category_name`	`STRING`
`wallet_name`	`STRING`
`wallet_available_balance`	`NUMERIC`
`supplier_company_name`	`STRING`
`wallet_capture_date`	`DATE`
`geo_region`	`STRING`
`local_timezone`	`STRING`
`has_search_term`	`INTEGER`
`has_category`	`INTEGER`
`catalog_id`	`STRING`
`catalog_name`	`STRING`
`enhanced_attribution_flag`	`STRING`
`enhanced_attribution_flag_2`	`STRING`
`retailer_taxonomy_halo`	`STRING`
`retailer_taxonomy_halo_2`	`STRING`
`view_attribution_window`	`NUMERIC`
`view_attribution_window_2`	`NUMERIC`
`click_attribution_window`	`NUMERIC`
`click_attribution_window_2`	`NUMERIC`
`attribution_time_frame`	`NUMERIC`
`attribution_time_frame_2`	`NUMERIC`
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo impression Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo impression Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo impression Attributions.

`reporting.env_namespace`

NAMESPACE

Namespace data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

Primary Key: env_namespace
Other Unique Key(s):
Partition: None

Foreign Key(s):
None

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`geo_region`	`STRING`	Geographical region of the Namespace (as defined by CitrusAd).
`local_timezone`	`STRING`	Not in use.
`zone_id`	`STRING`	Timezone (TZ) identifier standardised by IANA time zone database.
`banner_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on banner campaigns.
`display_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on display campaigns.
`wildcard_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on wildcard campaigns.
`brand_page_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on brand page campaigns.
`shotgun_banner_enable_sales_metrics`	`BOOLEAN`	Flag to indicate if sales metrics are enabled on shotgun banner campaigns.

`reporting.external_attribution_agg`

NAMESPACE
RETAILER
SUPPLIER

external Attribution data (aggregated, both models).

Layer: Reporting Datamart
Category: Fact
Status: Published

Primary Key: env_namespace, campaign_id, category_id, normalised_product_code, product_code, product_name, brand, retailer_taxonomy, halo_taxonomy, halo_taxonomy_level, order_date, ingressed_at, search_term, placement, platform, page_type, attribution_model, retailer_id, order_type
Other Unique Key(s):
Partition: ingressed_at (by DAY)

Foreign Key(s):
env_namespace on env_namespace.env_namespace = external_attribution_agg.env_namespace;
retailer on retailer.env_namespace = external_attribution_agg.env_namespace and retailer.retailer_id = external_attribution_agg.retailer_id;
campaign on campaign.env_namespace = external_attribution_agg.env_namespace and campaign.campaign_id = external_attribution_agg.campaign_id;
product on product.env_namespace = external_attribution_agg.env_namespace and product.product_code = external_attribution_agg.product_code;
search_term on search_term.env_namespace = external_attribution_agg.env_namespace and search_term.retailer_id = external_attribution_agg.retailer_id and search_term.search_term = external_attribution_agg.search_term;
category on category.env_namespace = external_attribution_agg.env_namespace and category.category_id = external_attribution_agg.category and category.retailer_id = external_attribution_agg.retailer_id;
placement on placement.env_namespace = external_attribution_agg.env_namespace and placement.placement = external_attribution_agg.placement;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`ingressed_at`	`DATE`	Date the Ads were realised.
`yyyymmdd`	`STRING`	Year+month+day of ingressed_at.
`yyyymm`	`STRING`	Year+month of ingressed_at.
`yyyy`	`STRING`	Year of ingressed_at.
`week_start_date`	`DATE`	First day of the week of the ingressed_at date.
`order_date`	`DATE`	Date of the Orders.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`product_name`	`STRING`	Name of the Product.
`brand`	`STRING`	Brand of the Product.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`halo_taxonomy`	`STRING`	The portion of the retailer_taxonomy relevant for the given halo_taxonomy_level. Note for halo_taxonomy_level=1 it is null since the Brand is level 1.
`halo_taxonomy_level`	`INTEGER`	Halo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`category_id`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`attribution_model`	`INTEGER`	Indicate if data is from first or second model.
`order_type`	`STRING`	Type of the Order.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`direct_unit_sales`	`NUMERIC`	Unit sales from CitrusAd platform Attributions.
`direct_sales_value`	`NUMERIC`	Revenue from CitrusAd platform Attributions.
`direct_conversions`	`NUMERIC`	Conversions from CitrusAd platform Attributions.
`view_through_unit_sales`	`NUMERIC`	Unit sales from Impression View Thru Attributions.
`view_through_sales_value`	`NUMERIC`	Revenue from Impression View Thru Attributions.
`view_through_conversions`	`NUMERIC`	Conversions from Impression View Thru Attributions.
`halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`total_click_sales`	`NUMERIC`	Direct unit sales + halo unit sales.
`total_click_sales_value`	`NUMERIC`	Direct sales value + halo sales value.
`total_unit_sales`	`NUMERIC`	Total click sales + view through unit sales.
`total_sales_value`	`NUMERIC`	Total click sales value + view through sales value.
`total_conversions`	`NUMERIC`	Total click conversions + view through conversions.
`imp_halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`imp_halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`imp_halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`retailer_group_1`	`STRING`	Retailer team group membership #1.
`retailer_group_2`	`STRING`	Retailer team group membership #2.
`supplier_group_1`	`STRING`	Supplier team group membership #1.
`supplier_group_2`	`STRING`	Supplier team group membership #2.
`supplier_group_3`	`STRING`	Supplier team group membership #3.
`supplier_group_4`	`STRING`	Supplier team group membership #4.
`supplier_id`	`STRING`	Internal identifier of the Supplier team (from the source system).
`enable_sales_metrics`	`BOOLEAN`	Used to set sales metrics to 0 for certain namespace/campaign types if required for reporting
`catalog_id`	`STRING`

`reporting.external_attribution_agg_ltz`

NAMESPACE
RETAILER
SUPPLIER

external Attribution data (aggregated, both models) LTZ Version.

Layer: Reporting Datamart
Category: Fact
Status: Published

Primary Key: env_namespace, campaign_id, category_id, normalised_product_code, product_code, product_name, brand, retailer_taxonomy, halo_taxonomy, halo_taxonomy_level, order_date, ingressed_at, search_term, placement, platform, page_type, attribution_model, retailer_id, order_type
Other Unique Key(s):
Partition: ingressed_at (by DAY)

Foreign Key(s):
env_namespace on env_namespace.env_namespace = external_attribution_agg_ltz.env_namespace;
retailer on retailer.env_namespace = external_attribution_agg_ltz.env_namespace and retailer.retailer_id = external_attribution_agg_ltz.retailer_id;
campaign on campaign.env_namespace = external_attribution_agg_ltz.env_namespace and campaign.campaign_id = external_attribution_agg_ltz.campaign_id;
product on product.env_namespace = external_attribution_agg_ltz.env_namespace and product.product_code = external_attribution_agg_ltz.product_code;
search_term on search_term.env_namespace = external_attribution_agg_ltz.env_namespace and search_term.retailer_id = external_attribution_agg_ltz.retailer_id and search_term.search_term = external_attribution_agg_ltz.search_term;
category on category.env_namespace = external_attribution_agg_ltz.env_namespace and category.category_id = external_attribution_agg_ltz.category and category.retailer_id = external_attribution_agg_ltz.retailer_id;
placement on placement.env_namespace = external_attribution_agg_ltz.env_namespace and placement.placement = external_attribution_agg_ltz.placement;

Column Name	Data Type	Description
`env`	`STRING`	Environment the Namespace is deployed in.
`namespace`	`STRING`	Name of the Namespace.
`env_namespace`	`STRING`	Namespace prefixed with the Environment it is deployed in.
`ingressed_at`	`DATE`	Date the Ads were realised.
`yyyymmdd`	`STRING`	Year+month+day of ingressed_at.
`yyyymm`	`STRING`	Year+month of ingressed_at.
`yyyy`	`STRING`	Year of ingressed_at.
`week_start_date`	`DATE`	First day of the week of the ingressed_at date.
`order_date`	`DATE`	Date of the Orders.
`normalised_product_code`	`STRING`	Product code prefixed with leading zeros to be 16 characters long.
`product_code`	`STRING`	Product code.
`product_name`	`STRING`	Name of the Product.
`brand`	`STRING`	Brand of the Product.
`retailer_taxonomy`	`STRING`	Taxonomy (product hierarchy) of the Product as specified by the Retailer.
`halo_taxonomy`	`STRING`	The portion of the retailer_taxonomy relevant for the given halo_taxonomy_level. Note for halo_taxonomy_level=1 it is null since the Brand is level 1.
`halo_taxonomy_level`	`INTEGER`	Halo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
`campaign_id`	`STRING`	Internal identifier of the Campaign (from the source system).
`search_term`	`STRING`	Search Term being targeted.
`category_id`	`STRING`	Category being targeted.
`placement`	`STRING`	Display name of the Placement.
`platform`	`STRING`	The platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
`page_type`	`INTEGER`	Legacy, superseded by Placement.
`attribution_model`	`INTEGER`	Indicate if data is from first or second model.
`order_type`	`STRING`	Type of the Order.
`retailer_id`	`STRING`	Internal identifier of the Retailer team (from the source system).
`impressions`	`NUMERIC`	Total impressions.
`clicks`	`NUMERIC`	Number of Clicks.
`ad_spend`	`NUMERIC`	Local currency amount charged by the Retailer to the Supplier for the Realised Ads.
`direct_unit_sales`	`NUMERIC`	Unit sales from CitrusAd platform Attributions.
`direct_sales_value`	`NUMERIC`	Revenue from CitrusAd platform Attributions.
`direct_conversions`	`NUMERIC`	Conversions from CitrusAd platform Attributions.
`view_through_unit_sales`	`NUMERIC`	Unit sales from Impression View Thru Attributions.
`view_through_sales_value`	`NUMERIC`	Revenue from Impression View Thru Attributions.
`view_through_conversions`	`NUMERIC`	Conversions from Impression View Thru Attributions.
`halo_unit_sales`	`NUMERIC`	Unit sales from halo click Attributions.
`halo_sales_value`	`NUMERIC`	Revenue from halo click Attributions.
`halo_conversions`	`NUMERIC`	Conversions from halo click Attributions.
`total_click_sales`	`NUMERIC`	Direct unit sales + halo unit sales.
`total_click_sales_value`	`NUMERIC`	Direct sales value + halo sales value.
`total_unit_sales`	`NUMERIC`	Total click sales + view through unit sales.
`total_sales_value`	`NUMERIC`	Total click sales value + view through sales value.