Building an ABM analytics suite — quick and cheap (with some free templates)

This article is co-authored by Ross McFadden and Lindsey Christensen

“What systems do you have?”

“What’s in your MarTech stack?”

“Do you have an ABM platform?”

There is no doubt ABM can be an effective methodology in sales and marketing. But when we collaborate on how to do it, why do we so often start the conversation with systems, tech. stacks, software and platforms?

Hopefully up to this point you’ve seen we started with a problem to be solved — marketing to a small segment of enterprises. If not, be sure to read our earlier posts on Getting started with ABM, and Creating and Executing your First ABM Plays. (We got a little distracted from continuing this series, sorry for the delay good people….)

Ross & Lindsey, back at it

But once you do identify that account based marketing is a good strategy for your needs, the topic of tools quickly enters the conversation. It’s because when done well, ABM is all about being data-driven to enforce focus and optimize results. Most marketers are working with tools that are not well-suited for their ABM efforts.

We knew this, which is why we’re writing this series. And in this post, we’re giving more than our two cents of advice.

Below is our system list, instructions on building an ABM database, and report templates. We spent $30/month extra on technology and built a kick-ass ABM analytics suite that you can use.

If you’re here for the template s— just take them!

Why do I need ABM analytics?

There are a couple reasons to utilize analytics for ABM. The first is that nearly all of us have bosses. It’s a sad fact (“sorry, Lindsey” -Ross). If ABM is going to work, you’ll need to prove it — to sales, to company leadership, to your board, to investors, and let’s get deep here — you need to prove it to yourself! And since, In God we Trust, all others bring Data, I’d say that one reason makes the whole endeavor worth it. Secondly, you can apply the insights from this data to all of your other go-to-market efforts. With the knowledge gained you can direct your marketing spend for non-ABM campaigns to more effective channels.

You don’t need my help justifying ABM analytics. Read the countless articles (mostly from vendors) of why ABM analytics are so important and how they have been proven effective. Here are a couple to wet your appetite (and your boss’s) — Engagio, Terminus, Bizible.

A couple assumptions about systems

Before we go too far, there are a couple things you need to know. First, we did not code our own software and visualizations. We have a couple tools (some great, some not so good) that make this whole thing possible. In our ABM suite we use,

  • Salesforce CRM
  • Google Sheets
  • Google Data Studio
  • G-force connector (used to pull Salesforce data into Google Sheets)

You do not have to use these exact systems! Apply these concepts and even parts of the architecture (if not Google Sheets, use Excel) to build your own ABM analytics suite.

A great place to start is looking at what relevant data you already have in your CRM, and how to extract it.

Here’s the big idea

We’re going to take Salesforce data and pull it down into Google Sheets using G-force connector tool. Then we’ll do some fancy Google Sheets work and pull that data into Google Data Studio for visualization and reporting. We’ve broken out instructions into three sections:

  1. CRM data you’ll need for ABM analytics
  2. Creating an ABM database
  3. ABM analytics with Google Data Studio

CRM data you’ll need for ABM analytics

Thank you Salesforce 😭

We need to talk about a little thang in Salesforce. Undoubtedly many of you already know this, but for those who have escaped Salesforce as your CRM, Salesforce splits prospects into Leads and Contacts. Like a caterpillar becomes a butterfly, so to a Contact is born of a Lead once that prospect is attached to an Opportunity. And like the butterfly, there is no going back. That means all of your Contacts are prospects that were associated with an Opportunity. And Leads are not. That sounds non-problematic at first, but when you want to simply look at prospects from a single Account, it actually makes things quite tricky.

First, once a person becomes a Contact, they are still a Lead. Technically they are both a Lead and a Contact. And second, given you do not have to attach a Lead to an Account, but rather a free-text Company field, it makes aggregating all the prospects within an account… challenging. Here’s another article titled The Salesforce Leads Database Sucks…

We’re sure to catch some shade from the Salesforce lovers which is fine. We don’t totally despise Salesforce, it just makes a couple ABM must-haves impossible. It’s why so many ABM solutions have popped up recently.

So let’s tackle this using 2 steps.

1. Create Accounts for Leads

The idea is to give Lead the same Account field as exists for Contacts.

First, create AccountLookUp on your Lead object within your salesforce developer studio. If you’re unclear on how to create a custom Lookup field — check out Salesforce’s guide here.

2. Pull all Contacts and the appropriate Leads

Now since we have our contacts and leads linked to the same Account, we can pull all Leads and contacts from that Account. Couple things to keep in mind — there is still a Lead for all your Contacts, we’ll need to remove those so as not to duplicate a person. And second, we can’t create a report across leads and contacts within Salesforce. We’ll cover pulling this data into Google Sheets in the next section.

Here is the data we’ll be working with from Salesforce. Often these are called tables. Each will be an individual sheet within our Google Sheet.

  • Lead
  • Contact
  • Account
  • Campaign
  • CampaignMember
  • Task
  • Opportunity
  • ContactOpportunityRole
  • User

Creating an ABM database

We’ll be creating a light “database” in Google Sheets for our ABM data. First, we need to get the data from our CRM, in this case, Salesforce, into Google Sheets.

  1. Using G-force connector to pull CRM data into Google Sheet

We mentioned above a $30/month cost — it’s because of this neat Google Sheets extension — G-force connector. This tool lets you connect directly to your Salesforce account and using SQL queries, pull data directly into Google Sheets. Check out G-force connector here (we aren’t paid by any system folks btw.)

In g-force connector take all your data from Leads, Contacts, Accounts, Tasks, Campaigns, and Activities.

2. Joining data in Google Sheet

At this point in the blog, I’m going to turn it over to our templates to do the explaining. You’ll notice in many headers we’ve tried to show you just what we’re doing in each sheet and each field. Those in blue are pulled from our CRM, those in blue are data we have hardcoded, and that in yellow are calculations and joins.

Here’s an example of what I’m talking about. Notice the colors in each column.

Red — Raw data (pulled directly from Salesforce using g-force connector).

Here’s an example query:

SELECT

id,
campaignId,
campaign.Name,
leadId,
contactId,
createdDate,
leadId,
contactId,
name,
title,
account.Name,
hasResponded,
status,
type,
firstRespondedDate

FROM campaignMember

WHERE hasResponded = TRUE

Yellow — References/calculations within this google sheet (vLookUp, ArrayList etc.) Here’s an example excel formula,

={task!P4:P; campaignMember!T4:T; opportunityContactRole!E4:E}

Blue — Hardcoded data. In the case above,

ContactId
OpportunityId

came directly from Salesforce. We hardcoded,

Engagement Category
Engagement Type

and joined,

Prospect Name
Prospect Title

Get a template for your own ABM analytics database here.

ABM Reports and Visualization with Google Data Studio

Once you’ve finished setting up your own google sheet template and started pulling in data, you can use Google Data Studio to visualize this data.

You now have the ability to look at leads and contacts over each account or user and see their entire marketing and sales engagement based on campaigns and tasks.

Here are the visualizations that we’ve already built for you.

Conclusion

What we have outlined is one way to get basic ABM reports and visualizations. And it can be argued that this is not the best way to do it. What we will say is that this method was very fast and very cheap. For you, it could be even faster and cheaper. It’s not exactly easy — you will need some basic knowledge of joins and data manipulation. But with a little googling, you can stand up analytics that could steer your next set of go-to-market campaigns.

If you have any questions about this article or any of our templates, please leave us a comment and we’ll help where we can!

- Lindsey & Ross