Skip to main content

Linear Attribution UDF

You can use the LiveRamp-provided linear attribution UDF to determine ad exposure credit for conversions.

The lr_linear_attribution UDF:

  • Attributes equal credit to all ad exposures within 30 days before the conversion regardless of the platform, creative, or property.

  • Uses the following metric: total_conversion (sum(conversion))

  • Returns attributed conversions and impressions for all cuts available in exposure files based on the audiences and the conversions' key performance indicators (KPIs).

  • Provides insights into the impact of different audience segments and cut values on conversion KPIs.

  • Uses RampIDs as a proxy for the number of households.

To collaborate with your partners using the lr_linear_attribution UDF, you and your partners need to reference data assets with columns that are consistent with the following definition, which is declared at the top of the UDF's SQL code:

UDF Header

lr_linear_attribution(
    exposure_table data<rampid string, exposure_timestamp timestamp, cut_value string>,
    conversion_table data<rampid string, conversion_timestamp timestamp, conversion_value float>,
    audience_table data<rampid string, segment string>,
    kpi string,
    cut_type string,
    window int,
    measurement_start_date string,
    measurement_end_date string
    )

Where:

  • exposure_table: The name of your exposure table, which includes columns for RampID strings, cut values (column names for certain parameters), and exposure dates

  • audience_table: The name of your audience table, which includes columns for RampID strings and segment ID integers

  • kpi: A string representing the conversions' key performance indicators (KPIs), such as Total Web Visits

  • cut_type: A string representing the parameter you want to use in the analysis, such as campaign, platform, creative, or daypart

  • measurement_start_date and measurement_end_date: The date range for your analysis in UTC, such as 2024-03-01

The following sample shows a portion of the lr_linear_attribution UDF to show parameter syntax.

Sample UDF Call

-- Use UDF to get linear attribution
select * from lr_linear_attribution(
-- 1st argument is exposure table
    (select 
    entity_id as rampid, 
    exposure_start_timestamp as exposure_timestamp ,
    'campaign' as cut_value 
    from dpm_adt_campaign_context_combined() 
    where exposure_start_timestamp >= '2023-08-01' 
    and exposure_start_timestamp <= '2023-08-29' ),
-- 2nd argument is conversion table             
    (select 
    entity_id as rampid,
    event_timestamp_utc as conversion_timestamp,
    1 as conversion_value 
    from dpm_adt_conversions 
    where conversion_goal_name = 'ADT_website_visits_v2' 
    and event_timestamp_utc >= '2024-03-01' 
    and event_timestamp_utc <= '2024-03-29' ), 
-- 3rd argument is audience table            
    (select 
    entity_id as rampid,
    audience_definition_name as segment 
    from dpm_adt_audiences),
-- 4th argument is kpi            
    'Total Web Visits', 
-- 5th argument is cut_type supported values = ['campaign', 'platform', 'property', 'creative', 'daypart' ]            
    'campaign', 
-- 6th argument is window
    7, 
-- 7th argument is measurement_start_date            
    '2024-03-01',
-- 8th argument is measurement_end_date            
    '2024-03-29'
    )

The following sample dashboard was created in Looker based on data returned by the lr_linear_attribution UDF.

UDFs-Linear_Attribution_Looker_Dashboard.png

Note

This UDF is compatible with LiveRamp's qt SQL function, which protects row-level data by setting a query threshold (minimum aggregation). Therefore, this UDF does not support window functions.

In this section: