Schema Definitions

Epsilon Retail Media's documentation is now centralized with our knowledgebase!

For up to date guides, please view this page in the new Integration APIs space of our documentation.

Dataset Naming Conventions

Access to tables and views is provided via a single, centralised project (insight-platform-external-iam), with each client having their own distinct client name embedded within their dataset names.

For example, for a client named “Company A”, the client name assigned might be “companya”. This would then be used to prefix dataset names. For example, sharing the Reporting Dataset would be via a dataset called: -

insight-platform-external-iam.companya_insight_reporting

Epsilon Retail Media will advise a client upon provisioning an endpoint what the names of their datasets will be.

---

Core Dataset

Location: insight-platform-external-iam.<client name>_<platform instance name>_analytics

(eg companya_retailerb_analytics)

Dimensions

Name	Access	Purpose
currency_aud	namespace retailer	Current conversion rates between the base currency (AUD) to other common currencies. ISO 4217 currency codes used.
currency_usd	namespace retailer	Current conversion rates between the base currency (USD) to other common currencies. ISO 4217 currency codes used.
dim_campaign	namespace retailer	Campaign master data dimension.
dim_campaign_attr	namespace retailer	Campaign custom attributes (certain retailers only).
dim_catalog	namespace retailer	Catalog master data dimension.
dim_category	namespace retailer	Category dimension.
dim_env_namespace	namespace retailer	Namespace dimension.
dim_placement	namespace retailer	Placement dimension.
dim_product	namespace retailer	Product dimension.
dim_retailer	namespace retailer	Retailer dimension.
dim_search_term	namespace retailer	Search Term dimension.
dim_supplier	namespace retailer	Supplier dimension.
dim_team	namespace retailer	Team dimension.
dim_user	namespace retailer	User dimension.
dim_wallet	namespace retailer	Wallet dimension.

Facts

Name	Access	Purpose
fact_ad_request	namespace retailer	Request fact (full detail).
fact_ad_request_agg	namespace retailer	Request fact (aggregated).
fact_ad_request_agg_ltz	namespace retailer	Request fact (aggregated) in local timezone.
fact_enhanced_attribution	namespace retailer	Enhanced Attribution (full detail, first model only).
fact_enhanced_attribution_2	namespace retailer	Enhanced Attribution (full detail, second model only).
fact_enhanced_attribution_agg	namespace retailer	Enhanced Attribution (aggregated).
fact_enhanced_attribution_agg_ltz	namespace retailer	Enhanced Attribution (aggregated) in local timezone.
fact_fta_campaign_spend	namespace retailer	FTA Campaign Spend fact.
fact_ledger	namespace retailer	Ledger fact (full detail).
fact_ledger_agg	namespace retailer	Ledger fact (aggregated into UTC+0 day buckets).
fact_ledger_agg_2	namespace retailer	Ledger fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
fact_ledger_agg_2_ltz	namespace retailer	Ledger fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.
fact_ledger_agg_ltz	namespace retailer	Ledger fact (aggregated into local timezone day buckets).
fact_order	namespace retailer	Order fact (full detail).
fact_order_agg	namespace retailer	Order fact (aggregated).
fact_order_agg_ltz	namespace retailer	Order fact (aggregated in local timezone dates).
fact_realised_ad	namespace retailer	Realised Ad fact (full detail).
fact_realised_ad_agg	namespace retailer	Realised Ad fact (aggregated into UTC+0 day buckets).
fact_realised_ad_agg_2	namespace retailer	Realised Ad fact (aggregated, customer centric). Includes customer_id, but no breakdown by Campaign, Placement and Product.
fact_realised_ad_agg_3	namespace retailer	Realised Ad fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
fact_realised_ad_agg_3_live	namespace	Realised Ad fact (aggregated into UTC+0 hourly buckets). Only for current + previous 2 Days.
fact_realised_ad_agg_3_ltz	namespace retailer	Realised Ad fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.
fact_realised_ad_agg_3_ltz_live	namespace	Realised Ad fact (aggregated into local timezone hourly buckets). Only for current + previous 2 days.
fact_realised_ad_agg_4a	namespace retailer	Realised Ad fact (aggregated by day). Based on fact_realised_ad with keyword and keyword_matchtype. Includes metrics aggregated at the search term level.
fact_realised_ad_agg_4a_ltz	namespace retailer	Realised Ad fact (aggregated by day). Based on fact_realised_ad with keyword and keyword_matchtype. Includes metrics aggregated at the search term level.
fact_realised_ad_agg_ltz	namespace retailer	Realised Ad fact (aggregated into local timezone day buckets).
fact_report_request_tracking	namespace retailer	Epsilon Retail Media client reporting usage fact.
fact_user_team	namespace retailer	List of all Team Users.

Logging

Name	Access	Purpose
log_status	namespace retailer	Logging table. Records when this dataset has been updated (both start time and end time with status).

Reporting Datamart

Location: insight-platform-external-iam.<client name>_insight_reporting

(eg companya_insight_reporting)

Dimensions

