> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lunarmc.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Report Data Dictionary

> The central repository for all metrics, dimensions, and formulas

## Overview

The `report_data_dictionary` table is the **single source of truth** for all metrics, dimensions, and formulas used in system-defined and custom reports. Every metric -- whether pulled from a source, calculated via formula, or manually entered -- is registered here.

## Schema

| Column            | Type         | Description                                                        |
| ----------------- | ------------ | ------------------------------------------------------------------ |
| `id`              | int (PK)     | Unique identifier                                                  |
| `metric`          | varchar(100) | Metric name (e.g., `CTR`, `Revenue`)                               |
| `source_table`    | varchar(100) | DB table the metric is pulled from (null for formula/user metrics) |
| `source_column`   | text         | Column name in source table                                        |
| `column_label`    | varchar(100) | Human-readable name for UI display                                 |
| `description`     | text         | Explanation of metric purpose                                      |
| `data_type`       | varchar(100) | `number`, `percent`, `currency`, `text`, `date`                    |
| `entry_type`      | varchar(100) | `System`, `Formula`, `User Data`, `Custom`                         |
| `client_id`       | varchar(100) | Owning client (`0` = system/global)                                |
| `formula`         | varchar(255) | Formula using dictionary IDs (e.g., `2/3` for clicks/impressions)  |
| `formula_label`   | text         | Display label for formula in UI                                    |
| `level`           | varchar(255) | Dimension level: `Campaign`, `AdSet`, `Ads`, `Account`, `Orders`   |
| `planning_id`     | int          | Links to planning/target data                                      |
| `variance`        | boolean      | Supports variance calculation                                      |
| `is_decimal`      | boolean      | Treat as decimal for calculations                                  |
| `is_deleted`      | boolean      | Soft delete flag                                                   |
| `is_agency`       | boolean      | Agency-specific metric                                             |
| `is_dimension`    | boolean      | True if entry is a dimension                                       |
| `attribute`       | varchar(255) | Which attribute a custom dimension/metric is built on              |
| `agency_id`       | int          | For agency multi-tenancy                                           |
| `heatmap_reverse` | boolean      | True for "lower is better" metrics (CPA, CPM)                      |
| `metric_config`   | JSON         | Additional config (thresholds, calculation rules, display)         |

## Key Columns Explained

### `source_table`

Specifies which database table the metric is pulled from. Only required for **system metrics** -- formula and user metrics leave this null.

| metric      | source\_table                      | source\_column | level   |
| ----------- | ---------------------------------- | -------------- | ------- |
| Clicks      | interaction\_insight\_ads\_tracker | clicks         | Account |
| Total Sales | orders\_new                        | total\_sales   | Orders  |

<Tip>
  If the metric is formula-only, `source_table` should be null.
</Tip>

### `source_column`

The exact column in `source_table` to pull values from. For derived metrics like ROAS, you can specify the calculation directly:

| metric  | source\_table                              | source\_column     | level    |
| ------- | ------------------------------------------ | ------------------ | -------- |
| ROAS    | interaction\_insight\_ads\_campaigntracker | conv\_value / cost | Campaign |
| Revenue | interaction\_insight\_ads\_campaigntracker | conv\_value        | Campaign |

<Tip>
  For derived system metrics (ROAS, CPA, CPC, CTR), you can set `source_column` as `conv_value / cost`. Also add the formula field (e.g., `2/3`) for footer calculations.
</Tip>

### `attribute`

Defines which attribute a custom metric or dimension is built on. This enables grouping and filtering.

| Name                                 | attribute      | is\_dimension |
| ------------------------------------ | -------------- | ------------- |
| Facebook Ads Paid Media Revenue (GA) | source         | FALSE         |
| Meta Prospecting Campaigns           | campaign\_name | TRUE          |

### `level`

Specifies which data level a metric belongs to. The same metric (e.g., Clicks) exists at multiple levels:

| metric | level    | source\_table                              |
| ------ | -------- | ------------------------------------------ |
| Clicks | Account  | interaction\_insight\_ads\_tracker         |
| Clicks | Campaign | interaction\_insight\_ads\_campaigntracker |
| Clicks | AdSet    | interaction\_insight\_ads\_adsettracker    |
| Clicks | Ads      | ads\_adtracker                             |

<Info>
  The UI uses `level` for filtering -- when a user selects a dimension, they see all metrics at that level.
</Info>

## Report Custom Fields

The `report_custom_fields` table maps metrics and dimensions to their associated sources.

* A system metric is always associated with **one** source
* A custom metric can be associated with **multiple** sources
* An attribute can be associated with single or multiple sources

| dictionary\_id | source\_id       | category\_id   |
| -------------- | ---------------- | -------------- |
| 1              | 1 (Shopify)      | 1 (Commerce)   |
| 2              | 2 (Google Ads)   | 2 (Paid Media) |
| 3              | 2 (Google Ads)   | 2 (Paid Media) |
| 3              | 3 (Facebook Ads) | 2 (Paid Media) |

## Design Principles

* **Single Source of Truth** -- All metrics, formulas, and dimensions originate from the data dictionary
* **Extensible** -- Adding a new system metric only requires inserting a row
* **Multi-Tenant** -- Metrics can be client- or agency-specific via `client_id` and `agency_id`
* **Soft Delete & Audit** -- `is_deleted` plus timestamps ensure traceability
* **Metrics + Dimensions** -- `is_dimension` differentiates grouping dimensions from value metrics