M&A data in Snowflake
The nine views in NOVI_MA_INTEL, how they join, the columns most analyses need, and SQL that counts each deal once.
Query M&A deals in Snowflake database NOVI_DATA_ACCESS, schema NOVI_MA_INTEL. The current release is 1.6.72, the first release.
Connect
Section titled “Connect”Connect the same ways as Novi UDM Data: a Novi-hosted reader account, a share into your own Snowflake account, or SQL over HTTPS. Setup: Delivery options.
- Its own subscription. M&A Intelligence is licensed separately. On your own Snowflake account it arrives in the same Novi share and database as any other Novi data you subscribe to.
- Every deal. There is no basin filter on this schema.
- Its own guide. The welcome email includes the M&A Intelligence guide (PDF) and an example cookbook with 10 recipes.
Set the database and schema at the start of a session, and the SQL on this page runs as printed.
USE DATABASE NOVI_DATA_ACCESS;USE SCHEMA NOVI_MA_INTEL;From a script, send the same SQL over HTTPS with a Snowflake programmatic access token (Authentication).
curl -X POST "$NOVI_SNOWFLAKE_URL/api/v2/statements" \ -H "Authorization: Bearer $NOVI_SNOWFLAKE_TOKEN" \ -H "Content-Type: application/json" \ -d '{"statement": "SELECT COUNT(*) FROM NOVI_MA_INTEL.MA_DEAL", "warehouse": "NOVI_WH", "database": "NOVI_DATA_ACCESS"}'Start with MA_DEAL, one row per deal. Every other deal view joins to it on MA_DEAL_KEY.
| View | One row per | A row is |
|---|---|---|
MA_ |
MA_ |
A deal |
MA_ |
MA_, BASIN, SUBBASIN |
A basin and subbasin that a deal touches |
MA_ |
MA_ |
Novi’s valuation of a deal |
MA_ |
MA_, WORKSHEET_ |
The analyst’s comment on one field of a deal |
MA_ |
MA_, PARTY_, ROLE, DEAL_ |
A party in one role on one side of a deal |
PARTY |
PARTY_ |
A company |
PARTY_ |
PARTY_, ALIAS |
A name a company was recorded under |
MA_ |
MA_, MA_ |
A document that covers a deal |
MA_ |
MA_ |
A document written by Novi |
How the views join
Section titled “How the views join”| From | To | On |
|---|---|---|
MA_, MA_, MA_, MA_, MA_ |
MA_ |
MA_ |
MA_ |
PARTY |
PARTY_ |
PARTY_ |
PARTY |
PARTY_ |
MA_ |
MA_ |
MA_ |
MA_DEAL_PARTY and MA_DEAL_DOCUMENT are link views: go through them to get from a deal to its parties or documents. MA_DEAL_KEY is a 64-bit integer, NUMBER(19,0).
Key columns
Section titled “Key columns”The columns most analyses start from. The guide describes every column.
MA_DEAL
Section titled “MA_DEAL”| Column | Meaning |
|---|---|
ANNOUNCE_, CLOSE_ |
Announcement and closing dates. CLOSE_ is empty until the deal closes |
DEAL_ |
ANNOUNCED, CLOSED or TERMINATED. Ownership has not moved until CLOSED |
DEAL_, ASSET_ |
Merger or acquisition, and whether assets or the corporate entity changed hands |
DEAL_ |
A one-line summary of the deal |
COMMODITY |
Primary commodity character of the deal’s assets |
TOTAL_ |
Headline consideration, USD millions. On all but a few deals it equals CASH_ + ISSUED_ + DEBT_ + OTHER_, empty parts as 0 |
TOTAL_ |
Production at deal time, thousand boe per day |
NET_, WORKING_ |
Net acres conveyed, and working interest as a fraction from 0 to 1 |
LIQUIDS |
Liquids share of production as the analyst reports it, a fraction from 0 to 1 |
CONSIDERATION_, CONSIDERATION_ |
Price per flowing boe/d and per net acre, in US dollars |
STATES, CONSIDERATION_, DEAL_ |
Lists, stored as VARIANT arrays |
DEAL_ |
Shared by deals recorded as parts of one package. Empty for most deals |
MA_DEAL_SCOPE
Section titled “MA_DEAL_SCOPE”| Column | Meaning |
|---|---|
BASIN |
From Novi’s controlled list; Novi Wells basin names where Novi Wells has coverage |
SUBBASIN |
Sub-basin or play as reported. Part of the grain, so a deal in the Delaware and the Midland is two rows |
REGION |
US_, GULF_, CANADA, ALASKA, INTERNATIONAL or UNDISCLOSED |
STATE |
State or province for this basin, when the source gives it |
MA_DEAL_VALUATION
Section titled “MA_DEAL_VALUATION”Novi’s deal-level comparables valuation, not per-well economics. Money is USD millions unless the column name says otherwise.
| Column | Meaning |
|---|---|
PDP_ |
Present value of proved developed producing reserves at 10% |
ALLOCATED_, ALLOCATED_, ALLOCATED_ |
How the price divides between producing wells, undeveloped locations and other assets |
IMPLIED_ |
Implied value per undeveloped location |
RQ_ to RQ_ |
Locations by Novi rock-quality tier |
PDP_ |
PDP PV10 per flowing boe/d, in US dollars |
BENCHMARK_, BENCHMARK_, STRIP_ |
Prompt-month WTI (USD/bbl) and Henry Hub (USD/MMBtu) at deal time, and the as-of date of the forward strip the valuation used |
MA_DEAL_PARTY, PARTY and PARTY_ALIAS
Section titled “MA_DEAL_PARTY, PARTY and PARTY_ALIAS”| Column | Meaning |
|---|---|
MA_ |
PRINCIPAL for a buyer or seller. SPONSOR, ADVISOR_, ADVISOR_, RECEIVER or TRUSTEE for a party attached to one side |
MA_ |
BUYER or SELLER. For a sponsor or advisor, the side it backs or advises |
MA_ |
The name as this deal recorded it |
MA_, IS_, MARKET_, ENTERPRISE_ |
A principal’s ticker, listing, market cap and enterprise value at the time of the deal |
PARTY. |
One display name per company, for grouping |
PARTY_, ALIAS_ |
Every name a company was recorded under, such as a former name before a rebrand |
MA_DEAL_COMMENT, MA_DEAL_DOCUMENT and MA_DOCUMENT
Section titled “MA_DEAL_COMMENT, MA_DEAL_DOCUMENT and MA_DOCUMENT”| Column | Meaning |
|---|---|
MA_, COMMENT_ |
The field the analyst commented on, and the comment |
MA_ |
Page where the deal appears in a document that covers several deals |
MA_, PUBLISHED_, PAGE_ |
Novi’s written analysis of a deal |
MA_ |
A download link, good for 24 hours after your query runs. Empty while a new file is not ready |
The documents are Novi’s own analysis, not press releases, investor presentations or other third-party material.
Get the numbers right
Section titled “Get the numbers right”- Sum deal values on
MA_DEALonly. A join toMA_DEAL_PARTY,MA_DEAL_SCOPE,MA_DEAL_COMMENTorMA_DEAL_DOCUMENTrepeats a deal’s values on each of its rows. Filter withEXISTSinstead. - A deal’s value is not split between basins. Count deals by basin; a sum by basin gives each basin the whole deal.
- Buyers and sellers need
ROLEandDEAL_SIDE. A buyer isROLE = 'PRINCIPAL'andDEAL_SIDE = 'BUYER'.DEAL_SIDEalone also returns sponsors and advisors. - Group companies on
PARTY_KEY. SearchPARTY_ALIAS.ALIASto find one by a former name. - Units are in the column name.
_USD_MMis millions of US dollars,_MBOE_Dthousand boe per day. Percentages are fractions (0.18 is 18%). Ratios and per-unit prices do not add up across deals. - Empty is not zero. It means not reported or not applicable.
- Lists are arrays. Test one value with
ARRAY_CONTAINS('TX'::VARIANT, STATES). UseLATERAL FLATTENfor one row per value, then count distinctMA_DEAL_KEY.
Starter SQL
Section titled “Starter SQL”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 50;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 DESC;SELECT SUM(d.TOTAL_CONSIDERATION_USD_MM) AS 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');That total includes the whole value of any deal that also touches another basin.
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.TITLE;More recipes, from the cookbook in your welcome email, are in the M&A Intelligence cookbook: deal value and price per unit, Novi’s valuation, sponsors and advisors, public company deals and premiums, states and tags, and linked deals with analyst comments.
Look up columns yourself
Section titled “Look up columns yourself”View and column descriptions in Snowflake’s catalog match the guide.
SELECT table_name, table_type, row_count, commentFROM NOVI_DATA_ACCESS.INFORMATION_SCHEMA.TABLESWHERE table_schema = 'NOVI_MA_INTEL'ORDER BY table_type, table_name;SELECT column_name, data_type, is_nullable, commentFROM NOVI_DATA_ACCESS.INFORMATION_SCHEMA.COLUMNSWHERE table_schema = 'NOVI_MA_INTEL' AND table_name = 'MA_DEAL'ORDER BY ordinal_position;