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_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_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). |
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_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_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: 3268fc6