Skip to main content

Getting Started with Media Intelligence for Brands

LiveRamp Clean Room’s Media Intelligence offering provides brands with critical media performance insights from individual publishers to quickly start collaboration.

Media Intelligence typically includes the following types of insights:

  • Audience Overlaps/Indices

  • Optimal Reach/Frequency

  • Attribution (First, Last, and Linear)

For more information on the specific insights available, see the "Run Reports" section below.

To learn more about Media Intelligence for brands, click here.

Overall Steps

Implementing Media Intelligence for brands typically involves the following overall steps:

Note

Before starting the collaboration, talk to the publisher to understand how reports can be generated and accessed (some publishers allow brands to run reports and view insights in LiveRamp Clean Room, while other publishers run the reports themselves and deliver the insights to the brand). This is typically handled during LiveRamp’s “matchmaking” process.

  1. If you’re a new LiveRamp Clean Room customer:

    • You sign the required agreements (some publishers require a separate Master Agreement).

    • You work with the LiveRamp Clean Room team on making key implementation decisions.

    • LiveRamp creates a Clean Room account for you.

    • You set up your account and add users.

  2. The publisher creates your Media Intelligence clean room and sends you an invitation to join it.

  3. You accept the Publisher’s invitation to the Media Intelligence clean room.

  4. You prepare your data.

  5. You connect your datasets by creating data connections to each dataset.

  6. You provision your datasets to the Media Intelligence clean room.

  7. You assign your datasets to each question.

  8. You or the publisher run reports (depending on the arrangement with the publisher).

  9. You access reports (depending on the arrangement with the publisher, this might be done in LiveRamp Clean Room or the publisher might send them to you).

For more information on performing these steps, see the sections below. For new LiveRamp Clean Room brand customers, see “Getting Started with LiveRamp Clean Room for Brands” for the steps required for new customers.

Accept the Clean Room Invitation

After the publisher has created the clean room, they’ll send you an invitation to join that clean room via email. To accept the invitation:

  1. From the navigation menu, select Clean Room → Clean Room Invitations to open the Clean Room Invitations page.

    LCR-Invitations_menu_selection.png

  2. Locate the row for the invitation from the Media Intelligence clean room owner and click Accept.

  3. From the dialog that appears, click Accept.

Prepare Your Data

For your Media Intelligence clean rooms, you’ll need to connect the following types of datasets:

  • Conversions dataset (containing transactions or other conversion events)

  • CRM dataset (with attribute data)

Note

LiveRamp will prepare and connect your CID | RampID mapping dataset.

When preparing your data for Media Intelligence clean rooms, make sure to follow the guidelines for file and table names, folder names, and headers for files and tables listed in “Guidelines for Standard Dataset Headers, File Names, and Folder Names” as well as the general formatting information in “Format Your Clean Room Data”.

Note

If you’re unable to format your data as presented below, you can perform certain data transformations when you provision the dataset to the clean room (such as changing a field’s data type, transforming a timestamp format, or adding a field that is not present in your data). For more information, see “Perform Dataset Transformations".

Prepare Your Conversions Dataset

When preparing your conversion dataset, make sure it includes the required fields shown below, with the correct data type for each field:

Field Contents

Recommended Field Name

Data Type

A unique user ID

cid

string

Unique identifier for the transaction

transaction_id

string

Unit price of a particular SKU/product ID in a transaction

gross_amt

double

Timestamp of the conversion event (ideally in UTC ISO8601 format: “ YYYY-MM-DDTHH:mm:ssZ”)

conversion_timestamp

timestamp

For information on formatting of additional fields you might include in your conversions dataset, see "Format a Conversions Dataset".

Prepare Your CRM Dataset

When preparing your CRM dataset, make sure it includes the required fields shown below, with the correct data type for each field:

Field Contents

Recommended Field Name

Data Type

A unique user ID

cid

string

An attribute category

<User Attribute 1>

string

You can include as many attribute fields as you like.

Note

