Extend Marketo Business Logic with Google Cloud Functions

This article proposes a solution to extend Marketo with some business logic capabilities with Google Cloud Platform (GCP), based on the following simple example:

3 custom fields on the Marketo Lead record:

  • OnLinePreference: an incremental score that indicates a prospect/customer appetence for online communications.
  • OfflinePreference: an incremental score that indicates a prospect/customer appetence for offline communications.
  • Preference: a field computed by GCP that displays “offline’ if the offline score is higher than the online one, and “online” the other way around

GCPFunctions8

This technology opens the way for more advanced business logic and eventually for calling out external web services, transforming and consolidating the results in Marketo.

About Google Cloud Platform and Functions

GCPFunctions0

Google Cloud Platform (GCP) is a suite of cloud computing services that runs on the same infrastructure that Google uses internally for its end-user products, such as Google Search and YouTube. Alongside a set of management tools, it provides a series of modular cloud services including computing, data storage, data analytics, machine learning, big data and much more.

We could have used many different GCP services for our need, such as Compute Engine, App Engine or Kubernetes Engine, but we opted for the Cloud Functions (still in Beta) for the following main advantages:

  • Serverless cloud computing where logic can be spun up on-demand in response to events such as HTTP calls.
  • Relieves most of the pain caused by server maintenance and deployments.
  • Cost effective, as you pay GCP only for each function call and not for keeping a server up and running.
  • Simple and fast to implement as you focus only on your application logic.
  • Automatic scaling, ready for very high workloads.

Please check GCP web site for more information about this technology and its pricing. Typically, this tutorial should not induce any important cost and will fit perfectly within the free credit of a GCP trial.

Preparation of your Google Cloud environment

You need a Google Cloud account. You can try GCP for free with a credit that is more than enough to run this tutorial, just click “Try it free” button on the GCP web site.

Follow all the steps from the section ‘Before you begin’ in the HTTP Tutorial from Google:

  1. Create a Cloud Platform project: GO TO THE MANAGE RESOURCES PAGE
    • GCPFunctions1
  2. Enable billing for your project: ENABLE BILLING
  3. Enable the Cloud Functions API: ENABLE THE API
  4. Install and initialize the Cloud SDK
  5. Update and install gcloud components
    gcloud components update &&

    gcloud components install beta

  6. Prepare your environment for Node.js development: GO TO THE SETUP GUIDE

Implementation of the scoreCompare Cloud Function

    1. Create a Cloud Storage bucket to stage your Cloud Functions files. You can do it with the command line:
      gsutil mb gs://[YOUR_STAGING_BUCKET_NAME]

      or from the Google Cloud web interface, by selecting your project and clicking the Storage menu:

      • Give your Storage bucket a unique name
      • Select the default storage class
      • Select the best suited regional locationGCPFunctions4
    2. Create a directory on your local system for the application code.
    3. Create an ‘index.js’ file in this directory with the following JavaScript code: the code is really simple to understand. It parses the two input parameters from the HTTP request body in JSON, does the processing and encodes in JSON the HTTP response.

 

/**
 * HTTP scoreCompare Cloud Function.
 *
 * @param {Object} req Cloud Function request context.
 * @param {Object} res Cloud Function response context.
 */
exports.scoreCompare = function scoreCompare (req, res) {
 var onlineScore=parseInt(req.body.onlineScore);
 var offlineScore=parseInt(req.body.offlineScore); 
 console.log('/scoreCompare: got values onlineScore =' + onlineScore + ', offlineScore =' + offlineScore);
 var result;
 if (onlineScore>offlineScore) {result = 'online';} else {result = 'offline';}
 console.log('/scoreCompare: and result is ' + result);
 res.status(200).json({output: result}).end();
};

Deploy the function scoreCompare with an HTTP trigger. Run the following command from your directory:

gcloud beta functions deploy [FUNCTION] –stage-bucket [YOUR_STAGING_BUCKET_NAME] –trigger-http

where [YOUR_STAGING_BUCKET_NAME] is the name of your staging Cloud Storage bucket.

In our example:

gcloud beta functions deploy scoreCompare –stage-bucket mktostorage –trigger-http
  1. Note the Cloud Function URL (httpsTrigger URL) from the console output, that looks like this: https://%5BYOUR_REGION%5D-%5BYOUR_PROJECT_ID%5D.cloudfunctions.net/%5BFUNCTION%5D where
    • [YOUR_REGION] is the region where your function is deployed. This is visible in your terminal when your function finishes deploying.
    • [YOUR_PROJECT_ID] is your Cloud project ID. This is visible in your terminal when your function finishes deploying.
    • [FUNCTION] is your function name.

    In our example:

    https://us-central1-marketo-cloud-logic.cloudfunctions.net/scoreCompare

  2. Test your function with a tool like Postman:

Call the Cloud Function from a Marketo’s Webhook

The three following custom fields must be created on the Lead record in Marketo:

  • OnlinePreference: Integer
  • OfflinePreference: Integer
  • Preference: String

Create the following webhook from the Marketo admin interface by using your ‘scoreCompare’ cloud function URL and the custom field’s tokens:

GCPFunctions5

Test the webhook with a Marketo triggered smart campaign. A smart list and flow are showed here after, as an example:

GCPFunctions6

GCPFunctions7

  • Marketo webhooks can only be invoked from triggered smart campaigns, not batch smart campaigns.
  • If you do not use your cloud function, delete it or delete the whole project, in order to avoid incurring charges to your Google Cloud Platform account.

Conclusion

We hope this tutorial was worth your time and that it will make you think about more advanced scenarios involving complex processing and 3rd party services.

A good example would be to leverage Google Cloud AI, the machine learning services from Google. You could, for example, parse some free text from a Marketo form and ask Google Natural Language API to reveal the structure and meaning of the text and then save back this analysis in Marketo; just opening the floodgates for ideas.

Internet of Things for Marketers with IFTTT and Zapier

The Internet of Things (IoT) is the inter-networking of connected devices, appliances, wearables, vehicles, etc. with embedded electronics, software, sensors, and network connectivity that enable these objects to collect and exchange data with cloud information systems. These technologies are growing and trending so fast that they will impact how we live, how we work and how we do business in no time.

Marketo the leading Marketing Engagement Platform is ready for the IoT with its capabilities to scale and interact with any form of communication channel. Marketo can track already over 70 types of activities related to emails, web, mobile, CRM, etc … and it supports also custom activities that can be fed by any 3rd party system. Marketo custom objects make possible to track all kinds of 3rd party metrics related to your business, and allows marketers to leverage those metrics right from Marketo smart campaign filters and triggers.

Implementing IoT for consumers would require a centralized server to interact with consumer devices and this server would exchange data with the Marketo open platform, with capabilities such as REST API, Custom Objects, Custom Activities, etc. – documented here. Not easy to demonstrate via a blog post.

Instead of that, we are going to integrate the IFTTT Service with Marketo in order to implement some cool IoT use cases for the Marketers like:

  • Cheering up your Marketing team each time a lead is registering to a road show by blinking a colored light in the office
  • Cheering up your Sales team each time a deal is won by automatically firing up a bell plugged to a connected power plug
  • Automatically post Marketing success milestones on social networks such as LinkedIn, Facebook, Slack, etc …
  • Automatically launch a Marketing Campaign based on:
    • when a weather alert occurs (wind, temperature, rain, etc.)
    • when a new article is published by a newspaper such as the New York Times, matching some specific criteria
    • when the U.S. Senate or House of Representatives votes
    • when the International Space Station passes over a certain location
    • etc …

You might find those scenarios fun but useless, but they are here to demonstrate a new conceptual way to do Marketing not only with people, but also with things in our connected world.

Another interesting point covered in this article, is how to leverage an open web integration platform such as Zapier as a “serving hatch” between a 3rd party system and Marketo, to manage the authentication for example.

 

About IFTTT

The IFTTT Service

IFTTT is an acronym for “IF This Then That”. It is a free web-based service that people use to create chains of simple conditional statements, called applets. An applet is triggered by changes that occur within some partner web services and as a result, actions are sent to other partner web services. IFTTT was launched on 2011 by Linden Tibbets, Jesse Tane, Scott Tong, and Alexander Tibbets in San Francisco.

At first sight, IFTTT looks similar to a service like Zapier for example, it has a much stronger focus on consumers and IoT devices (remotes, alarms, lights, thermostats, cars, printers, mobile phones, and so much more).

IFTTTMedia00

First of all, you must create an account for IFTTT from the IFTTT web site. Feel free to discover all the cool applets already available as that will give you some other scenarios ideas for sure!

The Maker Channel

A web application that does not have a channel, meaning a partnership with IFTTT, must use the Maker Channel.  With the Maker Channel, you can create Applets that work with any device or app that can make or receive a web request. It offers the following integrations:

  • Inbound Triggers to receive web request from a 3rd party system in order to trigger an action
  • Outbound Actions to make a web request to a 3rd party system publicly accessible on the Internet

From IFTTT, search for the “Maker” service and click on it.

IFTTTMedia01

The first time, you will need to activate it by clicking on the “Connect” button.

IFTTTMedia02

Now the Maker Channel is active.

You can obtain your secret key by clicking on the Maker Settings button:

IFTTTMedia03

Copy and paste the provided URL to your browser for more details.

 

Triggering Directly an IFTTT Action from Marketo

First, we are going to focus on triggering all kinds of 3rd party web service actions from Marketo.  For that we are going to use a Marketo Webhook. We’ll start with a push message on your mobile phone or tablet via the IFTTT mobile app, and then we’ll implement a IoT scenario blinking a Philips Hue light.

Marketo Webhook

To trigger an event from Marketo, acting as the “if” of IFTTT, is simple. All you need to do is send a POST web request to IFTTT with an event name and your secret key, following this pattern URL:

https://maker.ifttt.com/trigger/{event_name}/with/key/{secret_key}

The Maker makes it also possible to communicate up to 3 parameters via the web request.  This can be done using query parameters,

https://maker.ifttt.com/trigger/{event_name}/with/key/{secret_key}?value1={value1}&value2={value2}&value3={value3}

or using a JSON body consisting of up to three values:

{

“value1” : “{value1}”,

“value2” : “{value2}”,

“value3” : “{value3}”

}

In Marketo, create a new Webhook from the Admin interface.

IFTTTMedia04

Provide the following information for your new Webhook:

Webhook Name: IFTTT Program Success

Description: Trigger an event on IFTTT from a Smart Campaign for a Program Success

URL: https://maker.ifttt.com/trigger/{event_name}/with/key/{secret_key}?value1={{program.name}}&value2={{lead.Email Address}}&value3={{lead.Full Name}}

event_name, use MarketoProgramSuccess for example

secret_key, use the secret key from your IFTTT Maker Service

Use static text or Marketo tokens for the three available values. You can push more interactive messages by defining your own tokens at the program level and pass them through these values.

Request Type: POST

Template: Leave blank

Request Token Encoding: Form/Url

Response type: None

No need to define a response mapping.

IFTTT Applet

In IFTTT web portal, select “My Applets” in the main menu.

IFTTTMedia05

Click the button “New Applet” and click on the section +this.

IFTTTMedia06

Search for the Maker service.

IFTTTMedia07

Create the Trigger that will fire every time the Maker service receives a web request to notify it of an event.

Use the same Event Name as the one specified in the URL of your Marketo Webhook, e.g. “MarketoProgramSuccess” and click the “Create trigger” button.

IFTTTMedia08

Now it is time to specify the Action Service by clicking the section +that.

IFTTTMedia09

We are going to start with a simple action service that anyone would be able to test without having to invest in any IoT devices, the Notifications Service.

Search for and select the Notifications Service.

IFTTTMedia10

Choose the action “Send a notification” that will send a notification to your devices.

IFTTTMedia11

You can leverage the 3 values you have sent from Marketo by adding them as Ingredients in order to deliver a meaningful notification to the user, just like the example below …

IFTTTMedia12

And then click the button “Create action”.

Review and finish the IFTTT Applet. Make sure it is enabled.

IFTTTMedia13

Testing the IFTTT Applet

If you want to get notified on your Mobile, you must first download the IFTTT app for your device.

IFTTTMedia14

You can trigger a Marketo Program Success event by using the Webhook in a Marketo Smart Campaign Flow. Remember that Marketo Webhooks work exclusively with triggered Smart Campaigns (e.g. trigger once a contact filled-out form, was added to a list, etc.).

IFTTTMedia15

And here is an example of an IFTTT notification on your mobile phone.

IFTTTMedia16

Let’s Get Creative with IFTTT

IFTTT offers Applet Actions with over 300 partners, so your portfolio of apps and appliances together with your imagination are the limits …

IFTTTMedia17

Let’s take an example with the Hue lights from Philips that you can buy anywhere in electronics shops or online.

The following applets would blink one of your lights with its current assigned color when Marketo triggers a program success, that could boost your marketing team in the office.

We create a new Applet, following the same steps as before, where Marketo is triggering with a webhook, but this time we choose the action from Philips Hue service.

Let’s select the “Blink lights” action.

IFTTTMedia18

The app will request from Philips Hue all your available lights, so you can pick the one to blink. You would need to set up an account with Philips Hue first, the Hue bridge and of course at least one Hue bulb, light strip, projector or lamp.

IFTTTMedia19

We just added a new Applet that will blink a colored light each time a lead is registering to a roadshow or webinar. Your Marketing team will cheer up every day with that setup in the office.

 

Executing a Marketo Action from IFTTT, via Zapier

Now, we are going to trigger a Marketo Smart Campaign from the IFTTT Platform. For that we are going to use the Marketo REST API. Since this API is secured and requires an OAuth2 Authentication prior to invoke anything, we need to handle that authentication via another platform such as Zapier, because IFTTT doesn’t allow to chain two consecutive calls on an API with the Maker Channel.

We picked Zapier web app Automation Service since we published already this blog post introducing Zapier and explaining step by step how to implement a custom Marketo connector for Zapier. Other platforms such as Workato could be a solution too.

Marketo Campaign

Create your Marketo Program with a scheduled Smart Campaign.

For test purpose, you could create the following Smart Campaign as an example:

Smart List

IFTTTMedia21

Use only filters, not triggers. Make sure at least you would qualify.

Flow

IFTTTMedia22

Send you an email or any another kind of notification.

Schedule

IFTTTMedia23

Make sure you can run through the flow every time in order to handle your multiple tests.

You can obtain the Smart Campaign Id from the URL. Example: https://{{marketo_url}}/#SC4289A1 – the Smart Campaign Id would be 4289.

You can trigger this campaign via the Marketo REST API. You can use for example the Postman plugin for Chrome and send the 2 following consecutive HTTPS calls:

Authentication step:

https://{{Your Munchkin_Account_id}}.mktorest.com/identity/oauth/token?grant_type=client_credentials&client_id={{Your_Client_Id}}&client_secret={{Your_Client_Secret}}

Recover the access token from the JSON response.

Campaign kick-off step:

https://{{Your_Munchkin_Account_id}}.mktorest.com/rest/v1/campaigns/{{Campaign_Id}}/schedule.json?access_token={{access_token}}

Intermediate Zapier Custom Connector to Launch the Marketo Campaign

We need to build a custom Zapier connector that authenticates with the Marketo REST API and kicks off our Smart Campaign. For that we are re-iterating all the following steps from our previous article ‘Seamless Integration with Marketo and over 500 Apps using Zapier’:

  • Prerequisites
  • Implementation of the Marketo Connector for Zapier
  • Use a different title such as “Marketo Campaign”
    • Do the “Authentication” step
    • Do the “Triggers” step (required for Zapier testing purpose)
    • Do the following specific “Actions” step, responsible to launch a Marketo campaign, explained below:
Action

Click Add New Action to start the wizard and fill-up the following fields (fields not mentioned can be left blank):

Name and Description

Name Noun Key Description Important? Hide?
Launch Campaign Campaign launch_campaign Launch a Smart Campaign in Marketo Checked Not Checked

Action Fields

Label Key Type Required?
Campaign Id CampaignId Unicode Checked

Where to Send Data

Action Endpoint URL :

https://{{munchkin_account_id}}.mktorest.com/rest/v1/campaigns/{{CampaignId}}/schedule.json

Leave blank the other optional fields.

Scripting API

Zapier’s scripting feature allows you to manipulate the requests and responses that are exchanged between your app’s API and Zapier. You can modify HTTP requests just before they are sent and can parse responses before Zapier does anything with them. We need it in order to complete our custom ‘Session Auth’ authentication.  More information is available on the original article.

Copy the following code very similar to the original, we just changed the action methods:


var Zap = {
 
 get_session_info: function(bundle) {
 
 console.log('Entering get_session_info method ...');
 
 var access_token,
 access_token_request_payload,
 access_token_response;

 
 // Assemble the meta data for our Access Token swap request
 console.log('building Request with client_id=' + bundle.auth_fields.client_id + ', and client_secret=' + bundle.auth_fields.client_secret);
 access_token_request_payload = {
 method: 'POST',
 url: 'https://' + bundle.auth_fields.munchkin_account_id + '.mktorest.com/identity/oauth/token',
 params: {
 'grant_type' : 'client_credentials',
 'client_id' : bundle.auth_fields.client_id,
 'client_secret' : bundle.auth_fields.client_secret
 },
 headers: {
 'Content-Type': 'application/json', // Could be anything.
 Accept: 'application/json' 
 }
 };

 // Fire off the Access Token request.
 access_token_response = z.request(access_token_request_payload);

 // Extract the Access Token from returned JSON.
 access_token = JSON.parse(access_token_response.content).access_token;
 console.log('New Access_Token=' + access_token);
 
 // This will be mixed into bundle.auth_fields in future calls.
 //bundle.auth_fields.access_token=access_token;
 return {'access_token': access_token};
 },
 
 test_trigger_pre_poll: function(bundle) {
 
 console.log('Entering test_trigger_pre_poll method ...');
 
 bundle.request.params = {
 'access_token':bundle.auth_fields.access_token
 };
 
 return bundle.request;
 
 },
 
 test_trigger_post_poll: function(bundle) {
 
 console.log('Entering test_trigger_post_poll method ...');
 
 var data = JSON.parse(bundle.response.content);
 if ((!data.success)&&((data.errors[0].code=="601")||(data.errors[0].code=="600"))){
 console.log('Access Token expired or invalid, requesting new one - data.success=' + data.success + ', data.errors[0].code=' + data.errors[0].code);
 
 throw new InvalidSessionException(); // Calling get_session_info() to regenerate Access Token
 }

 return JSON.parse(bundle.response.content);
 },
 
 launch_campaign_pre_write: function(bundle) {
 
 bundle.request.params = {'access_token':bundle.auth_fields.access_token}; 
 return bundle.request;
 },

 launch_campaign_post_write: function(bundle) {
 
 var data = JSON.parse(bundle.response.content);
 if ((!data.success)&&((data.errors[0].code=="601")||(data.errors[0].code=="600"))){
 console.log('Access Token expired or invalid, requesting new one - data.success=' + data.success + ', data.errors[0].code=' + data.errors[0].code);
 throw new InvalidSessionException(); // Calling get_session_info() to regenerate Access Token
 }
 return JSON.parse(bundle.response.content);
 }
 
};
New Zap

From the Zapier Dashboard click the button “Make a new Zap”.

Trigger

  • Pick the “Webhooks by Zapier” Trigger App
  • Check “Catch Hook”
  • No need to pick off a child key
  • Zapier generated a custom webhook URL for you to send requests to, keep it safe somewhere
  • Test the webhook URL, by starting the “IFTTT Applet that calls the Zapier Webhook” scenario below. That will allow Zapier to learn about the webhook payload and to let you assign the campaign Id to the Action

Action

  • Select the Marketo Campaign connector previously created
  • Choose the only action available: Launch Campaign
  • Connect to your Marketo account, filling up the authentication parameters (Munchkin Account Id, Client Id, Client Secret)
  • Edit the Template and associate the Campaign ID from the Trigger to the “Launch Campaign” Campaign Id parameter

  • Test the step and check that the Marketo Campaign get launched

IFTTT Applet that Calls the Zapier Webhook

We start with a simple scenario that is easy to test. We pick in IFTTT a Date & Time trigger that will kick off the Marketo Campaign every hour.

The Action is a web request posting to the Zapier Webhook URL and passing over the Smart Campaign Id.

Make sure the Zapier Zap and the IFTTT Applet are both active and test everything is working as expected.

Let’s get Creative with IFTTT

IFTTT offers Applet Triggers with over 300 partners, so again your portfolio of apps and appliances together with your imagination are the limits …

Let’s take an example with the Weather Underground service that we are going to use to launch our Marketo campaign on weather alert.

The following trigger would kick-off when a Rain condition is announced.

And then associate the Trigger with the Maker Webhook Action, and just like previously fill in the Zapier webhook parameters.

Et voila, you just need now a good rain to come to double check this is actually working.

 

Conclusion

