Schema Definitions

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

CitrusAd 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

NameAccessPurpose
currency_audnamespace
retailer
Current conversion rates between the base currency (AUD) to other common currencies. ISO 4217 currency codes used.
currency_usdnamespace
retailer
Current conversion rates between the base currency (USD) to other common currencies. ISO 4217 currency codes used.
dim_campaignnamespace
retailer
Campaign master data dimension.
dim_campaign_attrnamespace
retailer
Campaign custom attributes (certain retailers only).
dim_catalognamespace
retailer
Catalog master data dimension.
dim_categorynamespace
retailer
Category dimension.
dim_env_namespacenamespace
retailer
Namespace dimension.
dim_placementnamespace
retailer
Placement dimension.
dim_productnamespace
retailer
Product dimension.
dim_retailernamespace
retailer
Retailer dimension.
dim_search_termnamespace
retailer
Search Term dimension.
dim_suppliernamespace
retailer
Supplier dimension.
dim_teamnamespace
retailer
Team dimension.
dim_usernamespace
retailer
User dimension.
dim_walletnamespace
retailer
Wallet dimension.

Facts

NameAccessPurpose
fact_ad_requestnamespace
retailer
Request fact (full detail).
fact_ad_request_aggnamespace
retailer
Request fact (aggregated).
fact_ad_request_agg_ltznamespace
retailer
Request fact (aggregated) in local timezone.
fact_enhanced_attributionnamespace
retailer
Enhanced Attribution (full detail, first model only).
fact_enhanced_attribution_2namespace
retailer
Enhanced Attribution (full detail, second model only).
fact_enhanced_attribution_aggnamespace
retailer
Enhanced Attribution (aggregated).
fact_enhanced_attribution_agg_ltznamespace
retailer
Enhanced Attribution (aggregated) in local timezone.
fact_fta_campaign_spendnamespace
retailer
FTA Campaign Spend fact.
fact_ledgernamespace
retailer
Ledger fact (full detail).
fact_ledger_aggnamespace
retailer
Ledger fact (aggregated into UTC+0 day buckets).
fact_ledger_agg_2namespace
retailer
Ledger fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
fact_ledger_agg_2_ltznamespace
retailer
Ledger fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.
fact_ledger_agg_ltznamespace
retailer
Ledger fact (aggregated into local timezone day buckets).
fact_ordernamespace
retailer
Order fact (full detail).
fact_order_aggnamespace
retailer
Order fact (aggregated).
fact_order_agg_ltznamespace
retailer
Order fact (aggregated in local timezone dates).
fact_realised_adnamespace
retailer
Realised Ad fact (full detail).
fact_realised_ad_aggnamespace
retailer
Realised Ad fact (aggregated into UTC+0 day buckets).
fact_realised_ad_agg_2namespace
retailer
Realised Ad fact (aggregated, customer centric). Includes customer_id, but no breakdown by Campaign, Placement and Product.
fact_realised_ad_agg_3namespace
retailer
Realised Ad fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
fact_realised_ad_agg_3_livenamespaceRealised Ad fact (aggregated into UTC+0 hourly buckets). Only for current + previous 2 Days.
fact_realised_ad_agg_3_ltznamespace
retailer
Realised Ad fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.
fact_realised_ad_agg_3_ltz_livenamespaceRealised Ad fact (aggregated into local timezone hourly buckets). Only for current + previous 2 days.
fact_realised_ad_agg_4anamespace
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_ltznamespace
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_ltznamespace
retailer
Realised Ad fact (aggregated into local timezone day buckets).
fact_report_request_trackingnamespace
retailer
CitrusAd client reporting usage fact.
fact_user_teamnamespace
retailer
List of all Team Users.

Logging

NameAccessPurpose
log_statusnamespace
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

NameAccessPurpose
campaignsupplier
retailer
namespace
Campaign data.
catalogretailer
namespace
Catalog data.
categorynamespaceCategory data.
deduped_productretailer
namespace
Deduped product data.
env_namespacenamespaceNamespace data.
placementnamespacePlacement data.
productretailer
namespace
Product data.
retailerretailer
namespace
Retailer data.
retailer_supplierretailerRetailer centric Supplier data (only relevant Suppliers as referenced within Realised Ads generated against the Retailer's Catalogs).
search_termnamespaceSearch Term data.
suppliersupplier
namespace
Supplier data.
supplier_productsupplierSupplier centric Product data (only relevant Products as referenced within Realised Ads generated by Supplier Campaigns).
supplier_retailersupplierSupplier centric Retailer data (only relevant Retailers as referenced within Realised Ads generated by Supplier Campaigns).
teamsupplier
retailer
namespace
Team data.
usernamespaceUser data.
user_teamnamespaceTeam users data.
walletsupplier
retailer
namespace
Wallet data.

Facts

NameAccessPurpose
ad_request_aggretailer
namespace
Request data (aggregated).
enhanced_attribution_aggretailer
namespace
Enhanced Attribution data (aggregated, both models).
external_attribution_aggnamespaceexternal Attribution data (aggregated, both models).
external_attribution_agg_ltznamespaceexternal Attribution data (aggregated, both models) LTZ Version.
fta_campaign_spendsupplier
namespace
FTA Campaign Spend data.
ledger_aggsupplier
retailer
namespace
Ledger data (aggregated into UTC+0 day buckets).
ledger_agg_2supplier
retailer
namespace
Ledger data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
order_ad_statsretailer
namespace
Order and Realised Ad statistics data (aggregated).
order_aggretailer
namespace
Order data (aggregated).
realised_ad_aggsupplierRealised Ad data (aggregated into UTC+0 day buckets).
realised_ad_agg_2namespaceRealised Ad data (aggregated by customerid). Includes customer_id, but no breakdown by Campaign, Placement and Product.
realised_ad_agg_3supplierRealised Ad data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
wallet_balancesupplier
retailer
namespace
Wallet balance data.

Decorated Facts

NameAccessPurpose
ad_request__realised_ad_ltz_viewnamespaceUnion 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_viewretailer
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_viewnamespaceRequest view (aggregated in local timezone). Decorated version of ad_request_agg.
ad_request_agg_viewretailer
namespace
Request view (aggregated). Decorated version of ad_request_agg.
enhanced_attribution_agg_ltz_viewnamespaceEnhanced Attribution view (aggregated, both models in local timezone). Decorated version of enhanced_attribution_agg.
enhanced_attribution_agg_viewretailer
namespace
Enhanced Attribution view (aggregated, both models). Decorated version of enhanced_attribution_agg.
external_attribution_agg_ltz_viewnamespaceExternal Attribution view (aggregated, both models) LTZ version. Decorated version of external_attribution_agg_ltz.
external_attribution_agg_viewnamespaceExternal Attribution view (aggregated, both models). Decorated version of external_attribution_agg.
realised_ad_agg_ltz_viewnamespaceRealised Ad view (aggregated in local timezone) + FTA Campaign spend data. Union of realised_ad_agg and fta_campaign_spend.
realised_ad_agg_viewretailer
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)

NamePurpose
viz_global_campaign_outCampaign data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is campaign.
viz_global_catalog_outCatalog data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is catalog.
viz_global_category_outCategory data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is category.
viz_global_daily_wallet_balanceDaily wallet balance data.
viz_global_decorated_ad__adrequest_stats_outRequest and Realised Ad data (aggregated and unioned). Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is ad_request__realised_ad_view.
viz_global_decorated_ad_stats_outRealised Ad (aggregated) data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is realised_ad_agg_view.
viz_global_decorated_adrequests_stats_outRequest (aggregated) data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is ad_request_agg_view.
viz_global_decorated_bucketed_order_ad_stats_outOrder and Realised Ad (aggregated) data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is order_ad_stats.
viz_global_decorated_report_request_tracking_outCitrusAd client reporting usage (aggregated) data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is report_request_tracking.
viz_global_halo_attribution_outEnhanced Attribution (aggregated, both models) data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is enhanced_attribution_agg_view.
viz_global_ledger_outLedger (aggregated) data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is ledger_agg.
viz_global_product_outProduct data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is product.
viz_global_retailer_outProduct data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is retailer.
viz_global_sov_category_mth_outShare of voice by Category data (month buckets). Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is insights_sov_category.
viz_global_sov_category_week_outShare of voice by Category data (weekly buckets). Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is insights_sov_category.
viz_global_sov_search_term_mth_outShare of voice by Search Term data (month buckets). Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is insights_sov_search_term.
viz_global_supplier_outSupplier data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is supplier.
viz_global_team_outTeam data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is team.
viz_global_team_user_outTeam Users data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is user_team.
viz_global_wallet_balance_outWallet balance (daily) data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is wallet_balance.
viz_global_wallet_outWallet data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is wallet.

Version: 430b893