Reference

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 NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.

analytics.conf_retailer

  • NAMESPACE
  • RETAILER

Configuration settings related to Retailers.

Layer: Core Dataset
Category: Configuration
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
global_suppress_flagSTRINGFlag to indicate Retailer is to be excluded from reporting (eg test data).
ads_requested_capNUMERICMax 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_termsSTRINGNot in use.
period_start_dateSTRINGNot in use.
banner_ads_requested_capNUMERICMax 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_windowNUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution.
click_attribution_windowNUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attribution (1st model).
retailer_taxonomy_haloSTRINGComma separated list of what halo levels will be computed for model 1.
enhanced_attribution_flagSTRINGIf true, the first enhanced attribution model will be calculated.
enhanced_attribution_flag_2STRINGIf true, the second enhanced attribution model will be calculated.
view_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution (2nd model).
click_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attribution (2nd model).
retailer_taxonomy_halo_2STRINGComma separated list of what halo levels will be computed for model 2.
attribution_time_frameNUMERICDetermines how many days of attribution results should be stored for the first model.
attribution_time_frame_2NUMERICDetermines how many days of attribution results should be stored for the second model.
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.

analytics.conf_supplier

  • NAMESPACE
  • SUPPLIER

Configuration settings related to Suppliers.

Layer: Core Dataset
Category: Configuration
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.

analytics.currency_aud

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

Layer: Core Dataset
Category: Dimension
Status: Published

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

Foreign Key(s):
None

Column NameData TypeDescription
currencySTRINGCurrency being converted (ISO 4217 currency code).
valueFLOATConversion rate to apply.
baseSTRINGCurrency being converted into (AUD).

analytics.currency_usd

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

Layer: Core Dataset
Category: Dimension
Status: Published

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

Foreign Key(s):
None

Column NameData TypeDescription
currencySTRINGCurrency being converted (ISO 4217 currency code).
valueFLOATConversion rate to apply.
baseSTRINGCurrency being converted into (USD).

analytics.dim_campaign

  • NAMESPACE

Campaign master data dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
campaign_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
row_versionINTEGERTrack changes with new row versions (sequentially increasing number to indicate chronology).
team_idSTRINGInternal identifier of the Team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
placement_idSTRINGInternal identifier of the Placement (from the source system).
first_catalog_idSTRINGFirst Catalog id.
campaign_nameSTRINGName of the Campaign.
campaign_typeSTRINGType of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
campaign_subtypeSTRINGLegacy, superseded by placement. Subtype of the Campaign (SEARCH_ONLY, CATEGORY_ONLY, CATEGORY_AND_SEARCH, BROAD_DISPLAY, CROSS_SELL, UPSELL, RUN_OF_SITE).
campaign_start_dateTIMESTAMPDate the Campaign will begin.
campaign_start_date_utcTIMESTAMPCurrently the same as campaign_start_date since all data is in UTC.
campaign_end_dateTIMESTAMPDate when the Campaign will end.
campaign_end_date_utcTIMESTAMPCurrently the same as campaign_end_date since all data is in UTC.
valid_stateSTRINGThe Campaign's valid state. (APPROVED, PENDING, REJECTED)
active_stateSTRINGThe Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
is_ftaSTRINGFlag indicating if the Campaign is Fixed Tenancy Agreement (FTA).
catalog_idsSTRINGCatalog ids.
search_termsSTRINGComma separated list of SearchTerms targeted by the Campaign.
has_search_termsBOOLEANFlag to indicate if the Campaign is targeting SearchTerms.
catalog_productsSTRINGThe Catalog's Products within the Campaign.
placementsSTRINGThe Campaign's selected Placement.
currency_codeSTRINGISO 4217 currency code
fta_costNUMERICTotal amount being spent by the given Fixed Tenancy Agreement (FTA) Campaign.
max_cost_per_clickNUMERICThe maximum cost per click set for the Campaign.
max_total_spendNUMERICThe maximum total spend set for the Campaign.
max_daily_spendNUMERICThe maximum daily spend set for the Campaign.
impression_goalNUMERICThe maximum number of impressions a Campaign is aiming for. Only relevant for campaigns with a spend type of Fixed Spend.
total_spendNUMERICTotal AdSpend to date for the Campaign.
retailer_ad_spend_typeSTRINGType of spending if Retailer Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
supplier_ad_spend_typeSTRINGType of spending if Supplier Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
budgetNUMERICThe Campaign's budget as set by the Campaign's manager.
targeted_filtersSTRINGThe filters selected by an advertiser that their campaign will appear for. Used for fixed tenancy campaigns.
location_filtersSTRINGFilters on the campaign specific to a location if a location filter class is created.
filter_whitelistsSTRINGThe 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_settingsSTRINGThe cross-sell targeting selection in the campaign.
upsell_settingsSTRINGThe Upsell targeting selection in the campaign if applicable.
categoriesSTRINGComma separated list of Categories targeted by the Campaign.
catalog_countINTEGERNumber of Catalogs being targeted by the Campaign.
last_spend_dateDATEMost recent date that AdSpend was incurred by the Supplier.
product_type_codeSTRINGProduct type code.
namespaceSTRINGName of the Namespace.
user_idSTRINGInternal identifier of the User (from the source system).
user_emailSTRINGUser's email address.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
created_atTIMESTAMPDate the record was created in the source system.
created_at_utcTIMESTAMPCurrently the same as created_at since all data is in UTC.
updated_atTIMESTAMPDate the record was updated in the source system.
updated_at_utcTIMESTAMPCurrently the same as updated_at since all data is in UTC.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
cta_textSTRINGCTA text.
suggested_search_termsSTRINGSuggested search terms.
has_suggested_search_termsBOOLEANFlag to indicate that any suggested search terms exist.

analytics.dim_campaign_attr

  • NAMESPACE

Campaign custom attributes.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
campaign_attr_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
row_versionINTEGERTrack changes with new row versions (sequentially increasing number to indicate chronology).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
attr1STRINGCampaign user defined attribute value 1.
attr2STRINGCampaign user defined attribute value 2.
attr3STRINGCampaign user defined attribute value 3.
attr4STRINGCampaign user defined attribute value 4.
attr5STRINGCampaign user defined attribute value 5.
namespaceSTRINGName of the Namespace.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.dim_catalog

  • NAMESPACE

Catalog master data dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
catalog_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
row_versionINTEGERTrack changes with new row versions (sequentially increasing number to indicate chronology).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_nameSTRINGName of the Catalog.
currency_codeSTRINGISO 4217 currency code
retailer_nameSTRINGName of the Retailer.
namespaceSTRINGName of the Namespace.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
is_visibleBOOLEANFlag to indicate if the catalog is visible in the source system.

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 NameData TypeDescription
category_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
category_idSTRINGCategory being targeted.
namespaceSTRINGName of the Namespace.
category_labelSTRINGLabel for the Category (more descriptive than the typical id provided).
cat_level_1STRINGLevel 1 component of the category_label (delimited by >).
cat_level_2STRINGLevel 2 component of the category_label (delimited by >).
cat_level_3STRINGLevel 3 component of the category_label (delimited by >).
cat_level_4STRINGLevel 4 component of the category_label (delimited by >).
cat_level_5STRINGLevel 5 component of the category_label (delimited by >).
cat_level_6STRINGLevel 6 component of the category_label (delimited by >).
cat_level_7STRINGLevel 7 component of the category_label (delimited by >).
category_label_countINTEGERCount(category_label) across env_namespace, retailer_id, category_id.
source_nameSTRINGSource name.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.dim_env_namespace

  • NAMESPACE

Namespace dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

Foreign Key(s):
None

Column NameData TypeDescription
env_namespace_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
zone_idSTRINGTimezone (TZ) identifier standardised by IANA time zone database.
local_hourNUMERICLocal hour.
enable_okta_loginBOOLEANFlag to indicate if okta login is enabled.
enable_credit_cardBOOLEANFlag to indicate if credit card transactions are enabled.
enabled_spend_typesSTRINGSpend types enabled in the system.
banner_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on banner campaigns.
banner_enable_tracking_tagsBOOLEANFlag to indicate if tracking tags are enabled on banner campaigns.
banner_enable_filter_targetingBOOLEANFlag to indicate if filter targeting is enabled on banner campaigns.
banner_enable_filter_whitelistingBOOLEANFlag to indicate if filter whitelisting is enabled on banner campaigns.
display_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on display campaigns.
display_enable_tracking_tagsBOOLEANFlag to indicate if tracking tags are enabled on display campaigns.
display_enable_filter_targetingBOOLEANFlag to indicate if filter targeting is enabled on display campaigns.
display_enable_filter_whitelistingBOOLEANFlag to indicate if filter whitelisting is enabled on display campaigns.
wildcard_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on wildcard campaigns.
wildcard_enable_tracking_tagsBOOLEANFlag to indicate if tracking tags are enabled on wildcard campaigns.
wildcard_enable_filter_targetingBOOLEANFlag to indicate if filter targeting is enabled on wildcard campaigns.
wildcard_enable_filter_whitelistingBOOLEANFlag to indicate if filter whitelisting is enabled on wildcard campaigns.
brand_page_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on brand page campaigns.
brand_page_enable_tracking_tagsBOOLEANFlag to indicate if tracking tags are enabled on brand page campaigns.
brand_page_enable_filter_targetingBOOLEANFlag to indicate if filter targeting is enabled on brand page campaigns.
brand_page_enable_filter_whitelistingBOOLEANFlag to indicate if filter whitelisting is enabled on brand page campaigns.
shotgun_banner_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on shotgun banner campaigns.
shotgun_banner_enable_tracking_tagsBOOLEANFlag to indicate if tracking tags are enabled on shotgun banner campaigns.
shotgun_banner_enable_filter_targetingBOOLEANFlag to indicate if filter targeting is enabled on shotgun banner campaigns.
shotgun_banner_enable_filter_whitelistingBOOLEANFlag to indicate if filter whitelisting is enabled on shotgun banner campaigns.
disabled_demographicsSTRINGDisabled Demographics.
disabled_economy_levelBOOLEANFlag to indicate if economy level is disabled.
enable_category_min_bidBOOLEANFlag to indicate if category minimum bid is enabled.
include_retailer_t_and_cBOOLEANFlag to indicate if Retailer Terms and Conditions is included.
disabled_strategy_typesSTRINGDisabled Strategy Types.
enable_advanced_reportsBOOLEANFlag to indicate if advanced reports are enabled.
enable_reporting_upc_fixBOOLEANFlag to indicate if reporting UPC fix is enabled.
can_group_products_by_codeBOOLEANFlag to indicate if products can be grouped by code.
default_reporting_periodSTRINGDefault reporting period.
enable_offsite_campaignsBOOLEANFlag to indicate if offsite Campaigns are enabled.
enable_search_term_min_bidBOOLEANFlag to indicate if search term minimum bid is enabled.
enable_campaign_templatesBOOLEANFlag to indicate if Campaign templates is enabled.
enable_language_selectionBOOLEANFlag to indicate if language selection is enabled.
enable_product_tag_managerBOOLEANFlag to indicate if product tag manager is enabled.
enable_daily_spend_rolloverBOOLEANFlag to indicate if daily spend rollover is enabled.
product_metadata_selectionBOOLEANFlag to indicate if metadata selection is enabled.
catalog_selection_reportingBOOLEANFlag to indicate if Catalog selection reporting is enabled.
enable_campaign_duplicationBOOLEANFlag to indicate if campaign duplication is enabled.
enable_powered_by_citrus_iconBOOLEANFlag to indicate if Powered by Citrus is enabled.
enable_retailer_team_managerBOOLEANFlag to indicate if Retailer Team Manager is enabled.
enabled_search_term_measuresSTRINGFlag to indicate if search term measures is enabled.
max_banner_slots_per_campaignINTEGERMaximum banner slots per Campaign.
enable_report_table_filteringBOOLEANFlag to indicate if report table filtering enabled.
enable_supplier_edit_team_infoBOOLEANFlag to indicate if the platform enable Suppliers to edit team info.
disabled_search_term_match_typeBOOLEANFlag to indicate if search term match type is disabled.
enable_search_term_collectionsBOOLEANFlag to indicate if search term collections is enabled.
enable_supplier_wallet_editingBOOLEANFlag to indicate if the platform enables Supplier to edit the wallets.
enable_external_user_managementBOOLEANFlag to indicate if external user management is enabled.
enable_retailer_user_managementBOOLEANFlag to indicate if Retailer user management is enabled.
enable_retailer_wallet_creationBOOLEANFlag to indicate if the platform enables Retailers to create wallets.
enable_supplier_wallet_creationBOOLEANFlag to indicate if the platform enables Supplier to create wallets.
enable_retailer_impression_shareBOOLEANFlag to indicate if Retailer impression share is enabled.
enable_single_campaign_reportingBOOLEANFlag to indicate if single Campaign reporting is enabled.
enable_supplier_impression_shareBOOLEANFlag to indicate if Supplier impression share is enabled.
enable_external_assets_managementBOOLEANFlag to indicate if external assets management is enabled.
enable_retailer_wallet_managementBOOLEANFlag to indicate if Retailer wallet management is enabled.
enable_supplier_wallet_daily_budgetBOOLEANFlag to indicate if Supplier wallet daily budget is enabled.
enable_campaign_media_spend_documentBOOLEANFlag to indicate if Campaign media spend document is enabled.
enable_retailer_campaign_managementBOOLEANFlag to indicate if Retailer Campaign management is enabled.
enable_retailer_wallet_external_id_fieldBOOLEANFlag to indicate if Retailer wallet external ID is enabled.
enable_supplier_wallet_external_id_fieldBOOLEANFlag to indicate if Supplier wallet external ID is enabled.
enable_retailer_restricted_team_creationBOOLEANFlag to indicate if Retailer restricted team creation is enabled.
enable_report_table_filtering_by_product_tagBOOLEANFlag to indicate if report table filtering by product tag is enabled.
enable_retailer_wallet_credit_limit_managementBOOLEANFlag to indicate if Retailer wallet credit limit management is enabled.
enable_retailer_to_view_supplier_campaign_reportsBOOLEANFlag to indicate if the platform enables Retailer to view the Campaign report of Suppliers.

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 NameData TypeDescription
placement_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
placement_idSTRINGInternal identifier of the Placement (from the source system).
ad_gen_idSTRINGAd gen id.
archivedSTRINGArchived status of the Placement.
display_nameSTRINGDisplay name of the Placement.
campaign_targetSTRINGCampaign target.
catalog_idsSTRINGCatalog ids.
campaign_typesSTRINGList of applicable campaign types.
namespaceSTRINGName of the Namespace.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

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 NameData TypeDescription
product_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
product_nameSTRINGName of the Product.
product_typeSTRINGType of the Product.
product_image_urlSTRINGURL to an image of the Product for the Ad.
retailer_nameSTRINGName of the Retailer.
currency_codeSTRINGISO 4217 currency code
product_tagsSTRINGTags associated with the Product.
brandSTRINGBrand of the Product.
google_product_taxonomySTRINGGoogle taxonomy (product hierarchy) of the Product. Levels of the hierarchy delimited using > symbol.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
seller_idSTRINGSeller that the Product is applicable for.
seller_nameSTRINGName of the Seller id.
upcSTRINGUPC of the Product.
inventorySTRINGHow much of the Product is in stock.
namespaceSTRINGName of the Namespace.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
product_collectionSTRINGProduct collections represent the parent product of a given product code.
is_product_collectionBOOLEANFlag to indicate if product collection.