We hope you’ll have a lot of fun applying the concepts provided in this article. But most important, we think it will help anybody wanting to integrate Marketo with other 3rd party systems, thanks to the key concepts from this article:

  • Marketo REST API
  • Marketo Webhooks
  • How to leverage an open web integration platform such as Zapier as a “serving hatch” between a 3rd party system and Marketo, to manage the authentication for example

Excel Integration Part 2: Build Advanced Marketo Reports and Data Visualizations Using Power Pivot and Power View

This is the second in a series of two articles that explain how to leverage the Power BI technology built into Microsoft Excel to create a true self-service business analytics experience with Marketo.

blog-powerquery-intro

With the concepts covered in these articles, you’ll be able to:

  • Import data from Marketo into Excel
  • Import and combine data from other sources (SaaS applications, databases, flat files, etc.)
  • Shape data for business needs and analysis purposes
  • Refresh data on demand within Excel
  • Create calculated columns and measures using formulas
  • Create relationships between heterogeneous data
  • Analyze data and build advanced reports with Pivot Tables and Pivot Charts
  • Produce stunning data visualizations

Here is a short video that highlights all the steps and benefits documented in these two articles:

 

Power Pivot and Power View for Excel

In this article, we provide examples of how to build the following:

  • Advanced Marketo reports that leverage relationships between different collections of Marketo data using Power Pivot
  • Cool static and animated visualizations using Power View

Power Pivot is an Excel add-in, already included in Excel 2016, you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. Data extracted from different data sources with Power Query can be sent to the data model, to the Excel spreadsheet, or to both. In the first article, we imported and shaped data from Marketo and sent it to the data model in order to perform more sophisticated analysis prior to making it available on the spreadsheet.

Power View is an alternative to the Excel visualization layer. It is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting and dashboarding.

All of the steps explained in this article have been tested on Excel 2016 for Windows. The concepts should be the same for Excel 2013 or Excel 2010 (without Power View) but some adaptations could be required. Power Pivot and Power View are currently only available on the Microsoft Windows version of Excel 2016, the Office 365 version of Excel 2016 does not support fully Power Pivot and Power View.

 

The Marketo Power Workbook

Download Workbook

In the first article, we covered the data import and shaping process using Power Query technology. We learned how to implement some advanced power queries in order to extract leads and activities from Marketo. Because some of you would want to jump directly to the point where they build reports and visualisations, without coding, we released the Marketo Power Workbook that you can download here.

This workbook contains all the queries detailed in the first article and a few more. We improved the error handling and added some extra parameters in the configuration worksheet. If you went through the first article, we still recommend you to download the Marketo Power Workbook and check out what has been added.

Disclaimer: The Marketo Power Workbook is not official Marketo Product, and therefore is not supported by by Marketo. Feel free to use and expand for your personal business needs, but do so at your own risk.

Configure Workbook

Please check out the Prerequisites and the Power Query Workbook Creation sections from the first article in order to understand the prerequisites and how to configure the Marketo configuration worksheet.

Fill in all of the required information from the Marketo configuration worksheet:

  • Marketo REST API Authentication: required
  • Scoping: set the Paging Token SinceDatetime and the Id of your Marketo static list containing all the leads you want to analyze
  • Leads: for the reports to come, you must at least specify the following Lead fields: id, firstName, lastName, email, createdAt, updatedAt, title, company, industry, inferredCountry, inferredCity
    • If the city information is more accurate in one of your custom fields, then you can use your own field instead
  • Activities: Activity types to fetch from the Marketo database are specified here for each Activity set, no need to change this now.
    • Note that we provided a utility query on the workbook that lists, right on the Excel workbook, all the existing Activity types if you want to adjust this information later on

ExcelIntegration-Blog2-Image1

Note that you may see some security related pop-ups. Trust external connections and set them to ‘Public’.

If you see the pop-up below, stay with ‘Anonymous’ web access content. The authentication to Marketo is directly managed by our custom queries, so no need to enable any other kind of access.

ExcelIntegration-Blog2-Image2

 

Download Marketo Data

Make sure first that the parameter you define in the Scoping area of the Marketo configuration worksheet will not result in downloading too much data, exceeding your Marketo API daily request limit (see first article).

When ready, click the ‘Refresh All’ button from the ‘Data’ menu and wait that all the data is downloaded into the workbook.

ExcelIntegration-Blog2-Image3

