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

February 2, 2017 | by

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

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