Power BI, QBO, and Google Ads

[Anonymous]

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?

[ReportMaster]

Hi, Since there's no native connector from Google Ads to PBI, a workaround could be to use an external connector instead to pull your data, that would let you avoid the manually and repetitive process you previously used. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Google Ads connector in the data sources list:

After that, just grant access to your Google Ads account using your credentials, then on preview and destination page you will see a preview of your Gads fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous]

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]

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]

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