analytics.dim_retailer

  • NAMESPACE
  • RETAILER

Retailer dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
retailer_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
row_versionINTEGERTrack changes with new row versions (sequentially increasing number to indicate chronology).
retailer_nameSTRINGName of the Retailer.
team_typeSTRINGType of Team (RETAILER).
company_nameSTRINGCompany name of the Retailer.
business_identifierSTRINGImplementation specific generic label.
industrySTRINGIndustry of the Retailer.
currency_codeSTRINGISO 4217 currency code
ads_requested_capNUMERICMax 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_capNUMERICMax 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_termsSTRINGNot in use.
global_suppress_flagSTRINGFlag to indicate Retailer is to be excluded from reporting (eg test data).
view_attribution_windowNUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution.
click_attribution_windowNUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attribution (1st model).
retailer_taxonomy_haloSTRINGComma separated list of what halo levels will be computed for model 1.
enhanced_attribution_flagSTRINGIf true, the first enhanced attribution model will be calculated.
enhanced_attribution_flag_2STRINGIf true, the second enhanced attribution model will be calculated.
view_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution (2nd model).
click_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attribution (2nd model).
retailer_taxonomy_halo_2STRINGComma separated list of what halo levels will be computed for model 2.
attribution_time_frameNUMERICDetermines how many days of attribution results should be stored for the first model.
attribution_time_frame_2NUMERICDetermines how many days of attribution results should be stored for the second model.
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
namespaceSTRINGName of the Namespace.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
created_atTIMESTAMPDate the record was created in the source system.
created_at_utcTIMESTAMPCurrently the same as created_at since all data is in UTC.
updated_atTIMESTAMPDate the record was updated in the source system.
updated_at_utcTIMESTAMPCurrently the same as updated_at since all data is in UTC.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.dim_search_term

  • NAMESPACE

Search Term dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
search_term_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
search_termSTRINGSearch Term being targeted.
num_ad_requestsINTEGERTotal number of Requests.
num_realised_adsINTEGERTotal number of Ads realised.
ad_request_rankINTEGERRank of the Search Term by total Requests.
ad_stat_rankINTEGERRank of the Search Term by total Ads realised.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.dim_supplier

  • NAMESPACE
  • SUPPLIER

Supplier dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
supplier_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
row_versionINTEGERTrack changes with new row versions (sequentially increasing number to indicate chronology).
supplier_nameSTRINGName of the Supplier.
team_typeSTRINGType of Team (SUPPLIER).
company_nameSTRINGCompany name of the Supplier.
business_identifierSTRINGImplementation specific generic label.
industrySTRINGIndustry of the Supplier.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
namespaceSTRINGName of the Namespace.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
created_atTIMESTAMPDate the record was created in the source system.
created_at_utcTIMESTAMPCurrently the same as created_at since all data is in UTC.
updated_atTIMESTAMPDate the record was updated in the source system.
updated_at_utcTIMESTAMPCurrently the same as updated_at since all data is in UTC.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.dim_team

  • NAMESPACE

Team dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
team_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
team_idSTRINGInternal identifier of the Team (from the source system).
row_versionINTEGERTrack changes with new row versions (sequentially increasing number to indicate chronology).
team_nameSTRINGName of the Team.
team_typeSTRINGType of Team (RETAILER or SUPPLIER).
company_nameSTRINGCompany name of the Team.
business_identifierSTRINGImplementation specific generic label.
industrySTRINGIndustry of the Team.
user_idSTRINGInternal identifier of the User (from the source system).
last_campaign_created_idSTRINGInternal identifier of the Campaign last created by the Team (from the source system).
last_campaign_created_dateTIMESTAMPDate the Team created the most recent Campaign.
last_campaign_updated_idSTRINGInternal identifier of the Campaign last modified by the Team (from the source system).
last_campaign_updated_dateTIMESTAMPDate the Team updated the most recent Campaign.
total_campaignsINTEGERTotal number of Campaigns associated with Team (including inactive).
total_active_campaignsINTEGERTotal active Campaigns associated with the Team.
total_spendNUMERICTotal AdSpend to date for the Team across all Campaigns.
last_spend_dateDATEMost recent date that AdSpend was incurred by the Supplier.
namespaceSTRINGName of the Namespace.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
created_atTIMESTAMPDate the record was created in the source system.
created_at_utcTIMESTAMPCurrently the same as created_at since all data is in UTC.
updated_atTIMESTAMPDate the record was updated in the source system.
updated_at_utcTIMESTAMPCurrently the same as updated_at since all data is in UTC.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

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 NameData TypeDescription
user_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
user_idSTRINGInternal identifier of the User (from the source system).
row_versionINTEGERTrack changes with new row versions (sequentially increasing number to indicate chronology).
adminSTRINGFlag if the User is an administrator.
emailSTRINGUser's email address.
user_nameSTRINGName of the User.
activatedBOOLEANFlag indicating that a User has activated their account.
system_permissionsSTRINGUser system permissions.
action_permissionSTRINGUser action permissions.
subject_permissionSTRINGUser subject permissions.
namespaceSTRINGName of the Namespace.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
created_atTIMESTAMPDate the record was created in the source system.
created_at_utcTIMESTAMPCurrently the same as created_at since all data is in UTC.
updated_atTIMESTAMPDate the record was updated in the source system.
updated_at_utcTIMESTAMPCurrently the same as updated_at since all data is in UTC.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.dim_wallet

  • NAMESPACE

Wallet dimension.

Layer: Core Dataset
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
wallet_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
wallet_team_idSTRINGInternal identifier of the Team (from the source system).
external_idSTRINGImplementation specific generic label.
wallet_nameSTRINGName of the Wallet.
team_nameSTRINGName of the Team.
archivedBOOLEANArchived status of the Wallet.
credit_limitNUMERICAmount the Wallet may go into credit.
currency_codeSTRINGISO 4217 currency code
default_walletBOOLEANFlag indicating if this is the default Wallet.
prepaid_balanceNUMERICPrepaid balance of the Wallet.
available_balanceNUMERICCurrent available balance of the Wallet.
total_purchased_creditsNUMERICTotal credits made to the Wallet.
latest_top_up_amountNUMERICAmount of the most recent credit applied to the Wallet.
earliest_top_up_amountNUMERICEarliest credit amount made against the Wallet.
largest_top_up_amountNUMERICLargest credit amount made against the Wallet.
smallest_top_up_amountNUMERICSmallest credit amount made against the Wallet.
average_top_up_amountNUMERICAverage of credits applied to the Wallet.
credit_limit_usdNUMERICcredit_limit in USD (current exchange rate only).
total_purchased_credits_usdNUMERICtotal_purchased_credits in USD (current exchange rate only).
available_balance_usdNUMERICavailable_balance in USD (current exchange rate only).
prepaid_balance_usdNUMERICprepaid_balance in USD (current exchange rate only).
latest_top_up_amount_usdNUMERIClatest_top_up_amount in USD (current exchange rate only).
earliest_top_up_amount_usdNUMERICearliest_top_up_amount in USD (current exchange rate only).
largest_top_up_amount_usdNUMERIClargest_top_up_amount in USD (current exchange rate only).
smallest_top_up_amount_usdNUMERICsmallest_top_up_amount in USD (current exchange rate only).
average_top_up_amount_usdNUMERICaverage_top_up_amount in USD (current exchange rate only).
active_campaign_countNUMERICNumber of currently active Campaigns associated to the Wallet.
active_campaign_max_total_spendNUMERICAggregate CampaignMaxTotalSpend for all currently active Campaigns associated to the Wallet.
namespaceSTRINGName of the Namespace.
earliest_credit_dateTIMESTAMPMost recent date a credit was made to the Wallet.
earliest_credit_date_utcTIMESTAMPCurrently the same as earliest_credit_date since all data is in UTC.
latest_credit_dateTIMESTAMPDate of the most recent credit.
latest_credit_date_utcTIMESTAMPCurrently the same as latest_credit_date since all data is in UTC.
wallet_capture_dateTIMESTAMPDate the Wallet details were captured on.
wallet_capture_date_utcTIMESTAMPCurrently the same as wallet_capture_date since all data is in UTC.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
daily_limitNUMERICAmount the Wallet may go into credit in a day.
capped_available_balanceNUMERICCurrent capped available balance of the Wallet.

analytics.fact_ad_request

  • NAMESPACE

Request fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
ad_request_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
request_trace_idSTRINGRequest trace id.
request_typeINTEGERRequest type.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
customer_idSTRINGCustomer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
session_idSTRINGCustomer 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_idSTRINGInternal identifier of the Placement (from the source system).
realised_ad_idsSTRINGInternal identifiers of the Realised Ads associated to the Ad Request (ids from the source system).
ingressed_atTIMESTAMPTimestamp the Requests were received.
ingressed_at_utcTIMESTAMPCurrently the same as ingressed_at since all data is in UTC.
search_termSTRINGSearch Term being targeted.
reserved_search_term_flagSTRINGNot in use.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
filter_modeINTEGERThe filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
slot_idsSTRINGSlot ids.
categorySTRINGCategory being targeted.
product_type_codeSTRINGProduct type code.
locationSTRINGLocation.
namespaceSTRINGName of the Namespace.
experiment_idSTRINGNot in use.
num_ads_consideredNUMERICTotal number of Ad candidates that were considered by relevancy algorithms.
num_ads_servedNUMERICTotal number of Ads that were served back.
num_ads_requestedNUMERICTotal number of Ads that were requested by the Requests.
capped_num_ads_servedNUMERICTotal number of Ads that were served back with Cap applied per Request.
capped_num_ads_requestedNUMERICTotal number of Ads that were requested with Cap applied per Request.
capped_num_requests_serving_max_adsNUMERICTotal number of Requests where the Capped number of Ads requested was served in full.
num_requests_serving_max_adsNUMERICTotal number of Requests where the number of Ads requested was served in full.
num_ad_requests_serving_no_adsNUMERICTotal number of Requests where no Ads were served.
num_ad_requests_serving_some_adsNUMERICTotal number of Requests where at least 1 Ad was served.
num_ad_requestsNUMERICTotal number of Requests.
num_ad_requests_with_saturationNUMERICTotal number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

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 NameData TypeDescription
ad_request_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
ingressed_atDATEDate the Requests were received.
request_typeINTEGERRequest type.
search_termSTRINGSearch Term being targeted.
reserved_search_term_flagSTRINGNot in use.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
filter_modeINTEGERThe filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
slot_idsSTRINGSlot ids.
categorySTRINGCategory being targeted.
namespaceSTRINGName of the Namespace.
num_ads_consideredNUMERICTotal number of Ad candidates that were considered by relevancy algorithms.
num_ads_servedNUMERICTotal number of Ads that were served back.
num_ads_requestedNUMERICTotal number of Ads that were requested by the Requests.
capped_num_ads_servedNUMERICTotal number of Ads that were served back with Cap applied per Request.
capped_num_ads_requestedNUMERICTotal number of Ads that were requested with Cap applied per Request.
capped_num_requests_serving_max_adsNUMERICTotal number of Requests where the number of Ads requested was served in full.
num_requests_serving_max_adsNUMERICTotal number of Requests where the number of Ads requested was served in full.
num_ad_requests_serving_no_adsNUMERICTotal number of Requests where no Ads were served.
num_ad_requests_serving_some_adsNUMERICTotal number of Requests where at least 1 Ad was served.
num_ad_requestsNUMERICTotal number of Requests.
num_ad_requests_with_saturationNUMERICTotal number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
placement_idSTRINGInternal identifier of the Placement (from the source system).

analytics.fact_ad_request_agg_ltz

  • NAMESPACE

Request fact (aggregated) in local timezone.

