Marketing attribution: The challenges of current software solutions

Ah… Marketing attribution — the key to optimizing marketing strategies and improving marketing spend ROI. 😌

Sounds perfect, right?

There’s just one, teensy problem: Current marketing attribution software solutions have marketers feeling unsatisfied at best. In fact, according to a survey by QueryClick, 58% of marketers say their current attribution models are an obstacle to implementing marketing activities. And only 11% of marketers are happy with their attribution solutions. 😬 While these existing software solutions do provide some valuable insights, they fall short of meeting today’s dynamic marketing needs.

In this blog post, we'll explore how to overcome the limitations of current marketing attribution software solutions by modeling marketing data in plain SQL.

The limitations of current marketing attribution software solutions

Like we said: Most marketers are dissatisfied with the current marketing attribution software solutions.

One of their main limitations? They’re often too rigid and inflexible. Existing solutions rely on predefined rules and models to assign credit to each touchpoint, so they (typically) lead to inaccurate and incomplete results. They simply don’t provide enough transparency into how the credit is assigned, making it difficult for marketers to understand and interpret the results.

Another roadblock is that current marketing attribution solutions don’t integrate very well with other marketing tools and platforms. 🚧 Among other things, that disconnect makes it difficult for marketers to bring together data from different sources and gain a comprehensive view of their marketing efforts.

So… if marketing attribution — when done correctly — improves marketing campaigns and ROI, but current solutions fail to meet expectations, where do you turn? 👂 Pssst… You can model your marketing data in plain SQL.

Why use SQL for marketing attribution?

SQL is a powerful tool for uniting data from multiple sources and applying sophisticated algorithms to assign credit to each touchpoint.

And using it for marketing attribution has several advantages over traditional marketing attribution software solutions.👇

1. SQL provides more flexibility and control over the attribution process. You can define your own attribution rules and models, and easily modify or adjust them as needed.
2. SQL provides greater transparency into the attribution process. Now, you can see how credit is assigned to each touchpoint, so you can understand the underlying logic behind the attribution rules and models.
3. SQL can help us integrate data from multiple sources and gain a comprehensive view of our marketing efforts. By joining your data from web analytics, CRMs, and marketing automation, you can analyze the effectiveness of different marketing channels and campaigns.

All about attribution modeling

Before we dive into the details of how to model marketing data in SQL, let's review the different types of attribution modeling:

• First touch: Assigns all the credit to the first touchpoint that led to a conversion.
• Last touch: Assigns all the credit to the last touchpoint that led to a conversion.
• Linear: Assigns equal credit to all touchpoints that led to a conversion.
• U shape: Assigns more credit to the first and last touchpoints, and less credit to the touchpoints in between.
• Time decay: Assigns more credit to the touchpoints that occurred closer in time to the conversion.

Of course, each attribution model has its own strengths and weaknesses, so the "best” model will depend on the specific needs and goals of your organization.

Building an attribution model in SQL

Let’s cut to the chase: To build an attribution model in SQL, you need to bring together data from multiple sources, including web analytics, CRM, and marketing automation. You also need to define your own attribution rules and models in order to apply sophisticated algorithms to assign credit to each touchpoint.

Here’s a basic table representation of the data schema involved:

In this example, we’ll use the schema in this table 👆 to build a view that assigns credit to each touchpoint based on our set of predefined rules. Then, once we analyze the effectiveness of different marketing channels and campaigns, we can optimize our marketing strategy and improve the ROI of our marketing spend.

Let’s write some SQL

Building a query takes four basic steps:

1. Gather your required data sources
2. Find all sessions before conversion
3. Calculate the total sessions and the session index
4. Allocate points

Here’s what each step looks like using actual SQL queries.

1. Gather your required data sources

The following snippet creates three CTEs for our three data sources — session, touch (can be any events), and conversion data — from their respective tables. We’re just prepping the data at this point, but this is what it looks like.

WITH sessions AS (
  SELECT
    session_id,
    user_id,
    start_time,
    end_time,
    source_channel
  FROM
    sessions_table
),

touches AS (
  SELECT
    touch_id,
    user_id,
    timestamp,
    channel,
    campaign
  FROM
    touches_table
),

conversions AS (
  SELECT
    conversion_id,
    user_id,
    conversion_time,
    revenue
  FROM
    conversions_table
)

2. Find all sessions before conversion

Now, we have our source data, so let’s get crunching. Start by creating a view that combines session and conversion data. The goal is to identify all sessions that occurred before a conversion, then join the session and conversion data together. Here’s how the query looks:

WITH sessions_before_conversion AS (
  SELECT
    s.session_id,
    s.user_id,
    s.start_time,
    s.end_time,
    s.source_channel,
    c.conversion_time
  FROM
    sessions s
    JOIN conversions c ON s.user_id = c.user_id AND s.start_time < c.conversion_time
)

3. Calculate the total sessions and the session index

With the session data in hand, we can assign the weight to each of these sessions based on the number of touches using the following query:

WITH touches_with_session AS (
  SELECT
    t.touch_id,
    t.user_id,
    t.timestamp,
    t.channel,
    t.campaign,
    s.session_id
  FROM
    touches t
    JOIN sessions_before_conversion s ON t.user_id = s.user_id AND t.timestamp BETWEEN s.start_time AND s.end_time
),

touchpoints AS (
  SELECT
    user_id,
    session_id,
    channel,
    campaign,
    COUNT(DISTINCT touch_id) AS touches
  FROM
    touches_with_session
  GROUP BY
    user_id,
    session_id,
    channel,
    campaign
),

