Skip to main content
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:

​
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:
ColumnTypeDescription
ipstringIPv4 or IPv6 address
observed_attimestampWhen 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.
Upload your dataset through the Narrative UI or SDK. The ip column does not need a Rosetta Stone mapping—you will join it directly against the "ip_address" attribute in Rosetta Stone.

​
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: 
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.
GAID (Android) typically has higher volume than IDFA (iOS) due to Apple’s App Tracking Transparency framework. See Mobile Ad IDs for details on availability trends.

​
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: 
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:
PatternPurpose
INNER JOIN company_dataMatches only IPs present in your seed dataset
mobile_id_unique_identifier.value / .typeExtracts MAID value and type (IDFA or GAID)
ip_addressRosetta Stone’s normalized IP address attribute
INTERVAL '45' DAYLimits to recent observations for freshness
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.

​
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: 
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.
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.

​
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: 
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: 
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: 
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
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.

​
Summary

You have built an IP-to-MAID enrichment pipeline with these components:
Query / ViewPurpose
Data availability previewAssess MAID volume and type coverage before enrichment
IP-to-MAID joinCore matching of IPs to mobile ad IDs via Rosetta Stone
Frequency-ranked resultsTop N MAIDs per IP based on observation count
ip_to_maid_enrichedAutomated 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

Simplified sourcing

Rosetta Stone queries all available data providers automatically—no need to identify and UNION across individual datasets

Frequency-based ranking

Nested window functions surface the most reliable IP-MAID associations, filtering out transient connections

Automated refresh

Weekly materialized views keep your MAID mappings current as new observations arrive

Quality signals

The maid_count column helps identify commercial or shared IPs that may produce low-quality matches

​
Important considerations

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 for details.
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 and CCPA compliance for details.

​
Related content

Mobile Ad IDs

Understanding IDFA, GAID, and privacy considerations

IP Addresses

IP address types, limitations, and best practices

Joining Datasets

Learn JOIN patterns for enrichment queries

Performance Patterns

Window functions, QUALIFY, and other advanced NQL patterns