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

Excel Integration Part 1: Extract & Shape Marketo Data Using Power Query

This is the first of 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 purpose
  • Refresh on demand the data from 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 Query for Excel

This first article covers the data import and shaping process using Power Query technology. Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop.

Power Query can connect to many data sources such as databases, Facebook, Salesforce, MS Dynamics CRM, etc. Marketo isn’t supported out of the box, but fortunately we can use Marketo REST APIs for remote execution of many of the system’s capabilities, and Power Query comes with a rich set of formulas (informally known as “M”) allowing you to script a custom data source.

 

Custom Connector

Scripting a single REST API call is trivial with Power Query, but it becomes more challenging to handle the following requirements:

  • Access token management including authentication mechanism and periodic token refresh
  • Pagination mechanism for large set of data
  • Error handling

This article explains how to build a robust custom connector that can consume the REST APIs of Marketo in order to pull all kinds of data (Leads, Activities, Custom Objects, Programs, etc.). Your only restriction will be down to your Marketo API daily request limit.
The concepts explained here focus on Marketo, but they could also be used to to integrate other SaaS solutions that provide a REST API.

 

Prerequisites

POWER QUERY

Prior to the release of Excel 2016, Microsoft Power Query for Excel functioned as an Excel add-in that was downloaded and installed on Excel 2010 or Excel 2013. From Excel 2016, this technology is a native feature integrated into the ‘Data’ ribbon under ‘Get & Transform’ section.

All of the scripts produced for this article have been tested on Excel 2016 for Windows. The concepts should be the same for Excel 2013 or Excel 2010 but some adaptations could be required.

blog-powerquery-data-ribbon

Power Query is currently only available on the Microsoft Windows version of Excel; the Mac version is unfortunately not supported.

MARKETO

Power Query will use the Marketo REST APIs to access data from Marketo. In order to use these APIs, you’ll need an API User and a Custom Service that you can create yourself if you are administrator of your Marketo instance. If not, then an administrator will need to provide those to you.

A step by step explanation of how to create the Marketo API User and the Custom Service can be found here.

Once you’re done, you should have the following credentials in order to invoke the Marketo REST APIs: Client Id and Client Secret.

The REST API Endpoint can be found on the REST API section of the Web Services Admin in Marketo and it should have the following pattern:

https://XXX-XXX-XXX.mktorest.com/rest

Marketo has a Daily Request Limit for its API and this limit can be found in the Web Services Admin along with a consumption report. Make sure to never exceed your daily limit when you design your queries as you may miss some data in your reports.

 

Power Query Workbook Creation

Let start with a new Excel workbook. We create a specific configuration worksheet for declaring all the Marketo REST API Settings.

blog-powerquery-daily-workbook-creation

In this worksheet, we create three tables:

1. Table ‘REST_API_Authentication’ with the columns:

URL: your Marketo REST API Endpoint.

Client ID: from your Marketo REST API OAuth2.0 credential.

Client Secret: from your Marketo REST API OAuth2.0 credential.

2. Table ‘Scoping’ with the columns:

Paging Token SinceDatetime: a date following the ISO 8601 standard date notation (e.g. “2016-10-06T13:22:17-08:00”, “2016-10-06” are valid date/time) that is used to fetch Marketo activities since a given period, thanks to an initial ‘date-based’ paging token. This date is mainly used to limit the amount of data to import into the workbook.

List ID: the ID of a static list in Marketo that reference all the leads/contacts we are dealing with. This static list can be managed freely in Marketo (e.g. a smart campaign can feed it periodically or in real time with leads and contacts). In order to get the ID of a static list, open it in Marketo and get its numerical ID from the URL, e.g. https://myorg.marketo.com/#ST3517A1LA1, List ID=3517.

Max Records Pages: this is used for our pseudo-recursive algorithms that iterates through the Marketo output data, using ‘position-based’ paging tokens, with a capacity of 300 max records per page. Since this our interest to get as many records per page as possible, we’ll stick to 300. So typically a Max Records Pages set to 33.333 means a capacity of 33.333 X 300 = 9.9999 million records; but it also means 33.333 K on your Marketo API Daily Request Limit. The algorithms will stop anyway as soon as all data from the queries are obtained, so this parameter is just a safety limit for a loop.

