Retail Competitive Intelligence

A national retailer is expanding to the West Coast and wants to understand their potential customers before opening new stores. Through Narrative, they can identify shoppers at competitor locations, segment them by loyalty, and enrich their profiles with purchase behavior—all without having any first-party data in those markets.

What you will build

This cookbook walks through creating a competitive intelligence pipeline that:

Identifies mobile device IDs (MAIDs) observed at competitor store locations in California, Nevada, and Oregon
Segments visitors into loyalty tiers based on visit frequency (loyal, casual, one-time)
Enriches loyal competitor shoppers with CPG purchase history (groceries, personal care, household goods)

The result is targetable audience segments for conquest marketing campaigns.

Prerequisites

Before starting, ensure you have:

Access to location data containing geo_coordinates and unique_identifier (MAID) attributes
Access to purchase transaction data with category information
Familiarity with NQL syntax
Understanding of materialized views

Step 1: Define competitor store locations

First, create a reference dataset of competitor store locations. This dataset contains the latitude/longitude coordinates for each store, which you will use as geofence centers.

CREATE MATERIALIZED VIEW "competitor_store_locations"
REFRESH_SCHEDULE = '@monthly'
DISPLAY_NAME = 'Competitor Store Locations - West Coast'
DESCRIPTION = 'Geofenced competitor retail locations in CA, NV, OR'
TAGS = ('competitive_intelligence', 'location', 'west_coast')
WRITE_MODE = 'overwrite'
AS
SELECT
    store_id,
    brand_name,
    latitude,
    longitude,
    region AS state
FROM company_data.store_directory
WHERE
    brand_name IN ('CompetitorA', 'CompetitorB', 'CompetitorC')
    AND region IN ('CA', 'NV', 'OR')

Replace company_data.store_directory with your actual store location dataset. You can also upload a CSV of competitor locations to create this reference table.

Step 2: Identify competitor store visitors

Query the Rosetta Stone table to find mobile advertising IDs (IDFA/GAID) observed within proximity of competitor store coordinates over the past 90 days.

CREATE MATERIALIZED VIEW "competitor_store_visitors"
REFRESH_SCHEDULE = '@weekly'
DISPLAY_NAME = 'Competitor Store Visitors - Raw'
DESCRIPTION = 'MAIDs observed at competitor retail locations in past 90 days'
TAGS = ('competitive_intelligence', 'location', 'audience')
WRITE_MODE = 'overwrite'
PARTITIONED_BY event_date DAY
AS
SELECT
    rs."unique_identifier"."value" AS maid,
    rs."unique_identifier"."type" AS maid_type,
    stores.store_id,
    stores.brand_name,
    stores.state,
    CAST(rs."event_timestamp" AS TIMESTAMP) AS visit_timestamp,
    DATE(rs."event_timestamp") AS event_date
FROM narrative.rosetta_stone rs
JOIN company_data."competitor_store_locations" stores
    ON ABS(rs."geo_coordinates"."latitude" - stores.latitude) < 0.002
    AND ABS(rs."geo_coordinates"."longitude" - stores.longitude) < 0.002
WHERE
    rs."event_timestamp" > CURRENT_TIMESTAMP - INTERVAL '90' DAY
    AND rs."unique_identifier"."type" IN ('idfa', 'gaid')
    AND rs."unique_identifier"."value" IS NOT NULL
    AND rs."geo_coordinates"."latitude" IS NOT NULL
    AND rs."region" IN ('CA', 'NV', 'OR')

Key patterns used:

Pattern	Purpose
`unique_identifier.value`	Extracts the MAID value from the identifier object
`unique_identifier.type IN ('idfa', 'gaid')`	Filters to mobile advertising IDs only
`ABS(latitude - stores.latitude) < 0.002`	Proximity match (~150-200 meters geofence)
`INTERVAL '90' DAY`	Rolling 90-day lookback window
`PARTITIONED_BY event_date DAY`	Optimizes queries filtering by date

The proximity threshold of 0.002 degrees equals approximately 150-200 meters depending on latitude. Adjust this value based on store size and urban density. Larger stores or rural areas may warrant a larger radius.

Step 3: Segment by visit frequency

Aggregate the visitor data to calculate how many times each MAID visited competitor stores, then segment into loyalty tiers.

CREATE MATERIALIZED VIEW "competitor_visitors_segmented"
REFRESH_SCHEDULE = '@weekly'
DISPLAY_NAME = 'Competitor Visitors - Loyalty Segments'
DESCRIPTION = 'MAIDs segmented by visit frequency: loyal (4+), casual (2-3), one-time (1)'
TAGS = ('competitive_intelligence', 'audience', 'segmentation')
WRITE_MODE = 'overwrite'
AS
SELECT
    maid,
    maid_type,
    COUNT(DISTINCT visit_timestamp) AS total_visits,
    COUNT(DISTINCT store_id) AS unique_stores_visited,
    COUNT(DISTINCT brand_name) AS unique_brands_visited,
    MIN(visit_timestamp) AS first_visit,
    MAX(visit_timestamp) AS last_visit,
    CASE
        WHEN COUNT(DISTINCT visit_timestamp) >= 4 THEN 'loyal'
        WHEN COUNT(DISTINCT visit_timestamp) BETWEEN 2 AND 3 THEN 'casual'
        ELSE 'one_time'
    END AS loyalty_segment
FROM company_data."competitor_store_visitors"
GROUP BY maid, maid_type

To create separate audiences for activation, you can also build segment-specific views:

Loyal shoppers (4+ visits)

