> ## 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.

# Example: LinkedIn Ads Report

> End-to-end walkthrough of creating a LinkedIn Ads system report

## Scenario

Create a system-defined report for **LinkedIn Ads** data. LinkedIn data is stored across these tables:

| Table                                      | Level    |
| ------------------------------------------ | -------- |
| interaction\_insight\_ads\_tracker         | Account  |
| interaction\_insight\_ads\_campaigntracker | Campaign |
| interaction\_insight\_ads\_adsettracker    | AdSet    |
| ads\_adtracker                             | Ads      |

## Walkthrough

<Steps>
  <Step title="Add dictionary entries">
    Create metrics for **each level** in `report_data_dictionary`:

    | metric      | source\_table                              | source\_column | level    |
    | ----------- | ------------------------------------------ | -------------- | -------- |
    | impressions | interaction\_insight\_ads\_tracker         | impressions    | Account  |
    | clicks      | interaction\_insight\_ads\_tracker         | clicks         | Account  |
    | spend       | interaction\_insight\_ads\_tracker         | cost           | Account  |
    | clicks      | interaction\_insight\_ads\_campaigntracker | clicks         | Campaign |
    | spend       | ads\_adtracker                             | cost           | Ads      |

    <Info>
      Create entries at every level even though the system report only uses Account level. Campaign, AdSet, and Ads level entries are used for custom reports and drill-down views.
    </Info>
  </Step>

  <Step title="Map metrics to source">
    Insert all dictionary IDs into `report_custom_fields`:

    ```sql theme={null}
    INSERT INTO report_custom_fields (dictionary_id, source_id, category_id)
    VALUES
      (101, 5, 2),  -- impressions → LinkedIn Ads → Paid Media
      (102, 5, 2),  -- clicks → LinkedIn Ads → Paid Media
      (103, 5, 2);  -- spend → LinkedIn Ads → Paid Media
    ```

    Now the LinkedIn source only exposes LinkedIn metrics.
  </Step>

  <Step title="Create the report">
    Insert into `report_master`:

    ```sql theme={null}
    INSERT INTO report_master (report_name, report_type)
    VALUES ('LinkedIn Ads', 'System');
    -- Returns report_id = 42
    ```
  </Step>

  <Step title="Map report to source">
    Insert into `report_source_master`:

    ```sql theme={null}
    INSERT INTO report_source_master (report_id, source_id)
    VALUES (42, 5);  -- LinkedIn Ads report → LinkedIn Ads source
    ```
  </Step>

  <Step title="Configure the template">
    Add **Account-level only** metrics to `report_template_master`:

    ```sql theme={null}
    INSERT INTO report_template_master
      (report_id, dictionary_id, display_order, is_sticky)
    VALUES
      (42, 101, 1, false),  -- impressions
      (42, 102, 2, false),  -- clicks
      (42, 103, 3, false);  -- spend
    ```

    <Warning>
      Do **not** add Campaign, AdSet, or Ads level metrics here. Account level already contains aggregated totals for system reports.
    </Warning>
  </Step>

  <Step title="Verify">
    The report should now appear automatically in the UI. The reporting engine will:

    1. Read metrics from `report_data_dictionary` for IDs 101, 102, 103
    2. Filter by source (LinkedIn Ads)
    3. Apply the template configuration (display order, labels)
    4. Build SQL queries against `interaction_insight_ads_tracker`
    5. Render the report
  </Step>
</Steps>