In today’s data-driven marketing landscape, understanding the customer journey is essential for optimizing campaigns and allocating resources effectively. Custom attribution models allow businesses to assign credit to various touchpoints along the customer’s path. Metabase, an open-source business intelligence tool, provides a flexible platform to build and analyze these models. This guide walks you through the process of creating your own attribution models using Metabase.

Understanding Attribution Models

An attribution model determines how credit for conversions is distributed across different marketing channels and touchpoints. Common models include last-touch, first-touch, linear, time decay, and position-based. Custom models enable tailoring attribution to your unique customer journey and business goals.

Prerequisites

  • An active Metabase instance with access to your marketing and website data.
  • Data sources connected to Metabase, such as databases or data warehouses.
  • Basic understanding of SQL queries and data modeling.
  • Defined customer journey data with touchpoints and timestamps.

Step 1: Prepare Your Data

Ensure your data includes detailed records of customer interactions with timestamps, channel identifiers, and unique customer IDs. Data should be cleaned and structured to facilitate analysis.

Create a Data Table

Use SQL or your data source’s interface to create a table or view that consolidates customer touchpoints. Example SQL:

SELECT customer_id, channel, timestamp
FROM customer_journey
ORDER BY customer_id, timestamp;

Step 2: Visualize Customer Journeys

Build a dashboard in Metabase to visualize the sequence of touchpoints for individual customers. Use line charts or sequence diagrams to identify common paths and bottlenecks.

Step 3: Define Your Attribution Logic

Decide how to assign credit. For example, in a linear model, each touchpoint gets equal credit. In a custom model, you might weight early or late touchpoints differently.

Example: Custom Weighted Model

Assign weights based on position or recency. For example, recent touchpoints might receive higher credit.

Step 4: Implement the Model with SQL

Write SQL queries to calculate attribution scores based on your logic. Example for a simple weighted model:

WITH touchpoints AS (
  SELECT customer_id, channel, timestamp,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY timestamp) AS position,
         COUNT(*) OVER (PARTITION BY customer_id) AS total_points
  FROM customer_journey
)
SELECT channel,
       SUM(
         CASE
           WHEN position = 1 THEN 0.5
           WHEN position = total_points THEN 0.3
           ELSE 0.2 / (total_points - 2)
         END
       ) AS attribution_score
FROM touchpoints
GROUP BY channel;

Step 5: Visualize and Analyze Results

Create visualizations in Metabase to compare channel contributions. Use bar charts or pie charts to display attribution scores across channels.

Step 6: Automate and Refine

Schedule regular updates of your attribution queries and dashboards. Continuously refine your model based on new data and insights.

Conclusion

Building custom attribution models in Metabase empowers your team to better understand the impact of marketing channels. By preparing your data, defining your logic, implementing SQL queries, and visualizing results, you can create tailored insights that drive smarter marketing decisions.