# M&A data in Snowflake

Source: https://developers.novilabs.com/ma/data/

> 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

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](https://developers.novilabs.com/data/delivery/).

- **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.

```sql title="Set your session"
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](https://developers.novilabs.com/start/authentication/#snowflake-login-and-token)).

```bash title="Count deals over HTTPS"
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"}'
```

## Views

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_DEAL` | `MA_DEAL_KEY` | A deal |
| `MA_DEAL_SCOPE` | `MA_DEAL_KEY`, `BASIN`, `SUBBASIN` | A basin and subbasin that a deal touches |
| `MA_DEAL_VALUATION` | `MA_DEAL_KEY` | Novi's valuation of a deal |
| `MA_DEAL_COMMENT` | `MA_DEAL_KEY`, `WORKSHEET_COLUMN` | The analyst's comment on one field of a deal |
| `MA_DEAL_PARTY` | `MA_DEAL_KEY`, `PARTY_KEY`, `ROLE`, `DEAL_SIDE` | A party in one role on one side of a deal |
| `PARTY` | `PARTY_KEY` | A company |
| `PARTY_ALIAS` | `PARTY_KEY`, `ALIAS` | A name a company was recorded under |
| `MA_DEAL_DOCUMENT` | `MA_DEAL_KEY`, `MA_DOCUMENT_KEY` | A document that covers a deal |
| `MA_DOCUMENT` | `MA_DOCUMENT_KEY` | A document written by Novi |

## How the views join

| From | To | On |
| --- | --- | --- |
| `MA_DEAL_SCOPE`, `MA_DEAL_VALUATION`, `MA_DEAL_COMMENT`, `MA_DEAL_PARTY`, `MA_DEAL_DOCUMENT` | `MA_DEAL` | `MA_DEAL_KEY` |
| `MA_DEAL_PARTY` | `PARTY` | `PARTY_KEY` |
| `PARTY_ALIAS` | `PARTY` | `PARTY_KEY` |
| `MA_DEAL_DOCUMENT` | `MA_DOCUMENT` | `MA_DOCUMENT_KEY` |

`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

The columns most analyses start from. The guide describes every column.

### MA_DEAL

| Column | Meaning |
| --- | --- |
| `ANNOUNCE_DT`, `CLOSE_DT` | Announcement and closing dates. `CLOSE_DT` is empty until the deal closes |
| `DEAL_STATUS` | `ANNOUNCED`, `CLOSED` or `TERMINATED`. Ownership has not moved until `CLOSED` |
| `DEAL_TYPE`, `ASSET_OR_CORPORATE` | Merger or acquisition, and whether assets or the corporate entity changed hands |
| `DEAL_DESCRIPTION` | A one-line summary of the deal |
| `COMMODITY` | Primary commodity character of the deal's assets |
| `TOTAL_CONSIDERATION_USD_MM` | Headline consideration, USD millions. On all but a few deals it equals `CASH_USD_MM` + `ISSUED_EQUITY_USD_MM` + `DEBT_ASSUMED_USD_MM` + `OTHER_CONSIDERATION_USD_MM`, empty parts as 0 |
| `TOTAL_PRODUCTION_MBOE_D` | Production at deal time, thousand boe per day |
| `NET_MINERAL_ACRES`, `WORKING_INTEREST` | 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_PER_FLOWING_BOE_D_USD`, `CONSIDERATION_PER_NET_ACRE_USD` | Price per flowing boe/d and per net acre, in US dollars |
| `STATES`, `CONSIDERATION_TYPE`, `DEAL_ASSET_TAGS` | Lists, stored as VARIANT arrays |
| `DEAL_GROUP_KEY` | Shared by deals recorded as parts of one package. Empty for most deals |

### 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_LOWER_48`, `GULF_OF_MEXICO`, `CANADA`, `ALASKA`, `INTERNATIONAL` or `UNDISCLOSED` |
| `STATE` | State or province for this basin, when the source gives it |

### 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_PV10_USD_MM` | Present value of proved developed producing reserves at 10% |
| `ALLOCATED_VALUE_PDP_USD_MM`, `ALLOCATED_VALUE_PUD_USD_MM`, `ALLOCATED_VALUE_OTHER_USD_MM` | How the price divides between producing wells, undeveloped locations and other assets |
| `IMPLIED_VALUE_PER_LOCATION_USD_MM` | Implied value per undeveloped location |
| `RQ_TIER1_LOCATIONS` to `RQ_TIER4_LOCATIONS` | Locations by Novi rock-quality tier |
| `PDP_PV10_PER_FLOWING_BOE_D_USD` | PDP PV10 per flowing boe/d, in US dollars |
| `BENCHMARK_WTI_PROMPT`, `BENCHMARK_HH_PROMPT`, `STRIP_DATE` | 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

| Column | Meaning |
| --- | --- |
| `MA_DEAL_PARTY.ROLE` | `PRINCIPAL` for a buyer or seller. `SPONSOR`, `ADVISOR_FINANCIAL`, `ADVISOR_LEGAL`, `RECEIVER` or `TRUSTEE` for a party attached to one side |
| `MA_DEAL_PARTY.DEAL_SIDE` | `BUYER` or `SELLER`. For a sponsor or advisor, the side it backs or advises |
| `MA_DEAL_PARTY.PARTY_NAME_REPORTED` | The name as this deal recorded it |
| `MA_DEAL_PARTY.TICKER`, `IS_PUBLIC`, `MARKET_CAP_USD_MM`, `ENTERPRISE_VALUE_USD_MM` | A principal's ticker, listing, market cap and enterprise value at the time of the deal |
| `PARTY.PARTY_NAME` | One display name per company, for grouping |
| `PARTY_ALIAS.ALIAS`, `ALIAS_TYPE` | Every name a company was recorded under, such as a former name before a rebrand |

### MA_DEAL_COMMENT, MA_DEAL_DOCUMENT and MA_DOCUMENT

| Column | Meaning |
| --- | --- |
| `MA_DEAL_COMMENT.WORKSHEET_COLUMN_LABEL`, `COMMENT_TEXT` | The field the analyst commented on, and the comment |
| `MA_DEAL_DOCUMENT.PAGE_NUMBER` | Page where the deal appears in a document that covers several deals |
| `MA_DOCUMENT.TITLE`, `PUBLISHED_DATE`, `PAGE_COUNT` | Novi's written analysis of a deal |
| `MA_DOCUMENT.DOWNLOAD_URL` | 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

- **Sum deal values on `MA_DEAL` only.** A join to `MA_DEAL_PARTY`, `MA_DEAL_SCOPE`, `MA_DEAL_COMMENT` or `MA_DEAL_DOCUMENT` repeats a deal's values on each of its rows. Filter with `EXISTS` instead.
- **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 `ROLE` and `DEAL_SIDE`.** A buyer is `ROLE = 'PRINCIPAL'` and `DEAL_SIDE = 'BUYER'`. `DEAL_SIDE` alone also returns sponsors and advisors.
- **Group companies on `PARTY_KEY`.** Search `PARTY_ALIAS.ALIAS` to find one by a former name.
- **Units are in the column name.** `_USD_MM` is millions of US dollars, `_MBOE_D` thousand 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)`. Use `LATERAL FLATTEN` for one row per value, then count distinct `MA_DEAL_KEY`.

## Starter SQL

```sql title="The 50 most recently announced deals, with buyers and sellers"
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;
```

```sql title="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;
```

```sql title="Total consideration of deals that touch the Permian, each deal once"
SELECT SUM(d.TOTAL_CONSIDERATION_USD_MM) AS 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'
);
```

That total includes the whole value of any deal that also touches another basin.

```sql title="Download links for Novi's deal analysis"
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;
```

More recipes, from the cookbook in your welcome email, are in the [M&A Intelligence cookbook](https://developers.novilabs.com/cookbook/ma-intelligence/): 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

View and column descriptions in Snowflake's catalog match the guide.

```sql title="Every view in NOVI_MA_INTEL"
SELECT table_name, table_type, row_count, comment
FROM NOVI_DATA_ACCESS.INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'NOVI_MA_INTEL'
ORDER BY table_type, table_name;
```

```sql title="Columns of MA_DEAL"
SELECT column_name, data_type, is_nullable, comment
FROM NOVI_DATA_ACCESS.INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'NOVI_MA_INTEL'
  AND table_name   = 'MA_DEAL'
ORDER BY ordinal_position;
```

> **Tip:**
> No rows in any view means your M&A Intelligence subscription is not active on that account. A total larger than you expect means a join to a view with several rows per deal counted a deal more than once.