3. Table ‘Leads’ with the column:

Lead Fields: comma separated lead fields to gather from Marketo when querying the leads and contacts.

Declaring a table in Excel is simple. Enter two rows in the spreadsheet with the columns names and values, highlight with the mouse the perimeter of the table, and select the icon Table in the ‘Insert’ menu, and then give it a name.

blog-powerquery-insert-table

The names given to the tables and their columns are important as they will be called directly by our scripts.

 

Authentication and Access Token

ABOUT MARKETO REST API AUTHENTICATION

Marketo’s REST APIs are authenticated with 2-legged OAuth 2.0.  Client IDs and Client Secrets are provided by custom services that you define.  Each custom service is owned by an API-Only user which has a set of roles and permissions which authorize the service to perform specific actions.  An access token is associated with a single custom service.

The full Authentication mechanism is documented here on the Marketo Developer site.

When an access token is originally created, it’s lifespan is 3600 seconds or 1 hour.  Each consecutive authentication call for the same custom service returns the current access token with its remaining lifespan. Once the token is expired, the authentication returns a brand new access token.

Managing access token expiration is important to ensure that your integration works smoothly and prevents unexpected authentication errors from occurring during normal operation.

CREATE QUERY

Create a new query by clicking the ‘New Query’ icon from the ‘Get&Transform’ section of the ‘Data’ Menu. Select a blank query to start with and give it a name such as ‘MktoAccessToken’.

blog-powerquery-new-query

Launch the Advanced Editor from the Query Editor, so you can script manually some Power Query formulas.

blog-powerquery-advanced-editor

Enter the following code in the advanced editor:


let
    // Get url and credentials from config worksheet - Table REST_API_Authentication
    mktoUrlStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[URL],
    clientIdStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[Client ID],
    clientSecretStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[Client Secret],

    // Calling Marketo API Get Access Token
    getAccessTokenUrl = mktoUrlStr & "/identity/oauth/token?grant_type=client_credentials&client_id=" & clientIdStr & "&client_secret=" & clientSecretStr, 
    TokenJson = try Json.Document(Web.Contents(getAccessTokenUrl)) otherwise "Marketo REST API Authentication failed, please check your credentials",

    // Parsing access token
    accessTokenStr = TokenJson [access_token]
       
in
    accessTokenStr
The comments embedded in the source code, preceded by “//” make the code self-explanatory. If you need any function reference, please check out the links provided in the Reference section of this article.

Click the button “Done”.

blog-powerquery-advanced-editor-save

Check that the Access token is displayed successfully in output for the final applied step ‘accessTokenStr’.

blog-powerquery-advanced-editor-check

One quick comment about the security in Excel; you may be asked occasionally to enable External Data Connections from the yellow banner. This is required in order to let the Queries work properly.

blog-powerquery-enable-content

CONVERTING QUERY INTO A FUNCTION

Return to the advanced Editor and wrap your code with the following function declaration:


let
    FnMktoGetAccessToken =()=>

        let
            // Get url and credentials from config worksheet - Table REST_API_Authentication
            mktoUrlStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[URL],
            clientIdStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[Client ID],
            clientSecretStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[Client Secret],

            // Calling Marketo API Get Access Token
           getAccessTokenUrl = mktoUrlStr & "/identity/oauth/token?grant_type=client_credentials&client_id=" & clientIdStr & "&client_secret=" & clientSecretStr, 
           TokenJson = try Json.Document(Web.Contents(getAccessTokenUrl)) otherwise "Marketo REST API Authentication failed, please check your credentials",

            // Parsing access token from Json 
           accessTokenStr = TokenJson [access_token]
       
        in
            accessTokenStr 

in FnMktoGetAccessToken

The function does not take any parameters in input but get those from the configuration worksheet. It produces the access token as an output.

Rename your query FnMktoGetAccessToken and save it.

Note that you can see all your queries at any time in Excel by clicking the button ‘Show Queries’ in the ‘Get & Transform’ section of the Data menu.

Your function should be now marked with the function icon ‘Fx’, just like in the screenshot below:

blog-powerquery-show-queries

 

Load Members of Static List

GET LEADS

The Marketo Lead API provides simple CRUD operations against lead records, the ability to modify a lead’s membership in static lists and programs, and initiate Smart Campaign processing for leads. All these capabilities are documented here.

A large set of lead records can be retrieved based on membership in a static list or a program.  Using the id of a static list, you can retrieve all lead records which are members of that static list. The id of the list is a path parameter in the call. See the chapter “List and Program Membership” in the Marketo Developers documentation for details.

The maximum number of lead records we can get per API call is 300, so we’ll need to leverage paging tokens in order to gather the records per pages of 300 records. We get the paging token in the Json answer after the first call and we know we’re done when the paging token is not in the output anymore.

BASIC QUERY

Let’s get started with a fully functioning query aimed at downloading all the leads from a static list.

Create a new blank query called ‘MktoLeads’ and enter the following code in the advanced editor:


let
    
    // Get Url from config worksheet - Table REST_API_Authentication
    mktoUrlStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[URL],
    // Get the number of iterations (pages of 300 records) - Table Scoping
    iterationsNum = Excel.CurrentWorkbook(){[Name="Scoping"]}[Content]{0}[Max Records Pages],
    // Get the List id - Table Scoping
    listIdStr = Number.ToText(Excel.CurrentWorkbook(){[Name="Scoping"]}[Content]{0}[List ID], "D", ""),
    // Get the Lead fields to extract - Table Leads
    LeadFieldsStr = Excel.CurrentWorkbook(){[Name="Leads"]}[Content]{0}[Lead Fields],


    // Build Multiple Leads by List Id URL
    getMultipleLeadsByListIdUrl = mktoUrlStr & "/rest/v1/list/" & listIdStr & "/leads.json?fields=" & LeadFieldsStr,
   
    // Build Marketo Access Token URL parameter
    accessTokenParamStr = "&access_token=" & FnMktoGetAccessToken(),

    pagingTokenParamStr = "",

    // Function iterating though the pages
    FnProcessOnePage =
    (accessTokenParamStr, pagingTokenParamStr) as record =>
        let
        
            // Send REST API Request             
            content = Web.Contents(getMultipleLeadsByListIdUrl & accessTokenParamStr & pagingTokenParamStr),
            
            // Recover Json output and watch if token is expired, in that case, regenerate access token
            newAccessTokenParamStr = if Json.Document(content)[success]=true then accessTokenParamStr else "?access_token=" & FnMktoGetAccessToken(),
            getMultipleLeadsByListIdJson = if Json.Document(content)[success]=true then Json.Document(content) else Json.Document(Web.Contents(getMultipleLeadsByListIdUrl & newAccessTokenParamStr & pagingTokenParamStr)),
            
            // Parse Json outputs: data and next page token     
            data = try getMultipleLeadsByListIdJson[result] otherwise null,          
            next  = try  "&nextPageToken=" & getMultipleLeadsByListIdJson[nextPageToken] otherwise null,
            res = [Data=data, Next=next, Access=newAccessTokenParamStr]
        in
            res,

    // Generates a list of values given four functions that generate the initial value initial, test against a condition, and if successful select the result and generate the next value next. An optional parameter, selector, may also be specified
    GeneratedList =
        List.Generate(
            ()=>[i=0, res = FnProcessOnePage(accessTokenParamStr, pagingTokenParamStr)],
            each [i]null,
            each [i=[i]+1, res = FnProcessOnePage([res][Access],[res][Next])],
            each [res][Data])
in
    GeneratedList

Power Query does not offer traditional looping functions (e.g. For-loop, While-loop) and does not support recursion. A good workaround is to implement a For-loop using List.Generate. This function is documented here. With List.Generate it’s possible to iterate over the pages. At each step of the iteration we extract a page of data, keeping the URL that includes the paging token for the next page, and store the results in the next item of the generated list. The blog from Datachant was a great resource for solving this. Our parameter ‘Max Records Pages’ is here to limit the number of pages and restrict it to a realistic range avoiding an infinite loop.

