This is Part 3 of the Antenna SQL Tutorial. In Part 1 we calculated custom Reach and Frequency metrics and in Part 2 we calculated custom attribution scores.
The Bid Performance Report in Beeswax [Reporting](🔗) has Win Rate data, but it doesn’t have all the dimensions that may be relevant to a more niche analysis of your bid performance. Similarly, while Beeswax has several reports, there are some “cross-dimensional” analyses that are not possible in the same report. What if you want to see win rates for domains also broken out by zip code? Let’s do it!
To start, we’re going to need to need the bid data in our “left” table with the relevant fields selected. We’re going to use the `
shared_bid_request_view` for this. This is because the Bid Request view is de-duplicated to a single row per auction. (By contrast, the `
shared_bids_view` will contain data for all bid responses that were made on a given auction.) If we make two bids from different line items on the same auction and one wins, we want to count that as a win, not a win and a loss.
Now we need to take this bid data and join it to our impression data to derive a win rate. We’ll do this using a `
LEFT JOIN`; we know that some of our bids won’t have a matching impression because the auction wasn’t won, so no impression will exist to match against. So our final table view needs all bids and the matching won impressions. The won impression columns should be null if there was no won impression. A left join will achieve this.
Finally, let’s calculate the win rate. We’ll do this by counting non-null records in the joined impression table and dividing it by the count of bids. A non-null record in the impression table means there was a matching impression and thus the bid was won by us.
We can now see which domain/zip code combos we’re bidding on that we’re winning on most frequently ordered to least frequently.
These queries are just a few examples of possible use cases with Antenna, but this is just a limited sample of the full capabilities of what you can do! We’re also planning to add new datasets and expand the Antenna integration more deeply with our Buzz platform in the future. Stay tuned for additional details in the coming months.
In the interim, check out additional documentation and tips for Antenna usage [here](🔗), and find the full list of available data points in the Antenna Data Warehouse [here](🔗).