B2B Marketing Blog

How To Visualize Your Marketing Attribution Data In PowerBI

By Andrew Nguyen
Sep 1, 2017

At Bizible we focused on building a product that can deliver great data. Great data is relevant, accurate, and precise. But what about great design? After ensuring data is reproducible and accurate many marketers begin to focus on the best way to communicate new information.

Good design is finding the straightest path, or the simplest solution, for communicating a message. In data visualization the point of good design is to communicate information quickly and effectively.

At Bizible we want to help marketers do this.

It’s one reason we created Data Warehouse. In addition to providing access to large swathes of marketing performance information, Data Warehouse provides the ability to use your favorite data visualization program to create well designed charts and dashboards. 

In this post we’ll walk through how to visualize your marketing attribution data in PowerBI. The same principles apply if you use a different program like Tableau.

The process is as follows:

  1. Connect PowerBI or Tableau to your Data Warehouse

  2. Map the relationships between different data tables (this enables you aggregate and plot data in one chart, even if the data is located in different tables).

  3. Aggregate your data.

  4. Create your chart, visualization or dashboard.

Let’s get started.

Choosing What To Communicate With Data Visualizations

Often times goals include increasing share of market, or improving unaided brand awareness, generating leads, reducing or keeping cost-per-lead the same, supporting sales teams to improve win rates, and improving NPS scores.

Despite the many sets of data available to you, a visualizer and Bizible's Data Warehouse makes it possible to communicate the revenue impact of marketing. 

For board meetings or planning meetings it is always a good idea to communicate revenue generated by segment or by channel.

As long as your data tables have a uniquely identifying column, like customer name or account name, you can pull it all into PowerBI and explore the connection to revenue.

With Bizible Data Warehouse you can show the relationship between any performance indicator and revenue. 

Connecting to Data Warehouse

To connect your visualizer to Data Warehouse you’ll need some information on your database. You’ll need the server name and database name.

Click “Get Data” and then select the database you want to connect to. This can be a MySQL, Amazon Redshift, Microsoft Azure, or other database service provider. For our example we connect to an Amazon Redshift database.

You’ll need the name of the server and the name of the database.

amazon-redshift-powerbi-connection-2.jpg

Next, select the data tables you’re interested in using. We’ll be using the biz_attribution_touchpoints table and the biz_opportunities table in this tutorial.

bizible-datawarehouse-connecting-to-powerbi-v2.jpg

In the biz_attribution_touchpoints table is touchpoints information telling us the attribution percentage value and position of each touchpoint. For example, we have a form fill touchpoint, its position is Lead Create touch (LC) and its percentage value is .25, meaning the touchpoint gets 25% of revenue credit under the attribution model used.

In the biz_opportunities table we have information like revenue amount which is either closed won revenue or pipeline revenue (we will filter for either in this tutorial).

If you’re interested in looking at how much revenue attribution a channel or campaign should get, you’ll need to combine the touchpoints table mentioned previously and the opportunity table which has revenue data.

There are different ways to do this including using SQL or the Query Editor, but we’ll stick to a method that doesn’t require a lot of code. Let's map the relationship in PowerBI.

Mapping Data Relationships

To “combine” two tables they must share a common column, i.e. an index, and you’ll map the two tables together. This enables you to run analyses across the two tables, treating them as if the two tables are one.

We’ll use the two tables mentioned previously. The biz_attribution_touchpoints table stores attribution information for each touchpoint. The biz_opportunities table has sales data like revenue. The former is made up of rows of touchpoints and the latter is rows of opportunities. Touchpoints data tells us how much credit marketing deserves for the deal while the opportunity data tells us the total revenue value for each opportunity.

The touchpoints table includes multiple attribution weights, i.e. each touchpoint has the weights for every attribution model Bizible offers. These weights are percentage values between 0 and 1, and the tell us how much revenue each touchpoint gets. And the table is structured like below:

biz attribution touchpoints table-01.jpg

In the biz_opportunities table the data looks like below:

biz attribution touchpoints table-02.jpg

We need to write a formula that creates a new column that gives us revenue. that is the product of the touchpoint weight and the total revenue of the opportunity. This gives us touchpoint value in terms of revenue, i.e. attribution.

biz attribution touchpoints table-03.jpg

