reddit_creative_performance_v2

This view joins the Reddit adlevel final table with the Reddit creative final table, producing a single denormalized output with performance metrics, creative metadata, URLs, and UDF-enriched campaign fields.

View Details

Property	Value
Full path	`paidteam-data-warehouse.customer_ads_suse.reddit_creative_performance_v2`
Source (adlevel)	`suse_reddit_adlevel_final_v2`
Source (creative)	`suse_reddit_creative_final_v2`
Join type	`LEFT JOIN`
Join key	`CampaignID` + `AdgroupID` + `AdID` (composite)

Join Logic

sql

FROM `paidteam-data-warehouse.customer_ads_suse.suse_reddit_adlevel_final_v2` a
LEFT JOIN `paidteam-data-warehouse.customer_ads_suse.suse_reddit_creative_final_v2` c
  ON a.CampaignID = c.CampaignID
  AND a.AdgroupID = c.AdGroupID
  AND a.AdID = c.AdID

Reddit does not use globally unique ad IDs, so a composite key of CampaignID + AdgroupID + AdID is required to correctly match creative metadata to performance rows. The LEFT JOIN ensures all adlevel rows are preserved even if no matching creative exists.

Composite Key

Unlike LinkedIn (which uses a single CreativeID), Reddit requires a three-column composite key for correct creative-to-performance matching.

Column Groups

Date and IDs

Column	Type	Source	Description
`Date`	DATE	adlevel	The reporting date
`CampaignID`	STRING	adlevel	Reddit campaign identifier
`AdgroupID`	STRING	adlevel	Reddit ad group identifier
`AdID`	STRING	adlevel	Reddit ad identifier

Names

Column	Type	Source	Description
`Campaignname`	STRING	adlevel	Campaign name
`Adgroupname`	STRING	adlevel	Ad group name
`Adname`	STRING	adlevel	Ad name

Campaign Metadata

Column	Type	Source	Description
`CampaignObjective`	STRING	adlevel	Campaign objective (e.g., CONVERSIONS, TRAFFIC)
`BidStrategy`	STRING	adlevel	Bidding strategy

Ad Status

Column	Type	Source	Description
`AdStatus`	STRING	creative	Current ad delivery status
`AdEffectiveStatus`	STRING	creative	Effective status considering campaign/adgroup status

URLs

Column	Type	Source	Description
`ClickURL`	STRING	creative	Click-through destination URL
`PostURL`	STRING	creative	URL to the Reddit post
`ThumbnailURL`	STRING	creative	URL to the ad thumbnail image
`MediaURL`	STRING	creative	URL to the ad media asset (image or video)

Core Metrics

