It was the end of May, and I had a simple question to answer: how much money were we spending on advertising each day across our three main channels?

At the time, the solutions I came up with were:

1. Spend a bunch of time each day manually downloading reports, sum the figures, and update a spreadsheet
2. Ask our already-strapped engineering team to write code and create a dashboard
3. Pay for and set up an expensive, overkill third party SEM-management tool
4. Build it myself with Parabola

I suppose you can guess what I did. The whole thing took me a couple hours and twenty seven Parabola steps. The flow runs every day at 8am PDT and updates a Google spreadsheet with the previous day’s ad spend. When our CEO asks me how much we spent on ads in July I can have the answer in a few seconds. It cost the company $0, and I have a work product I can feel good about!

‍

Below I’ll go into more detail on exactly how I made this work in Parabola, but you can also visit our site play around with or customize my solution to suit your needs.

Getting access to the ad platform APIs

The hardest part of building this was just getting access to the ad platforms’ APIs. We’re currently running ads on Google, Facebook and Quora, so I needed access to all those services:

• The Facebook Ads API was by far the simplest of the three to get started. I created an Ads Application from this page, added the Marketing API to it and was able to make calls to retrieve my account data right away.
• The Quora Ads API is in a private beta, so we reached out via the form on their site.
• For Google Ads, I had to follow the instructions here. In short: sign up, wait for approval, and then get going.

The other slightly complicated piece here was that both Google and Quora require authentication via OAuth 2.0. Parabola supports OAuth 2.0 now, but I still had to copy/paste a bunch of stuff and that created ample surface area for silly errors.

Fetching data from the ad platform APIs

Once I had access to the ad platform APIs, I needed to figure out what URLs to call to get the data I wanted. Parabola makes it trivially easy to extra whatever performance data you need from these APIs, but in my case I just wanted ad spend info from the last 30 days sliced by date.

Each of the APIs does this a bit differently:

Facebook:

GET https://graph.facebook.com/v3.3/act_{YOUR ACCOUNT NUMBER}/insights?date_preset=last_30d&time_increment=1&access_token={YOUR ACCESS TOKEN}

Quora:

GET https://api.quora.com/ads/v0/accounts/YOUR_ACCOUNT_ID?granularity=DAY&presetTimeRange=LAST_30_DAYSGoogle:POST https://googleads.googleapis.com/v1/customers/{YOUR ACCOUNT ID}/googleAds:search

POST body:

{"query" : "select segments.date, metrics.cost_micros from customer where segments.date during last_30_days"}

Compiling the spend report in Parabola

Once I had the data in Parabola, compiling the report was actually really easy.

Since each API delivered slightly different data, I made a few transforms to account for things like converting micro cents to dollars, standardizing the column names/positions, and trimming extraneous columns. Then I used the join step to merge all three data chunks together based on the date (YYYY-MM-DD format).

My main goal with this project was to log our daily ad spend in a Google Sheet so that I could reference it easily, but I also wanted historical data so I could assess things like how our June spend compared to our July spend.

The problem is that Parabola doesn’t have built in support for appending values to a Google Sheet yet. So I got the opportunity to implement the append functionality myself with Parabola.

To do this, I first partitioned my data into three chunks:

• Older data that was in the spreadsheet but was no longer being sent back from the ad platforms
• Recent data that was both in the spreadsheet and being sent back from the ad platforms
• Brand new data that was being sent back from the ad platforms but not in the spreadsheet yet

For each case, I told Parabola how I wanted it handled:

• 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 data from the ad platforms (in case there was an outage/error/whatever)
• New data can only come from the ad platforms

Finally, I used a table merge to squash the three data partitions on top of each other, and synced it to the spreadsheet.

If you want to see more about how this example and others actually work in Parabola, read more on our site. If you have feedback or questions, shoot me an email!

‍