Another challenge is to ensure that the access token is never expired. Tracking its remaining lifespan would be too complex with Power Query. So all calls to the REST API are backed up with an error check; if an error occurs, we assume the token has expired and we renew it first and replay the call again. If the second call fails, then the second failure will be notified to Excel (in the worst case, you’ll get no data as a result).

Launch the query, after saving it or by clicking the ‘Refresh button’ at any time.

blog-powerquery-list-generate

In our case, 1364 lead records were extracted fitting in 5 pages of data within 5 lists.

 

Shaping the Data

We need to shape the data to have all these records in a single flat list of records. There are two ways to do this:

  • Using more code
  • Leverage the Power Query UI

Right click on the output grid and choose ‘To Table’ in the contextual menu in order to convert it to a table of lists.

blog-powerquery-to-table

In the ‘To Table’ pop up, leave the default values in the 2 picklists.

blog-powerquery-to-table-picklists

Now expand the resulting table of lists.

blog-powerquery-to-table-expand

Now we have all the records in a single list. The records encoded in Json format contain the fields and their associated values. Expand again.

blog-powerquery-to-table-expand-again

Select in the pop up all the fields you want to keep, uncheck the tick box ‘Use original column name as prefix’.

blog-powerquery-to-table-select-fields

Et voila! All records are displayed nicely in our table.

blog-powerquery-to-table-display

If we re-open the advanced editor, we can see that 3 lines of code have been added to shape our data:


#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "updatedAt", "lastName", "email", "createdAt", "firstName"}, {"id", "updatedAt", "lastName", "email", "createdAt", "firstName"})

You can do much more with Power Query, like creating extra columns with computed values, we’ll see some more possibilities later on.

Let’s save and close this query. It can now be refreshed manually at any time or automatically via background refreshes.

 

Redirecting the Results

Now the question is, where to send the result data?

Hover over your query with the mouse and select the menu ‘Load To…’ in the contextual menu.

blog-powerquery-load-to

In the popup, then you can select:

  • ‘Table’ if you want to send all the shaped data to a worksheet (new or existing one),
  • ‘Only Create Connection’ if your goal is to do further analysis in the Power Pivot.

The check box ‘Add this to the Data Model allows you to exploit the data in the Power Pivot; this is what we want for the second part of this article.

blog-powerquery-load-to-load

 

Managing Pagination

Since the aim of our project is to build many more queries, let’s do some refactoring and extract a reusable function that would manage the pagination.

Create a new blank query called FnMktoGetPagedData and enter the following code in the advanced editor:


let
    FnMktoGetPagedData =(url, accessTokenParamStr, pagingTokenParamStr)=>

    let
    
        // Get the number of iterations (pages of 300 records) - Table Scoping
        iterationsNum = Excel.CurrentWorkbook(){[Name="Scoping"]}[Content]{0}[Max Records Pages],

        // Sub-function iterating though the REST API service result pages
        FnProcessOnePage =
        (accessTokenParamStr, pagingTokenParamStr) as record =>
            let
        
                // Send REST API Request             
                content = Web.Contents(url& accessTokenParamStr & pagingTokenParamStr),
            
                // Recover Json output and watch if token is expired, in that case, regenerate access token
                newAccessTokenParamStr = if Json.Document(content)[success]=true then accessTokenParamStr else "?access_token=" & FnMktoGetAccessToken(),
                contentJson = if Json.Document(content)[success]=true then Json.Document(content) else Json.Document(Web.Contents(url & newAccessTokenParamStr & pagingTokenParamStr)),
            
                // Parse Json outputs: data and next page token     
                data = try contentJson[result] otherwise null,          
                next  = try  "&nextPageToken=" & contentJson[nextPageToken] otherwise null,
                res = [Data=data, Next=next, Access=newAccessTokenParamStr]
            in
                res,

        // Generates a list of values given four functions that generate the initial value initial, test against a condition, and if successful select the result and generate the next value next. An optional parameter, selector, may also be specified
        GeneratedList =
            List.Generate(
                ()=>[i=0, res = FnProcessOnePage(accessTokenParamStr, pagingTokenParamStr)],
                each [i]null,
                each [i=[i]+1, res = FnProcessOnePage([res][Access],[res][Next])],
                each [res][Data])
    in
        GeneratedList

