analytics.conf_namespace
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
analytics.fact_ad_request_agg_ltz
- NAMESPACE
Request fact (aggregated) in local timezone.
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 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
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
analytics.fact_enhanced_attribution_2
- NAMESPACE
Enhanced Attribution (full detail, second 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_2_key
Partition: ingressed_at (by DAY)
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
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
analytics.fact_enhanced_attribution_agg_ltz
- NAMESPACE
Enhanced Attribution (aggregated) in local timezone.
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 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
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
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
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
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
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
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 | 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
analytics.fact_ledger_agg_ltz
- NAMESPACE
Ledger fact (aggregated into local timezone 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 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
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
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
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)
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 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
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
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
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
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
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): None
Partition: ingressed_at (by DAY)
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
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
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): None
Partition: ingressed_at (by DAY)
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_ltz
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
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): None
Partition: ingressed_at (by DAY)
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 | 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
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
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): None
Partition: ingressed_at (by DAY)
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 | 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
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
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): None
Partition: ingressed_at (by DAY)
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 | 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
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
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): None
Partition: ingressed_at (by DAY)
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 | 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
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
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): None
Partition: ingressed_at (by DAY)
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
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
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): None
Partition: ingressed_at (by DAY)
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_ltz
analytics.fact_realised_ad_agg_ltz
- NAMESPACE
Realised Ad fact (aggregated into local timezone 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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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. |