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 | Current conversion rates between the base currency (AUD) to other common currencies. ISO 4217 currency codes used. | |
currency_usd | 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_external_order_config | NAMESPACE | External Order Config dimension. |
dim_placement | NAMESPACE | Placement dimension. |
dim_product | NAMESPACE | Product dimension. |
dim_retailer | NAMESPACE RETAILER | Retailer dimension. |
dim_search_term | NAMESPACE | Search Term dimension. |
dim_supplier | NAMESPACE SUPPLIER | 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_4 | NAMESPACE | Realised Ad fact (aggregated by month). Based on fact_realised_ad with keyword and keyword_matchtype. |
fact_realised_ad_agg_4_ltz | NAMESPACE | Based on fact_realised_ad with keyword and keyword_matchtype grouped in retailer specified local timezone. |
fact_realised_ad_agg_4a | NAMESPACE | Realised Ad fact (aggregated by month). 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 month). 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 | 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 | NAMESPACE RETAILER SUPPLIER | Campaign data. |
catalog | NAMESPACE RETAILER | Catalog data. |
category | NAMESPACE RETAILER | Category data. |
currency | Current conversion rates between the base currency (AUD and USD) to other common currencies. ISO 4217 currency codes used. | |
deduped_product | NAMESPACE RETAILER | Deduped product data. |
env_namespace | NAMESPACE | Namespace data. |
placement | NAMESPACE | Placement data. |
product | NAMESPACE RETAILER | Product data. |
retailer | NAMESPACE RETAILER | Retailer data. |
retailer_supplier | NAMESPACE RETAILER SUPPLIER | Retailer centric Supplier data (only relevant Suppliers as referenced within Realised Ads generated against the Retailer's Catalogs). |
search_term | NAMESPACE RETAILER | Search Term data. |
supplier | NAMESPACE SUPPLIER | Supplier data. |
supplier_product | NAMESPACE SUPPLIER | Supplier centric Product data (only relevant Products as referenced within Realised Ads generated by Supplier Campaigns). |
supplier_retailer | NAMESPACE SUPPLIER RETAILER | Supplier centric Retailer data (only relevant Retailers as referenced within Realised Ads generated by Supplier Campaigns). |
team | NAMESPACE RETAILER SUPPLIER | Team data. |
user | NAMESPACE | User data. |
user_team | NAMESPACE RETAILER SUPPLIER | Team users data. |
wallet | NAMESPACE RETAILER SUPPLIER | Wallet data. |
Facts
| Name | Access | Purpose |
|---|---|---|
ad_request_agg | NAMESPACE RETAILER | Request data (aggregated). |
enhanced_attribution_agg | NAMESPACE RETAILER SUPPLIER | Enhanced Attribution data (aggregated, both models). |
external_attribution_agg | NAMESPACE RETAILER SUPPLIER | external Attribution data (aggregated, both models). |
external_attribution_agg_ltz | NAMESPACE RETAILER SUPPLIER | external Attribution data (aggregated, both models) LTZ Version. |
fta_campaign_spend | NAMESPACE RETAILER SUPPLIER | FTA Campaign Spend data. |
ledger_agg | NAMESPACE RETAILER SUPPLIER | Ledger data (aggregated into UTC+0 day buckets). |
ledger_agg_2 | NAMESPACE RETAILER SUPPLIER | Ledger data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months. |
order_ad_stats | NAMESPACE RETAILER | Order and Realised Ad statistics data (aggregated). |
order_agg | NAMESPACE RETAILER | Order data (aggregated). |
realised_ad_agg | NAMESPACE RETAILER SUPPLIER | Realised Ad data (aggregated into UTC+0 day buckets). |
realised_ad_agg_2 | NAMESPACE RETAILER SUPPLIER | Realised Ad data (aggregated by customerid). Includes customer_id, but no breakdown by Campaign, Placement and Product. |
realised_ad_agg_3 | NAMESPACE RETAILER SUPPLIER | Realised Ad data (aggregated into UTC+0 hourly buckets). Only for current + previous 3 months. |
wallet_balance | NAMESPACE RETAILER SUPPLIER | Wallet balance data. |
Decorated Facts
| Name | Access | Purpose |
|---|---|---|
ad_request__realised_ad_ltz_view | NAMESPACE RETAILER SUPPLIER | 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 | 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). |
ad_request_agg_ltz_view | NAMESPACE RETAILER | Request view (aggregated in local timezone). Decorated version of ad_request_agg. |
ad_request_agg_view | NAMESPACE RETAILER | Request view (aggregated). Decorated version of ad_request_agg. |
enhanced_attribution_agg_ltz_view | NAMESPACE RETAILER SUPPLIER | Enhanced Attribution view (aggregated, both models in local timezone). Decorated version of enhanced_attribution_agg. |
enhanced_attribution_agg_view | NAMESPACE RETAILER SUPPLIER | Enhanced Attribution view (aggregated, both models). Decorated version of enhanced_attribution_agg. |
external_attribution_agg_ltz_view | NAMESPACE RETAILER SUPPLIER | External Attribution view (aggregated, both models) LTZ version. Decorated version of external_attribution_agg_ltz. |
external_attribution_agg_view | NAMESPACE RETAILER SUPPLIER | External Attribution view (aggregated, both models). Decorated version of external_attribution_agg. |
realised_ad_agg_ltz_view | NAMESPACE RETAILER SUPPLIER | Realised Ad view (aggregated in local timezone) + FTA Campaign spend data. Union of realised_ad_agg and fta_campaign_spend. |
realised_ad_agg_view | NAMESPACE 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)
| 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_halo_attribution_out_ltz | 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_ltz. |
viz_global_halo_external_attribution_out | Union of Enhanced Attribution And External 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 and external_attribution_agg_view. |
viz_global_halo_external_attribution_out_ltz | Union of Enhanced Attribution And External Attribution (aggregated, both models) ltz data. Use to reconcile to the CitrusAd BI platform (if applicable). Legacy use only, preferred view to use is enhanced_attribution_agg_ltz_view and external_attribution_agg_ltz_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_sov_search_week_out | Share 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_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: 5a14122