Marketing dashboard heat maps click-open Marketo

Marketo Office Hours - 06/02/2022

5mn   Beginner Level

Our marketing automation and CRM solutions are limited in terms of reporting, especially for marketing dashboards.

When it comes to annual reports, you need a Business Intelligence solution to produce advanced reports.

Let’s find out how easy it is to get started and make your first marketing dashboard.

Global architecture of the Business Intelligence environment to create a marketing dashboard

The simplest solution in terms of Business Intelligence would be to plug a reporting tool directly into Marketo, for example.

This wouldn’t be very relevant in terms of performance, as Marketo doesn’t have APIs for rapid export.

On the other hand, Marketo deletes part of the behavioral data after 90 days and 25 months, so the marketing dashboard would not be complete.

A Data Warehouse needs to be inserted between Marketo and the reporting solution to benefit from :

  • A Marketo backup
  • A complete history of Marketo behavioral data
  • High performance for real-time reporting

The different bricks for building a marketing dashboard

Tableau Software

This is a reporting solution belonging to the Salesforce galaxy, among the best on the market.

It will enable you to build any report you want from source data saved in the data warehouse.

Competitors include Clickview, PowerBI (Microsoft) and Looker.

This is the solution in which we’ll build the marketing dashboard.

The Snowflake data warehouse

We chose snowflake at Merlin/Leonard for our Data Warehouse. It’s a French solution that guarantees data hosted in Europe.

It’s a Cloud solution with a pay-per-use model and storage volume. It can be set up extremely quickly.

The Data Warehouse will contain the data on which Tableau Software will be able to work. It guarantees high export speeds.

ETL Fivetran

ETL stands for Extract – Transform and Load. It’s a solution that will connect to different sources like Marketo or Salesforce, and connect on the other side to databases like Snowflake.

The quality of an ETL depends on the number of sources to which it can connect natively, and the number of databases it can feed without development.

We chose Fivetran at Merlin/Leonard because it connects natively to Marketo and natively to Salesforce.

Creation of the marketing dashboard in Tableau Software




This is the first step, which is done simply by choosing Snowflake as the source, then specifying the login credentials.

You then access the databases and data schemas hosted in Snowflake.

For this example, I’ve chosen the Marketo schema.

Definition of the reporting universe

This is a step we don’t do when working in Salesforce or Marketo because reporting universes are defined in advance. For example, the “Account & Contacts” report in Salesforce.

Here, we need to select the database tables containing the data of interest. Then link these tables with the right keys.

In our example, we’ll link the following Marketo tables:

  • Leads each record being identified by the “Id” field
  • Activity_Click_Email which is the table containing Marketo’s activity log actions relating to clicks in emails. The link will be made via the table’s Lead_Id field

Report creation

Marketing dashboard - building in Tableau SoftwareTableau Software lets us simply choose the rows and columns to display in our report. Here I choose:

  • the activity date from which I retain only the time.
  • and I build the day of the week based on the same field thanks to a calculated field

I then choose to count the “Id” of the Activity_Click_Email table.

Tableau offers me different representations and I opt for the heat map.

I add filters and name my report.

I can do the same with email opens, then put the two reconciliations together, and I have my first Marketing dashboard. ✨🎉


Reporting must be in Marketing’s hands

It’s pretty much accepted everywhere that marketing needs to be in control of its marketing campaigns, hence the emergence of marketing automation solutions.

We’re not there yet for business intelligence, but that’s the way things are going.

I’m convinced that, in the next few years, marketing teams will have a firm grip on their business intelligence environment.

Of course, the IT department must be involved in :

  • the choice of solution, but speed of implementation and the fact that marketing can appropriate the solutions must be two essential criteria
  • compliance and security of solutions, since it’s company data that’s going to end up in third-party tools. But modern solutions offer satisfactory levels of security, with data stored in continental Europe
  • the IT department can control the perimeter of data authorized for export, to ensure that certain sensitive data does not leave the company.

But once these steps have been taken, marketing needs to be able to easily export data, combine it, build any report.

Marketing has come under increasing pressure in recent years to justify its role and its contribution to revenue. It’s only right to give it the tools to prove this.

Posted By Vincent

Vincent is a Marketing Automation consultant at Merlin/Leonard and a certified Marketo expert. He helps his clients set up their marketing campaigns and accompanies them on their Marketing Automation projects. He is Merlin/Leonard's main trainer.