Cross-channel ad spend reporting
Report on your ad spend across all of your channels, and use Google Analytics to create a source of truth for conversions.
How much am I spending on online ads?
Answering this simple question might be harder than you think. Without an expensive third party solution you'd likely be left manually downloading reports from each ad platform and sum the data yourself.
Fortunately, Parabola is here to help. This examples lets you pull in ad spend data from three different channels (Facebook, Google, and Quora), sum it, and sync to Google sheets. As an added bonus, the example also shows how you'd pull in your conversion data from Google Analytics so that you can compute per-channel conversion rates against a single source of truth.
There are two things to keep in mind before diving in:
- In order to use this flow, you'll need to get access to the API of each ad platform you use. This example shows how to authenticate to Google, Facebook, and Quora, but instructions for your service might be different.
- Next, you'll want to figure out the web request you need to make in order to get the data cut you're interested in. This example shows how to fetch spend data for the last thirty days. If you want a different metric or a longer time window, you'll need to customize the example yourself.
Manipulating ad spend data in Parabola
Once you've got the data you need in Parabola, compiling the cross-channel report is actually pretty easy.
Since each API delivers the data in a slightly diferent form, the example uses a few transforms to account for things like converting micros to dollars, standardizing column names and positions, and trimming extraneous columns.
Net, we join on the date (YYYY-MM-DD format), and are ready to sync to Google Sheets.
The example does a bit of extra work to append to a Google Sheet (rather than clobbering it every time). To do this it partitions the data into three chunks: - Older data that was in the spreadsheet but not in the API response (prior to the last 30 days) - Data that was both in the spreadsheet and in the API response (within the last 30 days) - New data that was in the API response but not in the spreadsheet
Parabola handles the data differently in each case: - Old data would be passed through just as it was found in the source sheet - For data in both places, I'd replace the spreadsheet data with the most recent API response (in case there was an outage/error/whatever) - New data can only come from the API response
Finally, the example uses a table merge to squash the three data partitions on top of each other send sends it out to Google Sheets!