How to Automate Marketing Analytics

by Alex Ortiz and Toby Hunter

The board meeting is next week, and my marketing analytics key metrics are all over the place.

What is marketing analytics for the CMO of a fast-growing company like me? Only the most important, and most insidious, thing that sneaks up on you during board meeting season. You’re knee-deep in hiring, aligning, and “doing marketing,” and probably don’t have a dedicated team just to assemble your board deck.

At prior companies, I found myself scrambling to pull data from many different marketing and analytics services such as Google AdWords/Analytics, Marketo, Salesforce, and others. Then I’d aggregate it, massage it into something presentable (Google Sheets, Google Charts, and others) and pray my data was in step with the CFO’s and sales. It rarely was. I’d spend 80% of my time on the tedious assembly process and 20% of the time actually thinking about what the data was trying to tell me. Sound familiar?

Even worse is when a board member asks a business question your metrics can’t answer. Over time, you start to look like you don’t have a good handle on the business. It’s not surprising that the average tenure of a CMO tends to be the shortest among the C-suite. But now, I no longer need to worry about manually compiling metrics. I’ve learned how to automate marketing analytics which saves an enormous amount of time. I can focus on the important stuff: developing a winning strategy with our board.

That’s why automation should be imperative among your marketing analytics goals. The best automation for analytics gives you the speed, accuracy, and completeness that comes from combining all your important marketing data that’s typically siloed across multiple channels. In this post, I’ll walk you through how we used the Tray Platform to automate marketing analytics.

Milestone Analysis

I’ll cover just one example report: volume or milestone analysis. We use this to answer basic questions, such as:

  • How many leads did we acquire last week?
  • What is the value of the pipeline that we created last month?
  • What was the lead source of our closed-won deals last quarter?

Time Spans of Analysis

To build your marketing analytics roadmap, I’d recommend starting with your audience in mind. Consider how granular your audience likes to see data. Our time spans for analysis and audience are as follows:

  • Quarterly: for board reporting
  • Monthly: for executive team reviews
  • Weekly: for marketing team reviews
  • Daily: for individuals managing an area of the business, e.g. paid acquisition

Six steps to automate revenue funnel reporting

1. Define. First, you must define lead status, opportunity stages, and other key milestones of your revenue funnel. Use locked timestamps for when a lead or opportunity passes each revenue funnel milestone. Make sure that the entire business understands these definitions.

2. Create operational reports. Create operational reporting in each tool. This will give you the ability to validate data from your business intelligence (BI) reports with your source systems. For example, I have a full funnel dashboard with 20 dashlets in Salesforce. Why not use Salesforce dashboards in board reports? For one, it’s hard to add calculations like period-over-period growth rate or stage-to-stage conversion rates. It’s also not easy to add target KPIs into Salesforce dashboards. Finally, Salesforce doesn’t report on the top part of the revenue funnel, like website analytics.

3. Set up a data warehouse. It used to be that only big companies could afford a data warehouse and a team to set up and maintain it, but with the advent of cloud data warehouses like Amazon Redshift, Snowflake, and others, companies of all stripes are adopting this powerful data tool every day.

4. Automate the flow data into your data warehouse. Now, you need to actually pipe data from source systems into your data warehouse. Naturally, we use the Tray Platform to connect various marketing tools and stream data into our data warehouse via APIs. We also use a customer data platform, Segment, which makes it easy to pull data from Salesforce, Facebook, and Google AdWords and automatically creates the tables that we need in Redshift. The Tray Platform has connectors that Segment does not, so that is why we use both platforms in a complimentary way. With the Tray Platform you have the flexibility to pull data in real-time whereas Segment pulls data a couple of times daily.

Tray Connectors used:

  • Webmaster tools (Google Search Console integration)
  • SEO tools (Ahrefs integration)
  • Social media platforms (LinkedIn integration, Twitter Integration)
  • Retargeting (Adroll integration)
  • Website analytics (Google Analytics integration)
  • CRM (Salesforce integration)
  • Data Warehouse (Amazon Redshift integration)
  • BI and Data Visualization (Periscope Data integration)
  • Forecasting and Modeling (Google Sheets integration)

Here’s an example workflow that automates the flow of data from Google Analytics into Redshift:

Here is our workflow that automates the flow of data from Google Search Console into Redshift:

5. Data visualization/business intelligence (BI). Many cloud BI tools sit on top of cloud data warehouses. We happen to use Periscope Data. You’ll need someone on the team with some SQL skills for this step. Here are a couple of reports out of many that we use to manage the business.

Periscope Data - Revenue Funnel Dashboard

6. Automate flow of aggregated data into your modelling tool. Now that you have all of the metrics aggregated in one place (Redshift) and accessible (Periscope Data) It’s time to automate the flow of data to your analysis tool of choice. BI tools are generally not designed to support ad hoc analysis by a business person. I like using Google Sheets so I can easily model scenarios, add targets, and forecast. We use the Tray Platform to automate the flow of data from Redshift/Periscope Data into Google Sheets.

7. Automate flow of data to your presentation tool. I don’t like sharing raw spreadsheets to the exec team or board because they don’t tell a story. That’s why I prefer using Google Docs or Google Slides for our board pack so I can share my interpretation alongside the data. Now Google makes it easy to embedded Google Sheets into Docs and Slides. Refreshing is easy with the little “UPDATE” button.

Recap - Look forward to board meetings with automated marketing analytics

Now that I have this automated, I no longer spend time gathering data. I no longer wring my hands over building a presentable marketing analytics visualization to share with the board. I walk into board meetings better prepared to discuss where we are struggling and where we are excelling. We spend a lot more of our collective brainpower on where we want to take the business and what we’ll need to get there.

Let us know if you’d like to clone any of our workflows into your Tray Platform instance. We would be happy to share them with you. If you’d like to see how you too can automate marketing analytics, request a demo of the Tray Platform.

Get updates from the Tray.io blog directly to your inbox

All the latest product news, tips and tricks direct to your inbox weekly. You can unsubscribe when ever you want!