Name	Access	Purpose
campaign	supplier retailer namespace	Campaign data.
catalog	retailer namespace	Catalog data.
category	namespace	Category data.
deduped_product	retailer namespace	Deduped product data.
env_namespace	namespace	Namespace data.
placement	namespace	Placement data.
product	retailer namespace	Product data.
retailer	retailer namespace	Retailer data.
retailer_supplier	retailer	Retailer centric Supplier data (only relevant Suppliers as referenced within Realised Ads generated against the Retailer's Catalogs).
search_term	namespace	Search Term data.
supplier	supplier namespace	Supplier data.
supplier_product	supplier	Supplier centric Product data (only relevant Products as referenced within Realised Ads generated by Supplier Campaigns).
supplier_retailer	supplier	Supplier centric Retailer data (only relevant Retailers as referenced within Realised Ads generated by Supplier Campaigns).
team	supplier retailer namespace	Team data.
user	namespace	User data.
user_team	namespace	Team users data.
wallet	supplier retailer namespace	Wallet data.

Facts

Name	Access	Purpose
ad_request_agg	retailer namespace	Request data (aggregated).
enhanced_attribution_agg	retailer namespace	Enhanced Attribution data (aggregated, both models).
external_attribution_agg	namespace	external Attribution data (aggregated, both models).
external_attribution_agg_ltz	namespace	external Attribution data (aggregated, both models) LTZ Version.
fta_campaign_spend	supplier namespace	FTA Campaign Spend data.
ledger_agg	supplier retailer namespace	Ledger data (aggregated into UTC+0 day buckets).
ledger_agg_2	supplier retailer namespace	Ledger data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
order_ad_stats	retailer namespace	Order and Realised Ad statistics data (aggregated).
order_agg	retailer namespace	Order data (aggregated).
realised_ad_agg	supplier	Realised Ad data (aggregated into UTC+0 day buckets).
realised_ad_agg_2	namespace	Realised Ad data (aggregated by customerid). Includes customer_id, but no breakdown by Campaign, Placement and Product.
realised_ad_agg_3	supplier	Realised Ad data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
wallet_balance	supplier retailer namespace	Wallet balance data.

Decorated Facts

Name	Access	Purpose
ad_request__realised_ad_ltz_view	namespace	Union of ad_request_agg_view and realised_ad_agg_view (local timezone). Allows for convenient calculation of measures that require both Request and Realised Ad statistics (for example a ratio of impressions vs ads requested).
ad_request__realised_ad_view	retailer namespace	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).
ad_request_agg_ltz_view	namespace	Request view (aggregated in local timezone). Decorated version of ad_request_agg.
ad_request_agg_view	retailer namespace	Request view (aggregated). Decorated version of ad_request_agg.
enhanced_attribution_agg_ltz_view	namespace	Enhanced Attribution view (aggregated, both models in local timezone). Decorated version of enhanced_attribution_agg.
enhanced_attribution_agg_view	retailer namespace	Enhanced Attribution view (aggregated, both models). Decorated version of enhanced_attribution_agg.
external_attribution_agg_ltz_view	namespace	External Attribution view (aggregated, both models) LTZ version. Decorated version of external_attribution_agg_ltz.
external_attribution_agg_view	namespace	External Attribution view (aggregated, both models). Decorated version of external_attribution_agg.
realised_ad_agg_ltz_view	namespace	Realised Ad view (aggregated in local timezone) + FTA Campaign spend data. Union of realised_ad_agg and fta_campaign_spend.
realised_ad_agg_view	retailer namespace	Realised Ad view (aggregated) + FTA Campaign spend data. Union of realised_ad_agg and fta_campaign_spend.

Visualisation

Location: insight-platform-external-iam.<client name>_insight_reporting

(eg companya_insight_reporting)

Name	Purpose
viz_global_campaign_out	Campaign data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is campaign.
viz_global_catalog_out	Catalog data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is catalog.
viz_global_category_out	Category data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is category.
viz_global_daily_wallet_balance	Daily wallet balance data.
viz_global_decorated_ad__adrequest_stats_out	Request and Realised Ad data (aggregated and unioned). Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is ad_request__realised_ad_view.
viz_global_decorated_ad_stats_out	Realised Ad (aggregated) data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is realised_ad_agg_view.
viz_global_decorated_adrequests_stats_out	Request (aggregated) data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is ad_request_agg_view.
viz_global_decorated_bucketed_order_ad_stats_out	Order and Realised Ad (aggregated) data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is order_ad_stats.
viz_global_decorated_report_request_tracking_out	Epsilon Retail Media client reporting usage (aggregated) data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is report_request_tracking.
viz_global_halo_attribution_out	Enhanced Attribution (aggregated, both models) data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is enhanced_attribution_agg_view.
viz_global_ledger_out	Ledger (aggregated) data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is ledger_agg.
viz_global_product_out	Product data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is product.
viz_global_retailer_out	Product data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is retailer.
viz_global_sov_category_mth_out	Share of voice by Category data (month buckets). Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is insights_sov_category.
viz_global_sov_category_week_out	Share of voice by Category data (weekly buckets). Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is insights_sov_category.
viz_global_sov_search_term_mth_out	Share of voice by Search Term data (month buckets). Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is insights_sov_search_term.
viz_global_supplier_out	Supplier data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is supplier.
viz_global_team_out	Team data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is team.
viz_global_team_user_out	Team Users data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is user_team.
viz_global_wallet_balance_out	Wallet balance (daily) data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is wallet_balance.
viz_global_wallet_out	Wallet data. Use to reconcile to the Epsilon Retail Media BI platform (if applicable). Legacy use only, preferred view to use is wallet.

Version: 1ace13f