Column	Type	Description
`Impressions`	INTEGER	Number of times the ad was displayed
`Clicks`	INTEGER	Number of clicks on the ad
`Cost`	FLOAT64	Total spend in account currency (mapped from Reddit's `Spend` field)

Cost Mapping

Reddit's API reports spending as Spend. This view maps it to Cost for cross-platform consistency.

Rate Metrics

Column	Type	Description
`CTR`	FLOAT64	Click-through rate
`CPC`	FLOAT64	Average cost per click
`CPM`	FLOAT64	Cost per thousand impressions (mapped from Reddit's `ECPM` field)

CPM Mapping

Reddit's API reports CPM as ECPM (effective CPM). This view maps it to CPM for cross-platform consistency.

Reach

Column	Type	Description
`Reach`	INTEGER	Unique users who saw the ad
`Frequency`	FLOAT64	Average times each user saw the ad

Conversions (Reddit-Specific)

Reddit provides its own conversion tracking columns that differ from other platforms.

Column	Type	Description
`Conversions`	INTEGER	Total attributed conversions
`ViewThroughConversions`	INTEGER	Conversions attributed to ad views (no click)
`ClickThroughConversions`	INTEGER	Conversions attributed to ad clicks
`PurchaseRoas`	FLOAT64	Return on ad spend for purchase conversions

Video Metrics

Column	Type	Description
`VideoViews`	INTEGER	Number of video views
`VideoViewableImpressions`	INTEGER	Viewable video impressions
`VideoFullyViewedRate`	FLOAT64	Percentage of views that completed the full video
`Video25PctViews`	INTEGER	Views past 25% of video
`Video50PctViews`	INTEGER	Views past 50% of video
`Video75PctViews`	INTEGER	Views past 75% of video
`Video100PctViews`	INTEGER	Views to 100% completion

Calculated Fields

Column	Type	Description
`TotalConversions`	INTEGER	Sum of all conversion types
`CPA`	FLOAT64	Cost / TotalConversions (NULL-safe with `SAFE_DIVIDE`)

UDF-Enriched Fields

These fields are computed at query time by the dataset's UDFs. For Reddit, the AdGroupName (Adgroupname) is used as the source for naming convention parsing, as Reddit's ad group names carry the structured metadata.

Column	UDF	Input Source	Description
`Channel`	`get_channel()`	CampaignName	Advertising channel
`CampaignCode`	`get_campaign_code()`	CampaignName	Numeric campaign tracking code
`ProductLine`	`get_product_line()`	CampaignName	Product line
`Product`	`get_product()`	CampaignName	Specific product name
`SubProduct`	`get_subproduct()`	CampaignName	Sub-product or variant
`GEO`	`get_geo()`	CampaignName	Geographic targeting region
`Tier`	`get_tier()`	CampaignName	Tier classification
`Language`	`get_language()`	CampaignName	Language targeting
`Audience`	`get_audience()`	CampaignName	Audience segment
`ContentName`	`get_content_name()`	AdGroupName	Human-readable content name derived from the ad group name

ContentName Source

Unlike LinkedIn where ContentName is derived from the creative name, Reddit uses the ad group name (Adgroupname) as the input to get_content_name(), because Reddit's naming convention places the content descriptor in the ad group level.

Audit Timestamps

Column	Type	Source	Description
`_ingested_at`	TIMESTAMP	adlevel	When the adlevel row was written to the final table
`creative_ingested_at`	TIMESTAMP	creative	When the creative record was last merged

Example Queries

Top Ads by Spend (Last 30 Days)

sql

SELECT
  Adname,
  Campaignname,
  Adgroupname,
  MediaURL,
  SUM(Cost) AS TotalSpend,
  SUM(Impressions) AS TotalImpressions,
  SUM(Clicks) AS TotalClicks,
  SAFE_DIVIDE(SUM(Clicks), SUM(Impressions)) AS AvgCTR,
  SUM(TotalConversions) AS Conversions
FROM `paidteam-data-warehouse.customer_ads_suse.reddit_creative_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Adname, Campaignname, Adgroupname, MediaURL
ORDER BY TotalSpend DESC
LIMIT 20;

Video Completion Funnel

sql

SELECT
  Adname,
  SUM(Impressions) AS Impressions,
  SUM(VideoViews) AS Views,
  SUM(Video25PctViews) AS Q1_Views,
  SUM(Video50PctViews) AS Q2_Views,
  SUM(Video75PctViews) AS Q3_Views,
  SUM(Video100PctViews) AS Completions,
  SAFE_DIVIDE(SUM(Video100PctViews), SUM(VideoViews)) AS CompletionRate
FROM `paidteam-data-warehouse.customer_ads_suse.reddit_creative_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND VideoViews > 0
GROUP BY Adname
ORDER BY Views DESC;

Conversion Performance by Content

sql

SELECT
  ContentName,
  ProductLine,
  COUNT(DISTINCT AdID) AS UniqueAds,
  SUM(Cost) AS TotalSpend,
  SUM(Conversions) AS TotalConversions,
  SUM(ClickThroughConversions) AS ClickConversions,
  SUM(ViewThroughConversions) AS ViewConversions,
  SAFE_DIVIDE(SUM(Cost), SUM(Conversions)) AS CPA
FROM `paidteam-data-warehouse.customer_ads_suse.reddit_creative_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY ContentName, ProductLine
ORDER BY TotalSpend DESC;

Dependencies

Tables: suse_reddit_adlevel_final_v2, suse_reddit_creative_final_v2
UDFs: All 10 UDFs (get_channel, get_campaign_code, get_product_line, get_product, get_subproduct, get_geo, get_tier, get_language, get_audience, get_content_name)
Consumers: Looker Studio Reddit Creative Dashboard

reddit_creative_performance_v2 ​

View Details ​

Join Logic ​

Column Groups ​

Date and IDs ​

Names ​

Campaign Metadata ​

Ad Status ​

URLs ​

Core Metrics ​

Rate Metrics ​

Reach ​

Conversions (Reddit-Specific) ​

Video Metrics ​

Calculated Fields ​

UDF-Enriched Fields ​

Audit Timestamps ​

Example Queries ​

Top Ads by Spend (Last 30 Days) ​

Video Completion Funnel ​

Conversion Performance by Content ​

Dependencies ​