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
| Name | Access | Purpose |
|---|---|---|
currency_aud | namespace | Current conversion rates between the base currency (AUD) to other common currencies. ISO 4217 currency codes used. |
currency_usd | namespace | Current conversion rates between the base currency (USD) to other common currencies. ISO 4217 currency codes used. |
dim_campaign | namespace | Campaign master data dimension. |
dim_campaign_attr | namespace | Campaign custom attributes. |
dim_catalog | namespace | Catalog master data dimension. |
dim_category | namespace | Category dimension. |
dim_env_namespace | namespace | Namespace dimension. |
dim_placement | namespace | Placement dimension. |
dim_product | namespace | Product dimension. |
dim_retailer | namespace | Retailer dimension. |
dim_search_term | namespace | Search Term dimension. |
dim_supplier | namespace | Supplier dimension. |
dim_team | namespace | Team dimension. |
dim_user | namespace | User dimension. |
dim_wallet | namespace | Wallet dimension. |
Facts
| Name | Access | Purpose |
|---|---|---|
fact_ad_request | namespace | Request fact (full detail). |
fact_ad_request_agg | namespace | Request fact (aggregated). |
fact_ad_request_agg_ltz | namespace | Request fact (aggregated) in local timezone. |
fact_enhanced_attribution | namespace | Enhanced Attribution (full detail, first model only). |
fact_enhanced_attribution_2 | namespace | Enhanced Attribution (full detail, second model only). |
fact_enhanced_attribution_agg | namespace | Enhanced Attribution (aggregated). |
fact_enhanced_attribution_agg_ltz | namespace | Enhanced Attribution (aggregated) in local timezone. |
fact_fta_campaign_spend | namespace | FTA Campaign Spend fact. |
fact_ledger | namespace | Ledger fact (full detail). |
fact_ledger_agg | namespace | Ledger fact (aggregated into UTC+0 day buckets). |
fact_ledger_agg_2 | namespace | Ledger fact (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months. |
fact_ledger_agg_2_ltz | namespace | Ledger fact (aggregated into local timezone hourly buckets). Only for current + previous 3 months. |
fact_ledger_agg_ltz | namespace | Ledger fact (aggregated into local timezone day buckets). |
fact_order | namespace | Order fact (full detail). |
fact_order_agg | namespace | Order fact (aggregated). |
fact_order_agg_ltz | namespace | Order fact (aggregated in local timezone dates). |
fact_realised_ad | namespace | Realised Ad fact (full detail). |
fact_realised_ad_agg | namespace | Realised Ad fact (aggregated into UTC+0 day buckets). |
fact_realised_ad_agg_2 | namespace | Realised Ad fact (aggregated, customer centric). Includes customer_id, but no breakdown by Campaign, Placement and Product. |
fact_realised_ad_agg_3 | namespace | 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 | 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 | 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 | 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 | Realised Ad fact (aggregated into local timezone day buckets). |
fact_report_request_tracking | namespace | CitrusAd client reporting usage fact. |
fact_user_team | namespace | List of all Team Users. |
Logging
| Name | Access | Purpose |
|---|---|---|
log_status | namespace | 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 | retailer namespace | 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 | retailer namespace | 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 CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is campaign. |
viz_global_catalog_out | Catalog data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is catalog. |
viz_global_category_out | Category data. Use to reconcile to the CitrusAd 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 CitrusAd 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 CitrusAd 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 CitrusAd 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 CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is order_ad_stats. |
viz_global_decorated_report_request_tracking_out | CitrusAd 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_out | Enhanced 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_out | Ledger (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_out | Product data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is product. |
viz_global_retailer_out | Product 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_out | Share 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_out | Share 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_out | Share 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_out | Supplier data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is supplier. |
viz_global_team_out | Team data. Use to reconcile to the CitrusAd 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 CitrusAd 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 CitrusAd 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 CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is wallet. |
Version: 0bdfbc1