To do this we must first map the two tables. We can do this by mapping a relationship. To map the relationship:

  1. Click on Manage Relationships button found in the home tab at the top of the screen.

  2. Click New.

  3. For this example we’ll map a relationship between a table called biz_attribution_touchpoints and one called biz_opportunities. Select “opportunity_id” and “id”.

  4. Select “Many to one” and check the box to make the relationship active. Then click OK.

The Manage Relationship options look like below:

mapping relationships powerbi bizible data warehouse.jpg

This is a very common operation if you will be using PowerBI, so we won’t go into too much detail here. 

Now you are ready to start measuring revenue. To do so we need to create a new Measure in PowerBI, i.e. a new data column, that tells us how much marketing revenue each touchpoint is worth, as explained before. Luckily, it’s pretty easy since it’s a lot like Excel.

To create a new Measure in PowerBI:

1) Click “New Measure” under the Modeling tab at the top.

2) Enter the following formula in the formula bar:

Full Path Revenue = SUMX('biz_attribution_touchpoints',[full_path_percentage] * RELATED('biz_opportunities'[amount]))

dax formula bar powerbi-01-01.jpg

SUMX is a function that iterates an expression on each row of the biz_attribution_touchpoints  table. The expression multiplies the touchpoint attribution percentage by the revenue amount in the biz_opportunities table. In this case the attribution percentage is based on the Full Path attribution model, you can choose any other attribution models as well.  The result is a new column that gives us the touchpoint’s revenue value.

Visualizing Your Revenue Data

You’re now ready to create some great visualizations. Finally!

For this example we’ll recreate the charts you’d find with the core Bizible product, which sits inside the CRM. Again we are creating charts in a visualizer because CRMs lack the ability to customize design elements.

Let’s create a pie chart showing the amount of revenue each channel has been credited with under a full path attribution model.

  1. Navigate to the report tab in the left sidebar. It’s an icon that looks like a bar chart.

 

2) Select your data columns and drag it to the “Values” tab. For this example we drag and drop Channel and the measure we created, Full Path Revenue.

 

3) Under the Visualizations menu select the type of chart you need. For this example we’ll choose a pie chart. You should see a pie chart with slices proportion to the amount of revenue each channel is credited with.

powerbi-visualization-menu.jpg

Similarly to reporting with Bizible inside the CRM, it’s important to consider the types of filters you want to apply to your data. For example, you may want to filter your report based on a specific date range. If you’re doing quarterly reporting you can create a filter in PowerBI by dragging a data column into the Report Level Filters Tab.

Say you want to limit the report to opportunities that are in a certain stage of the sales cycle and whose close date is within a year (to limit to new business opportunities), you can do that by dragging those data columns into the Report Level Filters Tab and filtering based on your preferences. 

Using filters is important because it enables you to create many relevant reports. For example, you can create a pipeline report showing how much pipeline you’ve generated by filtering out Closed-Won deals and only including opportunities that are in a specific stage of the sales cycle (like contract negotiations or in a free trial).

Let’s focus simply on Closed Won revenue. We filter everything out except Closed Won and we choose a date range like last quarter.

Next you’ll want to refine the chart so that it communicates effectively. You can start by changing the the way the data is labeled. For this chart it will be helpful to see the amount of revenue next to each channel. So go to the Format tab and under Detail Labels you’ll find options for units of measure, and label styles. We choose to display the category label (Channel) and the data value (Revenue).

We can change colors, titles, and fonts to create the best looking chart by playing around with the formatting options in the Format tab.

bizible-marketing-performance-data-visualization.jpg

And just like that we have a well designed chart that delivers great information to the leadership team.

Now you’re able to put together a variety of visualizations and develop a dashboard with important marketing performance metrics.

data warehouse product shot v2.jpg

Conclusion

We’ve only scratched the surface with what you can learn and chart. With filters and data mapping you can explore your data at broad or granular levels.

Bizible’s Data Warehouse includes a lot of data that you don’t get in the base product. For example, you get all web session data for known and unknown users so you can study your site performance and customer journey in great detail.

With a program like PowerBI or Tableau you can pull in brand data, customer service data, product usage data, and more and start exploring how it all connects to the revenue data you get with Bizible.

Data Warehouse is how Bizible is expanding its product suite to respond to the  business intelligence needs of B2B marketers.

The CMO's Guide To Marketing Performance Management  Evaluate Your MPM Process And Improve Revenue Generation.  Download Now

Topics: marketing performance management, data warehouse, data visualization

  New Call-to-action