> ## Documentation Index
> Fetch the complete documentation index at: https://docs.narrative.io/llms.txt
> Use this file to discover all available pages before exploring further.

# IP Address to MAID Enrichment

> Resolve IP addresses to mobile advertising IDs using Rosetta Stone for cross-device targeting and mobile campaign activation

A company has server logs or web analytics data containing IP addresses, but needs mobile advertising IDs (MAIDs) to activate audiences on mobile channels. By joining their IP dataset against Rosetta Stone, they can resolve IPs to MAIDs observed on the same network, rank them by observation frequency, and build targetable mobile audiences—all without querying individual data providers.

## What you will build

This cookbook walks through creating an IP-to-MAID enrichment pipeline that:

1. Uploads a dataset of IP addresses from your server logs or web analytics
2. Previews MAID data availability by type (IDFA vs GAID) across Rosetta Stone
3. Matches IPs to MAIDs using an identity join against Rosetta Stone
4. Ranks MAIDs by observation frequency to surface the most reliable matches
5. Automates recurring enrichment with a materialized view

The result is a continuously refreshed dataset that maps your IP addresses to mobile advertising IDs for cross-device targeting.

## Prerequisites

Before starting, ensure you have:

* A dataset of IP addresses uploaded to Narrative
* Familiarity with [NQL syntax](/nql/general/syntax)
* Understanding of [Mobile Ad IDs](/concepts/identifiers/mobile-ad-ids) (IDFA, GAID)
* Understanding of [IP addresses](/concepts/data-formats/ip-addresses) and their limitations
* Understanding of [materialized views](/concepts/nql/materialized-views)

***

## Step 1: Upload your IP address dataset

Upload your IP address file to Narrative. This dataset serves as the seed list you want to enrich with MAIDs.

Your dataset should include at minimum:

| Column        | Type      | Description                             |
| ------------- | --------- | --------------------------------------- |
| `ip`          | string    | IPv4 or IPv6 address                    |
| `observed_at` | timestamp | When the IP was observed in your system |

You may also include additional context columns (session ID, page URL, etc.) that you want to carry through to the enriched output.

<Note>
  Upload your dataset through the Narrative UI or [SDK](/guides/sdk/uploading-data). The `ip` column does not need a Rosetta Stone mapping—you will join it directly against the `"ip_address"` attribute in Rosetta Stone.
</Note>

***

## Step 2: Preview IP-to-MAID data availability

Before running the full enrichment, query Rosetta Stone to understand how much MAID coverage is available by identifier type:

```sql theme={null}
SELECT
    rs."mobile_id_unique_identifier"."type" AS maid_type,
    APPROX_COUNT_DISTINCT(rs."ip_address") AS unique_ips,
    APPROX_COUNT_DISTINCT(rs."mobile_id_unique_identifier"."value") AS unique_maids
FROM narrative.rosetta_stone rs
WHERE
    rs."mobile_id_unique_identifier"."value" IS NOT NULL
    AND rs."ip_address" IS NOT NULL
    AND rs."event_timestamp" >= CURRENT_TIMESTAMP - INTERVAL '45' DAY
GROUP BY rs."mobile_id_unique_identifier"."type"
```

This shows you the relative volume of IDFA vs GAID data and helps set expectations for match rates.

<Tip>
  GAID (Android) typically has higher volume than IDFA (iOS) due to Apple's App Tracking Transparency framework. See [Mobile Ad IDs](/concepts/identifiers/mobile-ad-ids) for details on availability trends.
</Tip>

***

## Step 3: Match IPs to MAIDs

Join your IP dataset against Rosetta Stone to find MAIDs observed on the same IP addresses. This is the core enrichment query:

```sql theme={null}
SELECT
    cd."ip",
    rs."mobile_id_unique_identifier"."value" AS maid,
    rs."mobile_id_unique_identifier"."type" AS maid_type,
    rs."iso_3166_1_country" AS country_code,
    rs."event_timestamp" AS observed_at
FROM narrative.rosetta_stone rs
INNER JOIN company_data."your_ip_dataset" cd
    ON rs."ip_address" = cd."ip"
WHERE
    rs."mobile_id_unique_identifier"."value" IS NOT NULL
    AND rs."ip_address" IS NOT NULL
    AND rs."event_timestamp" >= CURRENT_TIMESTAMP - INTERVAL '45' DAY
```

**Key patterns used:**

| Pattern                                       | Purpose                                         |
| --------------------------------------------- | ----------------------------------------------- |
| `INNER JOIN company_data`                     | Matches only IPs present in your seed dataset   |
| `mobile_id_unique_identifier.value` / `.type` | Extracts MAID value and type (IDFA or GAID)     |
| `ip_address`                                  | Rosetta Stone's normalized IP address attribute |
| `INTERVAL '45' DAY`                           | Limits to recent observations for freshness     |

<Note>
  A single IP address may resolve to many MAIDs—especially shared or commercial IPs. Step 4 shows how to rank and limit results to the most reliable matches.
</Note>

***

## Step 4: Rank MAIDs by observation frequency

A single IP can map to many devices, and not all associations are equally reliable. Use `QUALIFY` with `ROW_NUMBER` and a nested `APPROX_COUNT_DISTINCT` window function to keep only the top N most-frequently-observed MAIDs per IP:

```sql theme={null}
SELECT
    cd."ip",
    rs."mobile_id_unique_identifier"."value" AS maid,
    rs."mobile_id_unique_identifier"."type" AS maid_type,
    rs."iso_3166_1_country" AS country_code
FROM narrative.rosetta_stone rs
INNER JOIN company_data."your_ip_dataset" cd
    ON rs."ip_address" = cd."ip"
WHERE
    rs."mobile_id_unique_identifier"."value" IS NOT NULL
    AND rs."ip_address" IS NOT NULL
    AND rs."event_timestamp" >= CURRENT_TIMESTAMP - INTERVAL '45' DAY
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY rs."iso_3166_1_country", cd."ip"
    ORDER BY APPROX_COUNT_DISTINCT(rs."event_timestamp") OVER (
        PARTITION BY rs."iso_3166_1_country", cd."ip",
                     rs."mobile_id_unique_identifier"."value",
                     rs."mobile_id_unique_identifier"."type"
    ) DESC
) <= 3
```

**How the nested window functions work:**

1. The **inner window** (`APPROX_COUNT_DISTINCT ... OVER (PARTITION BY country, ip, maid, maid_type)`) counts distinct observation timestamps for each IP-MAID pair. MAIDs seen more often on an IP address get a higher count.
2. The **outer window** (`ROW_NUMBER ... OVER (PARTITION BY country, ip ORDER BY ... DESC)`) ranks MAIDs within each country + IP group by that observation count, highest first.
3. `QUALIFY ... <= 3` keeps only the top 3 most-frequently-observed MAIDs per IP per country.

This approach prioritizes devices that consistently appear on an IP address, filtering out transient connections.

<Tip>
  Adjust the `<= 3` threshold based on your use case. For residential IPs, 1-3 MAIDs is typical. For commercial or CGNAT IPs shared across many users, consider filtering these out entirely or increasing the threshold.
</Tip>

***

## Step 5: Add country filtering and enrichment stats

Extend the query with geographic filtering and a `maid_count` column that shows the total unique MAIDs found per IP before ranking. This helps you assess match quality:

