Physical Architecture
The CitrusAd Reporting API provides secure access to the CitrusAd data warehouse. The data warehouse itself contains data and insights sourced from the CitrusAd platform.
The CitrusAd data warehouse is a distributed model. Some data is kept locally within the CitrusAd platform instances, other data in a centralised, global data warehouse.
The following diagram show the structure of the data model.
| Layer | Description |
|---|---|
| Platform Data | Source data from the CitrusAd platform. The platform itself is distributed across multiple physical instances. An instance exists per large retailer, or in some cases, is securely shared amongst several smaller retailers. Direct access to Platform Data is not permitted to external connections. |
| Staged Data | Relevant Platform Data is staged into the data warehouse in near real time. The Staged Data is maintained per platform instance in the same physical location as the Platform Data. Direct access to Staged Data is generally not permitted to external connections. |
| Core Dataset | On a daily basis, batch processes take Staged Data and model it into a simple to use snowflake schema referred to as the Core Dataset. The Core Dataset is also maintained per platform instance in the same physical location as the Staged Data and Platform Data. Each Core Dataset fact is available at both the individual transaction and aggregated level of detail. Tracking of changes is also present on many dimensions. Direct access to Core Datasets is permitted (upon request) to certain clients, principally at the retailer level (subject to approvals). |
| Aggregated Core Datasets | After the Core Datasets have updated, batch processes take the individual Core Datasets and transfer them to a centralised location (the global data warehouse). The individual Core Datasets are then consolidated into a single, unified Aggregated Core Dataset. Note, only aggregated fact data (not transactional data) is stored in the global data warehouse. Direct access to the Aggregated Core Dataset is generally not permitted to external connections. |
| Reporting Datamart | After the Aggregated Core Dataset has updated, daily batch processes compute a further simplified and aggregated Reporting Datamart. Like the Core Datasets, the Reporting Datamart is also a snowflake schema. Only current versions of dimensions are offered. Clients must maintain change history locally within their own data warehouses as required based on daily snapshots. The Reporting Datamart also contains views that decorate certain facts and simplify querying for clients even more. Direct access to the Reporting Datamart is permitted (upon request) to a wide range of clients, both retailers and suppliers (subject to approvals). |
| Insights [coming soon] | A series of views constructed on top of the Reporting Datamart that provide pre-built Insights commonly requested by clients. For example : Campaign Effectiveness (Realised Ad statistics eg CTR/ROAS) Inventory Fill Rates (% Requests being filled with paid advertising) Share of Voice (Supplier share ofimpressions by category/search term etc) Billing (wallet spend per Campaign per time period) Access to the Insights layer is permitted (upon request) to a wide range of clients, both retailers and suppliers (subject to approvals). This document also shares SQL code used to construct the Insights layer so that clients copying the Reporting Datamart to their data warehouse can understand how to easily replicate the logic. |
| Visualisation [limited release] | A series of views constructed on top of the Reporting Datamart and Insights layer that provides the equivalent data as seen in the legacy BI platform. Access to the Visualisation layer is currently permitted (upon request) to clients, both retailers and suppliers who require access to data exactly as was presented in our legacy BI platform (subject to approvals). NOTE: Unless there is a need to reconcile exactly to the legacy BI platform, it is advised to use the Reporting Datamart or Insights layer equivalents instead. They also contain more granular data over longer timeframes. |
Logical Data Model
Depending on your access type, the data available includes many commonly requested CitrusAd platform subject areas such as:
(please refer to the Glossary for further definitions)
- Dimensional Data
- Campaigns
- Product Catalogs
- Products
- Teams
- Retailers
- Suppliers
- Wallets
- Search Terms
- Categories
- Placements
- Fact Data
- Ad Request Statistics (Number of Requests by Search Term or Category etc)
- Realised Ad Statistics (Impressions/Clicks/AdSpend by Search Term or Category etc)
- Enhanced Attribution Statistics (where configured only - impression view thru, halo click attribution etc)
- FTA Campaign Spend Statistics
- Ledger Transaction Summaries (billing information, wallet top ups etc)
- Order Statistics
The Reporting API is typically used to share the schemas within the Core Dataset and/or Reporting Datamart. Both of these schemas cover the aforementioned subject areas, just at different levels of aggregation (the Core Dataset offering transaction level details for facts as well as aggregates).
User Type
The subject areas you will be able to see will be restricted according to what your organization is entitled to see. There are 4 types of organizational access:
- Namespace level: A client that controls an entire network of teams (both supplier and retailer teams). Namespace level access grants visibility to all data for a given implementation of CitrusAd.
- Retailer level : A client that is a specific retailer team (or group of retailer teams) only.
- Supplier level : A client that is a specific supplier team (or group of supplier teams) only.
- Integrator level: An Extension of Supplier level, except that they have access to certain Retailer level objects in full (at Retailer discretion only).
These are the key subject areas that are covered and how they apply to the 3 types of access.
| Subject Area | Namespace | Retailer | Supplier | Integrator |
|---|---|---|---|---|
| Campaigns | All data | Related to the Retailer Team(s) | Related to the Supplier Team(s) | Related to Supplier Team(s) |
| Product Catalogs | All data | Related to the Retailer Team(s) | No data | Related to the Retailer Team(s) that have granted approval to the Integrator |
| Products | All data | Related to the Retailer Team(s) | Only for Products appearing in Realised Ad Statistics supplier_product table only | Related to the Retailer Team(s) that have granted approval to the Integrator |
| Teams | All data | Related to the Retailer Team(s) | Related to the Supplier Team(s) | Related to the Supplier Team(s) |
| Retailers | All data | Related to the Retailer Team(s) | Only for Retailers appearing in Realised Ad Statistics supplier_retailer table only | Only for Retailers appearing in Realised Ad Statistics |
| Suppliers | All data | Only for the Suppliers appearing in Realised Ad Statistics retailer_supplier table only | Related to the Supplier Team(s) | Related to the Supplier Team(s) |
| Wallets | All data | Retailer Team Wallets only | Supplier Team(s) Wallets only | Supplier Team(s) Wallets only |
| Search Terms | All data | Related to the Retailer Team(s) | Only for Search Terms appearing in Realised Ad Statistics (only appears as is in the fact table) | Only for Search Terms appearing in Realised Ad Statistics (only appears as is in the fact table) |
| Categories | All data | Related to the Retailer Team(s) | Only for Categories appearing in Realised Ad Statistics (only appears as is in the fact table) | Only for Categories appearing in Realised Ad Statistics (only appears as is in the fact table) |
| Ad Request Statistics | All data | Related to the Retailer Team(s) | No data | No data |
| Enhanced Attribution Statistics | All data | Related to the Retailer Team(s) | Related to the Supplier Team(s) | Related to the Supplier Team(s) |
| FTA Campaign Spend Statistics | All data | Related to the Retailer Team(s) | Related to the Supplier Team(s) | Related to the Supplier Team(s) |
| Ledger Transaction Summaries | All data | Retailer Team Wallets only | Supplier Team(s) Wallets only | Supplier Team(s) Wallets only |
| Order Statistics | All data | Related to the Retailer Team(s) | No data | No data |
| Realised Ad Statistics (Impressions / Clicks AdSpend etc) | All data | Related to the Retailer Team(s) | Related to the Supplier Team(s) | Related to the Supplier Team(s) |
| Placements | All data | Related to the Retailer Team(s) | Only for Placements appearing in Ad Request and Realised Ad Statistics. | Related to the Retailer Team(s) that have granted approval to the Integrator |
User Access Method
Two types of access method exist, depending on what cloud platforms you use.
| User Type | Access Method |
|---|---|
| Native Google Cloud Platform (GCP) user | Direct Access As our data warehouse is deployed in BigQuery (GCP’s data warehouse service), clients who are in GCP themselves will be able to directly access the datasets. This will allow you to see the objects and query them via the BigQuery UI in your own GCP project. You simply need to provide us with an email address from your GCP project and we will grant privileges accordingly (pending approvals). Note, Direct Access users can also make calls to the BigQuery API via scripts etc (in addition to using the BigQuery API). |
| Other (non GCP) users | API If you are not in GCP (eg Azure or AWS), then CitrusAd will create an account for you (service account) and provide the credentials (pending approvals). This account will give you BigQuery API level access only (you will not be able to access the cloud console). |
Version: e76c330