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:
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?
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.
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*