Building a marketing attribution model with Segment & Looker

[LookerExperts]

This content, written by Erin Franz, was initially posted in Looker Blog on Mar 4, 2020. The content is subject to limited support.

It’s been almost five years since the original launch of the . At that time, the term “Looker Block” didn’t even exist. Now, our directory contains over 75 source Blocks developed by Looker and our partners to accelerate analytics for our customers. With that in mind, we figured it was a great time to take a closer at other challenges Looker Blocks can help solve.

One use case we often get from Looker customers is how to appropriately model marketing attribution with data. Understanding ROI on marketing campaigns is imperative to your business, but wrangling all the data and modeling it or trying to understand your current model can be pretty time-consuming. As a solution to make this process easier, we’re excited to announce the Block. This Block provides the necessary guidance to successfully build and implement a marketing attribution model using Looker and Segment.

The problem

Marketing Attribution is critical to measuring campaign performance against your most important outcomes — whether it be account creation, subscription sign-ups, or, ultimately, revenue. Despite its importance, it’s too often a point of frustration for a few common reasons:

1. Getting reliable user data: Behavioral data from websites and mobile applications can be difficult to configure, especially at the event and user level.

2. Pulling data from multiple sources: Many important data points are contained in advertising platforms themselves, leaving data consumers with the task of trying to patch together exports and canned reports from those systems.

3. Creating the marketing attribution model: All this data, even if reliable and available, is challenging to transform into a clear, reliable marketing attribution model.

If you’re already using Looker with Segment, you’ve likely made progress on those first two points. Segment makes it possible to get visibility into your user-level web and app interactions and makes that data available in your warehouse in a consistent format. Beyond that, data from popular advertising platforms like Facebook and Google Ads can be delivered to the same data warehouse with . So with Segment, all the data you need to build your marketing attribution model is available in your warehouse, and, of course, in Looker as well.

In this post, we’ll focus on the last point: how you can build the marketing the attribution model you need so you can garner insights and take action on those insights with Looker. We worked with the Segment team to design an extension to the that ties the user-level data with your marketing campaigns. The result is your marketing spend linked directly to user-level outcomes, like sign-ups and purchases. Let’s walk through how it works!

Capturing campaign attribution at the event and session level

In our and blog posts from our series on Looker + Segment back in 2015, we discussed first mapping events to a universal user ID, and second, mapping those events to individual visits or sessions.

1. We first take the raw tracked events and page views captured by Segment and enrich them with a consistent user identifier across both pre and post-authentication events, in order to track user behavior across all time.

2. Next, in order to track user behavior for an isolated visit, we further transform events into sessions, which are usually time boxed by a period of inactivity.

With Segment, the UTM parameters and marketing campaign source on the events we’ve used to define users and sessions are automatically captured. If we apply the same user behavior methodology to marketing campaign analysis, we can similarly track campaign attribution across our user’s lifetime as well as on any given visit. When mapping events from Segment, just be sure to grab both the context_campaign_source and context_campaign_name from the pages table (this is documented in the derived table in the Marketing Attribution Block). That way, we can persist campaign metadata throughout our analyses.

Tying campaigns to conversions

After associating events and sessions with campaigns, we can now tie those events and sessions to universal user IDs. In order to measure how much revenue is sourced from campaigns, we need to associate conversion events with our sessions. In Marketing Attribution by Segment Block, we already created an <a href="https://github.com/llooker/segment_marketing_attribution/blob/master/_E_event_facts.view.lkml" target="_blank" rel="noreferer noopener">event_facts</a> table that assigns a session id to each event, so you simply join in the event table, and that captures conversions. In the Block, the conversion event is named order_completed and the revenue field can be used to assign the conversion revenue to each visitor’s session, with associated SQL from the block below:

 SELECT
    event_facts.looker_visitor_id  AS looker_visitor_id,
    event_facts.session_id  AS session_id,
    min(event_facts.timestamp) AS start_time,
    max(event_facts.context_campaign_name)  AS context_campaign_name,
    max(event_facts.context_campaign_source) AS context_campaign_source,
    SUM(order_completed.revenue) AS session_revenue,
    FROM event_facts
        
 LEFT JOIN order_completed 
    ON event_facts.anonymous_id = order_completed.anonymous_id
    AND event_facts.timestamp = order_completed.timestamp 
    AND event_facts.event = order_completed

 GROUP BY 1,2 ;;

If there are any additional conversion metadata in your equivalent order_completed table that may be useful for campaign analysis — such as product name or quantity — you can update the query to include anything that’s relevant for the analysis.

Unifying the marketing and product data silos

Now that the product-side data is modeled, campaign data at the user and visit level that can be associated with conversion metrics like revenue. Actual return on campaigns can finally be aggregated. But what about all the valuable information contained in the marketing tools themselves? Commonly used tools like Facebook Ads and Google AdWords contain valuable information on impressions, clicks, and dollars spent on campaigns themselves.

Segment has addressed this concern by offering support for both Facebook Ads and Google AdWords, allowing data to be sourced directly from their APIs into the same data warehouse containing all the product event data collection. The also contains out of the box analysis for these individual sources as well as a combined view of ads metrics across both tools to get immediate value out of this data.

By having both product data and ads data sourced to the same location and modeled in Looker, comparing campaign cost and revenue to build out comprehensive ROI analyses becomes much easier.

Complete view of campaign performance

With the basics now covered and out of the way, a comprehensive view of marketing engagement can be made available through dashboards in Looker. The Marketing Attribution by Segment Block block provides out of the box reporting for:

• Revenue by marketing channel
• Facebook Ads/Google AdWorks revenue and cost comparison
• Conversion by acquisition referrer

Running more advanced analyses on lifetime user behavior can also include first touch/last touch models, repeat visitor insights, and more.

Ready to build your marketing attribution model?

Implement the or request a Looker to learn more about Looker Blocks.