If any attribute categories that you’ll want to use in Insights are “binary” data type, and you are not able to change those to “string” data type before connecting the dataset, you might want to recast those when provisioning the dataset to the clean room (for more information, see “Perform Dataset Transformations".

Connect Your Data

Once you've confirmed that you have the required datasets ready for use, you're ready to connect your data. If you’re going to be hosting your data at its source in a cloud warehouse, you’ll need to connect that data to your Clean Room account.

Note

  • These data connections can be used for multiple Media Intelligence clean rooms.

  • If you’ve chosen to have LiveRamp host the data, skip these steps and see the information in “Uploading Data”.

  • Before configuring your data connections in LiveRamp Clean Room, confirm you have the required datasets ready for use in a Media Intelligence clean room. Knowing where your datasets live, the credentials and tables required to access those datasets, and having the proper file and table formatting in place will make the remainder of your setup much more seamless. For more information, see the "Prepare Your Data" section above.

Connecting your data involves creating a data connection by performing the overall steps listed below. The type of connection to create will depend on your situation and business needs. Your LiveRamp representative will work with you to determine the type(s) of connections to create.

A data connection needs to be created for each dataset you want to utilize in a clean room, so you will need separate data connections for your conversions dataset and your CRM dataset.

For more information on the steps to perform for your cloud provider, see the articles in “Connect to Cloud-Based Data”. Specific instructions for each step will be listed in the appropriate help article for your data connection type.

Media Intelligence clean rooms are Hybrid-type clean rooms, so make sure to follow the instructions for the appropriate data connection type that supports Hybrid clean rooms:

After you create data connections, these connections appear on the Data Connections page, and you can then provision those datasets to your Media Intelligence clean rooms.

Provision Your Datasets to the Clean Room

After your data connections have been successfully connected, you can provision each associated dataset to a Media Intelligence clean room so it’s available to assign to questions.

Note

You need to provision your datasets to each Media Intelligence clean room.

During provisioning, you can configure the dataset so that only certain fields or field values are queryable. You can also rename fields (to make them easier to map when assigning the dataset to questions) and create new fields by applying a transformation to an existing field or fields.

For complete instructions, see “Provision a Dataset to a Clean Room".

Assign Your Datasets to Questions

After you’ve provisioned your datasets to the clean room, you can then assign those datasets to a Media Intelligence clean room’s questions. 

Note

You do not need to assign datasets for the following questions, as they only use publisher datasets and fields:

  • 7. Reach Analysis: What was the unique reach achieved by the advertiser and at what frequency was the media served?

  • 10. Reach And Frequency: What are the overall impressions, reach and frequency achieved across all advertisers and brands?

This involves the following steps:

  1. Assigning the dataset owner to each dataset use in that question.

  2. Assigning the appropriate dataset to each dataset macro for that question.

  3. Assigning the appropriate field to each field macro for that question.

This makes sure that each macro in the question is being linked to the correct field or dataset.

Note

You need to assign your datasets to the questions in each Media Intelligence clean room.

All questions use your CID | RampID mapping dataset. Some questions use your CRM dataset and some questions use your conversions dataset.

For instructions on how to assign datasets for each question type, see the sections below (for complete instructions on assigning datasets to questions, see “Configure Datasets for Questions”).

Note

If the field from your dataset does not appear in the drop-down menu when assigning fields to field macros, it is most likely because the data type for that field in the dataset is not the same as the data type that the field macro expects.

For example, if your dataset was connected with the CID field set as a “Long” data type, it will not show up in the Assign Dataset Fields step because the macro is looking for a “String” value to map to. You can use one of the following methods to resolve this:

  • Change the field to use the appropriate field type in the dataset in your cloud environment

  • If you’re not able to adjust the data at source, a new field can be added to the provisioned dataset from within the clean room, cast as the correct data type to align with the field macro. Note that the new field must have a different name than the original field. Also note that this might add to the processing time when questions are run.

    See below for an example of the creation of a new field to change the data type from "LONG" to "TIMESTAMP":

    LCR-Media_Intelligence-Create_new_field.png

Assign Datasets for Questions That Utilize Your CRM Dataset

Perform the steps below for the following questions that utilize your CRM dataset:

  • 6. Attribute Overlap Index: Attribute Level Overlap and Index Report

  • 8. Reach Analysis: What is the reach at the advertiser and campaign level and what is the overlap percentage with CRM?

  • 9. Overlap Analysis: What is the overlap between Advertiser CRM and publisher universe?

To assign datasets for these questions:

  1. From the Clean Room menu, select Questions and then click Assign Datasets next to the desired question.

    Questions-Assign-Datasets-Button.png

    The Manage Datasets wizard displays the dataset macros that are enabled for the question. For information about dataset macros, see "Add a Macro for a Dataset Type".

  2. Assign your organization as the owner for the “partner_crm” and “partnermap” datasets.

    LCR-Media_Intelligence-Assign_Datasets_CRM_Questions-Assign_Ownership.png

  3. Use the table below to assign the datasets to the appropriate dataset macro.

    LCR-Media_Intelligence-Assign_Datasets_CRM_Questions-Dataset_Macros_step.png

  4. Use the table below to assign the fields to the appropriate field macro.

    LCR-Media_Intelligence-Assign_Datasets_CRM_Questions-Field_Macros_step.png

Dataset Macro Name

Brand Dataset Used

Field Macro Name

Brand Field to Assign

@partnermap

CID | RampID mapping

@cid (string)

Your CID field

@rampid (string)

Your RampID field

@partner_crm

CRM

@cid (string)

Your CID field 

Assign Datasets for Questions That Utilize Your Conversions Dataset

Perform the steps below for the following questions that utilize your conversions dataset:

  • 1. First Touch Attribution: What is the attributed revenue of my campaign based on first touch with the custom attribution window?

  • 2. Last Touch Attribution: What is the attributed revenue of my campaign based on last touch with the custom attribution window?

  • 3. Linear Attribution: What is the attributed revenue of my campaign based on linear attribution with custom attribution window?

  • 4. Latency Analysis: How long does it take for a user to convert after first touch and last touch?

  • 5. Frequency Analysis: What is the optimal frequency to deliver my campaigns?

To assign datasets for these questions:

  1. From the Clean Room menu, select Questions and then click Assign Datasets next to the desired question.

    Questions-Assign-Datasets-Button.png

    The Manage Datasets wizard displays the dataset macros that are enabled for the question. For information about dataset macros, see "Add a Macro for a Dataset Type".

  2. Assign your organization as the owner for the “conversions” and “partnermap” datasets.

    LCR-Media_Intelligence-Assign_Datasets_Conversions_Questions-Assign_Ownership.png

  3. Use the table below to assign the datasets to the appropriate dataset macro.

    LCR-Media_Intelligence-Assign_Datasets_Conversions_Questions-Dataset_Macros_step.png

  4. Use the table below to assign the fields to the appropriate field macro.

    LCR-Media_Intelligence-Assign_Datasets_Conversions_Questions-Field_Macros_step.png

Dataset Macro Name

Brand Dataset Used

Field Macro Name

Brand Field to Assign

@partnermap

CID | RampID mapping

@cid (string)

Your CID field

@rampid (string)

Your RampID field

@conversions

Conversions

@cid (string)

Your CID field 

@gross_amt

Your gross amount field

transaction_id

Your transaction ID field

conversion_timestamp

Your conversion timestamp field

Run Reports

Depending on your arrangement with the publisher, either you or the publisher will run questions and schedule question runs to generate reports. If the publisher is handling this step, skip this section.

Where applicable, questions will have Runtime Parameters (RTPs) and/or data partition options at runtime. RTPs can be used to modify select inputs for a particular query without having to modify SQL. They enable flexibility for the end-user to adjust their analysis needs. For example, the ability to choose specific windows of time can significantly influence an analysis output. 

See the sections below for information on each question, and its runtime and partition parameters.

9. Overlap Analysis: What is the overlap between Advertiser CRM and publisher universe?

This query is useful for a business user to understand the overlap of users between you and the publisher, which can help with targeting and advertising strategies.

  • Runtime parameters: None

  • Partition parameters: None

SQL

-- What is the overlap between Advertiser CRM and publisher universe?
SELECT
'Advertiser and Publisher Overalp' as Dimension
,count(distinct @pubmap.@cid) as Overlap_Count
from @partner_crm
INNER JOIN @partnermap on @partner_crm.@cid = @partnermap.@cid
INNER JOIN @pubmap  on @pubmap.@rampid = @partnermap.@rampid
where
   @partner_crm.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
   and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0'
   and @pubmap.@rampid != 'UNMATCHED'

6. Attribute Overlap Index: Attribute Level Overlap and Index Report

This query calculates the overlap between the publisher audience and the partner CRM data sets &amp; indexes advertiser CRM attributes against publisher user segments.

  • Runtime parameters: Attribute field specified when the dataset was assigned to the question, such as “crm_attribute” (highlighted in SQL)

  • Partition parameters: None

SQL

-- Attribute level overlap and index report
SELECT
X1.ATTRIBUTE AS ATTRIBUTE, 
X1.ATTRIBUTE_VALUE AS ATTRIBUTE_VALUE, 
X1.segment_category AS AUDIENCE_SEGMENT_CATEGORY,
X1.segment_id AS segment_id,
X1.segment_name AS AUDIENCE_SEGMENT_NAME,
X1.OVERLAP AS OVERLAP, 
X2.SEGMENT_SIZE AS AUDIENCE_SEGMENT_SIZE, 
X3.ATTRIBUTE_VALUE_SIZE AS ATTRIBUTE_VALUE_SIZE, 
X4.DENOM_TOTAL AS TOTAL_SIZE, 
ROUND(TRY_DIVIDE(TRY_DIVIDE(1.0*X1.OVERLAP, 1.0*X3.ATTRIBUTE_VALUE_SIZE), TRY_DIVIDE(1.0*X2.SEGMENT_SIZE, 1.0*X4.DENOM_TOTAL)), 2) AS ATTRIBUTE_AUDIENCE_INDEX
FROM
(
SELECT
   '@CRM_ATTRIBUTE' AS ATTRIBUTE,
   @partner_crm.@crm_attribute_value AS ATTRIBUTE_VALUE,
   --'EDUCATION' AS ATTRIBUTE,
   --@partner_crm.@education AS ATTRIBUTE_VALUE,
   @publisher_audience.@segment_id AS segment_id,
   @publisher_audience.@segment_category AS segment_category,
   @publisher_audience.@segment_name AS segment_name,
   COUNT(DISTINCT @partner_crm.@cid) AS OVERLAP
FROM
   @partner_crm
   INNER JOIN @partnermap on @partner_crm.@cid = @partnermap.@cid
   INNER JOIN @pubmap on @partnermap.@rampid = @pubmap.@rampid
   INNER JOIN @publisher_audience on @publisher_audience.@cid = @pubmap.@cid
where
  @partner_crm.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
GROUP BY 1, 2, 3, 4, 5
) X1,
(
SELECT
   @publisher_audience.@segment_id  AS segment_id,
   @publisher_audience.@segment_category AS segment_category,
   @publisher_audience.@segment_name AS segment_name,
   COUNT(DISTINCT @publisher_audience.@cid) AS SEGMENT_SIZE
FROM
   @publisher_audience
   INNER JOIN @pubmap on @publisher_audience.@cid = @pubmap.@cid
 where
  @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
GROUP BY 1,2,3
) X2,
(
SELECT
   '@CRM_ATTRIBUTE' AS ATTRIBUTE,
   @partner_crm.@crm_attribute_value AS ATTRIBUTE_VALUE,
   COUNT(DISTINCT @partner_crm.@cid) AS ATTRIBUTE_VALUE_SIZE
FROM
   @partner_crm
   inner join @partnermap on @partner_crm.@cid = @partnermap.@cid
where
  @partner_crm.@cid IS NOT NULL
  and @partnermap.@rampid != 'UNKNOWN' and @partnermap.@rampid is not null and @partnermap.@rampid != '0' and @partnermap.@rampid != 'UNMATCHED'
GROUP BY 1, 2
) X3,
(
SELECT
   COUNT(DISTINCT @pubmap.@cid) AS DENOM_TOTAL
FROM
   @pubmap
where
  @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
) X4
WHERE
X1.ATTRIBUTE = X3.ATTRIBUTE
AND X1.ATTRIBUTE_VALUE = X3.ATTRIBUTE_VALUE
AND X1.SEGMENT_CATEGORY = X2.SEGMENT_CATEGORY
AND X1.SEGMENT_NAME = X2.SEGMENT_NAME
AND X1.SEGMENT_ID = X2.SEGMENT_ID

10. Reach And Frequency: What are the overall impressions, reach and frequency achieved across all advertisers and brands?

This query provides an overall analysis of impressions, reach and frequency for the total brand, by counting unique customer identifiers (CIDs) and RampIDs that were served varying numbers of impressions.

  • Runtime parameters: None

  • Partition parameters: Exposure Start and End Dates

SQL

with user_lvl_freq_cid AS
(
SELECT
@exposures.@advertiser_name as advertiser_name
,@exposures.@campaign_name as campaign_name
,@exposures.@cid as cid
,count(distinct @exposures.@event_id) as impressions_served
FROM @exposures
inner join @pubmap on @exposures.@cid = @pubmap.@cid
where
@exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
group by 1,2,3
),
user_lvl_freq_rampid AS
(
SELECT
@exposures.@advertiser_name as advertiser_name
,@exposures.@campaign_name as campaign_name
,@pubmap.@rampid as rmn_rampid
,count(distinct @exposures.@event_id) as impressions_served
FROM @exposures
inner join @pubmap on @exposures.@cid = @pubmap.@cid


where
@exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
group by 1,2,3
),
combined_dataset as
(
SELECT
'campaign level' as metric_level
,'count of cid' as frequency_type
,advertiser_name
,campaign_name
,sum(impressions_served) as impressions_served
,count(DISTINCT cid) as targeted_customers
from user_lvl_freq_cid
group by 3,4
union all
SELECT
'campaign level' as metric_level
,'count of rampid' as frequency_type
,advertiser_name
,campaign_name
,sum(impressions_served) as impressions_served
,count(DISTINCT rmn_rampid) as targeted_customers
from user_lvl_freq_rampid
group by 3,4
union all
SELECT
'advertiser level' as metric_level
,'count of cid' as frequency_type
,advertiser_name
,'all campaigns' as campaign_name
,sum(impressions_served) as impressions_served
,count(DISTINCT cid) as targeted_customers
from user_lvl_freq_cid
group by 3
union all
SELECT
'advertiser level' as metric_level
,'count of rampid' as frequency_type
,advertiser_name
,'all campaigns' as campaign_name
,sum(impressions_served) as impressions_served
,count(DISTINCT rmn_rampid) as targeted_customers
from user_lvl_freq_rampid
group by 3
union all
SELECT
'overall' as metric_level
,'count of cid' as frequency_type
,'all advertisers' as advertiser_name
,'all campaigns' as campaign_name
,sum(impressions_served) as impressions_served
,count(DISTINCT cid) as targeted_customers
from user_lvl_freq_cid


union all
SELECT
'overall' as metric_level
,'count of rampid' as frequency_type
,'all advertisers' as advertiser_name
,'all campaigns' as campaign_name
,sum(impressions_served) as impressions_served
,count(DISTINCT rmn_rampid) as targeted_customers
from user_lvl_freq_rampid


)
select
metric_level
,frequency_type
,advertiser_name
,campaign_name
,impressions_served
,targeted_customers
from combined_dataset

8. Reach Analysis: What is the reach at the advertiser and campaign level and what is the overlap percentage with CRM?

This query can be used to provide insights on the reach of your advertising campaigns and how many of those users are already in your CRM system. This information can help you make informed decisions about your advertising strategies and target your campaigns more effectively.

  • Runtime parameters: Attribute field specified when the dataset was assigned to the question, such as “crm_attribute” (highlighted in SQL)

  • Partition parameters: Exposure Start and End Dates

SQL

-- What is the reach at advertiser and campaign level and what is the overlap percentage with CRM?
with campaign as
(
SELECT
advertiser_name
,campaign_name
,count(DISTINCT pub.cid) as users_served_impressions
,count(DISTINCT part.cid) as overlap_with_adv_crm
,COUNT(DISTINCT pub.cid) - COUNT(DISTINCT part.cid) as users_not_known_to_adv
FROM
   (select distinct @exposures.@advertiser_name as advertiser_name
       , @exposures.@campaign_name as campaign_name
       , @exposures.@cid as cid
       , @pubmap.@rampid as rampid
   from @exposures
   INNER JOIN @pubmap
   on @exposures.@cid = @pubmap.@cid) pub
LEFT JOIN
   (select distinct @partnermap.@cid as cid
       , @partnermap.@rampid as rampid
   from @partnermap 
   INNER JOIN @partner_crm
   on @partnermap.@cid = @partner_crm.@cid) part
on pub.rampid = part.rampid
where
pub.cid IS NOT NULL
and pub.rampid != 'UNKNOWN' and pub.rampid is not null and pub.rampid != '0' and pub.rampid != 'UNMATCHED'
group by 1,2
),
advertiser as
(
SELECT
advertiser_name
,count(DISTINCT pub.cid) as users_served_impressions
,count(DISTINCT part.cid) as overlap_with_adv_crm
,COUNT(DISTINCT pub.cid) - COUNT(DISTINCT part.cid) as users_not_known_to_adv
FROM
   (select distinct @exposures.@advertiser_name as advertiser_name
       , @exposures.@campaign_name as campaign_name
       , @exposures.@cid as cid
       , @pubmap.@rampid as rampid
   from @exposures
   INNER JOIN @pubmap 
   on @exposures.@cid = @pubmap.@cid) pub
LEFT JOIN
   (select distinct @partnermap.@cid as cid
       , @partnermap.@rampid as rampid
   from @partnermap 
   INNER JOIN @partner_crm 
   on @partnermap.@cid = @partner_crm.@cid) part
on pub.rampid = part.rampid
where
pub.cid IS NOT NULL
and pub.rampid != 'UNKNOWN' and pub.rampid is not null and pub.rampid != '0' and pub.rampid != 'UNMATCHED'
group by 1
),
overall as
(
SELECT
count(DISTINCT pub.cid) as users_served_impressions
,count(DISTINCT part.cid) as overlap_with_adv_crm
,COUNT(DISTINCT pub.cid) - COUNT(DISTINCT part.cid) as users_not_known_to_adv
FROM
   (select distinct @exposures.@advertiser_name as advertiser_name
       , @exposures.@campaign_name as campaign_name
       , @exposures.@cid as cid
       , @pubmap.@rampid as rampid
   from @exposures
   INNER JOIN @pubmap 
   on @exposures.@cid = @pubmap.@cid) pub
LEFT JOIN
   (select distinct @partnermap.@cid as cid
       , @partnermap.@rampid as rampid
   from @partnermap 
   INNER JOIN @partner_crm
   on @partnermap.@cid = @partner_crm.@cid) part
on pub.rampid = part.rampid
where
pub.cid IS NOT NULL
and pub.rampid != 'UNKNOWN' and pub.rampid is not null and pub.rampid != '0' and pub.rampid != 'UNMATCHED'
),
combined as
(
select
'campaign level' as metric_level
,advertiser_name
,campaign_name
,users_served_impressions
,overlap_with_adv_crm
,users_not_known_to_adv
from campaign
union all
select
'advertiser level' as metric_level
,advertiser_name
,'all campaigns' as campaign_name
,users_served_impressions
,overlap_with_adv_crm
,users_not_known_to_adv
from advertiser
union all
select
'overall' as metric_level
,'all advertisers' as advertiser_name
,'all campaigns' as campaign_name
,users_served_impressions
,overlap_with_adv_crm
,users_not_known_to_adv
from overall
)
select 
metric_level as Granularity
,advertiser_name
,campaign_name
,users_served_impressions as reach
,overlap_with_adv_crm as reach_overlapping_with_crm
,users_not_known_to_adv as reach_not_in_crm
from combined

7. Reach Analysis: What was the unique reach achieved by the advertiser and at what frequency was the media served?

This query provides a detailed analysis of user exposure frequency across different levels—campaign, advertiser, and overall—by counting unique customer identifiers (CIDs) and RampIDs who were served varying numbers of impressions. The results categorize these users into different frequency buckets, enabling you to understand the distribution of ad exposure among targeted customers and identify potential areas for optimizing ad frequency to enhance campaign effectiveness.

  • Runtime parameters: None

  • Partition parameters: Exposure Start and End Dates

SQL

-- What was the unique reach achieved by the advertiser and at what frequency was the media served?
with user_lvl_freq_cid AS
(
SELECT
@exposures.@advertiser_name as advertiser_name
,@exposures.@campaign_name as campaign_name
,@exposures.@cid as cid
,count(distinct @exposures.@event_id) as impressions_served
FROM @exposures
inner join @pubmap on @exposures.@cid = @pubmap.@cid
where
@exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
group by 1,2,3
),
user_lvl_freq_rampid AS
(
SELECT
@exposures.@advertiser_name as advertiser_name
,@exposures.@campaign_name as campaign_name
,@pubmap.@rampid as rmn_rampid
,count(distinct @exposures.@event_id) as impressions_served
FROM @exposures
inner join @pubmap on @exposures.@cid = @pubmap.@cid


where
@exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
group by 1,2,3
),
combined_dataset as
(
SELECT
'campaign level' as metric_level
,'count of cid' as frequency_type
,advertiser_name
,campaign_name
,CASE WHEN impressions_served>10 then '10+'
when impressions_served between 6 and 10 then '06-10'
else concat('0',impressions_served) end as exposure_frequency
,count(DISTINCT cid) as targeted_customers
from user_lvl_freq_cid
group by 3,4,5
union all
SELECT
'campaign level' as metric_level
,'count of rampid' as frequency_type
,advertiser_name
,campaign_name
,CASE WHEN impressions_served>10 then '10+'
when impressions_served between 6 and 10 then '06-10'
else concat('0',impressions_served) end as exposure_frequency
,count(DISTINCT rmn_rampid) as targeted_customers
from user_lvl_freq_rampid
group by 3,4,5
union all
SELECT
'advertiser level' as metric_level
,'count of cid' as frequency_type
,advertiser_name
,'all campaigns' as campaign_name
,CASE WHEN impressions_served>10 then '10+'
when impressions_served between 6 and 10 then '06-10'
else concat('0',impressions_served) end as exposure_frequency
,count(DISTINCT cid) as targeted_customers
from user_lvl_freq_cid
group by 3,5
union all
SELECT
'advertiser level' as metric_level
,'count of rampid' as frequency_type
,advertiser_name
,'all campaigns' as campaign_name
,CASE WHEN impressions_served>10 then '10+'
when impressions_served between 6 and 10 then '06-10'
else concat('0',impressions_served) end as exposure_frequency
,count(DISTINCT rmn_rampid) as targeted_customers
from user_lvl_freq_rampid
group by 3,5
union all
SELECT
'overall' as metric_level
,'count of cid' as frequency_type
,'all advertisers' as advertiser_name
,'all campaigns' as campaign_name
,CASE WHEN impressions_served>10 then '10+'
when impressions_served between 6 and 10 then '06-10'
else concat('0',impressions_served) end as exposure_frequency
,count(DISTINCT cid) as targeted_customers
from user_lvl_freq_cid
group by 5
union all
SELECT
'overall' as metric_level
,'count of rampid' as frequency_type
,'all advertisers' as advertiser_name
,'all campaigns' as campaign_name
,CASE WHEN impressions_served>10 then '10+'
when impressions_served between 6 and 10 then '06-10'
else concat('0',impressions_served) end as exposure_frequency
,count(DISTINCT rmn_rampid) as targeted_customers
from user_lvl_freq_rampid
group by 5
)
select
metric_level
,frequency_type
,advertiser_name
,campaign_name
,exposure_frequency
,targeted_customers
from combined_dataset

5. Frequency Analysis: What is the optimal frequency to deliver my campaigns?

This query provides insights into the performance and effectiveness of advertising campaigns and helps you identify the optimal frequency to deliver campaigns. By segmenting by frequency of user exposure to ads, analyzing metrics such as the number of users reached, conversions, gross revenue, and the impression volume across different frequency buckets, you can refine targeting strategies and optimize ad spend. This query applies last touch attribution using a custom attribution window.

  • Runtime parameters (highlighted in SQL):

    • click_attribution_window (Integer)

    • imp_attribution_window (Integer)

  • Partition parameters:

    • Exposure Start and End Dates

    • Conversion Start and End Dates

SQL

--  What is the optimal frequency to deliver my campaigns?
with traffic AS (
 SELECT
   @exposures.@cid as pub_cid,
   @pubmap.@rampid as pub_rampid,
   @exposures.@advertiser_name as advertiser_name,
   @exposures.@campaign_name as campaign_name,
   @exposures.@event_type as event_type,
   unix_timestamp(@exposures.@event_timestamp) AS event_timestamp_UNIX,
   @exposures.@event_id as event_id
 FROM @exposures
 join @pubmap on @exposures.@cid = @pubmap.@cid
 where
  @exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'),
conversion AS (
 SELECT
   a.transaction_id,
   a.gross_amt,
   a.partner_cid as partner_cid,
   @partnermap.@rampid as partner_rampid,
   a.conversion_timestamp_UNIX as conversion_timestamp_UNIX
 FROM
   (select @conversions.@transaction_id as transaction_id, @conversions.@cid as partner_cid,
     unix_timestamp(@conversions.@conversion_timestamp) as conversion_timestamp_UNIX,
     sum(@conversions.@gross_amt) as gross_amt from @conversions group by 1,2,3) a
   join @partnermap on a.partner_cid = @partnermap.@cid
where
  a.partner_cid IS NOT NULL and @partnermap.@cid IS NOT NULL
  and @partnermap.@rampid != 'UNKNOWN' and @partnermap.@rampid is not null and @partnermap.@rampid != '0' and @partnermap.@rampid != 'UNMATCHED'),
matched_latency AS (
 SELECT
   c.transaction_id,
   t.event_id,
   t.pub_cid,
   t.advertiser_name,
   t.campaign_name,
   (case when t.event_type = 'click' then 1 else 2 end) AS match_type,
   (conversion_timestamp_UNIX - event_timestamp_UNIX) / 86400 AS match_age,
   max(c.gross_amt) as gross_amt
 FROM
   conversion c
   JOIN traffic t ON c.partner_rampid = t.pub_rampid
 group by 1,2,3,4,5,6,7),


first_ranked AS (
SELECT
 advertiser_name,
 campaign_name,
 ---transaction_id,
 event_id,
 ROW_NUMBER() OVER(
   PARTITION BY transaction_id
   ORDER BY match_age DESC, match_type DESC) AS match_rank,
 gross_amt,
 pub_cid
FROM
 matched_latency
WHERE
   (match_type=1 AND match_age BETWEEN 0 AND @click_attribution_window)
 OR (match_type=2 AND match_age BETWEEN 0 AND @imp_attribution_window)
),


last_ranked AS (
 SELECT
   advertiser_name,
   campaign_name,
   transaction_id,
   event_id,
   ROW_NUMBER() OVER(
     PARTITION BY transaction_id
     ORDER BY
       match_age ASC, -- last touch
       match_type ASC
   ) AS match_rank,
   gross_amt,
   pub_cid
 FROM
   matched_latency
 WHERE
   (match_type=1 and match_age BETWEEN 0 AND @click_attribution_window)
   OR (match_type=2 and match_age BETWEEN 0 AND @imp_attribution_window)
),
attributed_conv as (
 SELECT
   advertiser_name,
   campaign_name,
   pub_cid,
   count(distinct transaction_id) as conversions,
   sum(gross_amt) as gross_amt
 from last_ranked
 WHERE match_rank = 1
 group by 1,2,3
),
impression_users as (
 select
   @exposures.@cid as pub_cid,
   @exposures.@advertiser_name as advertiser_name,
   @exposures.@campaign_name as campaign_name,
   count(distinct @exposures.@event_id) as uc_imps
 FROM @exposures
 left join @pubmap on @pubmap.@cid = @exposures.@cid
 where
  @exposures.@cid IS NOT NULL
 and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'
 group by 1,2,3
),
impression_frequency as (
 select pub_cid, advertiser_name, campaign_name,
   case
     when uc_imps = 1 then '01'
     when uc_imps = 2 then '02'
     when uc_imps = 3 then '03'
     when uc_imps = 4 then '04'
     when uc_imps = 5 then '05'
     when uc_imps = 6 then '06'
     when uc_imps = 7 then '07'
     when uc_imps = 8 then '08'
     when uc_imps = 9 then '09'
     when uc_imps = 10 then '10'
     when uc_imps = 11 then '11'
     when uc_imps = 12 then '12'
     when uc_imps = 13 then '13'
     when uc_imps = 14 then '14'
     when uc_imps = 15 then '15'
     when uc_imps = 16 then '16'
     when uc_imps = 17 then '17'
     when uc_imps = 18 then '18'
     when uc_imps = 19 then '19'
     when uc_imps >= 20 and uc_imps <= 29 then '20-29'
     when uc_imps >= 30 and uc_imps <= 39 then '30-39'
     when uc_imps >= 40 and uc_imps <= 49 then '40-49'
     when uc_imps >= 50 and uc_imps <= 59 then '50-59'
     when uc_imps >= 60 and uc_imps <= 69 then '60-69'
     when uc_imps >= 70 and uc_imps <= 79 then '70-79'
     when uc_imps >= 80 and uc_imps <= 89 then '80-89'
     when uc_imps >= 90 and uc_imps <= 99 then '90-99'
     else 'Above 100' end as frequency_buckets,
     uc_imps
   from impression_users
)
select
 i.advertiser_name,
 i.campaign_name,
 i.frequency_buckets,
 count(distinct i.pub_cid) as imp_users,
 coalesce(count(distinct c.pub_cid), 0) as con_users,
 coalesce(SUM(c.conversions), 0) as conversions,
 coalesce(sum(c.gross_amt),0) as gross_amt,
 sum(i.uc_imps) as impressions
from impression_frequency i
left join attributed_conv c
USING (campaign_name, advertiser_name, pub_cid)
group by 1,2,3

4. Latency Analysis: How long does it take for a user to convert after first touch and last touch?

This query analyzes the impact of different touchpoints in a marketing campaign by determining the time it takes for users to convert after their first or last interaction with the advertiser. By segmenting users based on the days until conversion and associating it with either the first or last touch, you can gain insights into the effectiveness of your marketing strategy over time, optimizing campaigns for better performance.

  • Runtime parameters (highlighted in SQL):

    • click_attribution_window (Integer)

    • imp_attribution_window (Integer)

  • Partition parameters:

    • Exposure Start and End Dates

    • Conversion Start and End Dates

SQL

-- How long does it take for a user to convert after first touch and last touch?


with traffic AS (
 SELECT
   @exposures.@cid as pub_cid,
   @pubmap.@rampid as pub_rampid,
   @exposures.@advertiser_name as advertiser_name,
   @exposures.@campaign_name as campaign_name,
   @exposures.@event_type as event_type,
   unix_timestamp(@exposures.@event_timestamp) AS event_timestamp_UNIX,
   @exposures.@event_id as event_id
 FROM @exposures
 join @pubmap on @exposures.@cid = @pubmap.@cid
 where
  @exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'),
conversion AS (
 SELECT
   a.transaction_id,
   a.gross_amt,
   a.partner_cid as partner_cid,
   @partnermap.@rampid as partner_rampid,
   a.conversion_timestamp_UNIX as conversion_timestamp_UNIX
 FROM
   (select @conversions.@transaction_id as transaction_id, @conversions.@cid as partner_cid,
   unix_timestamp(@conversions.@conversion_timestamp) as conversion_timestamp_UNIX, sum(@conversions.@gross_amt) as gross_amt from @conversions group by 1,2,3) a
   join @partnermap  on a.partner_cid = @partnermap.@cid
 where
  a.partner_cid IS NOT NULL and @partnermap.@cid IS NOT NULL
  and @partnermap.@rampid != 'UNKNOWN' and @partnermap.@rampid is not null and @partnermap.@rampid != '0' and @partnermap.@rampid != 'UNMATCHED'),
matched_latency AS (
 SELECT
   c.transaction_id,
   t.event_id,
   t.pub_cid,
   t.advertiser_name,
   t.campaign_name,
   (case when t.event_type = 'click' then 1 else 2 end) AS match_type,
   (conversion_timestamp_UNIX - event_timestamp_UNIX) / 86400 AS match_age,
   max(c.gross_amt) as gross_amt
 FROM
   conversion c
   JOIN traffic t ON c.partner_rampid = t.pub_rampid
 group by 1,2,3,4,5,6,7),
first_ranked AS (
 SELECT
   advertiser_name,
   campaign_name,
   transaction_id,
   ROW_NUMBER() OVER(
     PARTITION BY transaction_id
     ORDER BY
       match_age DESC, -- first touch
       match_type ASC
   ) AS match_rank,
   match_age,
   match_type,
   gross_amt,
   pub_cid
 FROM
   matched_latency
 WHERE
   (match_type=1 and match_age BETWEEN 0 AND @click_attribution_window)
   OR (match_type=2 and match_age BETWEEN 0 AND @imp_attribution_window)
),
last_ranked AS (
 SELECT
   advertiser_name,
   campaign_name,
   transaction_id,
   ROW_NUMBER() OVER(
     PARTITION BY transaction_id
     ORDER BY
       match_age ASC, -- last touch
       match_type ASC
   ) AS match_rank,
   match_age,
   match_type,
   gross_amt,
   pub_cid
 FROM
   matched_latency
 WHERE
   (match_type=1 and match_age BETWEEN 0 AND @click_attribution_window)
   OR (match_type=2 and match_age BETWEEN 0 AND @imp_attribution_window)
),
final_ranked as (
(Select
   advertiser_name,
   campaign_name,
( CASE
     WHEN match_age > 0 AND match_age <= 1 THEN '01 | < 1 DAY'
     WHEN match_age <= 2 THEN '02 | 1 - 2 DAYS'
     WHEN match_age <= 3 THEN '03 | 2 - 3 DAYS'
     WHEN match_age <= 4 THEN '04 | 3 - 4 DAYS'
     WHEN match_age <= 5 THEN '05 | 4 - 5 DAYS'
     WHEN match_age <= 6 THEN '06 | 5 - 6 DAYS'
     WHEN match_age <= 7 THEN '07 | 6 - 7 DAYS'
     WHEN match_age <= 8 THEN '08 | 7 - 8 DAYS'
     WHEN match_age <= 9 THEN '09 | 8 - 9 DAYS'
     WHEN match_age <= 10 THEN '10 | 9 - 10 DAYS'
     WHEN match_age <= 11 THEN '11 | 10 - 11 DAYS'
     WHEN match_age <= 12 THEN '12 | 11 - 12 DAYS'
     WHEN match_age <= 13 THEN '13 | 12 - 13 DAYS'
     WHEN match_age <= 14 THEN '14 | 13 - 14 DAYS'
     WHEN match_age <= 20 THEN '15 | 15 - 20 DAYS'
     WHEN match_age <= 25 THEN '16 | 21 - 25 DAYS'
     WHEN match_age <= 30 THEN '17 | 26 - 30 DAYS'
     ELSE '18 | 30+ DAYS'
   END
 ) AS time_to_conversion,
 'FT - First Touch' as touch_type,
   SUM(gross_amt) as gross_amt,
   COUNT(DISTINCT pub_cid) as converted_users
from first_ranked
WHERE match_rank = 1
GROUP BY 1,2,3)
UNION ALL
(Select
   advertiser_name,
   campaign_name,
( CASE
     WHEN match_age > 0 AND match_age <= 1 THEN '01 | < 1 DAY'
     WHEN match_age <= 2 THEN '02 | 1 - 2 DAYS'
     WHEN match_age <= 3 THEN '03 | 2 - 3 DAYS'
     WHEN match_age <= 4 THEN '04 | 3 - 4 DAYS'
     WHEN match_age <= 5 THEN '05 | 4 - 5 DAYS'
     WHEN match_age <= 6 THEN '06 | 5 - 6 DAYS'
     WHEN match_age <= 7 THEN '07 | 6 - 7 DAYS'
     WHEN match_age <= 8 THEN '08 | 7 - 8 DAYS'
     WHEN match_age <= 9 THEN '09 | 8 - 9 DAYS'
     WHEN match_age <= 10 THEN '10 | 9 - 10 DAYS'
     WHEN match_age <= 11 THEN '11 | 10 - 11 DAYS'
     WHEN match_age <= 12 THEN '12 | 11 - 12 DAYS'
     WHEN match_age <= 13 THEN '13 | 12 - 13 DAYS'
     WHEN match_age <= 14 THEN '14 | 13 - 14 DAYS'
     WHEN match_age <= 20 THEN '15 | 15 - 20 DAYS'
     WHEN match_age <= 25 THEN '16 | 21 - 25 DAYS'
     WHEN match_age <= 30 THEN '17 | 26 - 30 DAYS'
     ELSE '18 | 30+ DAYS'
   END
 ) AS time_to_conversion,
 'LT - Last Touch' as touch_type,
   SUM(gross_amt) as gross_amt,
   COUNT(DISTINCT pub_cid) as converted_users
from last_ranked
WHERE match_rank = 1
GROUP BY 1,2,3)
ORDER BY 1,2,3
)
Select
advertiser_name,
campaign_name,
time_to_conversion,
touch_type,
sum(gross_amt) as gross_amt,
sum(converted_users) as converted_users
from final_ranked
group by 1,2,3,4

3. Linear Attribution: What is the attributed revenue of my campaign based on linear attribution with custom attribution window?

This query applies a linear attribution model, where credit for a conversion is distributed equally across all touchpoints associated with a transaction. By calculating the attributed revenue and conversions for each campaign, this approach provides a balanced view of how different marketing efforts contribute to conversions, helping you make informed decisions about resource allocation across campaigns.

  • Runtime parameters (highlighted in SQL):

    • click_attribution_window (Integer)

    • imp_attribution_window (Integer)

  • Partition parameters:

    • Exposure Start and End Dates

    • Conversion Start and End Dates

SQL

-- What is the attributed revenue of my campaign based on linear attribution with custom attribution window?
with traffic AS (
 SELECT
   @exposures.@cid as pub_cid,
   @pubmap.@rampid as pub_rampid,
   @exposures.@advertiser_name as advertiser_name,
   @exposures.@campaign_name as campaign_name,
   @exposures.@event_type as event_type,
   unix_timestamp(@exposures.@event_timestamp) AS event_timestamp_UNIX
   ---@exposures.@event_id
 FROM @exposures
 join @pubmap on @exposures.@cid = @pubmap.@cid
 where
  @exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'),
conversion AS (
 SELECT
   a.transaction_id,
   a.gross_amt,
   a.partner_cid as partner_cid,
   @partnermap.@rampid as partner_rampid,
   a.conversion_timestamp_UNIX as conversion_timestamp_UNIX
 FROM
   (select @conversions.@transaction_id as transaction_id, @conversions.@cid as partner_cid,
   unix_timestamp(@conversions.@conversion_timestamp) as conversion_timestamp_UNIX,
   sum(@conversions.@gross_amt) as gross_amt from @conversions group by 1,2,3) a
   join @partnermap on a.partner_cid = @partnermap.@cid
 where
  a.partner_cid IS NOT NULL and @partnermap.@cid IS NOT NULL
  and @partnermap.@rampid != 'UNKNOWN' and @partnermap.@rampid is not null and @partnermap.@rampid != '0' and @partnermap.@rampid != 'UNMATCHED'),
matched_latency AS (
 SELECT
   c.transaction_id,
   ---t.event_id,
   t.pub_cid,
   t.advertiser_name,
   t.campaign_name,
   (case when t.event_type = 'click' then 1 else 2 end) AS match_type,
   (conversion_timestamp_UNIX - event_timestamp_UNIX) / 86400 AS match_age,
   max(c.gross_amt) as gross_amt
 FROM
   conversion c
   JOIN traffic t ON c.partner_rampid = t.pub_rampid
 group by 1,2,3,4,5,6),
linear as (
 SELECT
   advertiser_name,
   campaign_name,
   transaction_id,
   pub_cid,
   gross_amt / count(campaign_name) over (partition by transaction_id) as attributed_amt,
   1 / count(campaign_name) over (partition by transaction_id) as attributed_conv
 from matched_latency
 WHERE
   (match_type=1 and match_age BETWEEN 0 AND @click_attribution_window)
   OR (match_type=2 and match_age BETWEEN 0 AND @imp_attribution_window)
)
Select
  advertiser_name,
  campaign_name,
   'Linear' as attribution_model,
   SUM(attributed_amt) as attributed_gross_amt,
   sum(attributed_conv) as attributed_conversions
from linear
GROUP BY 1,2,3

2. Last Touch Attribution: What is the attributed revenue of my campaign based on last touch with the custom attribution window?

This query implements a last-touch attribution model, where 100% of the credit for a conversion is given to the last interaction the user had before converting. By aggregating the gross amount, conversion count, and unique converters for each campaign, this query provides insights into the effectiveness of the final touchpoint in driving conversions, helping you optimize your strategies based on the last interaction before a sale.

  • Runtime parameters (highlighted in SQL):

    • click_attribution_window (Integer)

    • imp_attribution_window (Integer)

  • Partition parameters:

    • Exposure Start and End Dates

    • Conversion Start and End Dates

SQL

-- What is the attributed revenue of my campaign based on last touch with the custom attribution window?
with traffic AS (
 SELECT
   @exposures.@cid as pub_cid,
   @pubmap.@rampid as pub_rampid,
   @exposures.@advertiser_name as advertiser_name,
   @exposures.@campaign_name as campaign_name,
   @exposures.@event_type as event_type,
   unix_timestamp(@exposures.@event_timestamp) AS event_timestamp_UNIX,
   @exposures.@event_id as event_id
 FROM @exposures
 join @pubmap on @exposures.@cid = @pubmap.@cid
 where
  @exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'),
conversion AS (
 SELECT
   a.transaction_id,
   a.gross_amt,
   a.partner_cid as partner_cid,
   @partnermap.@rampid as partner_rampid,
   a.conversion_timestamp_UNIX as conversion_timestamp_UNIX
 FROM
   (select @conversions.@transaction_id as transaction_id, @conversions.@cid as partner_cid,
   unix_timestamp(@conversions.@conversion_timestamp) as conversion_timestamp_UNIX,
   sum(@conversions.@gross_amt) as gross_amt from @conversions group by 1,2,3) a
   join @partnermap on a.partner_cid = @partnermap.@cid
 where
  a.partner_cid IS NOT NULL and @partnermap.@cid IS NOT NULL
  and @partnermap.@rampid != 'UNKNOWN' and @partnermap.@rampid is not null and @partnermap.@rampid != '0' and @partnermap.@rampid != 'UNMATCHED'),
matched_latency AS (
 SELECT
   c.transaction_id,
   t.event_id,
   t.pub_cid,
   t.advertiser_name,
   t.campaign_name,
   (case when t.event_type = 'click' then 1 else 2 end) AS match_type,
   (conversion_timestamp_UNIX - event_timestamp_UNIX) / 86400 AS match_age,
   max(c.gross_amt) as gross_amt
 FROM
   conversion c
   JOIN traffic t ON c.partner_rampid = t.pub_rampid
 group by 1,2,3,4,5,6,7),
last_ranked AS (
 SELECT
   advertiser_name,
   campaign_name,
   transaction_id,
   --impression_id,
   ROW_NUMBER() OVER(
     PARTITION BY transaction_id
     ORDER BY
       match_age ASC, -- last touch
       match_type ASC
   ) AS match_rank,
   gross_amt,
   pub_cid
 FROM
   matched_latency
 WHERE
   (match_type=1 and match_age BETWEEN 0 AND @click_attribution_window)
   OR (match_type=2 and match_age BETWEEN 0 AND @imp_attribution_window)
)
Select
   advertiser_name,
   campaign_name,
   'Last Touch' as attribution_model,
   SUM(gross_amt) as attributed_gross_amt,
   count(distinct transaction_id) as attributed_conversions
from last_ranked
WHERE match_rank = 1
GROUP BY 1,2,3

1. First Touch Attribution: What is the attributed revenue of my campaign based on first touch with the custom attribution window?

This query applies a first-touch attribution model, where the entire credit for a conversion is assigned to the first interaction a user had with the campaign. By summing the gross amount, conversion count, and unique converters for each campaign, this analysis helps you understand the impact of the initial touchpoint in driving conversions, allowing for strategic optimizations to attract and engage potential customers right from their first interaction.

  • Runtime parameters (highlighted in SQL):

    • click_attribution_window (Integer)

    • imp_attribution_window (Integer)

  • Partition parameters:

    • Exposure Start and End Dates

    • Conversion Start and End Dates

SQL

-- What is the attributed revenue of my campaign based on first touch with the custom attribution window?
with traffic AS (
 SELECT
   @exposures.@cid as pub_cid,
   @pubmap.@rampid as pub_rampid,
   @exposures.@advertiser_name as advertiser_name,
   @exposures.@campaign_name as campaign_name,
   @exposures.@event_type as event_type,
   unix_timestamp(@exposures.@event_timestamp) as event_timestamp_UNIX,
   @exposures.@event_id as event_id
 FROM @exposures
 join @pubmap on @exposures.@cid = @pubmap.@cid
 where
  @exposures.@cid IS NOT NULL and @pubmap.@cid IS NOT NULL
  and @pubmap.@rampid != 'UNKNOWN' and @pubmap.@rampid is not null and @pubmap.@rampid != '0' and @pubmap.@rampid != 'UNMATCHED'),
conversion AS (
 SELECT
   a.transaction_id,
   a.gross_amt,
   a.partner_cid as partner_cid,
   @partnermap.@rampid as partner_rampid,
   a.conversion_timestamp_UNIX as conversion_timestamp_UNIX
 FROM
   (select @conversions.@transaction_id as transaction_id, @conversions.@cid as partner_cid,
     unix_timestamp(@conversions.@conversion_timestamp) as conversion_timestamp_UNIX,
     sum(@conversions.@gross_amt) as gross_amt from @conversions group by 1,2,3) a
   join @partnermap on a.partner_cid = @partnermap.@cid
 where
  a.partner_cid IS NOT NULL and @partnermap.@cid IS NOT NULL
  and @partnermap.@rampid != 'UNKNOWN' and @partnermap.@rampid is not null and @partnermap.@rampid != '0' and @partnermap.@rampid != 'UNMATCHED'),
matched_latency AS (
 SELECT
   c.transaction_id,
   t.event_id,
   t.pub_cid,
   t.advertiser_name,
   t.campaign_name,
   (case when t.event_type = 'click' then 1 else 2 end) AS match_type,
   (conversion_timestamp_UNIX - event_timestamp_UNIX) / 86400 AS match_age,
   max(c.gross_amt) as gross_amt
 FROM
   conversion c
   JOIN traffic t ON c.partner_rampid = t.pub_rampid
 group by 1,2,3,4,5,6,7),
first_ranked AS (
 SELECT
   advertiser_name,
   campaign_name,
   transaction_id,
   ROW_NUMBER() OVER(
     PARTITION BY transaction_id
     ORDER BY
       match_age DESC, -- first touch
       match_type ASC
   ) AS match_rank,
   match_age,
   match_type,
   gross_amt,
   pub_cid
 FROM
   matched_latency
 WHERE
   (match_type=1 and match_age BETWEEN 0 AND @click_attribution_window)
   OR (match_type=2 and match_age BETWEEN 0 AND @imp_attribution_window)
)
Select
   advertiser_name,
   campaign_name,
   'First Touch' as attribution_model,
   SUM(gross_amt) as attributed_gross_amt,
   count(distinct transaction_id) as attributed_conversions
from first_ranked
WHERE match_rank = 1
GROUP BY 1,2,3

Media Intelligence FAQs

Which publishers participate in Media Intelligence?

As of the time of publication, we have the following publishers integrated:

  • DirecTV

  • LG Ad Solutions

  • Nextdoor

  • Roku*

  • SamsungAds

  • SiriusXM / Pandora

  • Snap*

  • TelevisaUnivision*

  • Spectrum

*DirecTV, Roku, Snap, and TelevisaUnivision require Master Agreements between brand and publisher.

For information on these and other publisher integrations that might be available, contact your LiveRamp representative.

Can you share publisher taxonomies (that way I can prepare without needing to buy a clean room)?

Yes, publishers typically follow the schemas outlined in "Getting Started with LiveRamp Clean Room for Publishers".

What insights are typically included in Media Intelligence?

  • Audience Overlaps/Indices

  • Optimal Reach/Frequency

  • Attribution (First, Last, and Linear)

The list of insights might vary by publisher. Your LiveRamp account representative can provide more information during setup.

For more information on the available insights, see the “Run Reports” section above.

Does your integration with publishers include my spend data for ROAS?

Yes, LiveRamp can support this, but whether spend data is included depends on where the request originates:

  • Matching spend data to publisher inventory can be complex, particularly for Added Value placements and other environments where impression-based buying does not always directly align with spend allocation.

  • Brands often need to manually align media cost data with publisher-reported conversions to calculate Return on Ad Spend (ROAS) effectively.

If ROAS measurement is a priority, it’s critical to discuss spend data availability early in the integration process to ensure alignment between brand and publisher data sources.

Does each Media Intelligence publisher require a 12-month term / contract with LiveRamp? What if we only need to understand publisher overlaps or measurement one-time/short-term?

Yes, a 12-month term is required. 

Why wasn’t Total Reach included as a standard metric?

Totaal Rreach requires publishers to share their complete data to calculate % reach, which many are not ready to disclose initially. However, it's a metric we consider adding to our predefined insights in the future.

For last-touch attribution, is it based on clicks, exposures, or something else? Can we choose, or is it predetermined?

Clicks and impressions currently. Clicks are given higher priority than impressions, and you can choose the attribution window. You can also easily take the queries and customize them for your needs. 

Are there any min exposure/impression thresholds to be aware of?

There is output privacy control in the form of k-min enforcement that the publisher decides.

Do we need to be a LiveRamp customer for clean room collaboration with publishers offering Media Intelligence?

No. You will need to sign the Master Agreement for most publishers integrated with Media Intelligence. 

Publishers who require a separate Master Agreement to be signed are DirecTV, Roku, Snap, and TelevisaUnivision.

Can we swap Media Intelligence publishers in and out during the term of their contract?

Yes, the contract covers all integrated publishers with no connection limit, allowing you to update your connections as needed.

Does the Media Intelligence offering provide individual and household level reporting?

Reporting depends on the data availability from the data owner. This is determined on a case-by-case basis. 

What audiences are available for each publisher?

The audiences can be brand CRM and/or licensed 3PD, or audiences supplied by the publishers. This is case by case and defined during the scoping process.

If activation is not included for Media Intelligence clean rooms, how should we leverage overlap insights?

Audience Overlap insights can provide strategic value to brands by offering a deeper understanding of shared audiences between their CRM data and publisher partners. When activation is not included in the Media Intelligence scope, you can still harness these insights for:

  • Media Planning and Investment Decisions: Determine which publishers or platforms to prioritize based on audience overlap, ensuring efficient allocation of media budgets.

  • Audience Strategy Optimization: Tailor marketing strategies by leveraging insights into shared audiences' characteristics, enabling more precise segmentation and personalized messaging.

  • Cross-Publisher Collaboration: Leverage overlap data to coordinate campaigns across publishers, ensuring consistent reach to key audience segments.

  • Benchmarking and Performance Measurement: Use overlap insights to benchmark audience reach and engagement before and after campaign execution to measure success.

  • Strategic Audience Development: Identify potential untapped audience segments by analyzing gaps in overlaps, enabling brands to expand into new opportunities effectively.

By utilizing these insights, you can maximize the value of Media Intelligence even without direct activation, driving more strategic and informed decision-making in your marketing and media planning efforts.

What cuts of Reach and Frequency are available as part of the standard queries/offering (campaign, creative, audience, etc.)?

This is publisher dependent and will be clarified as part of the scoping process.

Is there any restriction on the types of conversions that can be leveraged (sign ups, page views, transactions, app download, etc)?

No.

How do CAPIs compare to Media Intelligence within LiveRamp’s Clean Room?

CAPIs provide in-flight optimizations by sharing conversion data with individual platform partners for packaged, platform-provided reporting. These tools are ideal for real-time adjustments but often involve separate UIs and methodologies across different partners, making reporting fragmented.

Quick Start Insights, offered via LiveRamp Clean Room, solve these challenges by providing standardized, pre-configured queries that deliver actionable insights across all integrated partners in one place. These insights include audience overlaps, shopper dynamics, and optimal frequency analysis, among others. Unlike CAPIs, Quick Start Insights simplify collaboration, standardize outputs, and reduce the need for extensive technical resources, enabling faster and more effective data-driven decisions. They also serve as the foundational starting point for clean room collaboration, with the ability to customize beyond, as the brand and publisher relationship develops.

What is the value of Media Intelligence for last touch attribution if I’m leveraging CAPIs today?

CAPIs provide in-flight optimizations by sharing conversion data with individual platform partners for packaged, platform-provided reporting. These tools are ideal for real-time adjustments but often involve separate UIs and methodologies across different partners, making reporting fragmented.

Quick Start Insights, offered via LiveRamp Clean Room, solve these challenges by providing standardized, pre-configured queries that deliver actionable insights across all integrated partners in one place. These insights include audience overlaps, shopper dynamics, and optimal frequency analysis, among others. Unlike CAPIs, Quick Start Insights simplify collaboration, standardize outputs, and reduce the need for extensive technical resources, enabling faster and more effective data-driven decisions. They also serve as the foundational starting point for clean room collaboration, with the ability to customize beyond, as the brand and publisher relationship develops.

CAPIs are going to allow your optimization in-platform at the pub. Think about CAPIs as letting the publisher’s machine optimize inside of your campaign. It's pretty inflexible because each publisher builds their own the way it serves them individually. These kinds of deep dives for how its working for YOUR business, allows you to better match to outcome metrics because you have transparency into the metrics. We find customers answering business questions for themselves whereas CAPIs are a machine learning algorithm that is helping to really just optimize campaigns.

Why should I start with Media Intelligence (and 1:1 clean room collab) before Cross-Media Intelligence?

Starting with Media Intelligence provides a turnkey solution that enables immediate collaboration with available 1:1 partners through a simple, one-time setup. This allows you to unlock actionable insights quickly and optimize individual publisher media and targeting strategies.

With these initial insights, you can fine-tune single-channel strategies and build a foundation of evidence-based results. These results help make a compelling case for cross-media measurement investment. By starting small and scaling, you gradually transition into cross-screen collaboration with confidence, leveraging proven success and minimizing additional implementation requirements.

What personas on the brand side are needed to support the Media Intelligence implementation and ongoing use?

  • Implementation: Requires a technical user, such as a Data Scientist, Engineer, or IT professional, to assist with data connectivity and collaboration setup.

  • Post-Implementation: Results are marketer-friendly and can also be downloaded or exported for integration into existing measurement tools by data science teams.

If activation is out of scope for Media Intelligence, how should we leverage overlap insights?

Audience Overlap insights can provide strategic value by offering a deeper understanding of shared audiences between your CRM data and publisher partners. When activation is not included in the Media Intelligence scope, you can still harness these insights for:

  • Media Planning and Investment Decisions: Determine which publishers or platforms to prioritize based on audience overlap, ensuring efficient allocation of media budgets.

    • Example: A retail brand compares its loyal customer list against a publisher’s audience and finds 30% overlap. If the overlap is high, they may decide to reduce budget allocation to that publisher and invest in other channels to extend reach. If the overlap is low, they may decide to double down on spend to drive incremental reach.

  • Audience Strategy Optimization: Tailor marketing strategies by leveraging insights into shared audiences' characteristics, enabling more precise segmentation and personalized messaging.

    • Example: A CPG brand identifies that a large portion of its highest-value customers overlap with the publisher’s sports content audience but are underrepresented in lifestyle content. The brand then tailors its creative messaging and placement strategy to increase engagement within the underexposed segment.

  • Multiple-Publisher Collaboration: Leverage overlap data to coordinate campaigns across publishers, ensuring consistent reach to key audience segments.

    • Example: A streaming service sees that Publisher A has a 60% overlap with Publisher B for a key audience. To prevent excessive frequency for the same users, they coordinate flighting—activating on Publisher A in the first half of the campaign, then shifting budget to Publisher B in the second half.

  • Benchmarking and Performance Measurement: Use overlap insights to benchmark audience reach and engagement before and after campaign execution to measure success.

    • Example: A financial services brand measures its audience overlap before and after a campaign with a publisher to assess brand penetration. If the overlap increases significantly post-campaign, it indicates successful engagement and audience growth within that ecosystem.

  • Strategic Audience Development: Identify potential untapped audience segments by analyzing gaps in overlaps, enabling brands to expand into new opportunities effectively.

    • Example: A beauty brand finds that Gen Z consumers are underrepresented in their overlap with a publisher’s audience. They use this insight to test new creatives and partnerships focused on this segment, helping them expand their reach into a key demographic. (Note: requires brand to have data attributes)

Even without direct activation within the Media Intelligence scope, overlap insights help you make data-driven media and marketing decisions—from optimizing spend allocation to refining audience messaging. While the ability to compare across multiple publishers strengthens these insights, even a single-publisher overlap analysis can drive meaningful improvements in media efficiency and targeting.

In this section: