Campaign Manager 360 API Connector

📝 Overview

The Campaign Manager 360 connector automates the import of:

Delivery / Aggregate Report (dcm_data_aggregate)
Floodlight Activity / Conversion Report (dcm_data_activity)
Eight CM360 metadata endpoints (advertisers, campaigns, sites, packages, placements, creatives, floodlight activities, and entity‑relationships)

This multi‑table design supports attribution, pacing, and creative‑level analytics across display & video campaigns.

Granularity
• Aggregate table ➜ one row per placement (or finer, depending on report) per day
• Activity table ➜ one row per activity ID per day
Purpose – Harmonise delivery, conversion, and cost data with rich CM360 dimension lookups.
Refresh cadence – Daily append for facts, daily refresh for meta.

⚙️ Connection Setup

Key	Description	Required	Type
`cm_user_reference`	Numeric value beside username in CM dropdown	✅	Text
`google_client_secret`	Google API Client Secret (hidden)	✅	Hidden
`google_client_id`	Google API Client ID (hidden)	✅	Hidden
`google_client_scopes`	OAuth scopes (hidden – DFAR, trafficking, GCS read)	✅	Hidden

Auth flow – OAuth 2.0 installed‑app grant. User completes one‑time Google consent; refresh tokens stored server‑side.

🗓️ Jobs & Schedules

Job Label	Feed	Look - back	First Run (UTC)	Poll	Targets
Campaign Manager – Data Import	`dfa_report_import`	5 days	06:00	24h	•`dcm_data_aggregate` (report ID required) • `dcm_data_activity` (report ID required)
Campaign Manager – Meta Refresh	`dfa_dimension_fetch`	1 day	06:00	24h	8 lookup tables (advertiser, campaign, site, package, placement, creative, activity, relations)

Target‑level Settings (Data Job)

Table	Hidden Option Key	Value	Update	Date Col
`dcm_data_aggregate`	`cm_report_id`	user‑supplied	append	`date`
`dcm_data_activity`	`cm_report_id`	user‑supplied	append	`date`

Target‑level Settings (Meta Job)

Each lookup table uses a hidden option cm_meta_mode with values: advertisers, campaigns, sites, placementGroups, placements, creatives, floodlightActivities, plus a pre‑joined relations table (dcm_meta_relations). All meta tables are truncated & refreshed daily.

📚 Tables & Schemas (Key Fields)

Fact 1 – `dcm_data_aggregate`

Field	Description
`date`	Report date
`advertiser_id`	Advertiser ID
`campaign_id`	Campaign ID
`placement_id`	Placement ID
`creative_id`	Creative ID
`site_id_cm360`	Site ID
`package_roadblock_id`	Package ID
`media_cost`	Media spend
`impressions`, `clicks`	Standard KPIs
Video columns …	First/Mid/Third quartile, completes, plays, replays, views
`platform_type`	Device / platform string

Fact 2 – `dcm_data_activity`

Field	Description
`date`	Activity date
`activity_id`	Floodlight activity ID
`campaign_id`, `creative_id`, `placement_id`, `advertiser_id`, `site_id_cm360`, `package_roadblock_id`	Foreign keys
`click_through_revenue`, `view_through_revenue`	Revenue columns
`view_through_conversions`	Conversions (view‑through)
`platform_type`	Device / platform

Meta Tables (refreshed)

Table	Key Column	Example Fields
`dcm_meta_advertiser`	`id`	`value` (advertiser name)
`dcm_meta_campaign`	`id`	`value`, `advertiser_id`
`dcm_meta_site`	`id`	`value`, `site_type`
`dcm_meta_package`	`id`	`value`, `campaign_id`
`dcm_meta_placement`	`id`	`value`, `package_roadblock_id`, `site_id_cm360`
`dcm_meta_creative`	`id`	`value`, `creative_type`
`dcm_meta_activity`	`id`	`value`, `floodlight_tag_type`
`dcm_meta_relations`	—	Pre‑resolved many‑to‑many mappings across placement↔creative etc.

Note – The actual CM360 feed returns hundreds of extra columns; all are captured even if not listed.

🏷️ Dimensions

Dimension Name	Mapping
Advertiser [CM360]	`dcm_meta_advertiser.value`
Campaign [CM360]	`dcm_meta_campaign.value`
Site [CM360]	`dcm_meta_site.value`
Package [CM360]	`dcm_meta_package.value`
Placement [CM360]	`dcm_meta_placement.value`
Creative [CM360]	`dcm_meta_creative.value`
Device [CM360]	`platform_type` from aggregate & activity tables

📊 Base Metrics

Metric Name	Formula	Round
Media Cost [CM360]	`SUM(dcm_data_aggregate.media_cost)`	2
Clicks [CM360]	`SUM(dcm_data_aggregate.clicks)`	0
Impressions [CM360]	`SUM(dcm_data_aggregate.impressions)`	0
25 % Video Views [CM360]	`SUM(dcm_data_aggregate.video_first_quartile_completions)`	0
50 % Video Views [CM360]	`SUM(dcm_data_aggregate.video_midpoints)`	0
75 % Video Views [CM360]	`SUM(dcm_data_aggregate.video_third_quartile_completions)`	0
Video Completes [CM360]	`SUM(dcm_data_aggregate.video_completions)`	0
Video Views [CM360]	`SUM(dcm_data_aggregate.video_views)`	0
Video Plays [CM360]	`SUM(dcm_data_aggregate.video_plays)`	0
Video Replays [CM360]	`SUM(dcm_data_aggregate.video_replays)`	0
CT Conversions [CM360]	`SUM(dcm_data_activity.view_through_conversions)`	0
VT Conversions [CM360]	`SUM(dcm_data_aggregate.video_replays)` (per JSON)	0
CT Revenue [CM360]	`SUM(dcm_data_activity.click_through_revenue)`	0
VT Revenue [CM360]	`SUM(dcm_data_activity.view_through_revenue)`	0

🧮 Composite Metrics

Name	Formula	Round	Format
CTR [CM360]	`(Clicks / Impressions) * 100`	2	%
CPM [CM360]	`(Media Cost / Impressions) * 1000`	2	£
CPC [CM360]	`Media Cost / Clicks`	2	£
VTR [CM360]	`(Video Completes / Impressions) * 100`	2	%
CPCV [CM360]	`Media Cost / Video Completes`	2	£

🔗 Data Joins

Joins are pre‑declared so BI tools can automatically enrich fact rows:

Fact Table	Foreign Key	Lookup Table	Field
Aggregate & Activity	`advertiser_id`	`dcm_meta_advertiser`	`id`
	`campaign_id`	`dcm_meta_campaign`	`id`
	`site_id_cm360`	`dcm_meta_site`	`id`
	`package_roadblock_id`	`dcm_meta_package`	`id`
	`placement_id`	`dcm_meta_placement`	`id`
	`creative_id`	`dcm_meta_creative`	`id`
Activity only	`activity_id`	`dcm_meta_activity`	`id`

The special table dcm_meta_relations provides pre‑joined many‑to‑many mappings (placement ↔ creative, package ↔ placement, etc.) to simplify complex dimension pivots.

✅ Summary

The CM360 connector delivers two daily‑appended fact tables and eight refreshed dimension tables, enabling advanced video, display, and conversion reporting. With robust OAuth security, configurable report IDs, and automated joins, this integration is ideal for media cost analysis, attribution, and pacing dashboards.