Set up evaluation of DoubleClick media advertising

With OWOX BI Attribution, you can extend your funnel with user actions that occur not on your website — impressions from DoubleClick media ads.

Before you begin

To use the feature, you need to have DoubleClick Campaign or Bid Manager data that can be matched with Google Analytics data by Client ID or User ID in your BigQuery project. How to do this — read in our "DoubleClick Bid and Campaign data" article.

Connect the data to your attribution model

1. Save the SQL query shown below as a Google BigQuery view.
   Make sure you changed "project-name", "DoubleClick", and "p_impression_000000" to your project's name, dataset, and table respectively.
   In "r'u1=([0-9\.]+)') as session_client_id", specify the key of your user-defined Client ID variable: "u1", "u2" etc.

   SELECT
    'dc_impression' as name,
    STRING(NULL) as category,
    MSEC_TO_TIMESTAMP(time) as time,
    STRING(NULL) as user_id,
    session_client_id as client_id,
    STRING(NULL) as transaction_id,
    FLOAT(NULL) as revenue,
    FLOAT(NULL) as cost,
    source as utm_source,
    medium as utm_medium,
    campaign as utm_campaign,
    '(not set)' as geo_region
   FROM (
    SELECT
    STRING('dfa') as source,
    'cpm' as medium,
    STRING(campaigns.Campaign) as campaign,
    activity.session_client_id as session_client_id,
    INTEGER(time/1e3) as time
    FROM (
    SELECT
    User_ID,
    Campaign_ID,
    Event_TIME as time
    FROM [project-name:DoubleClick.p_impression_000000]
    WHERE _PARTITIONTIME >= DATE_ADD(TIMESTAMP('2018-10-11'),-11,'DAY')
    AND _PARTITIONTIME < DATE_ADD(TIMESTAMP('2018-10-11'), 1, 'DAY')
    AND User_ID != '0'
   
   ) as impression
    JOIN EACH (
    SELECT
    User_ID,
    REGEXP_EXTRACT(FIRST(Other_Data), r'u1=([0-9\.]+)') as session_client_id,
    FROM [project-name:DoubleClick.p_impression_000000]
    WHERE _PARTITIONTIME >= DATE_ADD(TIMESTAMP('2018-10-11'),-11,'DAY')
    AND _PARTITIONTIME < DATE_ADD(TIMESTAMP('2018-10-11'), 1, 'DAY') AND User_ID != '0'
    GROUP EACH BY User_ID
    HAVING session_client_id IS NOT NULL
    ) as activity
    ON impression.User_ID=activity.User_ID
    LEFT JOIN EACH (
    SELECT
    FIRST(Campaign) as Campaign,
    Campaign_ID
    FROM [project-name:DoubleClick.p_impression_000000]
    WHERE _PARTITIONTIME >= DATE_ADD(TIMESTAMP("2018-10-11"),-11,'DAY')
    AND _PARTITIONTIME < DATE_ADD(TIMESTAMP('2018-10-11'), 1, 'DAY')
    GROUP EACH BY Campaign_ID
    ) as campaigns
    ON impression.Campaign_ID=campaigns.Campaign_ID
   )

2. On the attribution model page, click Add source: 
3. As a data source, select Custom events: 
4. Select the project, dataset, and the view containing the SQL query, Then click Save
5. Done. A new funnel step, DoubleClick impressions, will appear in your model. Now, run the model calculation.

DoubleClick campaigns reports

In the reports based on your model calculations, you'll see DoubleClick Campaign Manager campaigns with the following tags:

• source: dfa
• medium: cpm
• campaign: name of the campaign from DoubleClick Campaign manager

And DoubleClick Bid Manager campaigns with these tags:

• source: dbm
• medium: cpm
• campaign: URL of the impression