Adding a New Platform Creative Table

This workflow covers the end-to-end process for onboarding a new advertising platform's creative metadata into the data warehouse. Follow these steps in order.

Prerequisites

• The platform's adlevel staging and final tables already exist in V2
• You have access to the platform's creative data (either through Dataslayer or manual API export)
• You understand the platform's primary key for creative records

Step 1: Create the Staging Table

Create the staging table where the ingestion tool (Dataslayer or manual process) will write raw creative metadata.

CREATE TABLE IF NOT EXISTS `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_creative_staging_v2`
(
  -- Primary key column(s) - varies by platform
  CreativeID STRING,          -- or composite key columns

  -- Common creative metadata columns
  CreativeName STRING,
  CampaignName STRING,
  AdGroupName STRING,

  -- Platform-specific columns
  -- Add columns based on available platform data
  ImageURL STRING,
  VideoURL STRING,
  HeadlineText STRING,
  BodyText STRING,
  DestinationURL STRING
);

Primary key guidance by platform:

Platform	Primary Key	Notes
LinkedIn	CreativeID	Globally unique
Reddit	CampaignID + AdGroupID + AdID	Composite key required
Google	TBD	May need Search/Display split
Microsoft	TBD	In progress
6sense	TBD	Based on platform behavior
StackAdapt	TBD	Based on platform behavior

Step 2: Create the Final Table

Create the final table with the same schema as staging plus the _ingested_at audit column.

CREATE TABLE IF NOT EXISTS `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_creative_final_v2`
(
  -- Same columns as staging
  CreativeID STRING,
  CreativeName STRING,
  CampaignName STRING,
  AdGroupName STRING,
  ImageURL STRING,
  VideoURL STRING,
  HeadlineText STRING,
  BodyText STRING,
  DestinationURL STRING,

  -- Audit column (not in staging)
  _ingested_at TIMESTAMP
);

Step 3: Add MERGE Logic to Creative Refresh Script

Add a new BEGIN...EXCEPTION...END block to the Creative Refresh Script for the new platform. The MERGE uses the platform's primary key to match records.

Single-Column Primary Key (e.g., LinkedIn)

BEGIN
  MERGE `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_creative_final_v2` AS T
  USING `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_creative_staging_v2` AS S
  ON T.CreativeID = S.CreativeID
  WHEN MATCHED THEN
    UPDATE SET
      T.CreativeName = S.CreativeName,
      T.CampaignName = S.CampaignName,
      T.AdGroupName = S.AdGroupName,
      T.ImageURL = S.ImageURL,
      T.VideoURL = S.VideoURL,
      T.HeadlineText = S.HeadlineText,
      T.BodyText = S.BodyText,
      T.DestinationURL = S.DestinationURL,
      T._ingested_at = CURRENT_TIMESTAMP()
  WHEN NOT MATCHED BY TARGET THEN
    INSERT (CreativeID, CreativeName, CampaignName, AdGroupName,
            ImageURL, VideoURL, HeadlineText, BodyText, DestinationURL,
            _ingested_at)
    VALUES (S.CreativeID, S.CreativeName, S.CampaignName, S.AdGroupName,
            S.ImageURL, S.VideoURL, S.HeadlineText, S.BodyText, S.DestinationURL,
            CURRENT_TIMESTAMP());

  INSERT INTO `paidteam-data-warehouse.customer_ads_suse.creative_refresh_log_v2`
    (run_timestamp, platform, status, message)
  VALUES (CURRENT_TIMESTAMP(), '{platform}', 'OK', 'Creative merge completed');

EXCEPTION WHEN ERROR THEN
  INSERT INTO `paidteam-data-warehouse.customer_ads_suse.creative_refresh_log_v2`
    (run_timestamp, platform, status, message)
  VALUES (CURRENT_TIMESTAMP(), '{platform}', 'ERROR', @@error.message);
END;