Layer: Core Dataset
Category: Fact
Status: Published

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 NameData TypeDescription
ad_request_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
ingressed_atDATEDate the Requests were received in local timezone.
request_typeINTEGERRequest type.
search_termSTRINGSearch Term being targeted.
reserved_search_term_flagSTRINGNot in use.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
filter_modeINTEGERThe filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
slot_idsSTRINGSlot ids.
categorySTRINGCategory being targeted.
namespaceSTRINGName of the Namespace.
num_ads_consideredNUMERICTotal number of Ad candidates that were considered by relevancy algorithms.
num_ads_servedNUMERICTotal number of Ads that were served back.
num_ads_requestedNUMERICTotal number of Ads that were requested by the Requests.
capped_num_ads_servedNUMERICTotal number of Ads that were served back with Cap applied per Request.
capped_num_ads_requestedNUMERICTotal number of Ads that were requested with Cap applied per Request.
capped_num_requests_serving_max_adsNUMERICTotal number of Requests where the number of Ads requested was served in full.
num_requests_serving_max_adsNUMERICTotal number of Requests where the number of Ads requested was served in full.
num_ad_requests_serving_no_adsNUMERICTotal number of Requests where no Ads were served.
num_ad_requests_serving_some_adsNUMERICTotal number of Requests where at least 1 Ad was served.
num_ad_requestsNUMERICTotal number of Requests.
num_ad_requests_with_saturationNUMERICTotal number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
placement_idSTRINGInternal identifier of the Placement (from the source system).

analytics.fact_enhanced_attribution

  • NAMESPACE

Enhanced Attribution (full detail, first model only).

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
enhanced_attribution_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
namespaceSTRINGName of the Namespace.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
realised_ad_idSTRINGInternal identifier of the Realised Ad the Conversion is Attributed to.
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
session_idSTRINGCustomer 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_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
product_nameSTRINGName of the Product.
brandSTRINGBrand of the Product.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
halo_taxonomySTRINGThe 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_levelINTEGERHalo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
ingressed_atTIMESTAMPTimestamp the Requests were received.
impressioned_atTIMESTAMPTimestamp the impression occurred.
clicked_atTIMESTAMPTimestamp of the Click.
impressionedBOOLEANFlag indicating an impression.
clickedBOOLEANFlag indicating if clicked.
purchasedBOOLEANTotal number of purchases.
search_termSTRINGSearch Term being targeted.
category_idSTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
order_dateDATEDate of the Orders.
retailer_order_idSTRINGIdentifier of the Order the Conversion is Attributed to.
order_item_idSTRINGLine item of the Order the Conversion is Attributed to.
order_product_codeSTRINGOrder Product code.
order_normalised_productSTRINGNormalised Product code of the Order.
customer_idSTRINGCustomer 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_typeSTRINGType of the Order.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
direct_unit_salesNUMERICUnit sales from CitrusAd platform Attributions.
direct_sales_valueNUMERICRevenue from CitrusAd platform Attributions.
direct_conversionsNUMERICConversions from CitrusAd platform Attributions.
view_through_unit_salesNUMERICUnit sales from Impression View Thru Attributions.
view_through_sales_valueNUMERICRevenue from Impression View Thru Attributions.
view_through_conversionsNUMERICConversions from Impression View Thru Attributions.
halo_unit_salesNUMERICUnit sales from halo click Attributions.
halo_sales_valueNUMERICRevenue from halo click Attributions.
halo_conversionsNUMERICConversions from halo click Attributions.
row_typeSTRINGRow Type.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

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 NameData TypeDescription
enhanced_attribution_2_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
namespaceSTRINGName of the Namespace.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
realised_ad_idSTRINGInternal identifier of the Realised Ad the Conversion is Attributed to.
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
session_idSTRINGCustomer 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_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
product_nameSTRINGName of the Product.
brandSTRINGBrand of the Product.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
halo_taxonomySTRINGThe 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_levelINTEGERHalo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
ingressed_atTIMESTAMPTimestamp the Requests were received.
impressioned_atTIMESTAMPTimestamp the impression occurred.
clicked_atTIMESTAMPTimestamp of the Click.
impressionedBOOLEANFlag indicating an impression.
clickedBOOLEANFlag indicating if clicked.
purchasedBOOLEANTotal number of purchases.
search_termSTRINGSearch Term being targeted.
category_idSTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
order_dateDATEDate of the Orders.
retailer_order_idSTRINGIdentifier of the Order the Conversion is Attributed to.
order_item_idSTRINGLine item of the Order the Conversion is Attributed to.
order_product_codeSTRINGOrder Product code.
order_normalised_productSTRINGNormalised Product code of the Order.
customer_idSTRINGCustomer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
order_typeSTRINGType of the Order.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
direct_unit_salesNUMERICUnit sales from CitrusAd platform Attributions.
direct_sales_valueNUMERICRevenue from CitrusAd platform Attributions.
direct_conversionsNUMERICConversions from CitrusAd platform Attributions.
view_through_unit_salesNUMERICUnit sales from Impression View Thru Attributions.
view_through_sales_valueNUMERICRevenue from Impression View Thru Attributions.
view_through_conversionsNUMERICConversions from Impression View Thru Attributions.
halo_unit_salesNUMERICUnit sales from halo click Attributions.
halo_sales_valueNUMERICRevenue from halo click Attributions.
halo_conversionsNUMERICConversions from halo click Attributions.
row_typeSTRINGRow Type.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

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 NameData TypeDescription
enhanced_attribution_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
category_idSTRINGCategory being targeted.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
product_nameSTRINGName of the Product.
brandSTRINGBrand of the Product.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
halo_taxonomySTRINGThe 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_levelINTEGERHalo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
order_dateDATEDate of the Orders.
ingressed_atDATEDate the Ads were realised.
search_termSTRINGSearch Term being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
namespaceSTRINGName of the Namespace.
order_typeSTRINGType of the Order.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
direct_unit_salesNUMERICUnit sales from CitrusAd platform Attributions.
direct_sales_valueNUMERICRevenue from CitrusAd platform Attributions.
direct_conversionsNUMERICConversions from CitrusAd platform Attributions.
view_through_unit_salesNUMERICUnit sales from Impression View Thru Attributions.
view_through_sales_valueNUMERICRevenue from Impression View Thru Attributions.
view_through_conversionsNUMERICConversions from Impression View Thru Attributions.
halo_unit_salesNUMERICUnit sales from halo click Attributions.
halo_sales_valueNUMERICRevenue from halo click Attributions.
halo_conversionsNUMERICConversions from halo click Attributions.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
attribution_modelINTEGERIndicate if data is from first or second model.

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 NameData TypeDescription
enhanced_attribution_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
category_idSTRINGCategory being targeted.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
product_nameSTRINGName of the Product.
brandSTRINGBrand of the Product.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
halo_taxonomySTRINGThe 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_levelINTEGERHalo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
order_dateDATEDate of the Orders.
ingressed_atDATEDate the Ads were realised in local timezone.
search_termSTRINGSearch Term being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
namespaceSTRINGName of the Namespace.
order_typeSTRINGType of the Order.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
direct_unit_salesNUMERICUnit sales from CitrusAd platform Attributions.
direct_sales_valueNUMERICRevenue from CitrusAd platform Attributions.
direct_conversionsNUMERICConversions from CitrusAd platform Attributions.
view_through_unit_salesNUMERICUnit sales from Impression View Thru Attributions.
view_through_sales_valueNUMERICRevenue from Impression View Thru Attributions.
view_through_conversionsNUMERICConversions from Impression View Thru Attributions.
halo_unit_salesNUMERICUnit sales from halo click Attributions.
halo_sales_valueNUMERICRevenue from halo click Attributions.
halo_conversionsNUMERICConversions from halo click Attributions.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
attribution_modelINTEGERIndicate if data is from first or second model.

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 NameData TypeDescription
fta_campaign_spend_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
team_idSTRINGInternal identifier of the Team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
search_termSTRINGSearch Term being targeted.
product_codeSTRINGProduct code.
bucket_dateDATEBucket date.
bucket_date_keyINTEGERUnique identifier of the row in this table (surrogate key).
namespaceSTRINGName of the Namespace.
bucketed_costNUMERICBucketed cost.
fta_costNUMERICTotal amount being spent by the given Fixed Tenancy Agreement (FTA) Campaign.
catalog_cost_percentageNUMERICCatalog cost percentage.
num_of_search_termsINTEGERNumber of Search Terms.
num_of_productsINTEGERNumber of Products.
num_of_days_in_campaignINTEGERTotal number of days in the Campaign.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.fact_ledger

  • NAMESPACE