CREATE MATERIALIZED VIEW "loyal_competitor_shoppers"
REFRESH_SCHEDULE = '@weekly'
DISPLAY_NAME = 'Loyal Competitor Shoppers'
DESCRIPTION = 'MAIDs with 4+ visits to competitor stores'
TAGS = ('competitive_intelligence', 'audience', 'loyal')
WRITE_MODE = 'overwrite'
AS
SELECT
    maid,
    maid_type,
    COUNT(DISTINCT visit_timestamp) AS total_visits,
    MAX(visit_timestamp) AS last_visit
FROM company_data."competitor_store_visitors"
GROUP BY maid, maid_type
HAVING COUNT(DISTINCT visit_timestamp) >= 4

Non-loyal shoppers (1-2 visits)

CREATE MATERIALIZED VIEW "nonloyal_competitor_shoppers"
REFRESH_SCHEDULE = '@weekly'
DISPLAY_NAME = 'Non-Loyal Competitor Shoppers'
DESCRIPTION = 'MAIDs with 1-2 visits to competitor stores'
TAGS = ('competitive_intelligence', 'audience', 'nonloyal')
WRITE_MODE = 'overwrite'
AS
SELECT
    maid,
    maid_type,
    COUNT(DISTINCT visit_timestamp) AS total_visits,
    MAX(visit_timestamp) AS last_visit
FROM company_data."competitor_store_visitors"
GROUP BY maid, maid_type
HAVING COUNT(DISTINCT visit_timestamp) <= 2

Step 4: Enrich with purchase history

Join the loyal segment with purchase transaction data to understand CPG category affinity. This creates richer profiles for targeting.

CREATE MATERIALIZED VIEW "loyal_shoppers_enriched"
REFRESH_SCHEDULE = '@weekly'
DISPLAY_NAME = 'Loyal Competitor Shoppers - Purchase Enriched'
DESCRIPTION = 'Loyal competitor shoppers with CPG purchase category flags'
TAGS = ('competitive_intelligence', 'audience', 'enriched')
WRITE_MODE = 'overwrite'
AS
SELECT
    loyal.maid,
    loyal.maid_type,
    loyal.total_visits,
    loyal.last_visit AS last_store_visit,
    MAX(CASE WHEN purchases.purchase_category = 'groceries' THEN 1 ELSE 0 END) AS has_groceries,
    MAX(CASE WHEN purchases.purchase_category = 'personal_care' THEN 1 ELSE 0 END) AS has_personal_care,
    MAX(CASE WHEN purchases.purchase_category = 'household_goods' THEN 1 ELSE 0 END) AS has_household_goods,
    COUNT(DISTINCT purchases.transaction_id) AS total_purchases,
    SUM(purchases.transaction_amount) AS total_spend
FROM company_data."loyal_competitor_shoppers" loyal
LEFT JOIN company_data."purchase_transactions" purchases
    ON loyal.maid = purchases."unique_identifier"."value"
    AND purchases."event_timestamp" > CURRENT_TIMESTAMP - INTERVAL '180' DAY
GROUP BY
    loyal.maid,
    loyal.maid_type,
    loyal.total_visits,
    loyal.last_visit

This query:

Joins loyal shoppers with purchase data over a 180-day window
Pivots purchase categories into boolean flags for easy filtering
Calculates aggregate purchase metrics (count and total spend)
Uses LEFT JOIN to retain all loyal shoppers even if no purchase data exists

Summary

You have built a competitive intelligence pipeline with four materialized views:

View	Purpose
`competitor_store_locations`	Reference data for competitor store geofences
`competitor_store_visitors`	Raw visitor log with MAIDs and visit timestamps
`competitor_visitors_segmented`	Visitors segmented by loyalty tier
`loyal_shoppers_enriched`	Loyal shoppers enriched with purchase behavior

Use cases

Conquest campaigns: Target loyal competitor shoppers with promotional offers to drive store switching
Lookalike modeling: Use enriched profiles as seed audiences for prospecting
Store placement analysis: Understand which competitor locations draw specific shopper profiles
Assortment planning: Align product mix with purchase category preferences of local shoppers

Important considerations

MAID availability: iOS App Tracking Transparency and Android privacy changes have reduced MAID availability. Expect smaller audience sizes compared to historical baselines.

Privacy compliance: Location-based audiences may be subject to CCPA (California) and other privacy regulations. Ensure your use case complies with applicable data protection requirements. See CCPA compliance for details.

Creating Materialized Views

Step-by-step guide to creating and managing materialized views

Materialized View Syntax

Complete reference for REFRESH_SCHEDULE, PARTITIONED_BY, and other options

JOIN Performance

Optimize spatial and identity joins for large datasets

Overview

NQL Patterns

Use Cases

SDK Recipes

What you will build

Prerequisites

Step 1: Define competitor store locations

Step 2: Identify competitor store visitors

Step 3: Segment by visit frequency

Loyal shoppers (4+ visits)

Non-loyal shoppers (1-2 visits)

Step 4: Enrich with purchase history

Summary

Use cases

Important considerations

Creating Materialized Views

Materialized View Syntax

JOIN Performance

​What you will build

​Prerequisites

​Step 1: Define competitor store locations

​Step 2: Identify competitor store visitors

​Step 3: Segment by visit frequency

​Loyal shoppers (4+ visits)

​Non-loyal shoppers (1-2 visits)

​Step 4: Enrich with purchase history

​Summary

​Use cases

​Important considerations

​Related content

Creating Materialized Views

Materialized View Syntax

JOIN Performance

What you will build

Prerequisites

Step 1: Define competitor store locations

Step 2: Identify competitor store visitors

Step 3: Segment by visit frequency

Loyal shoppers (4+ visits)

Non-loyal shoppers (1-2 visits)

Step 4: Enrich with purchase history

Summary

Use cases

Important considerations

Related content