Marketo Analytics with Power BI Reporting?

[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.

[Anonymous]

Thanks, Angelica! That's why we wanted to use the connector for now. The idea of doing some sort of custom build is pretty unappealing.

[Angelica_Krott]

Hi Osman. The advantage over a custom build is flexibility and also being able to fine tune the extract more to improve your processing time. There’s a lot of variables to the best route to pursue. At first, we chose the connector route but the processing time through it proved to be problematic. So not, we don’t use it for everything now and more so rely on the custom web client. Building a custom web client though to handle it could be done in under a week’s time if you have a skilled dev team. If you outsource, be prepared to know every speck of the data and how it relates to the overall reports you are trying to create.

https://www.stitchdata.com/docs/integrations/saas/marketo

Just be aware that many connectors, especially ones that have an existing Marketo connection, will only push to a data lake/data warehouse or SQL server.

Try looking at something like Stitch. Stitch does have a connector to both Marketo’s REST API and Bulk Extract API. And look through the Marketo dev documentation as well so you understand what type of calls / extracts you would need to do. I haven’t used them myself though so I can’t speak to how efficient their connectors are. It’s just an example for you.

But I strongly recommending first starting out with drawing what your reports should look like, then determining the necessary datasets / fields you’d need to pull from Marketo and map it to your storage repository. Finally, understand the volume of data you’d need to extract and at what frequency. There’s also some gotchas in how Marketo stores data. And just a heads up that the data out of the box is not in relational format. If you’re looking to create a relational database like we did, then you have to know how to extract all the needed datasets / activities appropriately and how all the tables would come together, etc.

It’s not an easy undertaking but it is possible. Just take it one step at a time and know the data.

[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.