If formatting error messages are displayed when downloading the data, similar to ‘column1 not found’, that means one or more queries are failing to get the data, so the formatting is also failing. Try again later on, if the error persists, then check your version of Excel (do not use Excel 2016 from Office 365).

It is important also to respect the latency from the Marketo platform. If you do any changes in a static list, or in your lead data, then it is preferable to wait before launching the Power Queries.

 

Data Modeling in Power Pivot

Open Power Pivot by clicking the ‘Manage’ button from the Power Pivot menu, available in the top menu bar (if not available check your version of Excel, Power Pivot can be installed as an add-in in some versions of Excel).

ExcelIntegration-Blog2-Image4

All the data downloaded from Marketo and sent to the data model should be accessible from the different tabs at the bottom of the Power Pivot window.

ExcelIntegration-Blog2-Image5

Data Analysis Expressions (DAX)

We need to enrich or reformat the data for some reports. Let’s use Power Pivot Data Analysis Expressions (DAX) to define some custom calculations as calculated columns and measures (also known as calculated fields). See the ‘DAX in Power Pivot’ link in the References section to learn more about DAX.

Make sure the Calculation Area is showing in the Power Pivot window; if not, enable it from the Power Pivot Home menu.

ExcelIntegration-Blog2-Image7

Select the MktoLeads tab and add the Leads Count measure anywhere in the Leads Calculation Area: Leads Count:=DISTINCTCOUNT([id]).

This measure is counting the distinct leads available in the list, based on their id. It would also take into account the eventual filters in place in the context of a report.

This measure is not really necessary since the reports are capable of summing up the number of leads but we did it in order to have a lead count with a nicer name than ‘sum of MktoLeads’. It is also a simple example that let you easily imagine some more complex measures doing averages, min, max for a specific type of data entry (e.g. all the leads with a score higher than 50, average score, etc …).

ExcelIntegration-Blog2-Image8

Now let’s select the MktoWebActivities tab and create three calculated columns.

Insert the following calculated columns by scrolling to the far right of the table and by clicking the column ‘Add Column’.

ExcelIntegration-Blog2-Image9

Activity: Obtain the user-friendly Activity label by looking up the Activity Id in the table MktoActivtyTypes.

=LOOKUPVALUE(MktoActivityTypes[name],MktoActivityTypes[id],[activityTypeId])

Year-Month: reformat the Activity date with a pattern ‘YYYYmm’ that is more suitable for some reports.

=LEFT([activityDate],4)&MID([activityDate],6,2)

Date: Activity Date is just a String from our original query, transform it to a proper date.

=DATE(LEFT([activityDate],4),MID([activityDate],6,2),MID([activityDate],9,2))

Now let’s create the three same measures for the MktoEmailActivities tab, and 2 additional ones:

ExcelIntegration-Blog2-Image10

Campaign: Obtain the user-friendly Campaign name by looking up the Campaign Id in the table MktoCampaigns.

=LOOKUPVALUE(MktoCampaigns[name],MktoCampaigns[id],[campaignId])

Program: Obtain the user-friendly Program name by looking up the Campaign Id in the table MktoCampaigns. The table MktoPrograms can provide more details about the Program such as folder, workspace, etc.

=LOOKUPVALUE(MktoCampaigns[programName],MktoCampaigns[id],[campaignId])

Entity-Relationships

We saw previously a way to lookup information from another table within the model in order to complete some missing information. Power Pivot offers a more powerful option to define the relationships between some tables of the data model, allowing us to leverage those relationships directly from the reports. Let define the key relationships for our reports.

Select the Diagram View from the Power Pivot window.

ExcelIntegration-Blog2-Image11

Trace the following relationships within the Data model diagram:

  • MktoInterestingMomentActivities:leadId → MktoLeads:id
  • MktoScoringActivities:leadId → MktoLeads:id
  • MktoRevenueStageActivities:leadId → MktoLeads:id
  • MktoWebActivities:leadId → MktoLeads:id
  • MktoEmailActivities:leadId → MktoLeads:id

ExcelIntegration-Blog2-Image12

We’ll not use all of these relationships and objects in our reports, only the Leads, Web Activities and Email Activities.

Now it’s time to build some reports.

 

Emails Performance Pivot Chart

This first report is showing email performance KPIs based on a standard Excel Pivot Chart. It allows us to filter data by Industry and/or Campaign.