Ledger fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
ledger_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
ledger_idSTRINGInternal identifier of the Ledger transaction (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
team_idSTRINGInternal identifier of the Team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
transaction_dateTIMESTAMPDate of the transactions.
transaction_date_utcTIMESTAMPCurrently the same as transaction_date since all data is in UTC.
ledger_typeSTRINGDebit or Credit transaction type.
product_typeSTRINGType of the Product.
reasonSTRINGReason given for the transaction type.
namespaceSTRINGName of the Namespace.
amountNUMERICTotal of transaction amounts.
citrus_gross_marginNUMERICCitrus gross margin.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
reason_linkbackSTRINGLink back to what the transaction was for. E.g- Ids of the ads that were impressioned/clicked.

analytics.fact_ledger_agg

  • NAMESPACE

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

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
ledger_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
transaction_dateDATEDate of the transactions.
date_keyINTEGERUnique identifier of the row in this table (surrogate key).
team_idSTRINGInternal identifier of the Team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
ledger_typeSTRINGDebit or Credit transaction type.
product_ledgerSTRINGMore detailed transaction type.
namespaceSTRINGName of the Namespace.
reasonSTRINGReason given for the transaction type.
amountNUMERICTotal of transaction amounts.
transaction_countNUMERICTotal transaction count.
citrus_gross_marginNUMERICCitrus gross margin.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.fact_ledger_agg_2

  • NAMESPACE

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

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
namespaceSTRINGName of the Namespace.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
transaction_timestampTIMESTAMPTimestamp of the transactions rounded up to the hour
team_idSTRINGInternal identifier of the Team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
ledger_typeSTRINGDebit or Credit transaction type.
product_ledgerSTRINGType of the Product.
reasonSTRINGReason given for the transaction type.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
citrus_gross_marginNUMERICTotal of Citrus gross margin
amountNUMERICTotal of transaction amounts.
transaction_countINTEGERTotal number of transactions.

analytics.fact_ledger_agg_2_ltz

  • NAMESPACE

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

Layer: Core Dataset
Category: Fact
Status: Published

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 NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
namespaceSTRINGName of the Namespace.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
transaction_timestampTIMESTAMPTimestamp of the transactions rounded up to the hour in local timezone
team_idSTRINGInternal identifier of the Team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
ledger_typeSTRINGDebit or Credit transaction type.
product_ledgerSTRINGType of the Product.
reasonSTRINGReason given for the transaction type.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
citrus_gross_marginNUMERICTotal of Citrus gross margin
amountNUMERICTotal of transaction amounts.
transaction_countINTEGERTotal number of transactions.

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 NameData TypeDescription
ledger_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
transaction_dateDATEDate of the transactions in local timezone.
date_keyINTEGERUnique identifier of the row in this table (surrogate key).
team_idSTRINGInternal identifier of the Team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
ledger_typeSTRINGDebit or Credit transaction type.
product_ledgerSTRINGMore detailed transaction type.
namespaceSTRINGName of the Namespace.
reasonSTRINGReason given for the transaction type.
amountNUMERICTotal of transaction amounts.
transaction_countNUMERICTotal transaction count.
citrus_gross_marginNUMERICCitrus gross margin.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.fact_order

  • NAMESPACE

Order fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
order_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
sequence_idNUMERICSequence id.
retailer_order_idSTRINGIdentifier for the Order from the Retailer.
row_versionNUMERICTrack changes with new row versions (sequentially increasing number to indicate chronology).
order_item_idSTRINGLine item of the Order.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
issuer_team_idSTRINGInternal identifier of the Team (from the source system).
namespaceSTRINGName of the Namespace.
order_timestampTIMESTAMPTimestamp of the Order.
order_timestamp_utcTIMESTAMPCurrently the same as order_timestamp since all data is in UTC.
order_dateDATEDate of the Orders.
order_date_utcDATECurrently the same as order_date since all data is in UTC.
order_date_keyINTEGERUnique identifier of the row in this table (surrogate key).
realised_ad_idSTRINGRealised Ad the Order is Attributed to (as set by external processes).
product_codeSTRINGProduct code.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
session_idSTRINGCustomer 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_idSTRINGCustomer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
currency_codeSTRINGISO 4217 currency code
order_typeSTRINGType of the Order.
quantityNUMERICQuantity of the Product ordered.
total_priceNUMERICTotal paid for the products ordered.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

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 NameData TypeDescription
order_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
issuer_team_idSTRINGInternal identifier of the Team (from the source system).
order_dateDATEDate of the Orders.
order_date_keyINTEGERUnique identifier of the row in this table (surrogate key).
product_codeSTRINGProduct code.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
currency_codeSTRINGISO 4217 currency code
namespaceSTRINGName of the Namespace.
order_typeSTRINGType of the Order.
quantityNUMERICQuantity of the Product ordered.
total_priceNUMERICTotal paid for the products ordered.
total_price_usdFLOATtotal_price in USD (current exchange rate only).
order_itemsNUMERICTotal number of items ordered.
ordersNUMERICNumber of Orders made.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.fact_order_agg_ltz

  • NAMESPACE

Order fact (aggregated in local timezone dates).

Layer: Core Dataset
Category: Fact
Status: Published

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

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 NameData TypeDescription
order_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
issuer_team_idSTRINGInternal identifier of the Team (from the source system).
order_dateDATEDate of the Orders in local timezone.
order_date_keyINTEGERUnique identifier of the row in this table (surrogate key).
product_codeSTRINGProduct code.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
currency_codeSTRINGISO 4217 currency code
namespaceSTRINGName of the Namespace.
order_typeSTRINGType of the Order.
quantityNUMERICQuantity of the Product ordered.
total_priceNUMERICTotal paid for the products ordered.
total_price_usdFLOATtotal_price in USD (current exchange rate only).
order_itemsNUMERICTotal number of items ordered.
ordersNUMERICNumber of Orders made.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.fact_realised_ad

  • NAMESPACE

Realised Ad fact (full detail).

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
realised_ad_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
realised_ad_idSTRINGInternal identifier of the Realised Ad (from the source system)..
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
ingressed_atTIMESTAMPTimestamp the Requests were received.
ingressed_at_utcTIMESTAMPCurrently the same as ingressed_at since all data is in UTC.
purchased_atTIMESTAMPTimestamp of the purchase.
purchased_at_utcTIMESTAMPCurrently the same as purchased_at since all data is in UTC.
impressioned_atSTRINGTimestamp the impression occurred.
impressioned_at_utcTIMESTAMPCurrently the same as impressioned_at since all data is in UTC.
clicked_atTIMESTAMPTimestamp of the Click.
clicked_at_utcTIMESTAMPCurrently the same as clicked_at since all data is in UTC.
served_atTIMESTAMPTimestamp when Ads served.
served_at_utcTIMESTAMPCurrently the same as served_at since all data is in UTC.
namespaceSTRINGName of the Namespace.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
search_termSTRINGSearch Term being targeted.
product_codeSTRINGProduct code.
content_standard_idSTRINGContentStandardId as specified in the Ad Request.
slot_idSTRINGBanner SlotId as specified in the Ad Request.
session_idSTRINGCustomer 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_idSTRINGCustomer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
target_product_codeSTRINGTarget Product code.
reserved_search_term_flagSTRINGNot in use.
categorySTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
impressionedBOOLEANFlag indicating an impression.
clickedBOOLEANFlag indicating if clicked.
purchasedBOOLEANTotal number of purchases.
servedBOOLEANFlag indicating Ads were served.
click_invalidBOOLEANFlag indicating if the click was invalid.
impression_invalidBOOLEANFlag indicating if the impression was invalid.
campaign_team_idSTRINGInternal identifier of the Team (from the source system).
filter_modeINTEGERThe filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
page_typeINTEGERLegacy, superseded by Placement.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
impression_spendNUMERICAdSpend generated by impressions.
click_spendNUMERICAdSpend generated by clicks.
serve_spendNUMERICServe spend.
serve_priceNUMERICServe price.
impression_priceNUMERICPrice of an impression.
click_priceNUMERICClick Price.
amount_refunded_from_invalid_clickNUMERICRefund amount from invalid clicks.
amount_refunded_from_invalid_impressionNUMERICRefund amount from invalid impressions.
ad_revenueNUMERICPortion of Ad spend retained by the Retailer.
sales_revenueNUMERICTotal sales revenue generated by Conversions.
placement_idSTRINGInternal identifier of the Placement (from the source system).
product_type_codeSTRINGProduct type code.
locationSTRINGLocation.
order_item_idSTRINGLine item of the Order.
positionINTEGERPosition of the Ad.
experiment_idSTRINGNot in use.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
ad_requester_team_idSTRINGInternal identifier of the Team (from the source system).
email_serve_revenueNUMERICEmail serve revenue.
invalid_ad_revenueNUMERICTotal invalid revenue.
campaign_typeINTEGERType of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
product_collectionSTRINGProduct collection (parent child sku).

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 NameData TypeDescription
realised_ad_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
ingressed_atDATEDate the Ads were realised.
namespaceSTRINGName of the Namespace.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
search_termSTRINGSearch Term being targeted.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
product_codeSTRINGProduct code.
content_standard_idSTRINGContentStandardId as specified in the Ad Request.
slot_idSTRINGBanner SlotId as specified in the Ad Request.
categorySTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
impression_spendNUMERICAdSpend generated by impressions.
click_spendNUMERICAdSpend generated by clicks.
ad_revenueNUMERICPortion of Ad spend retained by the Retailer.
sales_revenueNUMERICTotal sales revenue generated by Conversions.
target_product_codeSTRINGTarget Product code.
position_sumNUMERICSum of all postions for the Ads. Used to calculate average ad position (avg position = position_sum / ads_with_position_count).
ads_with_position_countNUMERICTotal number of Ads that have a position set. avg position = position_sum / ads_with_position_count.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
ad_requester_team_idSTRINGInternal identifier of the Team (from the source system).
email_serve_revenueNUMERICEmail serve revenue.
invalid_ad_revenueNUMERICTotal invalid revenue.
placement_idSTRINGInternal identifier of the Placement (from the source system).

analytics.fact_realised_ad_agg_2

  • NAMESPACE

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

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
realised_ad_agg_2_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
customer_idSTRINGCustomer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
ingressed_atDATEDate the Ads were realised.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
search_termSTRINGSearch Term being targeted.
categorySTRINGCategory being targeted.
namespaceSTRINGName of the Namespace.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.fact_realised_ad_agg_3

  • NAMESPACE

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

Layer: Core Dataset
Category: Fact
Status: Published

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 NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
namespaceSTRINGName of the Namespace.
ingressed_atTIMESTAMPTimestamp the Ads were realised.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
search_termSTRINGSearch Term being targeted.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
product_codeSTRINGProduct code.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
content_standard_idSTRINGContentStandardId as specified in the Ad Request.
slot_idSTRINGBanner SlotId as specified in the Ad Request.
categorySTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
target_product_codeSTRINGTarget Product code.
has_search_termINTEGERFlag to indicate if the Ad is targeting a Search Term.
has_categoryINTEGERFlag to indicate if the Ad is targeting a Category.
sales_revenueNUMERICTotal sales revenue generated by Conversions.
ad_revenueNUMERICPortion of Ad spend retained by the Retailer.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
click_spendNUMERICAdSpend generated by clicks.
impression_spendNUMERICAdSpend generated by impressions.

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 NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
namespaceSTRINGName of the Namespace.
ingressed_atTIMESTAMPTimestamp the Ads were realised in local timezone.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
search_termSTRINGSearch Term being targeted.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
product_codeSTRINGProduct code.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
content_standard_idSTRINGContentStandardId as specified in the Ad Request.
slot_idSTRINGBanner SlotId as specified in the Ad Request.
categorySTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
target_product_codeSTRINGTarget Product code.
has_search_termINTEGERFlag to indicate if the Ad is targeting a Search Term.
has_categoryINTEGERFlag to indicate if the Ad is targeting a Category.
sales_revenueNUMERICTotal sales revenue generated by Conversions.
ad_revenueNUMERICPortion of Ad spend retained by the Retailer.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
click_spendNUMERICAdSpend generated by clicks.
impression_spendNUMERICAdSpend generated by impressions.

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 NameData TypeDescription
realised_ad_agg_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
ingressed_atDATEDate the Ads were realised in local timezone.
namespaceSTRINGName of the Namespace.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
search_termSTRINGSearch Term being targeted.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
product_codeSTRINGProduct code.
content_standard_idSTRINGContentStandardId as specified in the Ad Request.
slot_idSTRINGBanner SlotId as specified in the Ad Request.
categorySTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
impression_spendNUMERICAdSpend generated by impressions.
click_spendNUMERICAdSpend generated by clicks.
ad_revenueNUMERICPortion of Ad spend retained by the Retailer.
sales_revenueNUMERICTotal sales revenue generated by Conversions.
target_product_codeSTRINGTarget Product code.
position_sumNUMERICSum of all postions for the Ads. Used to calculate average ad position (avg position = position_sum / ads_with_position_count).
ads_with_position_countNUMERICTotal number of Ads that have a position set. avg position = position_sum / ads_with_position_count.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).
ad_requester_team_idSTRINGInternal identifier of the Team (from the source system).
email_serve_revenueNUMERICEmail serve revenue.
invalid_ad_revenueNUMERICTotal invalid revenue.
placement_idSTRINGInternal identifier of the Placement (from the source system).

analytics.fact_report_request_tracking

  • NAMESPACE

CitrusAd client reporting usage fact.

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
report_request_tracking_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
report_request_tracking_idSTRINGInternal identifier of the Report Request (from the source system).
user_idSTRINGInternal identifier of the User (from the source system).
team_idSTRINGInternal identifier of the Team (from the source system).
report_typeSTRINGType of report.
cache_levelSTRINGCache level.
report_spanSTRINGSpan of report.
request_dateDATEDate of the report request.
request_date_keyINTEGERUnique identifier of the row in this table (surrogate key).
request_timeTIMESTAMPRequest time.
namespaceSTRINGName of the Namespace.
counts_towards_quotaBOOLEANFlag indicating of usage counts towards quota.
big_query_billed_bytesNUMERICTotal bytes billed.
big_query_execution_duration_secondsNUMERICTotal duration of all queries.
big_query_finished_polling_duration_secondsNUMERICTotal polling duration of all queries.
total_execution_duration_secondsNUMERICTotal execution duration of the report.
max_billed_bytes_per_dayNUMERICMax billed bytes per day.
max_billed_bytes_per_monthNUMERICMax billed bytes per month.
max_uncached_requests_per_dayNUMERICMax uncached requests per day.
max_uncached_requests_per_monthNUMERICMax uncached requests per month.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.fact_user_team

  • NAMESPACE

List of all Team Users.

Layer: Core Dataset
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
user_team_keySTRINGUnique identifier of the row in this table (surrogate key).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
user_idSTRINGInternal identifier of the User (from the source system).
team_idSTRINGInternal identifier of the Team (from the source system).
role_idsSTRINGRole ids.
last_edit_dateTIMESTAMPDate of the last edit.
last_edit_date_utcTIMESTAMPCurrently the same as last_edit_date since all data is in UTC.
etl_created_atTIMESTAMPDate the row was inserted into this table (not the date the record was created in the source system).
etl_modified_atTIMESTAMPDate this row was last updated (not the date the record was updated in the source system).

analytics.log_status

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

Layer: Core Dataset
Category: Logging
Status: Published

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

Foreign Key(s):
None

Column NameData TypeDescription
jobidSTRINGId for the Job being executed.
logging_datetime_utcDATETIMEDate and time the Job log entry was made.
job_nameSTRINGLabel indicating what core dataset the Job is for.
statusSTRINGIndicate if the Job is running or completed.
return_codeSTRINGIndicate if the completed status was successful (0) or failed (1).

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 NameData TypeDescription
envSTRING
env_namespaceSTRING
ingressed_atDATE
ad_typeINTEGER
search_termSTRING
generic_search_termSTRING
reserved_search_term_flagSTRING
placementSTRING
platformSTRING
page_typeINTEGER
filter_modeINTEGER
slot_idSTRING
categorySTRING
catalog_idSTRING
catalog_nameSTRING
retailer_idSTRING
retailer_nameSTRING
retailer_industrySTRING
retailer_business_identifierSTRING
retailer_company_nameSTRING
retailer_group_1STRING
retailer_group_2STRING
geo_regionSTRING
local_timezoneSTRING
has_search_termINTEGER
has_categoryINTEGER
num_ads_consideredNUMERIC
num_ads_servedNUMERIC
num_ads_requestedNUMERIC
capped_num_ads_servedNUMERIC
capped_num_ads_requestedNUMERIC
capped_num_requests_serving_max_adsNUMERIC
num_requests_serving_max_adsNUMERIC
num_ad_requests_serving_no_adsNUMERIC
num_ad_requests_serving_some_adsNUMERIC
num_ad_requestsNUMERIC
num_ad_requests_with_saturationNUMERIC
campaign_idSTRING
campaign_nameSTRING
supplier_idSTRING
supplier_nameSTRING
supplier_industrySTRING
supplier_business_identifierSTRING
supplier_company_nameSTRING
product_codeSTRING
content_standard_idSTRING
target_product_codeSTRING
supplier_group_1STRING
supplier_group_2STRING
supplier_group_3STRING
supplier_group_4STRING
sales_revenueNUMERIC
ad_revenueNUMERIC
ad_spendNUMERIC
impressionsNUMERIC
clicksNUMERIC
conversionsNUMERIC
unit_salesNUMERIC
click_spendNUMERIC
impression_spendNUMERIC
position_sumNUMERIC
sales_revenue_usdFLOAT
ad_revenue_usdFLOAT
ad_spend_usdFLOAT
SQL Query
select
  env,
  env_namespace,
  ingressed_at,
  ad_type,
  search_term,
  generic_search_term,
  reserved_search_term_flag,
  placement,
  platform,
  page_type,
  filter_mode,
  slot_id,
  category,
  catalog_id,
  catalog_name,
  retailer_id,
  retailer_name,
  retailer_industry,
  retailer_business_identifier,
  retailer_company_name,
  retailer_group_1,
  retailer_group_2,
  geo_region,
  local_timezone,
  has_search_term,
  has_category,
  num_ads_considered,
  num_ads_served,
  num_ads_requested,
  capped_num_ads_served,
  capped_num_ads_requested,
  capped_num_requests_serving_max_ads,
  num_requests_serving_max_ads,
  num_ad_requests_serving_no_ads,
  num_ad_requests_serving_some_ads,
  num_ad_requests,
  num_ad_requests_with_saturation,
  null as campaign_id,
  null as campaign_name, 
  null as supplier_id,
  null as supplier_name,
  null as supplier_industry,
  null as supplier_business_identifier,
  null as supplier_company_name,
  null as product_code,
  null as content_standard_id,
  null as target_product_code,
  null as supplier_group_1,
  null as supplier_group_2,
  null as supplier_group_3,
  null as supplier_group_4,
  null as sales_revenue,
  null as ad_revenue,
  null as ad_spend,
  null as impressions,
  null as clicks,
  null as conversions,
  null as unit_sales,
  null as click_spend,
  null as impression_spend,
  null as position_sum,
  null as sales_revenue_usd,
  null as ad_revenue_usd,
  null as ad_spend_usd
from
  reporting.ad_request_agg_view
