Editor's note: TeamSnap is on a mission to help sports teams and organizations spend more time playing and less time organizing. They've been using Tableau and Google to help make that happen. We invited Chief Growth Officer Ken McDonald to share insights about how they are using Tableau to measure the right marketing metrics.

Marketing executives are constantly faced with pressure to increase the effectiveness of their marketing programs, and especially their marketing spend. The bar is raised yearly to deliver more trials, orders, or subscribers per marketing dollar.

The wrong metrics

Despite the pressure, most marketing organizations spend their time looking at the wrong marketing metrics. They spend too much time looking at the metrics highlighted in the ad platform they are using, such as click through rates, cost per click (CPC), cost per thousand (CPM), and cost per action (CPA) where CPA often refers to a top of the funnel action like a new lead or a new trial. The marketing team cuts these top-level metrics seven different ways ‘til Sunday and can share how they vary by campaign, ad group, audience, etc.

More sophisticated organizations dig deeper and get into cost per order or average selling price (ASP), again divided by campaign, ad set, etc. They may dig into average customer acquisition costs (CAC) compared to average lifetime value (LTV). This analysis is helpful because it looks at how much you are spending to acquire customers compared to how much they’re worth to your brand over their lifetime. The challenge is that by grouping all customers together, you lose the richness of customer segmentation.

What are the right metrics?

Marketing organizations should combine segmentation with higher level analyses comparing CAC to LTV that ultimately help determine which customer segments are most profitable. If you analyze your customer acquisition efforts at this level, you can dramatically increase customer acquisition effectiveness by easily identifying pockets of highly efficient marketing spend and pockets of wasted spend. This allows you to cut programs that aren’t working.

First calculate LTV

You should calculate actual LTV based on the projected spend for each customer segment, by looking at how much a segment spent in year one and their projected churn. Even if you are not a traditional recurring online business (e.g., SaaS or subscription), you can typically use this method to calculate projected LTV of your customer segments.

Explore segmentation

Think about deep segmentation – far deeper than marketers typically consider. You should still consider CAC to LTV for standard segments, such as source (campaign, audience, ad set, etc.), audience (age, gender, geo, etc.), platform (device category, OS, browser, etc.) and behavior in your app (feature usage, data provided during sign-up and set-up).

Then look deeper at various segment combinations. Start with hypotheses about the segments that may or may not be working and then investigate whether that pocket of spending is working or not.

Deeper segment examples:

  • How does CAC compare to LTV for users who saw a Facebook campaign on an Android device and completed your trial sign-up flow?
  • How was CAC to LTV for women in Canada who saw a YouTube ad on Chrome?


Exploring ROI by Segment using Tableau

Our success story

TeamSnap saved $300,000 the first day we used this technique in Tableau. We uncover new pockets of spending daily that are or are not working, and can reallocate roughly millions of ad spend per year. As a growth-stage company, the business impact of aggressively re-allocating marketing dollars is incredibly strategic for us.

Why is this system overlooked?

While this system has strategic importance, it can be overlooked if an agency is employed to manage marketing spend. The data required to perform these analyses spans ad platforms (e.g., Google and Facebook), ad servers, CRMs (e.g., Salesforce), and back-end billing systems. The agency typically only has access to the ad platforms. And some agencies charge a fee tied to how much media they place, providing little incentive to easily point out pockets of inefficient spend, even if it helps their client.

This agency issue aside, while stitching together systems can be difficult, it is a necessity to get a true picture of customer acquisition economics. Computing LTV requires tracking customers over a fairly long period of time. Using ad platform conversion pixels isn’t ideal because cookies are erased and customers switch devices so you must dig deep into your CRM and back-end systems.

Tableau is the glue to make it possible

We used Tableau as the glue to integrate our systems and activate this type of CAC to LTV by segment analysis. In our case we:

  1. Pulled data from Google Analytics into Google’s BigQuery to analyze direct click attribution data (i.e, data on users who click on an ad and then convert).
  2. Pulled view-through conversion data (i.e., data from users who view an ad, never click, but eventually convert through another source, often branded search) into BigQuery.
  3. Pulled cost data from AdWords into BigQuery. These data transfers are all done through Google’s Data Transfer service.
  4. Leveraged Tableau’s BigQuery connector to pull everything above into Tableau for self-service analytics and collaboration.
  5. Leveraged Tableau’s Salesforce data connector to pull customer data from Salesforce into Tableau.
  6. Extracted data from our back-end billing and operational systems using Tableau’s SQL connector.

All the data is mixed together using cross-database joins and blending in Tableau.

Then, we leverage Tableau’s visual analytics platform to create a number of dashboards that allow for easy data exploration.

Reporting and filters

High-level reports are sent out via the Tableau subscription feature giving our TeamSnap marketing team a glance at how we’re doing every morning.

Email dashboards are helpful, but the really interesting data is found when users click through into Tableau Online. We’ve published a large number of filters that allow users to slice the data in nearly unlimited ways without any knowledge of the underlying data structures. Even though the filters are super easy to use, behind the scenes they are tapping into fairly complex data structures spanning multiple systems.

When marketers move to this type of system, it is eye opening with a realization you can squeeze a lot more out of each marketing dollar. You will quickly realize that within marketing campaigns that look profitable, there are highly profitable and unprofitable pools of spend.

Want to learn more? Listen to this webinar to hear how TeamSnap is driving ad ROI with Google + Tableau.

You might also be interested in...

Subscribe to our blog