cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Power BI, QBO, and Google Ads

I apologize if this issue has been brought up before, but I'm new to the community and wanted to see what people have to say. 

 

Subject: E-commerce ROAS Tracking

 

End goal: to be able to have a report that lists out the following:

  • Product name/SKU
  • Amount spent on Google Shopping ads to advertize each product
  • Amount of revenue brought in by sales of each product
  • Ability to see trends for those metrics over time

 

Sounds simple enough, right?

 

(disclaimer: using traditional advertising management in Google Ads is not reliable enough for my needs, as the attribution between Google Ads and Analytics leads to incorrect data, AND not all sales for products go directly through the e-commerce site)

 

So instead, I want to look at true sales data from Quickbooks Online, as well as product level ad spend from Google Analytics (or directly from Google Ads using some sort of API), then simply use those numbers to derive the percentage of sales for each product that was spent on advertising. I have found that I can use a simple master product list look-up table to form the data model relationship between the SKU in Quickbooks Online and the woo_commerce product ID in Google.

 

I've had success building models that spit out the information we need by manually downloading the reports from both QBO and Google Ads, but the process is repetitive, time consuming, and not very agile. QBO exports reports in accounting format, so there is a lot of refinement that has to happen in the Power Query Editor before the data can be usable. This has cause issues with directing Power BI to a folder as the data source (in an ideal world, I would point Power BI to a folder that contains weekly sales data that I get from by contact over at the e-commerce company; but getting the Applied Steps to play nicely with that set-up has proved to be problematic. 

 

So ultimately, I am looking for a way to have a direct connection to both Quickbooks Online and Google Ads that returns the attributes that I'm looking for at the product level.

 

Has anyone had any success connecting to QBO or Google Ads in this fashion? And if so, what is required?

3 REPLIES 3
Anonymous
Not applicable

Hi there. I'd first check if there are any dedicated DATA CONNECTORS for the sources you mention. I'm not sure but there probably are connectors for Qickbooks Online. Not sure about Google Ads. But if there aren't, then certainly there is some kind of API that lets you connect to them and download data, and then you can marry Python with M (the Power Query language) to get the data and shape it.

Best
D

Anonymous
Not applicable

Hi D,

 

There is a native data connector for QBO, but it's in beta and quite frankly doesn't work super well for this application. It would be great for doing some basic accounting visualizations, but it doesn't have good reporting at the product level, and I haven't found anywhere that it can bring in the SKU data, which is really the hinge that all of this would work on. 

 

As for Google Ads, I may have to do somet digging to see if anyone has had success with it. I was thinking some sort of custom API to grab the specific data points that I need from QBO, but that's a little outside my wheelhouse right now. Some day.... *looks dreamily off into the distance*

Anonymous
Not applicable

When all else fails, I think you should use API's, Python and Power Query. If there are APIs, you certainly can do it and automate it.

Best
D

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors