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_audCurrent conversion rates between the base currency (AUD) to other common currencies. ISO 4217 currency codes used.
currency_usdCurrent conversion rates between the base currency (USD) to other common currencies. ISO 4217 currency codes used.
dim_campaignNAMESPACECampaign master data dimension.
dim_campaign_attrNAMESPACECampaign custom attributes.
dim_catalogNAMESPACECatalog master data dimension.
dim_categoryNAMESPACECategory dimension.
dim_env_namespaceNAMESPACENamespace dimension.
dim_placementNAMESPACEPlacement dimension.
dim_productNAMESPACEProduct dimension.
dim_retailerNAMESPACE
RETAILER
Retailer dimension.
dim_search_termNAMESPACESearch Term dimension.
dim_supplierNAMESPACE
SUPPLIER
Supplier dimension.
dim_teamNAMESPACETeam dimension.
dim_userNAMESPACEUser dimension.
dim_walletNAMESPACEWallet dimension.

Facts

NameAccessPurpose
fact_ad_requestNAMESPACERequest fact (full detail).
fact_ad_request_aggNAMESPACERequest fact (aggregated).
fact_ad_request_agg_ltzNAMESPACERequest fact (aggregated) in local timezone.
fact_enhanced_attributionNAMESPACEEnhanced Attribution (full detail, first model only).
fact_enhanced_attribution_2NAMESPACEEnhanced Attribution (full detail, second model only).
fact_enhanced_attribution_aggNAMESPACEEnhanced Attribution (aggregated).
fact_enhanced_attribution_agg_ltzNAMESPACEEnhanced Attribution (aggregated) in local timezone.
fact_fta_campaign_spendNAMESPACEFTA Campaign Spend fact.
fact_ledgerNAMESPACELedger fact (full detail).
fact_ledger_aggNAMESPACELedger fact (aggregated into UTC+0 day buckets).
fact_ledger_agg_2NAMESPACELedger fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
fact_ledger_agg_2_ltzNAMESPACELedger fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.
fact_ledger_agg_ltzNAMESPACELedger fact (aggregated into local timezone day buckets).
fact_orderNAMESPACEOrder fact (full detail).
fact_order_aggNAMESPACEOrder fact (aggregated).
fact_order_agg_ltzNAMESPACEOrder fact (aggregated in local timezone dates).
fact_realised_adNAMESPACERealised Ad fact (full detail).
fact_realised_ad_aggNAMESPACERealised Ad fact (aggregated into UTC+0 day buckets).
fact_realised_ad_agg_2NAMESPACERealised Ad fact (aggregated, customer centric). Includes customer_id, but no breakdown by Campaign, Placement and Product.
fact_realised_ad_agg_3NAMESPACERealised Ad fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
fact_realised_ad_agg_3_ltzNAMESPACERealised Ad fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months.
fact_realised_ad_agg_ltzNAMESPACERealised Ad fact (aggregated into local timezone day buckets).
fact_report_request_trackingNAMESPACECitrusAd client reporting usage fact.
fact_user_teamNAMESPACEList of all Team Users.

Logging

NameAccessPurpose
log_statusLogging 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
campaignNAMESPACE
RETAILER
SUPPLIER
Campaign data.
catalogNAMESPACE
RETAILER
Catalog data.
categoryNAMESPACE
RETAILER
Category data.
currencyCurrent conversion rates between the base currency (AUD and USD) to other common currencies. ISO 4217 currency codes used.
deduped_productNAMESPACE
RETAILER
Deduped product data.
env_namespaceNAMESPACENamespace data.
placementNAMESPACEPlacement data.
productNAMESPACE
RETAILER
Product data.
retailerNAMESPACE
RETAILER
Retailer data.
retailer_supplierNAMESPACE
RETAILER
SUPPLIER
Retailer centric Supplier data (only relevant Suppliers as referenced within Realised Ads generated against the Retailer's Catalogs).
search_termNAMESPACE
RETAILER
Search Term data.
supplierNAMESPACE
SUPPLIER
Supplier data.
supplier_productNAMESPACE
SUPPLIER
Supplier centric Product data (only relevant Products as referenced within Realised Ads generated by Supplier Campaigns).
supplier_retailerNAMESPACE
SUPPLIER
RETAILER
Supplier centric Retailer data (only relevant Retailers as referenced within Realised Ads generated by Supplier Campaigns).
teamNAMESPACE
RETAILER
SUPPLIER
Team data.
userNAMESPACEUser data.
user_teamNAMESPACE
RETAILER
SUPPLIER
Team users data.
walletNAMESPACE
RETAILER
SUPPLIER
Wallet data.

Facts

NameAccessPurpose
ad_request_aggNAMESPACE
RETAILER
Request data (aggregated).
enhanced_attribution_aggNAMESPACE
RETAILER
SUPPLIER
Enhanced Attribution data (aggregated, both models).
fta_campaign_spendNAMESPACE
RETAILER
SUPPLIER
FTA Campaign Spend data.
ledger_aggNAMESPACE
RETAILER
SUPPLIER
Ledger data (aggregated into UTC+0 day buckets).
ledger_agg_2NAMESPACE
RETAILER
SUPPLIER
Ledger data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
order_ad_statsNAMESPACE
RETAILER
Order and Realised Ad statistics data (aggregated).
order_aggNAMESPACE
RETAILER
Order data (aggregated).
realised_ad_aggNAMESPACE
RETAILER
SUPPLIER
Realised Ad data (aggregated into UTC+0 day buckets).
realised_ad_agg_2NAMESPACE
RETAILER
SUPPLIER
Realised Ad data (aggregated by customerid). Includes customer_id, but no breakdown by Campaign, Placement and Product.
realised_ad_agg_3NAMESPACE
RETAILER
SUPPLIER
Realised Ad data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months.
wallet_balanceNAMESPACE
RETAILER
SUPPLIER
Wallet balance data.

Decorated Facts

NameAccessPurpose
ad_request__realised_ad_viewNAMESPACE
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).
ad_request_agg_viewNAMESPACE
RETAILER
Request view (aggregated). Decorated version of ad_request_agg.
enhanced_attribution_agg_viewNAMESPACE
RETAILER
SUPPLIER
Enhanced Attribution view (aggregated, both models). Decorated version of enhanced_attribution_agg.
realised_ad_agg_viewNAMESPACE
RETAILER
SUPPLIER
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_sov_search_week_outShare of voice by Search Term data (weekly 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: 96e63c0