Composite Primary Key (e.g., Reddit)

BEGIN
  MERGE `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_creative_final_v2` AS T
  USING `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_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
      T.CreativeName = S.CreativeName,
      -- ... update all non-key columns
      T._ingested_at = CURRENT_TIMESTAMP()
  WHEN NOT MATCHED BY TARGET THEN
    INSERT (CampaignID, AdGroupID, AdID, CreativeName, ..., _ingested_at)
    VALUES (S.CampaignID, S.AdGroupID, S.AdID, S.CreativeName, ..., CURRENT_TIMESTAMP());

  INSERT INTO `paidteam-data-warehouse.customer_ads_suse.creative_refresh_log_v2`
    (run_timestamp, platform, status, message)
  VALUES (CURRENT_TIMESTAMP(), '{platform}', 'OK', 'Creative merge completed');

EXCEPTION WHEN ERROR THEN
  INSERT INTO `paidteam-data-warehouse.customer_ads_suse.creative_refresh_log_v2`
    (run_timestamp, platform, status, message)
  VALUES (CURRENT_TIMESTAMP(), '{platform}', 'ERROR', @@error.message);
END;

Step 4: Create the Performance View

Create a view that joins the creative final table with the adlevel final table. This enables creative-level performance analysis.

CREATE OR REPLACE VIEW `paidteam-data-warehouse.customer_ads_suse.{platform}_creative_performance_v2` AS
SELECT
  a.Date,
  a.CampaignName,
  a.AdGroupName,
  a.AdName,

  -- Creative metadata from creative table
  c.CreativeName,
  c.ImageURL,
  c.VideoURL,
  c.HeadlineText,
  c.BodyText,
  c.DestinationURL,

  -- UDF-derived fields
  customer_ads_suse.get_channel(a.CampaignName) AS Channel,
  customer_ads_suse.get_product_line(a.CampaignName, '{platform}') AS ProductLine,
  customer_ads_suse.get_product(a.CampaignName, '{platform}') AS Product,
  customer_ads_suse.get_geo(a.CampaignName, '{platform}') AS Geo,
  customer_ads_suse.get_content_name(c.CreativeName, a.CampaignName) AS ContentName,
  customer_ads_suse.get_campaign_code(a.CampaignName, a.AdName, '{platform}') AS CampaignCode,

  -- Performance metrics from adlevel table
  a.Impressions,
  a.Clicks,
  a.Cost,
  a.Conversions

FROM `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_adlevel_final_v2` a
LEFT JOIN `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_creative_final_v2` c
  ON a.AdID = c.CreativeID  -- Adjust join key per platform
;

WARNING

The join key between adlevel and creative tables varies by platform. Ensure you use the correct ID column(s) for the join.

Step 5: Test with Manual Refresh

Before relying on the scheduled pipeline, run a manual test:

1. Manually populate the staging table with sample data
2. Run the MERGE statement manually
3. Verify results in the final table
4. Query the performance view to confirm the join works

-- Verify final table after manual merge
SELECT COUNT(*) AS row_count, MAX(_ingested_at) AS latest_ingestion
FROM `paidteam-data-warehouse.customer_ads_suse.suse_{platform}_creative_final_v2`;

-- Test the performance view
SELECT *
FROM `paidteam-data-warehouse.customer_ads_suse.{platform}_creative_performance_v2`
LIMIT 10;

Step 6: Update Documentation

After the creative table is live:

1. Update the Data Dictionary with new table columns
2. Add the platform to the Daily Pipeline schedule
3. Log the change in the Changelog
4. Update project instructions if any platform-specific rules were discovered

Checklist

• [ ] Staging table created
• [ ] Final table created with _ingested_at
• [ ] MERGE logic added to Creative Refresh Script
• [ ] Performance view created with correct join keys
• [ ] Manual test completed successfully
• [ ] Refresh log shows OK status
• [ ] Documentation updated