Breadcrumbs

Creative ID methodology

Overview

The creative_performance report consists of creative-level data tied to an ad from platforms on which customers serve media.

This guide walks through where the Creative ID comes from and how it is parsed from the respective platforms.

Methodology by platform

The following SQL examples are for Redshift.

Platform

Creative ID Source Column

Logic for Creative Id Extraction

Example SQL

CM360

Column: creativeid

Source: {alli_client}.cm360_insights_creative

We pull creativeid directly from the cm360_insights_creative Alli datasource and alias it to creative_id within creative_performance
SQL
select
  distinct creativeid as creative_id
from {alli_client}.cm360_insights_creative;

Facebook Ads/Meta Ads

Column: creative

Source:{alli_client}.facebookads_structure_ad

We parse a JSON object called creative and extract the value of the id key from the facebookads_structure_ad Alli datasource
SQL
select
  json_extract_path_text(creative, 'id') AS creative_id
from {alli_client}.facebookads_structure_ads;

Snapchat Ads

Column: id

Source:

{alli_client}.snapchat_structure_creative

We pull id directly from the snapchat_structure_creative Alli datasource and alias it to creative_id within creative_performance
SQL
select
  id as creative_id
from {alli_client}.snapchat_structure_creative;

TikTok Ads

Columns: video_id, image_ids

Source: {alli_client}.tiktokads_settings_ad

We pull the video_id and image_ids directly from the tiktokads_settings_ad Alli datasource and coalesce them together with the video_id taking precedence before aliasing to creative_id with creative_performance

Note: We do a split part function on the image_ids column to return the appropriate split part as the image id.
SQL
with creative_ids as (
  select
    nullif(lower(video_id), 'null') as video_id,
    nullif(nullif(lower(image_ids), ''), 'null') as image_ids
  from {alli_client}.tiktokads_settings_ad
)

select distinct
  coalesce(video_id,split_part(image_ids, ',', 1)) as creative_id
from creative_ids

YouTube - DV360

Column: video_id

Source: {alli_client}.dv360_structure_youtube_ad

We pull video_id directly from the dv360_structure_youtube_adAlli datasource which is the Structured Data File from DV360 UI and alias it to creative_id within creative_performance.

Reservation Line Items are not supported within the SDF files so video id is not available from these items currently.
SQL
select
  video_id as creative_id
from {alli_client}.dv360_structure_youtube_ad


YouTube - Google Ads

Column: youtube_video_asset_youtube_video_id

Source:

{alli_client}.googleads_settings_asset

We pull youtube_video_asset_youtube_video_id directly from the googleads_settings_asset Alli datasource and alias it to creative_id within creative_performance
SQL
select
  distinct youtube_video_asset_youtube_video_id as creative_id 
from {alli_client}.googleads_settings_asset

Pinterest Ads

Column: pin_id

Source: {alli_client}.pinterestads_settings_pins

We pull pin_id directly from the pinterestads_settings_pins Alli datasource and alias it to creative_id within creative_performance
SQL
select
  pin_id as creative_id
from {alli_client}.pinterestads_settings_pins

Reddit Ads

Column: id

Source:

{alli_client}.redditads_settings_post

We pull id directly from the redditads_settings_post Alli Datasource and alias it to creative_id within creative_performance
SQL
select
  id as creative_id
from {alli_client}.redditads_settings_post