Novi M&A Intelligence recipes
Ready-to-run Snowflake SQL recipes for Novi M&A Intelligence, from the cookbook your welcome email points to.
These recipes query the NOVI_MA_INTEL schema. They match Novi M&A Intelligence release 1.6.72, the release your account reads, and the full cookbook your welcome email points to holds the same SQL.
Run this once per session first:
USE DATABASE NOVI_DATA_ACCESS;1. Recent deals
Section titled “1. Recent deals”The newest deals, with the buyer and the seller of each. A deal can have several buyers or several sellers, so the names are collected into one list per side and each deal stays one row.
Tables: MA_DEAL, MA_DEAL_PARTY, PARTY.
The 50 most recently announced deals
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.DEAL_TYPE, d.DEAL_STATUS, d.COMMODITY, d.TOTAL_CONSIDERATION_USD_MM, LISTAGG(DISTINCT CASE WHEN dp.DEAL_SIDE = 'BUYER' THEN p.PARTY_NAME END, '; ') AS buyers, LISTAGG(DISTINCT CASE WHEN dp.DEAL_SIDE = 'SELLER' THEN p.PARTY_NAME END, '; ') AS sellersFROM NOVI_MA_INTEL.MA_DEAL dLEFT JOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp ON dp.MA_DEAL_KEY = d.MA_DEAL_KEY AND dp.ROLE = 'PRINCIPAL'LEFT JOIN NOVI_MA_INTEL.PARTY p ON p.PARTY_KEY = dp.PARTY_KEYGROUP BY d.MA_DEAL_KEY, d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.DEAL_TYPE, d.DEAL_STATUS, d.COMMODITY, d.TOTAL_CONSIDERATION_USD_MMORDER BY d.ANNOUNCE_DT DESC NULLS LASTLIMIT 50Deals announced but not closed. Ownership has not moved until DEAL_STATUS is CLOSED
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.TOTAL_CONSIDERATION_USD_MMFROM NOVI_MA_INTEL.MA_DEAL dWHERE d.DEAL_STATUS = 'ANNOUNCED'ORDER BY d.ANNOUNCE_DT DESC2. Deals by basin
Section titled “2. Deals by basin”Deals by basin. MA_DEAL_SCOPE has one row per deal, basin and subbasin, and holds no values. A deal in three basins is three rows there, and its value is stored once on MA_DEAL. Count deals by basin, but do not add up a deal’s value by basin: each basin would get the whole deal.
Tables: MA_DEAL, MA_DEAL_SCOPE.
Number of deals that touch each basin
SELECT s.BASIN, COUNT(DISTINCT s.MA_DEAL_KEY) AS deal_countFROM NOVI_MA_INTEL.MA_DEAL_SCOPE sGROUP BY s.BASINORDER BY deal_count DESCDeals in one basin, newest first
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.DEAL_STATUS, d.TOTAL_CONSIDERATION_USD_MMFROM NOVI_MA_INTEL.MA_DEAL dWHERE EXISTS ( SELECT 1 FROM NOVI_MA_INTEL.MA_DEAL_SCOPE s WHERE s.MA_DEAL_KEY = d.MA_DEAL_KEY AND s.BASIN = 'Permian')ORDER BY d.ANNOUNCE_DT DESCTotal consideration of the deals in exactly one basin, by basin and year
Multi-basin deals are left out, so no value is counted twice.
WITH single_basin AS ( SELECT s.MA_DEAL_KEY, MIN(s.BASIN) AS BASIN FROM NOVI_MA_INTEL.MA_DEAL_SCOPE s GROUP BY s.MA_DEAL_KEY HAVING COUNT(DISTINCT s.BASIN) = 1)SELECT sb.BASIN, YEAR(d.ANNOUNCE_DT) AS announce_year, COUNT(*) AS deal_count, SUM(d.TOTAL_CONSIDERATION_USD_MM) AS total_consideration_usd_mmFROM NOVI_MA_INTEL.MA_DEAL dJOIN single_basin sb ON sb.MA_DEAL_KEY = d.MA_DEAL_KEYGROUP BY sb.BASIN, announce_yearORDER BY sb.BASIN, announce_year4. Deal value and metrics
Section titled “4. Deal value and metrics”Deal value over time and price paid per unit. Values are in USD millions. The per-unit columns are ratios, so average them and do not add them up.
Tables: MA_DEAL.
Deal count and total consideration by announcement year
SELECT YEAR(d.ANNOUNCE_DT) AS announce_year, COUNT(*) AS deal_count, SUM(d.TOTAL_CONSIDERATION_USD_MM) AS total_consideration_usd_mm, MEDIAN(d.TOTAL_CONSIDERATION_USD_MM) AS median_consideration_usd_mmFROM NOVI_MA_INTEL.MA_DEAL dWHERE d.ANNOUNCE_DT IS NOT NULLGROUP BY announce_yearORDER BY announce_yearMedian price per flowing barrel and per net acre, by year and commodity
SELECT YEAR(d.ANNOUNCE_DT) AS announce_year, d.COMMODITY, COUNT(d.CONSIDERATION_PER_FLOWING_BOE_D_USD) AS deals_with_flowing_metric, MEDIAN(d.CONSIDERATION_PER_FLOWING_BOE_D_USD) AS median_usd_per_boe_d, MEDIAN(d.CONSIDERATION_PER_NET_ACRE_USD) AS median_usd_per_net_acreFROM NOVI_MA_INTEL.MA_DEAL dWHERE d.ANNOUNCE_DT IS NOT NULLGROUP BY announce_year, d.COMMODITYORDER BY announce_year, d.COMMODITYHow the consideration was paid. The four parts add up to the total; NULL means the deal had no such part
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.TOTAL_CONSIDERATION_USD_MM, d.CASH_USD_MM, d.ISSUED_EQUITY_USD_MM, d.DEBT_ASSUMED_USD_MM, d.OTHER_CONSIDERATION_USD_MMFROM NOVI_MA_INTEL.MA_DEAL dWHERE d.TOTAL_CONSIDERATION_USD_MM >= 1000ORDER BY d.TOTAL_CONSIDERATION_USD_MM DESC5. Valuation
Section titled “5. Valuation”Novi’s valuation of a deal: how the price divides between producing wells (PDP), undeveloped locations (PUD) and other assets, and the assumptions behind it. A column is empty where Novi has no figure for the deal.
Tables: MA_DEAL, MA_DEAL_VALUATION.
Price allocation and implied value per location for the largest valued deals
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.TOTAL_CONSIDERATION_USD_MM, v.PDP_PV10_USD_MM, v.ALLOCATED_VALUE_PDP_USD_MM, v.ALLOCATED_VALUE_PUD_USD_MM, v.ALLOCATED_VALUE_OTHER_USD_MM, v.REMAINING_LOCATIONS_NET, v.IMPLIED_VALUE_PER_LOCATION_USD_MMFROM NOVI_MA_INTEL.MA_DEAL dJOIN NOVI_MA_INTEL.MA_DEAL_VALUATION v ON v.MA_DEAL_KEY = d.MA_DEAL_KEYWHERE v.PDP_PV10_USD_MM IS NOT NULLORDER BY d.TOTAL_CONSIDERATION_USD_MM DESC NULLS LASTLIMIT 50The price and cost assumptions of each valuation. Read LEASE_OPERATING_EXPENSE and GPTM together with OPEX_UNITS
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, v.PRICING_BASIS, v.STRIP_DATE, v.ASSUMED_OIL_PRICE_USD_BBL, v.ASSUMED_GAS_PRICE_USD_MCF, v.OPEX_UNITS, v.LEASE_OPERATING_EXPENSE, v.GPTM, v.IMPLIED_PDP_DISCOUNT_RATE, v.PDP_VALUE_SOURCEFROM NOVI_MA_INTEL.MA_DEAL dJOIN NOVI_MA_INTEL.MA_DEAL_VALUATION v ON v.MA_DEAL_KEY = d.MA_DEAL_KEYWHERE v.PRICING_BASIS IS NOT NULLORDER BY d.ANNOUNCE_DT DESC6. Sponsors and advisors
Section titled “6. Sponsors and advisors”The parties behind the principals. A SPONSOR row is the owner or backer of one side, and an ADVISOR row is a firm that side engaged. DEAL_SIDE on these rows is the side they stand behind.
Tables: MA_DEAL, MA_DEAL_PARTY, PARTY.
Sponsors by number of deals, split by the side they backed
SELECT p.PARTY_NAME AS sponsor, COUNT(DISTINCT CASE WHEN dp.DEAL_SIDE = 'BUYER' THEN dp.MA_DEAL_KEY END) AS deals_backing_buyer, COUNT(DISTINCT CASE WHEN dp.DEAL_SIDE = 'SELLER' THEN dp.MA_DEAL_KEY END) AS deals_backing_sellerFROM NOVI_MA_INTEL.MA_DEAL_PARTY dpJOIN NOVI_MA_INTEL.PARTY p ON p.PARTY_KEY = dp.PARTY_KEYWHERE dp.ROLE = 'SPONSOR'GROUP BY p.PARTY_NAMEORDER BY deals_backing_buyer + deals_backing_seller DESCLIMIT 25Sales by sellers that private equity owned, by year
SELECT YEAR(d.ANNOUNCE_DT) AS announce_year, COUNT(DISTINCT d.MA_DEAL_KEY) AS deal_countFROM NOVI_MA_INTEL.MA_DEAL dJOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp ON dp.MA_DEAL_KEY = d.MA_DEAL_KEYWHERE dp.ROLE = 'PRINCIPAL' AND dp.DEAL_SIDE = 'SELLER' AND dp.PRIVATE_TYPE = 'PRIVATE_EQUITY'GROUP BY announce_yearORDER BY announce_yearFinancial and legal advisors by number of deals
SELECT dp.ROLE, p.PARTY_NAME AS advisor, COUNT(DISTINCT dp.MA_DEAL_KEY) AS deal_countFROM NOVI_MA_INTEL.MA_DEAL_PARTY dpJOIN NOVI_MA_INTEL.PARTY p ON p.PARTY_KEY = dp.PARTY_KEYWHERE dp.ROLE IN ('ADVISOR_FINANCIAL', 'ADVISOR_LEGAL')GROUP BY dp.ROLE, p.PARTY_NAMEORDER BY deal_count DESCLIMIT 257. Public company deals
Section titled “7. Public company deals”Public buyers and sellers. The ticker, share price, market capitalization and enterprise value are on the principal’s MA_DEAL_PARTY row, as they were before the deal was announced.
Tables: MA_DEAL, MA_DEAL_PARTY.
Public buyers: deal size against the buyer’s own size
SELECT d.ANNOUNCE_DT, dp.PARTY_NAME_REPORTED AS buyer, dp.TICKER, d.TOTAL_CONSIDERATION_USD_MM, dp.MARKET_CAP_USD_MM, dp.ENTERPRISE_VALUE_USD_MM, d.DEAL_PRICE_TO_BUYER_EVFROM NOVI_MA_INTEL.MA_DEAL dJOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp ON dp.MA_DEAL_KEY = d.MA_DEAL_KEYWHERE dp.ROLE = 'PRINCIPAL' AND dp.DEAL_SIDE = 'BUYER' AND dp.IS_PUBLICORDER BY d.ANNOUNCE_DT DESCTake-outs of public companies, with the premium paid. Premiums are fractions: 0.15 means 15%
SELECT d.ANNOUNCE_DT, dp.PARTY_NAME_REPORTED AS seller, dp.TICKER, d.TOTAL_CONSIDERATION_USD_MM, d.EQUITY_PREMIUM, d.EV_PREMIUMFROM NOVI_MA_INTEL.MA_DEAL dJOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp ON dp.MA_DEAL_KEY = d.MA_DEAL_KEYWHERE dp.ROLE = 'PRINCIPAL' AND dp.DEAL_SIDE = 'SELLER' AND d.ASSET_OR_CORPORATE = 'CORPORATE' AND d.EQUITY_PREMIUM IS NOT NULLORDER BY d.ANNOUNCE_DT DESC8. States types and tags
Section titled “8. States types and tags”Filter by deal structure, and read the list columns. STATES, CONSIDERATION_TYPE and DEAL_ASSET_TAGS hold a list of values per deal. Use. ARRAY_CONTAINS to test for one value and LATERAL FLATTEN to get one row per value.
Tables: MA_DEAL.
Deals by type, scope and structure
SELECT d.DEAL_TYPE, d.ASSET_OR_CORPORATE, d.DEAL_STRUCTURE_TAG, COUNT(*) AS deal_countFROM NOVI_MA_INTEL.MA_DEAL dGROUP BY d.DEAL_TYPE, d.ASSET_OR_CORPORATE, d.DEAL_STRUCTURE_TAGORDER BY deal_count DESCDeals that list Texas
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.STATESFROM NOVI_MA_INTEL.MA_DEAL dWHERE ARRAY_CONTAINS('TX'::VARIANT, d.STATES)ORDER BY d.ANNOUNCE_DT DESCNumber of deals per state. A deal that lists two states counts once in each
SELECT s.VALUE::STRING AS state, COUNT(DISTINCT d.MA_DEAL_KEY) AS deal_countFROM NOVI_MA_INTEL.MA_DEAL d, LATERAL FLATTEN(INPUT => d.STATES) sGROUP BY stateORDER BY deal_count DESCMineral and royalty deals that include stock in the consideration
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.CONSIDERATION_TYPE, d.NET_ROYALTY_ACRESFROM NOVI_MA_INTEL.MA_DEAL dWHERE ARRAY_CONTAINS('MINERAL_ROYALTY'::VARIANT, d.DEAL_ASSET_TAGS) AND ARRAY_CONTAINS('STOCK'::VARIANT, d.CONSIDERATION_TYPE)ORDER BY d.ANNOUNCE_DT DESC9. Linked deals and comments
Section titled “9. Linked deals and comments”Deals recorded as parts of one package, and the analyst’s comments on a deal. DEAL_GROUP_KEY is the same on every deal of a package and NULL on a deal that stands alone. A comment belongs to one field of a deal, and. WORKSHEET_COLUMN_LABEL names that field.
Tables: MA_DEAL, MA_DEAL_COMMENT.
Packages of linked deals, with the number of deals in each
SELECT d.DEAL_GROUP_KEY, COUNT(*) AS deals_in_package, MIN(d.ANNOUNCE_DT) AS first_announced, SUM(d.TOTAL_CONSIDERATION_USD_MM) AS package_consideration_usd_mmFROM NOVI_MA_INTEL.MA_DEAL dWHERE d.DEAL_GROUP_KEY IS NOT NULLGROUP BY d.DEAL_GROUP_KEYORDER BY first_announced DESCComments on the most recent deals
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, c.WORKSHEET_COLUMN_LABEL, c.COMMENT_TEXTFROM NOVI_MA_INTEL.MA_DEAL_COMMENT cJOIN NOVI_MA_INTEL.MA_DEAL d ON d.MA_DEAL_KEY = c.MA_DEAL_KEYORDER BY d.ANNOUNCE_DT DESC, c.WORKSHEET_COLUMN_LABELLIMIT 10010. Novi deal documents
Section titled “10. Novi deal documents”Novi’s own written analysis of a deal. MA_DOCUMENT has one row per document, and MA_DEAL_DOCUMENT links a document to each deal it covers. One document can cover several deals. DOWNLOAD_URL is a link that downloads the file of a document. At this time, only Novi research has a file: the views do not hold press releases or other documents from third parties.
Tables: MA_DEAL, MA_DEAL_DOCUMENT, MA_DOCUMENT.
Deals that have a Novi document, newest first
SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, doc.DOCUMENT_TYPE, doc.TITLE, doc.PUBLISHED_DATE, doc.PAGE_COUNT, dd.PAGE_NUMBERFROM NOVI_MA_INTEL.MA_DEAL dJOIN NOVI_MA_INTEL.MA_DEAL_DOCUMENT dd ON dd.MA_DEAL_KEY = d.MA_DEAL_KEYJOIN NOVI_MA_INTEL.MA_DOCUMENT doc ON doc.MA_DOCUMENT_KEY = dd.MA_DOCUMENT_KEYORDER BY d.ANNOUNCE_DT DESCDownload links for Novi research. Snowflake makes each link when the query
runs, and the link stops working 24 hours later; run the query again for a new link. DOWNLOAD_URL is NULL while a new document’s file is not ready.
SELECT doc.TITLE, doc.PUBLISHED_DATE, doc.PAGE_COUNT, doc.SIZE_BYTES, doc.DOWNLOAD_URLFROM NOVI_MA_INTEL.MA_DOCUMENT docWHERE doc.DOWNLOAD_URL IS NOT NULLORDER BY doc.PUBLISHED_DATE DESC NULLS LAST, doc.TITLEIn the notebook only
Section titled “In the notebook only”These recipes are in the full cookbook your welcome email points to, not on this page:
- Cookbook recipe 3: Buyers and sellers