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.
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
|
Related articles