```sql theme={null}
SELECT
    cd."ip",
    rs."mobile_id_unique_identifier"."value" AS maid,
    rs."mobile_id_unique_identifier"."type" AS maid_type,
    rs."iso_3166_1_country" AS country_code,
    APPROX_COUNT_DISTINCT(rs."mobile_id_unique_identifier"."value") OVER (
        PARTITION BY cd."ip"
    ) AS maid_count
FROM narrative.rosetta_stone rs
INNER JOIN company_data."your_ip_dataset" cd
    ON rs."ip_address" = cd."ip"
WHERE
    rs."mobile_id_unique_identifier"."value" IS NOT NULL
    AND rs."ip_address" IS NOT NULL
    AND rs."event_timestamp" >= CURRENT_TIMESTAMP - INTERVAL '45' DAY
    AND rs."iso_3166_1_country" IN ('US', 'CA', 'GB', 'AU')
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY rs."iso_3166_1_country", cd."ip"
    ORDER BY APPROX_COUNT_DISTINCT(rs."event_timestamp") OVER (
        PARTITION BY rs."iso_3166_1_country", cd."ip",
                     rs."mobile_id_unique_identifier"."value",
                     rs."mobile_id_unique_identifier"."type"
    ) DESC
) <= 3
```

The `maid_count` column reveals IP quality:

* **Low count (1-5):** Likely residential IPs with a small number of devices—high-confidence matches
* **High count (50+):** Likely commercial, VPN, or CGNAT IPs—consider excluding these from activation

### MAID type breakdown variation

To see the split between IDFA and GAID per IP, replace the `maid_count` window with a type-specific count:

```sql theme={null}
SELECT
    cd."ip",
    rs."mobile_id_unique_identifier"."value" AS maid,
    rs."mobile_id_unique_identifier"."type" AS maid_type,
    rs."iso_3166_1_country" AS country_code,
    APPROX_COUNT_DISTINCT(
        CASE WHEN rs."mobile_id_unique_identifier"."type" = 'idfa' THEN rs."mobile_id_unique_identifier"."value" END
    ) OVER (PARTITION BY cd."ip") AS idfa_count,
    APPROX_COUNT_DISTINCT(
        CASE WHEN rs."mobile_id_unique_identifier"."type" = 'gaid' THEN rs."mobile_id_unique_identifier"."value" END
    ) OVER (PARTITION BY cd."ip") AS gaid_count
FROM narrative.rosetta_stone rs
INNER JOIN company_data."your_ip_dataset" cd
    ON rs."ip_address" = cd."ip"
WHERE
    rs."mobile_id_unique_identifier"."value" IS NOT NULL
    AND rs."ip_address" IS NOT NULL
    AND rs."event_timestamp" >= CURRENT_TIMESTAMP - INTERVAL '45' DAY
    AND rs."iso_3166_1_country" IN ('US', 'CA', 'GB', 'AU')
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY rs."iso_3166_1_country", cd."ip"
    ORDER BY APPROX_COUNT_DISTINCT(rs."event_timestamp") OVER (
        PARTITION BY rs."iso_3166_1_country", cd."ip",
                     rs."mobile_id_unique_identifier"."value",
                     rs."mobile_id_unique_identifier"."type"
    ) DESC
) <= 3
```

***

## Step 6: Automate with a materialized view

Wrap the final query in a materialized view that refreshes automatically:

```sql theme={null}
CREATE MATERIALIZED VIEW "ip_to_maid_enriched"
REFRESH_SCHEDULE = '@weekly'
DISPLAY_NAME = 'IP to MAID Enrichment'
DESCRIPTION = 'IP addresses enriched with top mobile ad IDs ranked by observation frequency'
TAGS = ('enrichment', 'ip', 'maid', 'cross-device')
WRITE_MODE = 'overwrite'
AS
SELECT
    cd."ip",
    rs."mobile_id_unique_identifier"."value" AS maid,
    rs."mobile_id_unique_identifier"."type" AS maid_type,
    rs."iso_3166_1_country" AS country_code,
    APPROX_COUNT_DISTINCT(rs."mobile_id_unique_identifier"."value") OVER (
        PARTITION BY cd."ip"
    ) AS maid_count
FROM narrative.rosetta_stone rs
INNER JOIN company_data."your_ip_dataset" cd
    ON rs."ip_address" = cd."ip"
WHERE
    rs."mobile_id_unique_identifier"."value" IS NOT NULL
    AND rs."ip_address" IS NOT NULL
    AND rs."event_timestamp" >= CURRENT_TIMESTAMP - INTERVAL '45' DAY
    AND rs."iso_3166_1_country" IN ('US', 'CA', 'GB', 'AU')
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY rs."iso_3166_1_country", cd."ip"
    ORDER BY APPROX_COUNT_DISTINCT(rs."event_timestamp") OVER (
        PARTITION BY rs."iso_3166_1_country", cd."ip",
                     rs."mobile_id_unique_identifier"."value",
                     rs."mobile_id_unique_identifier"."type"
    ) DESC
) <= 3
```

