Skip to main content

​
Scenario

Create a system-defined report for LinkedIn Ads data. LinkedIn data is stored across these tables:
TableLevel
interaction_insight_ads_trackerAccount
interaction_insight_ads_campaigntrackerCampaign
interaction_insight_ads_adsettrackerAdSet
ads_adtrackerAds

​
Walkthrough

1

Add dictionary entries

Create metrics for each level in report_data_dictionary:
metricsource_tablesource_columnlevel
impressionsinteraction_insight_ads_trackerimpressionsAccount
clicksinteraction_insight_ads_trackerclicksAccount
spendinteraction_insight_ads_trackercostAccount
clicksinteraction_insight_ads_campaigntrackerclicksCampaign
spendads_adtrackercostAds
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.
2

Map metrics to source

Insert all dictionary IDs into report_custom_fields:
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.
3

Create the report

Insert into report_master:
INSERT INTO report_master (report_name, report_type)
VALUES ('LinkedIn Ads', 'System');
-- Returns report_id = 42
4

Map report to source

Insert into report_source_master:
INSERT INTO report_source_master (report_id, source_id)
VALUES (42, 5);  -- LinkedIn Ads report → LinkedIn Ads source
5

Configure the template

Add Account-level only metrics to report_template_master:
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
Do not add Campaign, AdSet, or Ads level metrics here. Account level already contains aggregated totals for system reports.
6

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