Skip to content
Developers
Get an API token

Novi M&A Intelligence recipes

Ready-to-run Snowflake SQL recipes for Novi M&A Intelligence, from the cookbook your welcome email points to.

View MarkdownOpen in Claude(opens in a new tab)

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;

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 sellers
FROM NOVI_MA_INTEL.MA_DEAL d
LEFT 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_KEY
GROUP BY d.MA_DEAL_KEY, d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.DEAL_TYPE, d.DEAL_STATUS,
d.COMMODITY, d.TOTAL_CONSIDERATION_USD_MM
ORDER BY d.ANNOUNCE_DT DESC NULLS LAST
LIMIT 50

Deals announced but not closed. Ownership has not moved until DEAL_STATUS is CLOSED

SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.TOTAL_CONSIDERATION_USD_MM
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE d.DEAL_STATUS = 'ANNOUNCED'
ORDER BY d.ANNOUNCE_DT DESC

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_count
FROM NOVI_MA_INTEL.MA_DEAL_SCOPE s
GROUP BY s.BASIN
ORDER BY deal_count DESC

Deals in one basin, newest first

SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.DEAL_STATUS, d.TOTAL_CONSIDERATION_USD_MM
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE 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 DESC

Total 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_mm
FROM NOVI_MA_INTEL.MA_DEAL d
JOIN single_basin sb ON sb.MA_DEAL_KEY = d.MA_DEAL_KEY
GROUP BY sb.BASIN, announce_year
ORDER BY sb.BASIN, announce_year

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_mm
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE d.ANNOUNCE_DT IS NOT NULL
GROUP BY announce_year
ORDER BY announce_year

Median 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_acre
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE d.ANNOUNCE_DT IS NOT NULL
GROUP BY announce_year, d.COMMODITY
ORDER BY announce_year, d.COMMODITY

How 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_MM
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE d.TOTAL_CONSIDERATION_USD_MM >= 1000
ORDER BY d.TOTAL_CONSIDERATION_USD_MM DESC

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_MM
FROM NOVI_MA_INTEL.MA_DEAL d
JOIN NOVI_MA_INTEL.MA_DEAL_VALUATION v ON v.MA_DEAL_KEY = d.MA_DEAL_KEY
WHERE v.PDP_PV10_USD_MM IS NOT NULL
ORDER BY d.TOTAL_CONSIDERATION_USD_MM DESC NULLS LAST
LIMIT 50

The 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_SOURCE
FROM NOVI_MA_INTEL.MA_DEAL d
JOIN NOVI_MA_INTEL.MA_DEAL_VALUATION v ON v.MA_DEAL_KEY = d.MA_DEAL_KEY
WHERE v.PRICING_BASIS IS NOT NULL
ORDER BY d.ANNOUNCE_DT DESC

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_seller
FROM NOVI_MA_INTEL.MA_DEAL_PARTY dp
JOIN NOVI_MA_INTEL.PARTY p ON p.PARTY_KEY = dp.PARTY_KEY
WHERE dp.ROLE = 'SPONSOR'
GROUP BY p.PARTY_NAME
ORDER BY deals_backing_buyer + deals_backing_seller DESC
LIMIT 25

Sales by sellers that private equity owned, by year

SELECT YEAR(d.ANNOUNCE_DT) AS announce_year,
COUNT(DISTINCT d.MA_DEAL_KEY) AS deal_count
FROM NOVI_MA_INTEL.MA_DEAL d
JOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp ON dp.MA_DEAL_KEY = d.MA_DEAL_KEY
WHERE dp.ROLE = 'PRINCIPAL'
AND dp.DEAL_SIDE = 'SELLER'
AND dp.PRIVATE_TYPE = 'PRIVATE_EQUITY'
GROUP BY announce_year
ORDER BY announce_year

Financial and legal advisors by number of deals