<Note>
  Adjust the `REFRESH_SCHEDULE` based on how frequently your IP dataset changes. `@weekly` works well for server logs that update regularly. Use `@monthly` if your IP list is relatively static.
</Note>

***

## Summary

You have built an IP-to-MAID enrichment pipeline with these components:

| Query / View              | Purpose                                                                |
| ------------------------- | ---------------------------------------------------------------------- |
| Data availability preview | Assess MAID volume and type coverage before enrichment                 |
| IP-to-MAID join           | Core matching of IPs to mobile ad IDs via Rosetta Stone                |
| Frequency-ranked results  | Top N MAIDs per IP based on observation count                          |
| `ip_to_maid_enriched`     | Automated materialized view with country filtering and quality metrics |

### Use cases

* **Cross-device targeting:** Reach users on mobile who were previously only identifiable by IP
* **Mobile campaign activation:** Build MAID-based audiences from web traffic data for DSP activation
* **Device graph enrichment:** Augment your identity graph with IP-to-MAID linkages
* **Measurement and attribution:** Connect web sessions (IP) to mobile app activity (MAID)

### Key advantages

<CardGroup cols={2}>
  <Card title="Simplified sourcing" icon="wand-magic-sparkles">
    Rosetta Stone queries all available data providers automatically—no need to identify and UNION across individual datasets
  </Card>

  <Card title="Frequency-based ranking" icon="arrow-down-1-9">
    Nested window functions surface the most reliable IP-MAID associations, filtering out transient connections
  </Card>

  <Card title="Automated refresh" icon="arrows-rotate">
    Weekly materialized views keep your MAID mappings current as new observations arrive
  </Card>

  <Card title="Quality signals" icon="chart-simple">
    The `maid_count` column helps identify commercial or shared IPs that may produce low-quality matches
  </Card>
</CardGroup>

### Important considerations

<Warning>
  **MAID availability:** iOS App Tracking Transparency and Android privacy changes have reduced MAID availability. GAID volume is significantly higher than IDFA. Consider supplementing with hashed email or other identifier types for broader iOS coverage. See [Mobile Ad IDs](/concepts/identifiers/mobile-ad-ids) for details.
</Warning>

<Note>
  **IP addresses as personal data:** Under GDPR, IP addresses are considered personal data. Ensure your enrichment workflow complies with applicable privacy regulations, particularly when processing IPs from EU users. See [GDPR compliance](/concepts/compliance/gdpr) and [CCPA compliance](/concepts/compliance/ccpa) for details.
</Note>

***

## Related content

<CardGroup cols={2}>
  <Card title="Mobile Ad IDs" icon="mobile" href="/concepts/identifiers/mobile-ad-ids">
    Understanding IDFA, GAID, and privacy considerations
  </Card>

  <Card title="IP Addresses" icon="globe" href="/concepts/data-formats/ip-addresses">
    IP address types, limitations, and best practices
  </Card>

  <Card title="Joining Datasets" icon="link" href="/guides/nql/joining-datasets">
    Learn JOIN patterns for enrichment queries
  </Card>

  <Card title="Performance Patterns" icon="gauge-high" href="/cookbooks/nql/performance-patterns">
    Window functions, QUALIFY, and other advanced NQL patterns
  </Card>
</CardGroup>