suse_reddit_creative_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_reddit_creative_final_v2Type: Final | Rows: 666 | Size: 0.3 MB

Reddit creative metadata final table (V2). The authoritative lookup table for Reddit ad creative assets. Updated via MERGE from the staging table with column-casing normalization. Joined to the adlevel final table via CampaignID + AdGroupID + AdID for creative performance analysis.

Schema

Column	Type	Nullable	Description
CampaignID	STRING	Yes	Reddit campaign ID
AdGroupID	STRING	Yes	Reddit ad group ID (PascalCase, normalized from staging)
AdID	STRING	Yes	Reddit ad ID
CampaignName	STRING	Yes	Campaign name (PascalCase, normalized from staging)
AdGroupName	STRING	Yes	Ad group name (PascalCase, normalized from staging)
AdName	STRING	Yes	Ad name (PascalCase, normalized from staging)
CampaignObjective	STRING	Yes	Campaign objective (PascalCase, normalized from staging)
GoalType	STRING	Yes	Goal type
AdEffectiveStatus	STRING	Yes	Current effective status of the ad
AdClickURL	STRING	Yes	Click-through URL
AdPostURL	STRING	Yes	URL of the Reddit post used as the ad
AdPostID	STRING	Yes	ID of the Reddit post used as the ad
_ingested_at	TIMESTAMP	Yes	Timestamp of last MERGE update

Partitioning & Clustering

• Partitioned by: None (metadata table, no Date column)
• Clustered by: CampaignID, AdGroupID, AdID

Clustering on the composite key optimizes JOIN operations with the adlevel table.

Refresh Logic

The Creative Refresh Script performs a MERGE operation with column-casing normalization:

MERGE INTO suse_reddit_creative_final_v2 AS T
USING (
  SELECT
    CampaignID,
    AdgroupID AS AdGroupID,
    AdID,
    Campaignname AS CampaignName,
    Adgroupname AS AdGroupName,
    Adname AS AdName,
    Campaignobjective AS CampaignObjective,
    GoalType,
    AdEffectiveStatus,
    AdClickURL,
    AdPostURL,
    AdPostID
  FROM suse_reddit_creative_staging_v2
) AS S
ON T.CampaignID = S.CampaignID
  AND T.AdGroupID = S.AdGroupID
  AND T.AdID = S.AdID
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

• MATCHED: All columns are updated; _ingested_at is set to CURRENT_TIMESTAMP()
• NOT MATCHED: New creatives are inserted
• NOT MATCHED BY SOURCE: Rows are preserved (historical creatives kept)

Composite MERGE key: CampaignID + AdGroupID + AdID

Results are logged to creative_refresh_log_v2.

Sample Query

-- Join creative metadata with adlevel performance
SELECT
  a.Date,
  c.AdName,
  c.CampaignObjective,
  c.AdEffectiveStatus,
  c.AdClickURL,
  SUM(a.Impressions) AS impressions,
  SUM(a.Clicks) AS clicks,
  SUM(a.Spend) AS spend
FROM `paidteam-data-warehouse.customer_ads_suse.suse_reddit_adlevel_final_v2` a
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
WHERE a.Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY a.Date, c.AdName, c.CampaignObjective, c.AdEffectiveStatus, c.AdClickURL
ORDER BY spend DESC
LIMIT 100;

Notes

• Composite primary key: Unlike LinkedIn (which uses a single CreativeID), Reddit requires a three-column composite key (CampaignID + AdGroupID + AdID) because Reddit ad IDs are not globally unique.
• Column casing normalization: The final table uses PascalCase (AdGroupID, CampaignName) while the staging table uses Dataslayer's original casing (AdgroupID, Campaignname). This normalization happens in the MERGE subquery.
• When joining with the adlevel table, note the casing difference: the adlevel table has AdgroupID (lowercase 'g') while this creative final table has AdGroupID (uppercase 'G'). The JOIN in the example above handles this correctly.
• The final table has 666 rows vs. 202 in staging, meaning 464 historical creatives have been preserved by the MERGE.
• Columns like Campaigngoaltype, CampaignObjectiveType, OptimizationStrategyType, AdPreviewExpiry, AdPreviewURL, and Spend from the staging table are not carried into the final table.