Marketo Analytics with Power BI Reporting?

[Anonymous]

Has anyone had any success getting their Marketo data into Power BI using this out of the box integration? We went through all the steps documented in this Microsoft article​, but we keep receiving Marketo notifications for web services errors.

It's the same error each time. Error code 606: Max rate limit ‘%s’ exceeded with in ‘%s’ secs. The number of calls in the past 20 seconds was greater than 100.

We selected the minimum amount of data -- it asks how many months to load, and we selected "1" -- and for fives days running it made anywhere from 23,000 to 43,000 API calls each day... but that never resulted in a working dashboard.

If we keep letting it grind along, will we eventually get reports? Or should we give up on this and build something on our own? (Or is there a way to modify it to avoid those errors and eventually get the data we need?)

[Adolfo_Correa]

A quick, temporarily workaround that worked for me:

1. In the "All People" System Smart List set a customized a view including columns you are interested in.
2. Set a daily subscription for the table to be sent to an email.
3. In Outlook, create a rule that downloads the .csv file and saves it to a Sharepoint file.
4. Connect PowerBi to that Sharepoint file.

This way I can export any column without restriction while I learn to deal with the API. I can also do it for any subset of the Database (smart lists). The VBA script is very simple, works on demand too.

Works ok so far. 30 min set up.

[Barry_Dupont1]

My task to look at Marketo / PowerBI has been put off to later this year - but I'm going to make 30mins of time to try this! Ingenious idea Adolfo.

[Anonymous]

So, I posted my issue to the BI Community. I'd love it if anyone interested in the connector — particularly those who weren't able to get it working — would post behind me with whatever details they can provide about their issues and to express their interest in the connector.

There are a number of old threads (1, 2, 3) about the plugin, but the most recent updates to those threads are in September of 2017.

[Matthew_Wainwri]

Might be worth taking a look at Bedrock data here in Boston. We're friendly with them and they recently launched a product that allows you to feed data out of Marketo into an automated cloud data warehouse that you can use as a data source for PowerBI (or whatever).

It gives you tables you can point to directly from your PowerBI. It gets around the API uses by pre-loading the warehouse, and then bringing updates from there rather than continuously trying to pull data and hitting limits.

https://www.bedrockdata.com/fusion/marketo-data-warehouse

[Anonymous]

Awesome! Another option to look at. Thanks, Matthew!

[Jonathan_Ng2]

Bedrock was high on our list for a viable solution to getting to working data. Would encourage you to take a good look at it.

[Anonymous]

Thanks, Jonathan! Good to know.

[Matthew_Wainwri]

Surely. Hope it's what you're looking for!

[Anonymous]

Honestly, we'd prefer to get Microsoft connecter working first and see what we can get from that — but if that doesn't happen we'll need to look at other options... so we may as well begin compiling a list.

Thanks again, Matthew! Have a great weekend!

[Barry_Dupont1]

Hi,

We are having the exact same issue, and I'm due in the next couple of weeks to look into a solution for this. If I find any simpler solutions (not too hopeful TBH!) I'll comment on them here, appreciate it if you can update this post with anything you find as well.

[Anonymous]

Will do. I'm going to be posting something to the Power BI community about the issues with the connector and see if that won't draw some response from Microsoft.

I'll be sure to post here if we come up with some solution or make some headway on this.

[Jonathan_Ng2]

Same here for us. Tried using Power BI a few times, once when it originally came out and again when they did a re-release with better integrations. In both cases, they timed out and could barely get connected. We went as far as opening tickets with both Marketo and Power BI and in both cases they circled back on each other telling us to work with the other vendor to resolve. We dropped it and ended up building a data warehouse/datamart collecting from various sources. We then throw it into Power BI if we want to conduct additional reporting.

[Anonymous]

Very cool. Thanks, Jonathan!

It's a pity the connector doesn't work a bit better. A real bummer actually, because it gets your hopes up... but then... nope. Nothing.

[Dan_Stevens_]

I agree - the out of the box reporting/integration is bad. We use the API to extract data each evening to a separate data warehouse. And now have a very flexible environment (slowly phasing out our reliance of RCE, due to all its recent issues) to do both ad-hoc and real-time analysis. Here's a very small sample of our dashboards/reports:

And our custom "opportunity influence analyzer":

[Anonymous]

Very cool, Dan!

So this is Power BI generating a dashboard from data stored in an Azure SQL Database?

What platform or tools do you use to extract the data while also staying below your Marketo instance's request limit?

[Dan_Stevens_]

Yes it is (and yes, the data sits in an Azure SQL DB). The team doesn't even come close to exceeding our daily API limit (100,000) - probably averages a little over 10,000 per day. The team has done a great job in optimizing those calls to grab data as efficiently as possible. Our DB size is about 220,000 records.

[Anonymous]

Awesome! Thanks, Dan!

And the API calls? Is that all custom work, or are you using a Microsoft product there as well?

[Dan_Stevens_]

Going into the project, the team had no prior experience using the API. Spent a lot of time getting up to speed and then started plugging away. It's all custom developed - no specific product used.

[Anonymous]

That's what I though. Just hoping there was a simpler way.

Thanks so much for responding though. Seeing your great dashboards definitely gives me hope.

[Angelica_Krott]

We use PowerBI for reporting as well. The Marketo out of the box connection is very limited and only gives you the data that Marketo wants to provide.

For our reporting needs, we needed to be able to see by email the total performance which included Sends, Delivered, Opens, Clicks, etc. We also need to supply lead data to our advertisers as well. The amount of data you need to retrieve from Marketo goes back to how much activity / leads you have to report on. We have to report on over 1MM newsletter deployments a day and also on just under 1MM subscribers. With the REST API, it has a limit on not just the calls (50,000) but also the amount of data you can retrieve. For our purposes, since we needed multiple marketo activities along with the lead database changes, we were well over the REST API limits and had to connect with the BULK EXTRACT API and pay more for increased data extract size allotment.

So now, we have a custom web client built which pulls the extracts needed daily into an Azure MySQL database. The database then we connect to in PBI. With a little bit of post production work and categorizing of those deployments, we're able to provide robust dashboards with full metrics at an email level, advertiser level, etc.

Also, keep in mind that Marketo does not capture Gross Opens. They don't even record Gross opens which support has confirmed for me. While many marketers do prefer to report on uniques, that's not what our advertisers want. So we had to also implement a custom email beacon to record opens and that data too is pushed into our Azure MySQL database.

The hardest part is the planning and knowing exactly what data (by field name) you want to extract from Marketo.

You might want to explore 3rd party connectors. Some of them have been engineered to work not just with the REST API but the BULK EXTRACT API as well. However, they would be an additional cost.