union all
select
  env,
  env_namespace,
  ingressed_at ,
  ad_type,
  search_term,
  generic_search_term,
  null as reserved_search_term_flag,
  placement,
  platform,
  null as page_type,
  null as filter_mode,
  slot_id,
  category,
  catalog_id,
  catalog_name,
  retailer_id,
  retailer_name,
  retailer_industry,
  retailer_business_identifier,
  retailer_company_name,
  retailer_group_1,
  retailer_group_2,
  null as geo_region,
  null as local_timezone,
  null as has_search_term,
  null as has_category,
  null as num_ads_considered,
  null as num_ads_served,
  null as num_ads_requested,
  null as capped_num_ads_served,
  null as capped_num_ads_requested,
  null as capped_num_requests_serving_max_ads,
  null as num_requests_serving_max_ads,
  null as num_ad_requests_serving_no_ads,
  null as num_ad_requests_serving_some_ads,
  null as num_ad_requests,
  null as num_ad_requests_with_saturation,
  campaign_id,
  campaign_name, 
  supplier_id,
  supplier_name,
  supplier_industry,
  supplier_business_identifier,
  supplier_company_name,
  product_code,
  content_standard_id,
  target_product_code,
  supplier_group_1,
  supplier_group_2,
  supplier_group_3,
  supplier_group_4,
  sales_revenue,
  ad_revenue,
  ad_spend,
  impressions,
  clicks,
  conversions,
  unit_sales,
  click_spend,
  impression_spend,
  position_sum,
  sales_revenue_usd,
  ad_revenue_usd,
  ad_spend_usd

