Campaign Spend Attribution and Row-Level Data with Sigma

What Is Campaign Spend Attribution?

Ever wanted to know exactly how your marketing efforts are performing? You can with Campaign spend attribution, which enables you to see how marketing strategies are generating leads, pipeline, sales, and customer lifetime value.

Attribution models allow marketers to assign credit to marketing touchpoints that take place throughout the customer journey. Since every organization has a unique customer journey, using attribution models gives marketers a better understanding of which parts of the customer journey are having the most impact and leading to more sales. Marketers can use multi-touch modeling, to assign credit to multiple touchpoints and view all marketing interactions and how they affect the sales funnel.

Why You Need Access To Row-level Data

Sigma gives access to granular row-level data empowering marketers to do more — enabling them to drill down into campaign performance to gain deeper insights which can be used to make data-driven decisions on the fly. This can all be done without the help of a data team, engineer, or analyst and can be done by any non-technical user within the company.

Imagine looking at a campaign's performance and being able to dig into the individual leads or opportunities. From there you can see exactly how each lead interacted with your site, campaign, product, etc. at each stage of their journey. Ultimately enabling you to create a personalized experience for each customer type and journey.

Legacy BI tools on the market do not offer access to row-level data or require complex methods and technical users who have coding experience to step in. It's critical for business users to be able to answer their own questions or any ad hoc questions coming in. With traditional BI tools, it can take days or even weeks for a data team to finally make time to assist marketers or business users in answering their questions since most of the time one needs to understand SQL or other coding languages.

Marketers and analysts want to create dashboards that show metrics from mid-level details (ad spend) and still be able to explore and drill down into the lower-level data (individual leads) to gain deeper insights. This helps marketers use the data to better understand the leads and opportunities and come up with strategies to help close more deals.

Ad hoc filtering scenario:

You've been tracking campaign ROI in terms of dollars spent divided by total sales. Now, your company starts targeting a new customer profile, and the CEO asks you to investigate how the existing campaigns perform against that specific segment. In order to figure this out, you need access to granular data to filter out leads that are not in this new profile and recalculate ROI. In this case, aggregate data just won't cut it.

What Are Fan-outs & How To Solve Them

Fan-outs happen when you join two tables together and the values get duplicated. This causes the aggregation values to be inaccurate because some values are double-counted.

Since we're doing a fact-to-fact join with data at two different levels simultaneously (campaigns vs. leads), we need to be careful to not duplicate rows which can result in inaccurate numbers.

Other BI tools deal with fan-outs in different ways. In Tableau, users use level of detail calculations. In ThoughtSpot, users use group aggregation functions. In Looker, data teams have to model these unique relationships ahead of time. All these methods take time and dedicated manpower from multiple people and teams, leading to slower turnaround times.

With Sigma, we can approach fan-outs directly by using the powerful grouping window function capabilities to make sure we only count values once. Sigma’s flexibility also lets you implement other approaches if necessary.

How It’s Done In Sigma

What you will need: 

1. A BI and analytics solution like Sigma

2. Campaign data, including but not limited to:

• Campaign name
• Campaign cost

3. Leads data, including but not limited to:

• Campaign name
• Lead name
• Lead email
• For this example, we will use a field called “Lead Opportunity Amount”. You could pull in any other relevant lead information. The sky’s the limit here, and depends on your data.

How To Do Campaign Spend Attribution With Row-Level Data In Sigma

Step 1: Combine your row-level data with your high-level data

First, we’ll pull in both our campaign table and leads table into a Sigma Workbook. Then we’ll group the leads data by campaign name, and use a Lookup on the grouped campaign name field to join in campaign cost (and any other data) from our campaign table.  

Step 2: Add row numbers within each campaign grouping

We’ll deal with fan-out by adding a RowNumber column inside each campaign grouping. By doing this, only one row within each group will have the row number “1”. This will let us avoid double-counting when we build calculations.

‍

Step 3: Create a visualization, and correct the formulas to handle fan-out

Create a visualization from your grouped table; a simple bar chart will work. Put Campaign Name on the x-axis, and Campaign Cost on the y-axis.

Note that we are currently double-counting the actual Campaign Cost by counting rows multiple times.

To correct this, modify the Campaign Cost value on the y-axis to use the RowNumber column we added to the table: change the formula from `Sum([Campaign Cost])` to `Sum(If([RowNumber] = 1, [Campaign Cost]))`. Now the visualized Campaign Cost is correct!

You can continue to add additional calculations to the y-axis. Just remember to change the formula to use `If([RowNumber] = 1, …)` if referencing values from the campaign level.

‍

Step 4: Now that you have your dashboard, you can do analysis across both campaign and lead levels of detail

For instance, say you want to be able to analyze a campaign’s performance for a specific user segment. You can apply filters dynamically on the table, and the visualization will update your lead-level metrics (say, leads brought in, or total opportunities value) while keeping your campaign-level metrics (campaign cost) the same.

You can also dig into the underlying data on the visualization at any point. If you’re analyzing a specific campaign and need to see the lead data, you can right-click “Show underlying data” on the visualization to see the underlying leads.

This enables you to do more valuable analyses and quickly get the insight you need.

Why Use Sigma For Campaign Spend Analyses

Sigma is a self-serving data exploration and analysis tool that sits at the end of the modern data stack. Sigma enables organizations to extract value from their data with the simplicity of a spreadsheet and without SQL. Users can drill down to see granular data to answer ad hoc questions and make data-driven decisions.

Collaboration is at the heart of Sigma. Teams can seamlessly work together while keeping data secure and governed through permissions.

Let's Sigma together! Schedule a demo today.