touchpoint_weights AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, session_id
      ORDER BY touches DESC
    ) AS touchpoint_rank
  FROM
    touchpoints
)

4. Allocate points

Allocate points to each touchpoint based on the model you want. Here, we are assigning 100% of the credit to the first touchpoint and only 50% of the credit to the second touchpoint with the following query.

WITH attribution AS (
  SELECT
    user_id,
    session_id,
    channel,
    campaign,
    touches,
    CASE touchpoint_rank
      WHEN 1 THEN 1
      WHEN 2 THEN 0.5
      ELSE 0
    END AS attribution_weight
  FROM
    touchpoint_weights
  WHERE
    touchpoint_rank <= 2
)

5. Join in revenue and ad spend data

Use the allocated credit to analyze the effectiveness of different marketing channels and campaigns based on the revenue they generated plus the cost of your ads. The final output calculates the ROI for each marketing channel and campaign based on the attributed credit and revenue data.

WITH attribution AS (
  ...
),

attribution_by_channel_campaign AS (
  SELECT
    channel,
    campaign,
    SUM(attribution_weight) AS attribution
  FROM
    attribution
  GROUP BY
    channel,
    campaign
),

revenue_by_channel_campaign AS (
  SELECT
    channel,
    campaign,
    SUM(revenue) AS revenue
  FROM
    conversions
  GROUP BY
    channel,
    campaign
)

SELECT
  abc.channel,
  abc.campaign,
  abc.attribution,
  rbc.revenue,
  abc.attribution / rbc.revenue AS roi
FROM
  attribution_by_channel_campaign abc
  JOIN revenue_by_channel_campaign rbc ON abc.channel = rbc.channel AND abc.campaign = rbc.campaign

And when you combine the above five steps, you get an attribution model in SQL that assigns credit to each touchpoint based on a set of predefined rules. 🙌  Putting it all together, you get the full query.

WITH sessions_before_conversion AS (
  SELECT
    s.session_id,
    s.user_id,
    s.start_time,
    s.end_time,
    s.source_channel,
    c.conversion_time
  FROM
    sessions s
    JOIN conversions c ON s.user_id = c.user_id AND s.start_time < c.conversion_time
),

touches_with_session AS (
  SELECT
    t.touch_id,
    t.user_id,
    t.timestamp,
    t.channel,
    t.campaign,
    s.session_id
  FROM
    touches t
    JOIN sessions_before_conversion s ON t.user_id = s.user_id AND t.timestamp BETWEEN s.start_time AND s.end_time
),

touchpoints AS (
  SELECT
    user_id,
    session_id,
    channel,
    campaign,
    COUNT(DISTINCT touch_id) AS touches
  FROM
    touches_with_session
  GROUP BY
    user_id,
    session_id,
    channel,
    campaign
),

touchpoint_weights AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, session_id
      ORDER BY touches DESC
    ) AS touchpoint_rank
  FROM
    touchpoints
),

attribution AS (
  SELECT
    user_id,
    session_id,
    channel,
    campaign,
    touches,
    CASE touchpoint_rank
      WHEN 1 THEN 1
      WHEN 2 THEN 0.5
      ELSE 0
    END AS attribution_weight
  FROM
    touchpoint_weights
  WHERE
    touchpoint_rank <= 2
),

attribution_by_channel_campaign AS (
  SELECT
    channel,
    campaign,
    SUM(attribution_weight) AS attribution
  FROM
    attribution
  GROUP BY
    channel,
    campaign
),

revenue_by_channel_campaign AS (
  SELECT
    channel,
    campaign,
    SUM(revenue) AS revenue
  FROM
    conversions
  GROUP BY
    channel,
    campaign
)

SELECT
  abc.channel,
  abc.campaign,
  abc.attribution,
  rbc.revenue,
  abc.attribution / rbc.revenue AS roi
FROM
  attribution_by_channel_campaign abc
  JOIN revenue_by_channel_campaign rbc ON abc.channel = rbc.channel AND abc.campaign = rbc.campaign

Limitations of SQL-based attribution modeling

While modeling marketing data in SQL can help us overcome the limitations of current marketing attribution software solutions, there are still some limitations to be aware of.

1. Positional attribution is not a perfect representation of human decision-making. In reality, customers may be influenced by multiple touchpoints, so their decision-making process may be more complex than can be captured by a simple attribution model.
2. The quality of the source data is often low. Web traffic is often poorly tagged with UTM parameters, and CRM data may be incomplete or inaccurate. Poor source data quality can make assigning credit to each touchpoint difficult.

Despite these few limitations, modeling marketing data in SQL helps you gain deeper insights into the effectiveness of our marketing campaigns. And by experimenting with different attribution models and analyzing the results, you can continuously improve marketing attribution and drive better business outcomes. 🚀

The future of marketing attribution is SQL

Sure, marketing attribution is a game-changer for marketers. But if (and when) current software solutions fall short of meeting your evolving needs, start modeling your marketing data in plain SQL — and gain deeper insights into the effectiveness of your marketing campaigns along the way.

Remember: The SQL code example we ran through is just one way to model marketing data. There are so many other approaches you can use to gain deeper insights into the effectiveness of your marketing campaigns and improve ROI. It all just comes down to the specific needs of your organization.

💪 Want to learn how Census can empower your marketing attribution mission? Book a demo with a Census product specialist.