from
  reporting.realised_ad_agg_view

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 NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
ingressed_atDATEDate the Requests were received.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
search_termSTRINGSearch Term being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe 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_flagSTRINGNot in use.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
page_typeINTEGERLegacy, superseded by Placement.
filter_modeINTEGERThe filterMode used to structure the productFilters in the ad request. Defaults in OR_AND if not specified. (OR_AND, AND_OR).
slot_idsSTRINGSlot ids.
categorySTRINGCategory being targeted.
has_search_termINTEGERFlag to indicate if the Request is targeting a Search Term.
has_categoryINTEGERFlag to indicate if the Request is targeting a Category.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
num_ads_consideredNUMERICTotal number of Ad candidates that were considered by relevancy algorithms.
num_ads_servedNUMERICTotal number of Ads that were served back.
num_ads_requestedNUMERICTotal number of Ads that were requested by the Requests.
capped_num_ads_servedNUMERICTotal number of Ads that were served back with Cap applied per Request.
capped_num_ads_requestedNUMERICTotal number of Ads that were requested with Cap applied per Request.
capped_num_requests_serving_max_adsNUMERICTotal number of Requests where the number of Ads requested was served in full.
num_requests_serving_max_adsNUMERICTotal number of Requests where the number of Ads requested was served in full.
num_ad_requests_serving_no_adsNUMERICTotal number of Requests where no Ads were served.
num_ad_requests_serving_some_adsNUMERICTotal number of Requests where at least 1 Ad was served.
num_ad_requestsNUMERICTotal number of Requests.
num_ad_requests_with_saturationNUMERICTotal number of Requests that served at least 4 Ads or filled the entire Request (if < 4 were requested).
placement_idSTRINGInternal identifier of the Placement (from the source system).
namespaceSTRINGName of the Namespace.

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 NameData TypeDescription
envSTRING
env_namespaceSTRING
ingressed_atDATE
ad_typeINTEGER
search_termSTRING
generic_search_termSTRING
reserved_search_term_flagSTRING
placementSTRING
platformSTRING
page_typeINTEGER
filter_modeINTEGER
slot_idSTRING
categorySTRING
catalog_idSTRING
catalog_nameSTRING
retailer_idSTRING
retailer_nameSTRING
retailer_industrySTRING
retailer_business_identifierSTRING
retailer_company_nameSTRING
retailer_group_1STRING
retailer_group_2STRING
geo_regionSTRING
local_timezoneSTRING
has_search_termINTEGER
has_categoryINTEGER
namespaceSTRING
ad_type_nameSTRING
num_ads_consideredNUMERIC
num_ads_servedNUMERIC
num_ads_requestedNUMERIC
capped_num_ads_servedNUMERIC
capped_num_ads_requestedNUMERIC
capped_num_requests_serving_max_adsNUMERIC
num_requests_serving_max_adsNUMERIC
num_ad_requests_serving_no_adsNUMERIC
num_ad_requests_serving_some_adsNUMERIC
num_ad_requestsNUMERIC
num_ad_requests_with_saturationNUMERIC
SQL Query
select
  far.env env,
  far.env_namespace env_namespace,
  far.ingressed_at as ingressed_at,
  far.ad_type,
  far.search_term as search_term,
  far.generic_search_term as generic_search_term,
  far.reserved_search_term_flag reserved_search_term_flag,
  far.placement placement,
  far.platform platform,
  far.page_type,
  far.filter_mode,
  far.slot_ids as slot_id,
  far.category as category,
  far.catalog_id catalog_id,
  dc.catalog_name as catalog_name,
  far.retailer_id as retailer_id,
  dr.retailer_name as retailer_name,
  dr.retailer_industry as retailer_industry,
  dr.retailer_business_identifier as retailer_business_identifier,
  dr.retailer_company_name as retailer_company_name,
  dr.retailer_group_1 as retailer_group_1,
  dr.retailer_group_2 as retailer_group_2,
  rn.geo_region as geo_region,
  rn.local_timezone as local_timezone,
  if(far.search_term is not null, 1, 0) as has_search_term,
  if(far.category is not null, 1, 0) as has_category,
  far.namespace namespace,
  ifnull(ad.ad_type_name, &#39;&#39;) ad_type_name,
  far.num_ads_considered,
  far.num_ads_served,
  far.num_ads_requested,
  far.capped_num_ads_served,
  far.capped_num_ads_requested,
  far.capped_num_requests_serving_max_ads,
  far.num_requests_serving_max_ads,
  far.num_ad_requests_serving_no_ads,
  far.num_ad_requests_serving_some_ads,
  far.num_ad_requests,
  far.num_ad_requests_with_saturation
from
  reporting.ad_request_agg far
  left join reporting.catalog dc on far.catalog_id = dc.catalog_id 
                                                        and far.env_namespace = dc.env_namespace
  left join reporting.retailer dr on far.retailer_id = dr.retailer_id
                                                        and dr.env_namespace = far.env_namespace
  left join reporting.env_namespace rn on far.env_namespace = rn.env_namespace
  left join reporting.view_ad_type ad on far.ad_type = ad.ad_type
where
  far.ingressed_at &gt;= &#39;2000-01-01&#39;

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 NameData TypeDescription
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
campaign_nameSTRINGName of the Campaign.
campaign_typeSTRINGType of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
campaign_subtypeSTRINGLegacy, superseded by placement. Subtype of the Campaign (SEARCH_ONLY, CATEGORY_ONLY, CATEGORY_AND_SEARCH, BROAD_DISPLAY, CROSS_SELL, UPSELL, RUN_OF_SITE).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
retailer_nameSTRINGName of the Retailer.
supplier_nameSTRINGName of the Supplier.
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
is_ftaSTRINGFlag indicating if the Campaign is Fixed Tenancy Agreement (FTA).
fta_costNUMERICTotal amount being spent by the given Fixed Tenancy Agreement (FTA) Campaign.
wallet_nameSTRINGName of the Wallet.
wallet_external_idSTRINGImplementation specific generic label.
wallet_archivedBOOLEANArchived status of the Wallet.
wallet_credit_limitNUMERICAmount the Wallet may go into credit.
wallet_currency_codeSTRINGISO 4217 currency code
wallet_total_purchased_creditsNUMERICTotal credits made to the Wallet.
wallet_available_balanceNUMERICCurrent available balance of the Wallet.
wallet_credit_limit_usdNUMERICwallet_credit_limit in USD (current exchange rate only).
wallet_available_balance_usdNUMERICwallet_available_balance in USD (current exchange rate only).
wallet_total_purchased_credits_usdNUMERICwallet_total_purchased_credits in USD (current exchange rate only).
campaign_start_dateTIMESTAMPDate the Campaign will begin.
campaign_end_dateTIMESTAMPDate when the Campaign will end.
campaign_valid_stateSTRINGThe Campaign's valid state. (APPROVED, PENDING, REJECTED)
campaign_active_stateSTRINGThe Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
campaign_created_atTIMESTAMPDate the record was created in the source system.
campaign_updated_atTIMESTAMPDate the record was updated in the source system.
campaign_placementsSTRINGThe Campaign's selected Placement.
campaign_total_spendNUMERICTotal AdSpend to date for the Campaign.
campaign_total_spend_usdNUMERICcampaign_total_spend in USD (current exchange rate only).
campaign_max_cost_per_clickNUMERICThe maximum cost per click set for the Campaign.
campaign_max_total_spendNUMERICThe maximum total spend set for the Campaign.
campaign_max_daily_spendNUMERICThe maximum daily spend set for the Campaign.
campaign_impression_goalNUMERICThe maximum number of impressions a Campaign is aiming for. Only relevant for campaigns with a spend type of Fixed Spend.
campaign_catalog_productsSTRINGThe Catalog's Products within the Campaign.
campaign_attr_1STRINGCampaign user defined attribute 1.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
team_idSTRINGInternal identifier of the Team (from the source system).
retailer_ad_spend_typeSTRINGType of spending if Retailer Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
supplier_ad_spend_typeSTRINGType of spending if Supplier Campaign (Always On, Daily Spend, Fixed Spend, Fixed Tenancy, Total Spend).
budgetNUMERICThe Campaign's budget as set by the Campaign's manager.
targeted_filtersSTRINGThe filters selected by an advertiser that their campaign will appear for. Used for fixed tenancy campaigns.
location_filtersSTRINGFilters on the campaign specific to a location if a location filter class is created.
filter_whitelistsSTRINGThe 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_settingsSTRINGThe cross-sell targeting selection in the campaign.
upsell_settingsSTRINGThe Upsell targeting selection in the campaign if applicable.
categoriesSTRINGComma separated list of Categories targeted by the Campaign.
catalog_countINTEGERNumber of Catalogs being targeted by the Campaign.
catalog_idsSTRINGCatalog ids.
search_termsSTRINGComma separated list of SearchTerms targeted by the Campaign.
has_search_termsBOOLEANFlag to indicate if the Campaign is targeting SearchTerms.
last_spend_dateDATEMost recent date that AdSpend was incurred by the Supplier.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
product_type_codeSTRINGProduct type code.
cta_textSTRINGCTA text.
suggested_search_termsSTRINGSuggested search terms.
has_suggested_search_termsBOOLEANFlag to indicate that any suggested search terms exist.
is_currentBOOLEANFlag if the row is the latest version in the source system.
active_stateSTRINGThe Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
valid_stateSTRINGThe Campaign's valid state. (APPROVED, PENDING, REJECTED)
namespaceSTRINGName of the Namespace.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
currency_codeSTRINGISO 4217 currency code

reporting.catalog

  • NAMESPACE
  • RETAILER

Catalog data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_nameSTRINGName of the Catalog.
retailer_nameSTRINGName of the Retailer.
currency_codeSTRINGISO 4217 currency code
namespaceSTRINGName of the Namespace.
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.

reporting.category

  • NAMESPACE
  • RETAILER

Category data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
category_idSTRINGCategory being targeted.
namespaceSTRINGName of the Namespace.
category_labelSTRINGLabel for the Category (more descriptive than the typical id provided).
cat_level_1STRINGLevel 1 component of the category_label (delimited by >).
cat_level_2STRINGLevel 2 component of the category_label (delimited by >).
cat_level_3STRINGLevel 3 component of the category_label (delimited by >).
cat_level_4STRINGLevel 4 component of the category_label (delimited by >).
cat_level_5STRINGLevel 5 component of the category_label (delimited by >).
cat_level_6STRINGLevel 6 component of the category_label (delimited by >).
cat_level_7STRINGLevel 7 component of the category_label (delimited by >).
category_label_countINTEGERCount(category_label) across env_namespace, retailer_id, category_id.
source_nameSTRINGSource name.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
retailer_nameSTRINGName of the Retailer.

reporting.currency

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

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

Foreign Key(s):
None

Column NameData TypeDescription
currencySTRINGCurrency being converted (ISO 4217 currency code).
valueFLOATConversion rate to apply.
baseSTRINGCurrency being converted into.

reporting.deduped_product

  • NAMESPACE
  • RETAILER

Deduped product data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
product_nameSTRINGName of the Product.
product_typeSTRINGType of the Product.
retailer_nameSTRINGName of the Retailer.
currency_codeSTRINGISO 4217 currency code
product_tagsSTRINGTags associated with the Product.
product_image_urlSTRINGURL to an image of the Product for the Ad.
brandSTRINGBrand of the Product.
google_product_taxonomySTRINGGoogle taxonomy (product hierarchy) of the Product. Levels of the hierarchy delimited using > symbol.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
namespaceSTRINGName of the Namespace.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.

reporting.enhanced_attribution_agg

  • NAMESPACE
  • RETAILER
  • SUPPLIER

Enhanced Attribution data (aggregated, both models).

Layer: Reporting Datamart
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
namespaceSTRINGName of the Namespace.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
ingressed_atDATEDate the Ads were realised.
yyyymmddSTRINGYear+month+day of ingressed_at.
yyyymmSTRINGYear+month of ingressed_at.
yyyySTRINGYear of ingressed_at.
week_start_dateDATEFirst day of the week of the ingressed_at date.
order_dateDATEDate of the Orders.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
product_nameSTRINGName of the Product.
brandSTRINGBrand of the Product.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
halo_taxonomySTRINGThe 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_levelINTEGERHalo level number (1-9). 1 = Brand level halo, Levels 2-9 are the HaloTaxonomy component, 9 being the specific Product Code (SKU).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
search_termSTRINGSearch Term being targeted.
category_idSTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
page_typeINTEGERLegacy, superseded by Placement.
attribution_modelINTEGERIndicate if data is from first or second model.
order_typeSTRINGType of the Order.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
direct_unit_salesNUMERICUnit sales from CitrusAd platform Attributions.
direct_sales_valueNUMERICRevenue from CitrusAd platform Attributions.
direct_conversionsNUMERICConversions from CitrusAd platform Attributions.
view_through_unit_salesNUMERICUnit sales from Impression View Thru Attributions.
view_through_sales_valueNUMERICRevenue from Impression View Thru Attributions.
view_through_conversionsNUMERICConversions from Impression View Thru Attributions.
halo_unit_salesNUMERICUnit sales from halo click Attributions.
halo_sales_valueNUMERICRevenue from halo click Attributions.
halo_conversionsNUMERICConversions from halo click Attributions.
total_click_salesNUMERICDirect unit sales + halo unit sales.
total_click_sales_valueNUMERICDirect sales value + halo sales value.
total_unit_salesNUMERICTotal click sales + view through unit sales.
total_sales_valueNUMERICTotal click sales value + view through sales value.
total_conversionsNUMERICTotal click conversions + view through conversions.
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
enable_sales_metricsBOOLEANUsed to set sales metrics to 0 for certain namespace/campaign types if required for reporting

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 NameData TypeDescription
envSTRING
namespaceSTRING
env_namespaceSTRING
ingressed_atDATE
yyyymmddSTRING
yyyymmSTRING
yyyySTRING
week_start_dateDATE
order_dateDATE
normalised_product_codeSTRING
product_codeSTRING
product_nameSTRING
brandSTRING
retailer_taxonomySTRING
halo_taxonomySTRING
halo_taxonomy_levelINTEGER
campaign_idSTRING
search_termSTRING
placementSTRING
platformSTRING
page_typeINTEGER
attribution_modelINTEGER
order_typeSTRING
impressionsNUMERIC
clicksNUMERIC
ad_spendNUMERIC
direct_unit_salesNUMERIC
direct_sales_valueNUMERIC
direct_conversionsNUMERIC
view_through_unit_salesNUMERIC
view_through_sales_valueNUMERIC
view_through_conversionsNUMERIC
halo_unit_salesNUMERIC
halo_sales_valueNUMERIC
halo_conversionsNUMERIC
total_click_salesNUMERIC
total_click_sales_valueNUMERIC
total_unit_salesNUMERIC
total_sales_valueNUMERIC
total_conversionsNUMERIC
retailer_group_1STRING
retailer_group_2STRING
supplier_group_1STRING
supplier_group_2STRING
supplier_group_3STRING
supplier_group_4STRING
enable_sales_metricsBOOLEAN
category_idSTRING
campaign_nameSTRING
campaign_start_dateTIMESTAMP
campaign_end_dateTIMESTAMP
campaign_typeSTRING
campaign_subtypeSTRING
wallet_idSTRING
active_stateSTRING
valid_stateSTRING
cta_textSTRING
supplier_idSTRING
retailer_idSTRING
retailer_nameSTRING
team_typeSTRING
company_nameSTRING
supplier_nameSTRING
category_nameSTRING
wallet_nameSTRING
wallet_available_balanceNUMERIC
supplier_company_nameSTRING
wallet_capture_dateDATE
geo_regionSTRING
local_timezoneSTRING
has_search_termINTEGER
has_categoryINTEGER
enhanced_attribution_flagSTRING
enhanced_attribution_flag_2STRING
retailer_taxonomy_haloSTRING
retailer_taxonomy_halo_2STRING
view_attribution_windowNUMERIC
view_attribution_window_2NUMERIC
click_attribution_windowNUMERIC
click_attribution_window_2NUMERIC
attribution_time_frameNUMERIC
attribution_time_frame_2NUMERIC
SQL Query

select
  DISTINCT
  halo.* EXCEPT(category_id,retailer_id,supplier_id),
  cast(halo.category_id as string) category_id,
  dc.campaign_name campaign_name,
  dc.campaign_start_date AS campaign_start_date,
  dc.campaign_end_date AS campaign_end_date,
  dc.campaign_type campaign_type,
  dc.campaign_subtype campaign_subtype,
  dc.wallet_id wallet_id,
  dc.active_state active_state,
  dc.valid_state valid_state,
  dc.cta_text,
  halo.supplier_id supplier_id,
  halo.retailer_id retailer_id,
  dr.retailer_name,
  dr.team_type team_type,
  dr.retailer_company_name company_name,
  ds.supplier_name supplier_name,
  cat.category_label AS category_name,
  dw.wallet_name wallet_name,
  dw.available_balance AS wallet_available_balance,
  ds.supplier_company_name AS supplier_company_name,
  DATE(dw.wallet_capture_date) AS wallet_capture_date,
  rn.geo_region,
  rn.local_timezone,
  if(ifnull(halo.search_term,&#39;&#39;)&lt;&gt;&#39;&#39; ,1,0) has_search_term,
  if(ifnull(cat.category_label,&#39;&#39;)&lt;&gt;&#39;&#39; ,1,0) has_category,
  dr.enhanced_attribution_flag,
  dr.enhanced_attribution_flag_2,
  dr.retailer_taxonomy_halo,
  dr.retailer_taxonomy_halo_2,
  dr.view_attribution_window,
  dr.view_attribution_window_2,
  dr.click_attribution_window,
  dr.click_attribution_window_2,
  dr.attribution_time_frame,
  dr.attribution_time_frame_2
from reporting.enhanced_attribution_agg halo
  left join reporting.campaign dc on halo.campaign_id = dc.campaign_id
                                                            and halo.env_namespace = dc.env_namespace
  left join reporting.retailer dr on halo.env_namespace  =  dr.env_namespace
                                                            and halo.retailer_id = dr.retailer_id
  left join reporting.supplier ds on halo.env_namespace  =  ds.env_namespace
                                                            and halo.supplier_id = ds.supplier_id
  left join reporting.category cat on cat.category_id  = halo.category_id
                                                            and cat.env_namespace = halo.env_namespace
                                                            and cat.retailer_id = dr.retailer_id
  left join reporting.wallet dw on dw.env_namespace = dc.env_namespace
                                                        and dw.wallet_id = dc.wallet_id
  left join reporting.env_namespace rn on halo.env_namespace = rn.env_namespace
where rn.geo_region &lt;&gt; &#39;NotProductionData&#39;

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 NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
zone_idSTRINGTimezone (TZ) identifier standardised by IANA time zone database.
banner_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on banner campaigns.
display_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on display campaigns.
wildcard_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on wildcard campaigns.
brand_page_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on brand page campaigns.
shotgun_banner_enable_sales_metricsBOOLEANFlag to indicate if sales metrics are enabled on shotgun banner campaigns.

reporting.fta_campaign_spend

  • NAMESPACE
  • RETAILER
  • SUPPLIER

FTA Campaign Spend data.

Layer: Reporting Datamart
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):
Partition: None

Foreign Key(s):
env_namespace on env_namespace.env_namespace = fta_campaign_spend.env_namespace;
campaign on campaign.env_namespace = fta_campaign_spend.env_namespace and campaign.campaign_id = fta_campaign_spend.campaign_id;
supplier on supplier.env_namespace = fta_campaign_spend.env_namespace and supplier.supplier_id = fta_campaign_spend.supplier_id;
retailer on retailer.env_namespace = fta_campaign_spend.env_namespace and retailer.retailer_id = fta_campaign_spend.retailer_id;
wallet on wallet.env_namespace = fta_campaign_spend.env_namespace and wallet.wallet_id = fta_campaign_spend.wallet_id;
catalog on catalog.env_namespace = fta_campaign_spend.env_namespace and catalog.catalog_id = fta_campaign_spend.catalog_id;
search_term on search_term.env_namespace = fta_campaign_spend.env_namespace and search_term.retailer_id = fta_campaign_spend.retailer_id and search_term.search_term = fta_campaign_spend.search_term;
product on product.env_namespace = fta_campaign_spend.env_namespace and product.product_code = fta_campaign_spend.product_code;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
namespaceSTRINGName of the Namespace.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
search_termSTRINGSearch Term being targeted.
product_codeSTRINGProduct code.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_nameSTRINGName of the Supplier.
campaign_nameSTRINGName of the Campaign.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
bucketed_costNUMERICBucketed cost.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
click_spendNUMERICAdSpend generated by clicks.
clicksINTEGERNumber of Clicks.
impressionsINTEGERTotal impressions.
conversionsINTEGERTotal standard Conversions (purchases of the exact Product in the Ad).
sales_revenueINTEGERTotal sales revenue generated by Conversions.
unit_salesINTEGERTotal number of Products purchased from Conversions.
bucket_dateDATEBucket date.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
enable_sales_metricsBOOLEANUsed to set sales metrics to 0 for certain namespace/campaign types if required for reporting

reporting.ledger_agg

  • NAMESPACE
  • RETAILER
  • SUPPLIER

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

Layer: Reporting Datamart
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):
Partition: None

Foreign Key(s):
env_namespace on env_namespace.env_namespace = ledger_agg.env_namespace;
retailer on retailer.env_namespace = ledger_agg.env_namespace and retailer.retailer_id = ledger_agg.retailer_id;
campaign on campaign.env_namespace = ledger_agg.env_namespace and campaign.campaign_id = ledger_agg.campaign_id;
team on team.env_namespace = ledger_agg.env_namespace and team.team_id = ledger_agg.team_id;
wallet on wallet.env_namespace = ledger_agg.env_namespace and wallet.wallet_id = ledger_agg.wallet_id;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
team_idSTRINGInternal identifier of the Team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
team_nameSTRINGName of the Team.
supplier_nameSTRINGName of the Supplier.
supplier_company_nameSTRINGCompany name of the Supplier.
retailer_nameSTRINGName of the Retailer.
retailer_company_nameSTRINGCompany name of the Retailer.
wallet_nameSTRINGName of the Wallet.
wallet_currency_codeSTRINGISO 4217 currency code
wallet_external_idSTRINGImplementation specific generic label.
campaign_nameSTRINGName of the Campaign.
campaign_typeSTRINGType of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
namespaceSTRINGName of the Namespace.
ledger_typeSTRINGDebit or Credit transaction type.
product_typeSTRINGType of the Product.
reasonSTRINGReason given for the transaction type.
wallet_archivedBOOLEANArchived status of the Wallet.
transaction_dateDATEDate of the transactions.
campaign_start_dateTIMESTAMPDate the Campaign will begin.
campaign_end_dateTIMESTAMPDate when the Campaign will end.
campaign_valid_stateSTRINGThe Campaign's valid state. (APPROVED, PENDING, REJECTED)
campaign_active_stateSTRINGThe Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
campaign_is_ftaSTRINGFlag indicating if the Campaign is Fixed Tenancy Agreement (FTA).
amountNUMERICTotal of transaction amounts.
campaign_fta_costNUMERICTotal amount being spent by the given Fixed Tenancy (FTA) Campaign.
campaign_max_cost_per_clickNUMERICThe maximum cost per click set for the Campaign.
campaign_max_total_spendNUMERICThe maximum total spend set for the Campaign.
campaign_max_daily_spendNUMERICThe maximum daily spend set for the Campaign.
campaign_total_spendNUMERICTotal AdSpend to date for the Campaign.
wallet_active_campaign_countNUMERICNumber of currently active Campaigns associated to the Wallet.
wallet_active_campaign_max_total_spendNUMERICAggregate CampaignMaxTotalSpend for all currently active Campaigns associated to the Wallet.
currency_valueFLOATLocal currency to USD conversion rate (current rate only).
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
transaction_countNUMERICTotal transaction count.

reporting.ledger_agg_2

  • NAMESPACE
  • RETAILER
  • SUPPLIER

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

Layer: Reporting Datamart
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):
Partition: None

Foreign Key(s):
env_namespace on env_namespace.env_namespace = ledger_agg_2.env_namespace;
retailer on retailer.env_namespace = ledger_agg_2.env_namespace and retailer.retailer_id = ledger_agg_2.retailer_id;
campaign on campaign.env_namespace = ledger_agg_2.env_namespace and campaign.campaign_id = ledger_agg_2.campaign_id;
team on team.env_namespace = ledger_agg_2.env_namespace and team.team_id = ledger_agg_2.team_id;
wallet on wallet.env_namespace = ledger_agg_2.env_namespace and wallet.wallet_id = ledger_agg_2.wallet_id;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
team_idSTRINGInternal identifier of the Team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
wallet_idSTRINGInternal identifier of the Wallet (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
team_nameSTRINGName of the Team.
supplier_nameSTRINGName of the Supplier.
supplier_company_nameSTRINGCompany name of the Supplier.
retailer_nameSTRINGName of the Retailer.
retailer_company_nameSTRINGCompany name of the Retailer.
wallet_nameSTRINGName of the Wallet.
wallet_currency_codeSTRINGISO 4217 currency code
wallet_external_idSTRINGImplementation specific generic label.
campaign_nameSTRINGName of the Campaign.
campaign_typeSTRINGType of Campaign (BANNER, BRAND_PAGE, DISPLAY, EMAIL, SHOTGUN_BANNER, WILDCARD)
namespaceSTRINGName of the Namespace.
ledger_typeSTRINGDebit or Credit transaction type.
product_typeSTRINGType of the Product.
reasonSTRINGReason given for the transaction type.
wallet_archivedBOOLEANArchived status of the Wallet.
transaction_timestampTIMESTAMPTimestamp of the transactions rounded up to the hour
campaign_start_dateTIMESTAMPDate the Campaign will begin.
campaign_end_dateTIMESTAMPDate when the Campaign will end.
campaign_valid_stateSTRINGThe Campaign's valid state. (APPROVED, PENDING, REJECTED)
campaign_active_stateSTRINGThe Campaign's active state as set in the platform. (ACTIVE, PAUSED, DRAFT, ARCHIVED).
campaign_is_ftaSTRINGFlag indicating if the Campaign is Fixed Tenancy Agreement (FTA).
amountNUMERICTotal of transaction amounts.
campaign_fta_costNUMERICTotal amount being spent by the given Fixed Tenancy (FTA) Campaign.
campaign_max_cost_per_clickNUMERICThe maximum cost per click set for the Campaign.
campaign_max_total_spendNUMERICThe maximum total spend set for the Campaign.
campaign_max_daily_spendNUMERICThe maximum daily spend set for the Campaign.
campaign_total_spendNUMERICTotal AdSpend to date for the Campaign.
wallet_active_campaign_countNUMERICNumber of currently active Campaigns associated to the Wallet.
wallet_active_campaign_max_total_spendNUMERICAggregate CampaignMaxTotalSpend for all currently active Campaigns associated to the Wallet.
currency_valueFLOATLocal currency to USD conversion rate (current rate only).
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
transaction_countINTEGERTotal transaction count.

reporting.order_ad_stats

  • NAMESPACE
  • RETAILER

Order and Realised Ad statistics data (aggregated).

Layer: Reporting Datamart
Category: Fact
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
namespaceSTRINGName of the Namespace.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
product_codeSTRINGProduct code.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
ad_stats_product_codeSTRINGProduct code.
order_dateDATEDate of the Orders.
conversions_from_adsNUMERICTotal Conversions occuring from CitrusAd Ads.
unit_sales_from_adsNUMERICTotal number of Products purchased from Conversions.
total_priceNUMERICTotal paid for the products ordered.
quantityNUMERICQuantity of the Product ordered.
order_itemsNUMERICTotal number of items ordered.
ordersNUMERICNumber of Orders made.
product_nameSTRINGName of the Product.
brandSTRINGBrand of the Product.
product_typeSTRINGType of the Product.
product_tagsSTRINGTags associated with the Product.
currency_codeSTRINGISO 4217 currency code
retailer_nameSTRINGName of the Retailer.
currency_valueFLOATLocal currency to USD conversion rate (current rate only).
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.

reporting.order_agg

  • NAMESPACE
  • RETAILER

Order data (aggregated).

Layer: Reporting Datamart
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):
Partition: order_date (by DAY)

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
issuer_team_idSTRINGInternal identifier of the Team (from the source system).
product_nameSTRINGName of the Product.
brandSTRINGBrand of the Product.
product_typeSTRINGType of the Product.
product_tagsSTRINGTags associated with the Product.
retailer_nameSTRINGName of the Retailer.
currency_codeSTRINGISO 4217 currency code
namespaceSTRINGName of the Namespace.
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
order_typeSTRINGType of the Order.
order_dateDATEDate of the Orders.
total_priceNUMERICTotal paid for the products ordered.
total_price_usdFLOATtotal_price in USD (current exchange rate only).
quantityNUMERICQuantity of the Product ordered.
order_itemsNUMERICTotal number of items ordered.
ordersNUMERICNumber of Orders made.

reporting.placement

  • NAMESPACE

Placement data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

Foreign Key(s):
env_namespace on env_namespace.env_namespace = placement.env_namespace;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
placement_idSTRINGInternal identifier of the Placement (from the source system).
ad_gen_idSTRINGAd gen id.
catalog_idsSTRINGCatalog ids.
display_nameSTRINGDisplay name of the Placement.
campaign_targetSTRINGCampaign target.
campaign_typesSTRINGList of applicable campaign types.
namespaceSTRINGName of the Namespace.
archivedSTRINGArchived status of the Placement.

reporting.product

  • NAMESPACE
  • RETAILER

Product data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
normalised_product_codeSTRINGProduct code prefixed with leading zeros to be 16 characters long.
product_codeSTRINGProduct code.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
product_nameSTRINGName of the Product.
product_typeSTRINGType of the Product.
retailer_nameSTRINGName of the Retailer.
currency_codeSTRINGISO 4217 currency code
product_tagsSTRINGTags associated with the Product.
product_image_urlSTRINGURL to an image of the Product for the Ad.
brandSTRINGBrand of the Product.
google_product_taxonomySTRINGGoogle taxonomy (product hierarchy) of the Product. Levels of the hierarchy delimited using > symbol.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
namespaceSTRINGName of the Namespace.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
catalog_nameSTRINGName of the Catalog.
typeSTRINGType of Product.
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
seller_idSTRINGSeller that the Product is applicable for.
upcSTRINGUPC of the Product.
inventorySTRINGHow much of the Product is in stock.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.

reporting.realised_ad_agg

  • NAMESPACE
  • RETAILER
  • SUPPLIER

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

Layer: Reporting Datamart
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):
Partition: ingressed_at (by DAY)

Foreign Key(s):
env_namespace on env_namespace.env_namespace = realised_ad_agg.env_namespace;
retailer on retailer.env_namespace = realised_ad_agg.env_namespace and retailer.retailer_id = realised_ad_agg.retailer_id;
catalog on catalog.env_namespace = realised_ad_agg.env_namespace and catalog.catalog_id = realised_ad_agg.catalog_id;
product on product.env_namespace = realised_ad_agg.env_namespace and product.catalog_id = realised_ad_agg.catalog_id and product.normalised_product_code = realised_ad_agg.product_code;
supplier on supplier.env_namespace = realised_ad_agg.env_namespace and supplier.supplier_id = realised_ad_agg.supplier_id;
campaign on campaign.env_namespace = realised_ad_agg.env_namespace and campaign.campaign_id = realised_ad_agg.campaign_id;
category on category.env_namespace = realised_ad_agg.env_namespace and category.retailer_id = realised_ad_agg.retailer_id and category.category_id = realised_ad_agg.category

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
ingressed_atDATEDate the Ads were realised.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
search_termSTRINGSearch Term being targeted.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
product_codeSTRINGProduct code.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
content_standard_idSTRINGContentStandardId as specified in the Ad Request.
slot_idSTRINGBanner SlotId as specified in the Ad Request.
categorySTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
target_product_codeSTRINGTarget Product code.
has_search_termINTEGERFlag to indicate if the Ad is targeting a Search Term.
has_categoryINTEGERFlag to indicate if the Ad is targeting a Category.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
sales_revenueNUMERICTotal sales revenue generated by Conversions.
ad_revenueNUMERICPortion of Ad spend retained by the Retailer.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
click_spendNUMERICAdSpend generated by clicks.
impression_spendNUMERICAdSpend generated by impressions.
position_sumNUMERICSum of all postions for the Ads. Used to calculate average ad position (avg position = position_sum / ads_with_position_count).
placement_idSTRINGInternal identifier of the Placement (from the source system).
namespaceSTRINGName of the Namespace.
ads_with_position_countNUMERICTotal number of Ads that have a position set. avg position = position_sum / ads_with_position_count.
enable_sales_metricsBOOLEANUsed to set sales metrics to 0 for certain namespace/campaign types if required for reporting

reporting.realised_ad_agg_2

  • NAMESPACE
  • RETAILER
  • SUPPLIER

Realised Ad data (aggregated by customerid). Includes customer_id, but no breakdown by Campaign, Placement and Product.

Layer: Reporting Datamart
Category: Fact
Status: Published

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

Foreign Key(s):
env_namespace on env_namespace.env_namespace = realised_ad_agg_2.env_namespace;
retailer on retailer.env_namespace = realised_ad_agg_2.env_namespace and retailer.retailer_id = realised_ad_agg_2.retailer_id;
supplier on supplier.env_namespace = realised_ad_agg_2.env_namespace and supplier.supplier_id = realised_ad_agg_2.supplier_id;
search_term on search_term.env_namespace = realised_ad_agg_2.env_namespace and search_term.retailer_id = realised_ad_agg_2.retailer_id and search_term.search_term = realised_ad_agg_2.search_term;
category on category.env_namespace = realised_ad_agg_2.env_namespace and category.category_id = realised_ad_agg_2.category and category.retailer_id = realised_ad_agg_2.retailer_id;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
customer_idSTRINGCustomer identifier as provided by the Retailer (as in loyalty number etc, where available). Sometimes used for Attribution.
ingressed_atDATEDate the Ads were realised.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
search_termSTRINGSearch Term being targeted.
categorySTRINGCategory being targeted.
namespaceSTRINGName of the Namespace.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
retailer_nameSTRINGName of the Retailer.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
supplier_nameSTRINGName of the Supplier.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.

reporting.realised_ad_agg_3

  • NAMESPACE
  • RETAILER
  • SUPPLIER

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

Layer: Reporting Datamart
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):
Partition: ingressed_at (by DAY)

