unified_ads_base (V1 Legacy)

Deprecated -- V1 Legacy

This view is part of the V1 pipeline and is no longer actively maintained. The underlying V1 tables are not refreshed by the current daily pipeline. Use V2 tables and views for all new reporting. A V2 replacement (unified_performance_v2) is planned on the roadmap.

Overview

unified_ads_base is a UNION ALL view that combines performance data from all 6 ad platform final tables into a single cross-platform table with standardized column names. It was the foundation of V1 cross-platform reporting, enabling Looker Studio dashboards to query all platforms through one interface.

View Details

Property	Value
Full path	paidteam-data-warehouse.customer_ads_suse.unified_ads_base
Type	UNION ALL of 6 platform tables
Status	Deprecated (V1 Legacy)
Replacement	unified_performance_v2 (planned)

Source Tables

The view unions data from these 6 V1 final tables:

Platform	Source Table
Google	suse_google_adlevel_final
Microsoft	suse_microsoft_adlevel_final
LinkedIn	suse_linkedin_adlevel_final
Reddit	suse_reddit_adlevel_final
6sense	suse_6sense_adlevel_final
StackAdapt	suse_stackadapt_adlevel_final

Output Columns

Column	Type	Description
Date	DATE	The reporting date
Platform	STRING	Platform identifier (e.g., Google, Microsoft, LinkedIn, Reddit, 6sense, StackAdapt)
CampaignID	STRING	Campaign identifier (standardized from platform-specific names)
Campaign	STRING	Campaign name
AdGroup	STRING	Ad group name
AdName	STRING	Ad name
Impressions	INTEGER	Number of impressions
Clicks	INTEGER	Number of clicks
Cost	FLOAT64	Total spend (standardized across platforms)
Conversions	INTEGER	Total conversions

Platform-Specific Mappings

Each platform uses slightly different column names in its source tables. The view standardizes these into the unified schema:

Cost Field

Platform	Source Column	Mapped To
Google	Cost	Cost
Microsoft	Spend	Cost
LinkedIn	Cost	Cost
Reddit	Spend	Cost
6sense	Cost	Cost
StackAdapt	Spend	Cost

Campaign ID Field

Platform	Source Column	Mapped To
Google	CampaignID	CampaignID
Microsoft	CampaignID	CampaignID
LinkedIn	CampaignID	CampaignID
Reddit	CampaignID	CampaignID
6sense	Campaign_ID	CampaignID
StackAdapt	Campaign_ID	CampaignID

Ad Group Field

Platform	Source Column	Mapped To
Google	Adgroup	AdGroup
Microsoft	AdGroupName	AdGroup
LinkedIn	CampaignName	AdGroup
Reddit	Adgroupname	AdGroup
6sense	AdGroup	AdGroup
StackAdapt	AdGroup	AdGroup

LinkedIn Hierarchy

LinkedIn uses a three-tier hierarchy (Campaign Group > Campaign > Creative) rather than the standard two-tier (Campaign > Ad Group > Ad). In this view, LinkedIn's CampaignName is mapped to AdGroup and CampaignGroupName is mapped to Campaign.

Conceptual SQL

-- Simplified representation of the view logic
CREATE VIEW `paidteam-data-warehouse.customer_ads_suse.unified_ads_base` AS

SELECT
  Date,
  'Google' AS Platform,
  CAST(CampaignID AS STRING) AS CampaignID,
  Campaign,
  Adgroup AS AdGroup,
  AdName,
  Impressions,
  Clicks,
  Cost,
  Conversions
FROM `customer_ads_suse.suse_google_adlevel_final`

UNION ALL

SELECT
  Date,
  'Microsoft' AS Platform,
  CAST(CampaignID AS STRING) AS CampaignID,
  CampaignName AS Campaign,
  AdGroupName AS AdGroup,
  AdName,
  Impressions,
  Clicks,
  Spend AS Cost,
  Conversions
FROM `customer_ads_suse.suse_microsoft_adlevel_final`

UNION ALL

-- ... (LinkedIn, Reddit, 6sense, StackAdapt follow the same pattern)

Usage Notes

• All IDs are cast to STRING for cross-platform compatibility.
• Cost is always in the account's local currency (no currency conversion is applied).
• Conversions reflects the primary conversion metric for each platform, which may differ in attribution model.
• The view does not include creative metadata, engagement metrics, or video metrics. Only the core performance columns are unified.

Example Query

-- Cross-platform spend summary by month (V1 legacy)
SELECT
  Platform,
  DATE_TRUNC(Date, MONTH) AS Month,
  SUM(Cost) AS TotalSpend,
  SUM(Impressions) AS TotalImpressions,
  SUM(Clicks) AS TotalClicks,
  SUM(Conversions) AS TotalConversions
FROM `paidteam-data-warehouse.customer_ads_suse.unified_ads_base`
GROUP BY Platform, Month
ORDER BY Month DESC, TotalSpend DESC;

Dependencies

• Tables: All 6 V1 _final tables (deprecated, no longer refreshed)
• Consumers: unified_ads_enriched (V1), legacy Looker Studio dashboards