Skip to main content
Skip table of contents

Placement ID methodology in Data Library

Overview

The ad_performance report combines (1) ad-level data from DSPs on which customer serves media, and (2) placement-level data from CM360 using a join on cm_placement_id and date.

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

Methodology by platform

The following SQL examples are for Redshift.

To see the BigQuery version, (1) replace the function regexp_substr with regexp_extract, and (2) add an r before the opening quotation mark in the 2nd argument, e.g.:

regexp_substr(arg1, 'arg2') → regexp_extract(arg1, r'arg2')

Platform

PID Source (in order of priority)

Example SQL

Amazon DSP

Creative Name

SQL 
select
  regexp_substr(
    creativename, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.amazondsp_insights_ad

Bidtellect

Creative Name

SQL 
select
  regexp_substr(
    ndsp_client_reporting_creative_name, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.bidtellect_insights_hit

DV360

CM360 Placement ID

SQL 
select
  coalesce(
    filter_cm360_placement_id, 
    filter_creative_integration_code
  ) as cm_placement_id 
from {alli_client}.dv360_insights_ad

DV360 Trueview (Youtube)

Trueview Ad Group Name

SQL 
select
  regexp_substr(
    filter_trueview_ad_group, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.dv360_insights_ad_trueview

Google Ads

Ad Group Name

SQL 
select
  regexp_substr(
    ad_group_name, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.googleads_settings_adgroup

Ad Name

SQL 
select
  regexp_substr(
    ad_name, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.googleads_settings_ad

Ad Tracking URL Template

SQL 
select
  replace(
    regexp_substr(
      ad_tracking_url_template, '\.[0-9]{8,9}'
    ), '.', ''
  ) as cm_placement_id
from {alli_client}.googleads_settings_ad

Microsoft Bing

Ad Group Name

SQL 
select
  regexp_substr(
    adgroupname, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.bing_insights_adgroup

The Trade Desk

Ad Server Creative Placement ID

SQL 
select
  ad_server_creative_placement_id as cm_placement_id
from {alli_client}.tradedesk_insights_ad

Vistar DSP

Creative Name

SQL 
select
  regexp_substr(
    creative_name, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.vistar_insights_ad

Yahoo DSP

Creative Custom ID

SQL 
select
  coalesce(
    nullif(creative_custom_id_1, '0'),
    nullif(creative_custom_id_2, '0'),
    nullif(creative_custom_id_3, '0')
  ) as cm_placement_id
from {alli_client}.verizon_insights_ad

Creative Name

SQL 
select
  regexp_substr(
    creative_name, '[0-9]{8,9}$'
  ) as cm_placement_id
from {alli_client}.verizon_insights_ad
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close