Foreign Key(s):
env_namespace on env_namespace.env_namespace = realised_ad_agg_3.env_namespace;
retailer on retailer.env_namespace = realised_ad_agg_3.env_namespace and retailer.retailer_id = realised_ad_agg_3.retailer_id;
catalog on catalog.env_namespace = realised_ad_agg_3.env_namespace and catalog.catalog_id = realised_ad_agg_3.catalog_id;
product on product.env_namespace = realised_ad_agg_3.env_namespace and product.catalog_id = realised_ad_agg_3.catalog_id and product.normalised_product_code = realised_ad_agg_3.product_code;
supplier on supplier.env_namespace = realised_ad_agg_3.env_namespace and supplier.supplier_id = realised_ad_agg_3.supplier_id;
campaign on campaign.env_namespace = realised_ad_agg_3.env_namespace and campaign.campaign_id = realised_ad_agg_3.campaign_id;
category on category.env_namespace = realised_ad_agg_3.env_namespace and category.retailer_id = realised_ad_agg_3.retailer_id and category.category_id = realised_ad_agg_3.category;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
ingressed_atTIMESTAMPTimestamp the Ads were realised.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
campaign_idSTRINGInternal identifier of the Campaign (from the source system).
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
search_termSTRINGSearch Term being targeted.
generic_search_termSTRINGSame as SearchTerm except leading and trailing whitespace is removed to improve consolidation.
product_codeSTRINGProduct code.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
content_standard_idSTRINGContentStandardId as specified in the Ad Request.
slot_idSTRINGBanner SlotId as specified in the Ad Request.
categorySTRINGCategory being targeted.
placementSTRINGDisplay name of the Placement.
platformSTRINGThe platform of the User as specified in the Request. Only populated if the requester specifies the platform as platform:<VALUE> as a filter.
ad_typeINTEGERType of Ad (0 = Product, 1 = Banner, 4 = BannerX).
target_product_codeSTRINGTarget Product code.
has_search_termINTEGERFlag to indicate if the Ad is targeting a Search Term.
has_categoryINTEGERFlag to indicate if the Ad is targeting a Category.
sales_revenueNUMERICTotal sales revenue generated by Conversions.
ad_revenueNUMERICPortion of Ad spend retained by the Retailer.
ad_spendNUMERICLocal currency amount charged by the Retailer to the Supplier for the Realised Ads.
impressionsNUMERICTotal impressions.
clicksNUMERICNumber of Clicks.
conversionsNUMERICTotal standard Conversions (purchases of the exact Product in the Ad).
unit_salesNUMERICTotal number of Products purchased from Conversions.
click_spendNUMERICAdSpend generated by clicks.
impression_spendNUMERICAdSpend generated by impressions.
retailer_nameSTRINGName of the Retailer.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
supplier_nameSTRINGName of the Supplier.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
enable_sales_metricsBOOLEANUsed to set sales metrics to 0 for certain namespace/campaign types if required for reporting

reporting.realised_ad_agg_view

  • NAMESPACE
  • RETAILER
  • SUPPLIER

Realised Ad view (aggregated) + FTA Campaign spend data. Union of realised_ad_agg and fta_campaign_spend.

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

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

Foreign Key(s):
Refer to view code