in FnMktoGetPagedData

Save the query. We are going to use it next.

 

Simplified Query

Let’s rewrite again our query ‘MktoLeads’ which will be calling the FnMktoGetPagedData function.


let
    
    // Get Url from config worksheet - Table REST_API_Authentication
    mktoUrlStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[URL],
    // Get the List id - Table Scoping
    listIdStr = Number.ToText(Excel.CurrentWorkbook(){[Name="Scoping"]}[Content]{0}[List ID], "D", ""),
    // Get the Lead fields to extract - Table Leads
    LeadFieldsStr = Excel.CurrentWorkbook(){[Name="Leads"]}[Content]{0}[Lead Fields],


    // Build Multiple Leads by List Id URL
    getMultipleLeadsByListIdUrl = mktoUrlStr & "/rest/v1/list/" & listIdStr & "/leads.json?fields=" & LeadFieldsStr,
   
    // Build Marketo Access Token URL parameter
    accessTokenParamStr = "&access_token=" & FnMktoGetAccessToken(),

    // No initial paging token required for this call
    pagingTokenParamStr = "",

    // Invoke the multiple REST API calls through the FnMktoGetPagedData function
    result = FnMktoGetPagedData (getMultipleLeadsByListIdUrl , accessTokenParamStr, pagingTokenParamStr) 
        
in
    result

As you can see, our query is now really simple to read and to maintain. We are going to leverage again the FnMktoGetPagedData function for the other queries.

 

 

Load Specific Activities from a Defined Period of Time

GET ACTIVITIES WITH PAGINATION

Marketo permits a huge variety of activity types related to lead records.  Nearly every change, action or flow step is recorded against a lead’s activity log and can be retrieved via the API or leveraged in Smart List and Smart Campaign filters and triggers.  Activities are always related back to the lead record via the leadId, corresponding to the Id of the record, and also have a unique integer id of its own. You’ll find the complete REST API documentation here.

There are a very large number of potential activity types, which may vary from subscription to subscription, and have unique definitions for each.  While every activity will have its own unique id, leadId and activityDate, the primaryAttributeValueId and primaryAttributeValue will vary in their meaning.

We are going to focus on the Interesting Moments, one kind of Marketo tracked activities with the ID 46.

The new challenges we are going to resolve are:

  • We need to initiate a ‘date-based’ paging token to define the period of time when the activities happened,
  • Shaping the data is a bit trickier as depending on the activity types, a list of activity-specific attributes is provided in Json and need to be parsed and flatted out in order to ease up the analysis.

DATE BASED PAGING TOKEN

We need to build first this function in order to generate the initial ‘date-based’ paging token, required to scope the period of time for our Activity queries. You’ll find the documentation about the paging token here.

Create a new blank query called FnMktoGetPagingToken and enter the following code in the advanced editor:


let
    FnMktoGetPagingToken =(accessTokenStr)=>

        let
            // Get url from config worksheet - Table REST_API_Authentication
            mktoUrlStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[URL],

            // Get Paging Token SinceDatetime from config worksheet - Table Scoping
            mktoPTSinceDatetimeStr = DateTime.ToText(Excel.CurrentWorkbook(){[Name="Scoping"]}[Content]{0}[Paging Token SinceDatetime], "yyyy-MM-ddThh:mm:ss"),

            // Building URL for API Call
            getPagingTokenUrl = mktoUrlStr & "/rest/v1/activities/pagingtoken.json?access_token=" & accessTokenStr & "&sinceDatetime=" & mktoPTSinceDatetimeStr, 

            // Calling Marketo API Get Paging Token
            content = Web.Contents(getPagingTokenUrl),

            // Recover Json output and watch if access token is expired, in that case, regenerate it
            newAccessTokenStr = if Json.Document(content)[success]=true then accessTokenStr else "?access_token=" & FnMktoGetAccessToken(),
            pagingTokenJson = if Json.Document(content)[success]=true then Json.Document(content) else Json.Document(Web.Contents(mktoUrlStr & "/rest/v1/activities/pagingtoken.json?access_token=" & newAccessTokenStr & "&sinceDatetime=" & mktoPTSinceDatetimeStr)),           

            // Parsing Paging Token
            pagingTokenStr = pagingTokenJson[nextPageToken]
       
        in
            pagingTokenStr 