You can create a Pivot Chart right from the Power Pivot menu by selecting ‘Pivot Chart’ from the ‘Pivot Table’ selector.

ExcelIntegration-Blog2-Image13

An alternative is to create a Pivot Chart directly from the Excel spreadsheet, ticking the option ‘Use this workbook’s Data Model’.

ExcelIntegration-Blog2-Image14

Drag and drop the fields from the MktoEmailActivities and the MktoLeads tables, like the figure below:

MktoEmailActivities.Activity → Legend (this use the DAX calculated column we implemented on MktoEmailActivities earlier)

MktoEmailActivities.Date → Axis (this use the DAX calculated column we implemented on MktoEmailActivities earlier)

MktoEmailActivities.Id → ∑ Values

MktoEmailActivities.Campaign → Filter

MktoLeads.industry → Filter

ExcelIntegration-Blog2-Image15

You can create custom name by selecting ‘Value Field Settings’ on each dropped field. In this case, we dropped the Email Activity id field into the ‘∑ Values’ section and edited its custom name as ‘Number of Activities’.

ExcelIntegration-Blog2-Image16

Now let’s configure the Pivot Chart. Right click directly on the chart and select the ‘Change Chart Type’ option in the contextual menu.

ExcelIntegration-Blog2-Image17

And this is how we selected the different chart type for all data series.

ExcelIntegration-Blog2-Image18

 

Leads Map with Power View

The second report displays your Leads and Contacts by geography on a world map and by Industry.

We’ll need Power View for this report. Please follow the reference link below ‘Turn-on Power View in Excel 2016’ in order to turn on the menu in Excel. Or you can just type ‘power view’ in the Excel search box.

Select ‘Insert a Power View Report’.

ExcelIntegration-Blog2-Image19

On the blank Power View report, select the MktoLeads table on the right panel and drag & drop the lead location field (e.g. inferredCity). Now the menu ‘Design’ appear in the main menu.

ExcelIntegration-Blog2-Image20

Switch to the Map visualization by selecting ‘Map’ in the Power View ‘Design’ menu.

Drag and drop the fields from the MktoLeads table, like the figure below:

MktoLeads.industry → Color

MktoLeads.inferredCity → Locations

MktoLeads.Leads Count → ∑ Size (this use the DAX measure we implemented on MktoLeads earlier)

ExcelIntegration-Blog2-Image21

And your Leads map is ready! You just need to adjust the size of the map, customize the title and legends.

Power View allows you to build advanced dashboards with multiple graphs on one single spreadsheet. Check out the referenced tutorial below ‘Create Amazing Power View Reports’ to see how to proceed with more dashboard components with Power View.

 

Web Activities Animated on a 3D Map

This third report displays your Lead web activities, by industry, on a 3D world map.

We’ll need a 3D Map for this report. Just type ‘3D’ in the Excel search box and select ‘3D Map’.

ExcelIntegration-Blog2-Image22

Create a new tour from the pop-up window.

ExcelIntegration-Blog2-Image23

Select the Bubble Chart on the right panel.

ExcelIntegration-Blog2-Image24

Drag and drop the fields, from the MktoLeads and the MktoWebActivities tables, like the figure below:

MktoLeads.industry → Category

MktoLeads.inferredCity → Location

MktoWebActivities.Activity → Time (this use the DAX calculated column we implemented on MktoWebActivities earlier. The id field could also be used for counting activities.)

MktoWebActivities.Date → Time (this use the DAX calculated column we implemented on MktoWebActivities earlier)

MktoWebActivities.Activity can also be used as a filter to filter out the different types of web activities.

ExcelIntegration-Blog2-Image25

Use the ‘Themes’ button in order to change the color scheme of your 3D Map.

ExcelIntegration-Blog2-Image26

Open the ‘Scene Options’ in order to customize your animations.

ExcelIntegration-Blog2-Image27

And you’re done with the 3D World Map, now you can have fun animating the globe and creating video from it. 

 

Next Steps

We just scratched the surface of what is possible to do with the Excel Power BI tools. We recommend you to search the web for other great articles and tutorials to expand your Excel skills and design the reports you need to achieve your business goals.

We hope you enjoyed these articles and that they helped you leverage the great benefits of Excel and Marketo combined.

 

References

Power Pivot

Power View