Column NameData TypeDescription
envSTRING
env_namespaceSTRING
ingressed_atDATE
catalog_idSTRING
campaign_idSTRING
supplier_idSTRING
search_termSTRING
generic_search_termSTRING
product_codeSTRING
retailer_idSTRING
content_standard_idSTRING
slot_idSTRING
categorySTRING
placementSTRING
platformSTRING
ad_typeINTEGER
target_product_codeSTRING
sales_revenueNUMERIC
ad_revenueNUMERIC
ad_spendNUMERIC
impressionsNUMERIC
clicksNUMERIC
conversionsNUMERIC
unit_salesNUMERIC
click_spendNUMERIC
impression_spendNUMERIC
position_sumNUMERIC
ads_with_position_countNUMERIC
namespaceSTRING
enable_sales_metricsBOOLEAN
ad_type_nameSTRING
catalog_nameSTRING
supplier_nameSTRING
supplier_industrySTRING
supplier_business_identifierSTRING
supplier_company_nameSTRING
retailer_nameSTRING
retailer_industrySTRING
retailer_business_identifierSTRING
retailer_company_nameSTRING
retailer_group_1STRING
retailer_group_2STRING
supplier_group_1STRING
supplier_group_2STRING
supplier_group_3STRING
supplier_group_4STRING
campaign_nameSTRING
campaign_total_spendNUMERIC
currency_codeSTRING
is_ftaSTRING
wallet_available_balanceNUMERIC
campaign_active_stateSTRING
campaign_placementsSTRING
campaign_typeSTRING
wallet_nameSTRING
campaign_start_dateTIMESTAMP
campaign_max_total_spendNUMERIC
campaign_created_atTIMESTAMP
campaign_max_cost_per_clickNUMERIC
campaign_end_dateTIMESTAMP
wallet_idSTRING
campaign_catalog_productsSTRING
wallet_total_purchased_creditsNUMERIC
campaign_subtypeSTRING
campaign_valid_stateSTRING
campaign_impression_goalNUMERIC
fta_costNUMERIC
wallet_archivedBOOLEAN
campaign_max_daily_spendNUMERIC
campaign_total_spend_usdNUMERIC
wallet_external_idSTRING
campaign_updated_atTIMESTAMP
wallet_credit_limitNUMERIC
campaign_attr_1STRING
cta_textSTRING
wallet_team_nameSTRING
wallet_capture_dateTIMESTAMP
earliest_top_up_amount_usdNUMERIC
category_labelSTRING
product_nameSTRING
product_typeSTRING
product_tagsSTRING
brandSTRING
typeSTRING
upcSTRING
geo_regionSTRING
local_timezoneSTRING
sales_revenue_usdFLOAT
ad_revenue_usdFLOAT
ad_spend_usdFLOAT
SQL Query
with ads as (
  select
    faa.env,
    faa.env_namespace,
    faa.ingressed_at,
    faa.catalog_id,
    faa.campaign_id,
    faa.supplier_id,
    faa.search_term,
    faa.generic_search_term,
    faa.product_code,
    faa.retailer_id,
    faa.content_standard_id,
    faa.slot_id,
    faa.category,
    faa.placement,
    faa.platform,
    faa.ad_type,
    faa.target_product_code,
    faa.sales_revenue,
    faa.ad_revenue,
    faa.ad_spend,
    faa.impressions,
    faa.clicks,
    faa.conversions,
    faa.unit_sales,
    faa.click_spend,
    faa.impression_spend,
    faa.position_sum,
    faa.ads_with_position_count,
    faa.namespace,
    faa.enable_sales_metrics
  from
    reporting.realised_ad_agg faa
  where
    faa.ingressed_at &gt;= &#39;2000-01-01&#39;
  union all
  select 
    fta.env,
    fta.env_namespace,
    fta.bucket_date ingressed_at,
    fta.catalog_id,
    fta.campaign_id,
    fta.supplier_id,
    fta.search_term,
    &#39;&#39; as generic_search_term,
    fta.product_code,
    fta.retailer_id,
    cast(&#39;&#39; AS string)  as content_standard_id,
    cast(&#39;&#39; AS string)  slot_id,
    cast(&#39;&#39; AS string)  category,
    cast(&#39;&#39; AS string)  placement,
    cast(&#39;&#39; AS string)  platform,
    fta.ad_type,
    cast(&#39;&#39; AS string) target_product_code,
    0 as sales_revenue,
    0 as ad_revenue,
    fta.bucketed_cost ad_spend,
    NULL as impressions,
    NULL as clicks,
    0 as conversions,
    0 as unit_sales,
    fta.bucketed_cost click_spend,
    0 as impression_spend,
    0 as position_sum,
    0 as ads_with_position_count,
    fta.namespace,
    fta.enable_sales_metrics
  from reporting.fta_campaign_spend fta
)
select 
  ads.*,
  ad.ad_type_name,
  dctl.catalog_name,
  ds.supplier_name,
  ds.supplier_industry,
  ds.supplier_business_identifier,
  ds.supplier_company_name,
  dr.retailer_name,
  dr.retailer_industry,
  dr.retailer_business_identifier,
  dr.retailer_company_name,
  dr.retailer_group_1,
  dr.retailer_group_2,
  ds.supplier_group_1,
  ds.supplier_group_2,
  ds.supplier_group_3,
  ds.supplier_group_4,
  dcmp.campaign_name,
  /*domo specific*/
  dcmp.campaign_total_spend,
  dcmp.currency_code,
  dcmp .is_fta,
  dcmp.wallet_available_balance,
  dcmp.campaign_active_state,
  dcmp.campaign_placements,
  dcmp.campaign_type,
  dcmp.wallet_name,
  dcmp.campaign_start_date,
  dcmp.campaign_max_total_spend,
  dcmp.campaign_created_at,
  dcmp.campaign_max_cost_per_click,
  dcmp.campaign_end_date,
  dcmp.wallet_id,
  dcmp.campaign_catalog_products,
  dcmp.wallet_total_purchased_credits,
  dcmp.campaign_subtype,
  dcmp.campaign_valid_state,
  dcmp.campaign_impression_goal,
  dcmp.fta_cost,
  dcmp.wallet_archived,
  dcmp.campaign_max_daily_spend,
  dcmp.campaign_total_spend_usd,
  dcmp.wallet_external_id,
  dcmp.campaign_updated_at,
  dcmp.wallet_credit_limit,
  dcmp.campaign_attr_1,
  dcmp.cta_text,
  w.team_name wallet_team_name,
  w.wallet_capture_date,
  w.earliest_top_up_amount_usd,
  ca.category_label,
  p.product_name,
  p.product_type,
  p.product_tags,
  p.brand,
  p.type,
  p.upc,
  rn.geo_region as geo_region,
  rn.local_timezone as local_timezone,
  ads.sales_revenue/currency.value sales_revenue_usd,
  ads.ad_revenue/currency.value ad_revenue_usd,
  ads.ad_spend/currency.value ad_spend_usd
from
  ads
  left join reporting.catalog dctl on dctl.catalog_id = ads.catalog_id
                                  and dctl.env_namespace = ads.env_namespace
  left join reporting.campaign dcmp on dcmp.campaign_id = ads.campaign_id
                                   and dcmp.env_namespace = ads.env_namespace
  left join reporting.retailer dr on dr.retailer_id = ads.retailer_id
                                 and dr.env_namespace = ads.env_namespace
  left join reporting.supplier ds on ds.supplier_id = ads.supplier_id
                                 and ds.env_namespace = ads.env_namespace
  left join reporting.view_ad_type ad on ads.ad_type = ad.ad_type
  left join reporting.wallet w     on dcmp.env_namespace = w.env_namespace
                                  and dcmp.wallet_id = w.wallet_id
  left join reporting.category ca  on ads.env_namespace = ca.env_namespace
                                  and ads.category = ca.category_id
                                  and ads.retailer_id = ca.retailer_id
  left join reporting.product p  on ads.env_namespace = p.env_namespace
                                and ads.catalog_id = p.catalog_id
                                and ads.product_code = p.normalised_product_code
  left join reporting.env_namespace rn on ads.env_namespace = rn.env_namespace
  left join reporting.currency currency on dcmp.currency_code =currency.currency and currency.base=&#39;USD&#39;

reporting.retailer

  • NAMESPACE
  • RETAILER

Retailer 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 = retailer.env_namespace;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
retailer_nameSTRINGName of the Retailer.
team_typeSTRINGType of Team (RETAILER).
retailer_company_nameSTRINGCompany name of the Retailer.
retailer_business_identifierSTRINGImplementation specific generic label.
retailer_industrySTRINGIndustry of the Retailer.
currency_codeSTRINGISO 4217 currency code
namespaceSTRINGName of the Namespace.
ads_requested_capNUMERICMax 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_capNUMERICMax 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_termsSTRINGNot in use.
global_suppress_flagSTRINGFlag to indicate Retailer is to be excluded from reporting (eg test data).
retailer_group_1STRINGRetailer team group membership #1.
retailer_group_2STRINGRetailer team group membership #2.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.
created_atTIMESTAMPDate the record was created in the source system.
updated_atTIMESTAMPDate the record was updated in the source system.
enhanced_attribution_flagSTRINGIf true, the first enhanced attribution model will be calculated.
enhanced_attribution_flag_2STRINGIf true, the second enhanced attribution model will be calculated.
retailer_taxonomy_haloSTRINGComma separated list of what halo levels will be computed for model 1.
retailer_taxonomy_halo_2STRINGComma separated list of what halo levels will be computed for model 2.
view_attribution_windowNUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution (1st model).
view_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution (2nd model).
click_attribution_windowNUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attribution (1st model).
click_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attribution (2nd model).
attribution_time_frameNUMERICDetermines how many days of attribution results should be stored for the first model.
attribution_time_frame_2NUMERICDetermines how many days of attribution results should be stored for the second model.

reporting.retailer_supplier

  • NAMESPACE
  • RETAILER
  • SUPPLIER

Retailer centric Supplier data (only relevant Suppliers as referenced within Realised Ads generated against the Retailer's Catalogs).

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

Foreign Key(s):
Not Applicable

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
supplier_group_1STRINGSupplier group membership #1.
supplier_group_2STRINGSupplier group membership #2.
supplier_group_3STRINGSupplier group membership #3.
supplier_group_4STRINGSupplier group membership #4.
supplier_nameSTRINGName of the Supplier.
supplier_company_nameSTRINGCompany name of the Supplier.
supplier_business_identifierSTRINGImplementation specific generic label.
supplier_industrySTRINGIndustry of the Supplier.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.

reporting.search_term

  • NAMESPACE
  • RETAILER

Search Term data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

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

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
retailer_nameSTRINGName of the Retailer.
search_termSTRINGSearch Term being targeted.
num_ad_requestsINTEGERTotal number of Requests.
num_realised_adsINTEGERTotal number of Ads realised.
ad_request_rankINTEGERRank of the Search Term by total Requests.
ad_stat_rankINTEGERRank of the Search Term by total Ads realised.
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.

reporting.supplier

  • NAMESPACE
  • SUPPLIER

Supplier data.

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

Foreign Key(s):
env_namespace on env_namespace.env_namespace = supplier.env_namespace;

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
supplier_nameSTRINGName of the Supplier.
team_typeSTRINGType of Team (SUPPLIER).
supplier_company_nameSTRINGCompany name of the Supplier.
supplier_business_identifierSTRINGImplementation specific generic label.
supplier_industrySTRINGIndustry of the Supplier.
namespaceSTRINGName of the Namespace.
supplier_group_1STRINGSupplier team group membership #1.
supplier_group_2STRINGSupplier team group membership #2.
supplier_group_3STRINGSupplier team group membership #3.
supplier_group_4STRINGSupplier team group membership #4.
created_atTIMESTAMPDate the record was created in the source system.
updated_atTIMESTAMPDate the record was updated in the source system.
last_campaign_created_idSTRINGInternal identifier of the Campaign last created by the Supplier Team (from the source system).
last_campaign_created_dateTIMESTAMPDate the Supplier created the most recent Campaign.
last_campaign_updated_idSTRINGInternal identifier of the Campaign last modified by the Supplier Team (from the source system).
last_campaign_updated_dateTIMESTAMPDate the Supplier updated the most recent Campaign.
total_campaignsINTEGERTotal number of Campaigns associated with Supplier (including inactive).
total_active_campaignsINTEGERTotal active Campaigns associated with the Supplier.
total_spendNUMERICTotal AdSpend to date for the Supplier across all Campaigns.
last_spend_dateDATEMost recent date that AdSpend was incurred by the Supplier.
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
is_activeBOOLEANFlag to indicate that the record is active in the source system.
is_deletedBOOLEANFlag to indicate that the record has been soft deleted in the source system.
is_currentBOOLEANFlag if the row is the latest version in the source system.

reporting.supplier_product

  • NAMESPACE
  • SUPPLIER

Supplier centric Product data (only relevant Products as referenced within Realised Ads generated by Supplier Campaigns).

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

Foreign Key(s):
Not Applicable

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
supplier_group_1STRINGSupplier group membership #1.
supplier_group_2STRINGSupplier group membership #2.
supplier_group_3STRINGSupplier group membership #3.
supplier_group_4STRINGSupplier group membership #4.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
product_codeSTRINGProduct code.
product_nameSTRINGName of the Product.
product_typeSTRINGType of the Product.
retailer_taxonomySTRINGTaxonomy (product hierarchy) of the Product as specified by the Retailer.
google_product_taxonomySTRINGGoogle taxonomy (product hierarchy) of the Product. Levels of the hierarchy delimited using > symbol.
catalog_idSTRINGInternal identifier of the Catalog (from the source system).
catalog_nameSTRINGName of the Catalog.
product_image_urlSTRINGURL to an image of the Product for the Ad.
brandSTRINGBrand of the Product.

reporting.supplier_retailer

  • NAMESPACE
  • SUPPLIER
  • RETAILER

Supplier centric Retailer data (only relevant Retailers as referenced within Realised Ads generated by Supplier Campaigns).

Layer: Reporting Datamart
Category: Dimension
Status: Published

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

Foreign Key(s):
Not Applicable

Column NameData TypeDescription
envSTRINGEnvironment the Namespace is deployed in.
env_namespaceSTRINGNamespace prefixed with the Environment it is deployed in.
supplier_idSTRINGInternal identifier of the Supplier team (from the source system).
supplier_group_1STRINGRetailer group membership #1.
supplier_group_2STRINGRetailer group membership #2.
supplier_group_3STRINGRetailer group membership #3.
supplier_group_4STRINGRetailer group membership #4.
retailer_idSTRINGInternal identifier of the Retailer team (from the source system).
retailer_group_1STRINGRetailer group membership #1.
retailer_group_2STRINGRetailer group membership #2.
retailer_nameSTRINGName of the Retailer.
retailer_company_nameSTRINGCompany name of the Retailer.
retailer_business_identifierSTRINGImplementation specific generic label.
retailer_industrySTRINGIndustry of the Retailer.
currency_codeSTRINGISO 4217 currency code
geo_regionSTRINGGeographical region of the Namespace (as defined by CitrusAd).
local_timezoneSTRINGNot in use.
supplier_nameSTRINGName of the Supplier.
enhanced_attribution_flagSTRINGIf true, the first enhanced attribution model will be calculated.
enhanced_attribution_flag_2STRINGIf true, the second enhanced attribution model will be calculated.
retailer_taxonomy_haloSTRINGComma separated list of what halo levels will be computed for model 1.
retailer_taxonomy_halo_2STRINGComma separated list of what halo levels will be computed for model 2.
view_attribution_windowNUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution.
view_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for View Thru Attribution (2nd model).
click_attribution_windowNUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attribution (1st model).
click_attribution_window_2NUMERICMax number of days allowed between the Order and Realised Ad for Halo Click Attributi