Antenna SQL Tutorial: Calculating Win and Bid Rates

👍

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.

SELECT
	domain,
	geo_zip,
	COUNT(*) AS bids
FROM shared_bid_request_view
GROUP BY domain, geo_zip

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.

...

FROM shared_bid_request_view bids
LEFT JOIN shared_impression_details_view imps
  ON bids.auction_id = imps.auction_id

...

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.

SELECT
	bids.domain,
	bids.geo_zip,
	COUNT(*) AS bids,
	SUM(CASE WHEN imps.auction_id IS NOT null THEN 1 ELSE 0 END) AS impressions
	SUM(CASE WHEN imps.auction_id IS NOT null THEN 1 ELSE 0 END)/COUNT(*) AS win_rate
FROM shared_bid_request_view bids
LEFT JOIN shared_impression_details_view imps
  ON bids.auction_id = imps.auction_id
WHERE bids.bid_time_utc >= CURRENT_DATE() - 7
  -- We don't want to scan the whole table if we can help it, so we want to set a date filter, but the impressions table will be null for lost auctions, so we specify a condition to handle for nulls as well.
  AND (imps.bid_time_utc >= CURRENT_DATE() - 7 OR imps.bid_time_utc IS null)
GROUP BY domain, geo_zip
-- For good measure, we'll filter out anything with a small number of bids
HAVING bids > 100
ORDER BY win_rate DESC

We can now see which domain/zip code combos we’re bidding on that we’re winning on most frequently ordered to least frequently.

Conclusion

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.