in FnMktoGetPagingToken

Save the function. We are going to use it next.

INTERESTING MOMENTS ACTIVITIES

Let’s write now the query ‘MktoInterestingMomentsActivities’ which will be calling the FnMktoGetPagedData and FnMktoGetPagingToken functions.


let
    
    // Get Url from config worksheet - Table REST_API_Authentication
    mktoUrlStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[URL],
    // Get the List id - Table Scoping
    listIdStr = Number.ToText(Excel.CurrentWorkbook(){[Name="Scoping"]}[Content]{0}[List ID], "D", ""),

    // Build Get Activities URL
    getActivitiesUrl = mktoUrlStr & "/rest/v1/activities.json?ListId=" & listIdStr & "&activityTypeIds=46",
   
    // Build Marketo Access Token URL parameter
    accessTokenStr = FnMktoGetAccessToken(),
    accessTokenParamStr = "&access_token=" & accessTokenStr,

    // Obtain date-based paging token used to scope in time the activities
    pagingTokenParamStr = "&nextPageToken=" & FnMktoGetPagingToken(accessTokenStr),

    // Invoke the multiple REST API calls through the FnMktoGetPagedData function
    result = FnMktoGetPagedData (getActivitiesUrl , accessTokenParamStr, pagingTokenParamStr)
   
in
    result

The result of this query is again a list of lists, so it needs some further data processing to be usable for analysis.

blog-powerquery-list-tools

 

Shaping the Data

Let’s do the same shaping operations we did for the Leads:

  • Right click on the output grid and choose ‘To Table’ in the contextual menu in order to convert it to a table of lists,
  • Expand the resulting table of lists,
  • Expand one more time, selecting in the pop up all the fields you want to keep (uncheck the tick box ‘Use original column name as prefix’).

You can see the columns with their values, except the column ‘attributes’ that still contain a list of specific attributes associated to the interesting moments.

Let’s expand these attributes.

blog-powerquery-expand-attributes

Now the list has expanded into records, we expand again, selecting the fields we want (name and value of each attribute) and we uncheck the tick box ‘use original column name as prefix’.

blog-powerquery-name-as-prefix

As a result, all our data are visible, including attributes, but each interesting moment activity is spanned over 3 lines. This is going to be hard to use for our analysis.

blog-powerquery-interesting-moment

Ideally we want just one line per activity, with all attributes displayed as extra columns. We can easily do that by pivoting the 3 attributes from our table.

Select the 2 columns ‘Name’ and ‘Value’ from the activity attributes and click on ‘Pivot Column’ in the ‘Transform’ menu.

blog-powerquery-pivot-column

Ask for the advances options in the pop-up and select ‘Values Column’ = value and ‘Don’t aggregate’ value function.

blog-powerquery-pivot-column-ok

Click ‘OK’ and you have in output one single line of data per activity.

blog-powerquery-interesting-moment-activity

The following ‘data shaping’ lines of code should have been appended automatically to the script of your query:


#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "leadId", "activityDate", "activityTypeId", "campaignId", "primaryAttributeValue", "attributes"}, {"id", "leadId", "activityDate", "activityTypeId", "campaignId", "primaryAttributeValue", "attributes"}),
    #"Expanded attributes" = Table.ExpandListColumn(#"Expanded Column2", "attributes"),
    #"Expanded attributes1" = Table.ExpandRecordColumn(#"Expanded attributes", "attributes", {"name", "value"}, {"name", "value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded attributes1", List.Distinct(#"Expanded attributes1"[name]), "name", "value")
in
    #"Pivoted Column"

Next Steps

You should be able now to design all the queries you need to access any specific Marketo data available through its REST APIs.

We hope you enjoyed this article and that it helped you to leverage the great benefits of Excel and Marketo combined.

Please check the next article for a deep dive into the Marketo data analysis with Power Pivot.

References

MARKETO

POWER QUERY