SELECT dp.ROLE, p.PARTY_NAME AS advisor, COUNT(DISTINCT dp.MA_DEAL_KEY) AS deal_count
FROM NOVI_MA_INTEL.MA_DEAL_PARTY dp
JOIN NOVI_MA_INTEL.PARTY p ON p.PARTY_KEY = dp.PARTY_KEY
WHERE dp.ROLE IN ('ADVISOR_FINANCIAL', 'ADVISOR_LEGAL')
GROUP BY dp.ROLE, p.PARTY_NAME
ORDER BY deal_count DESC
LIMIT 25

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_EV
FROM NOVI_MA_INTEL.MA_DEAL d
JOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp ON dp.MA_DEAL_KEY = d.MA_DEAL_KEY
WHERE dp.ROLE = 'PRINCIPAL'
AND dp.DEAL_SIDE = 'BUYER'
AND dp.IS_PUBLIC
ORDER BY d.ANNOUNCE_DT DESC

Take-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_PREMIUM
FROM NOVI_MA_INTEL.MA_DEAL d
JOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp ON dp.MA_DEAL_KEY = d.MA_DEAL_KEY
WHERE dp.ROLE = 'PRINCIPAL'
AND dp.DEAL_SIDE = 'SELLER'
AND d.ASSET_OR_CORPORATE = 'CORPORATE'
AND d.EQUITY_PREMIUM IS NOT NULL
ORDER BY d.ANNOUNCE_DT DESC

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_count
FROM NOVI_MA_INTEL.MA_DEAL d
GROUP BY d.DEAL_TYPE, d.ASSET_OR_CORPORATE, d.DEAL_STRUCTURE_TAG
ORDER BY deal_count DESC

Deals that list Texas

SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.STATES
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE ARRAY_CONTAINS('TX'::VARIANT, d.STATES)
ORDER BY d.ANNOUNCE_DT DESC

Number 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_count
FROM NOVI_MA_INTEL.MA_DEAL d,
LATERAL FLATTEN(INPUT => d.STATES) s
GROUP BY state
ORDER BY deal_count DESC

Mineral and royalty deals that include stock in the consideration

SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.CONSIDERATION_TYPE, d.NET_ROYALTY_ACRES
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE ARRAY_CONTAINS('MINERAL_ROYALTY'::VARIANT, d.DEAL_ASSET_TAGS)
AND ARRAY_CONTAINS('STOCK'::VARIANT, d.CONSIDERATION_TYPE)
ORDER BY d.ANNOUNCE_DT DESC

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_mm
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE d.DEAL_GROUP_KEY IS NOT NULL
GROUP BY d.DEAL_GROUP_KEY
ORDER BY first_announced DESC

Comments on the most recent deals

SELECT d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, c.WORKSHEET_COLUMN_LABEL, c.COMMENT_TEXT
FROM NOVI_MA_INTEL.MA_DEAL_COMMENT c
JOIN NOVI_MA_INTEL.MA_DEAL d ON d.MA_DEAL_KEY = c.MA_DEAL_KEY
ORDER BY d.ANNOUNCE_DT DESC, c.WORKSHEET_COLUMN_LABEL
LIMIT 100

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_NUMBER
FROM NOVI_MA_INTEL.MA_DEAL d
JOIN NOVI_MA_INTEL.MA_DEAL_DOCUMENT dd ON dd.MA_DEAL_KEY = d.MA_DEAL_KEY
JOIN NOVI_MA_INTEL.MA_DOCUMENT doc ON doc.MA_DOCUMENT_KEY = dd.MA_DOCUMENT_KEY
ORDER BY d.ANNOUNCE_DT DESC

Download 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_URL
FROM NOVI_MA_INTEL.MA_DOCUMENT doc
WHERE doc.DOWNLOAD_URL IS NOT NULL
ORDER BY doc.PUBLISHED_DATE DESC NULLS LAST, doc.TITLE

These recipes are in the full cookbook your welcome email points to, not on this page:

  • Cookbook recipe 3: Buyers and sellers