Sample Queries

Industry Level Insights:

Track consumer spend by industry or sector

Measure the overall consumer spend for all of the Retail Sales segment.

SELECT 
    aggregation_name AS series_name,
    date,
    value,
    measure,
    frequency
FROM 
    cybersyn.consumer_spending_industry_timeseries AS ts
WHERE 
    aggregation_type = 'MARTS Segment'
    AND aggregation_name = 'Retail Sales: Retail Trade and Food Services'
    AND measure = 'Sales_YoY'
    AND frequency = 'Month'
    AND member_income = 'All Income Brackets'
    AND member_age = 'All Age Brackets'
    AND consumer_geo_id = 'country/USA'
    AND merchant_geo_id = 'country/USA'
ORDER BY 
    ts.date;

Track consumer spend by industry or sector and channel across CBSAs in a given state

Measure the monthly in-store consumer spend for all of the Retail Sales segment by purchases in different CBSAs in California.

SELECT 
    ts.aggregation_name AS series_name,
    states.related_geo_name AS state,
    ts.merchant_geo_name AS cbsa,
    ts.channel,
    ts.measure,
    ts.unit,
    ts.frequency,
    ts.date,
    ts.value
FROM 
    cybersyn.consumer_spending_industry_timeseries AS ts
JOIN 
    cybersyn.geography_relationships AS states
    ON ts.merchant_geo_id = states.geo_id
WHERE 
    ts.aggregation_type = 'MARTS Segment'
    AND ts.aggregation_name = 'Retail Sales: Retail Trade and Food Services'
    AND ts.measure = 'Sales_YoY'
    AND ts.frequency = 'Month'
    AND ts.channel = 'Offline'
    AND ts.member_income = 'All Income Brackets'
    AND ts.member_age = 'All Age Brackets'
    AND ts.consumer_geo_name = 'United States'
    AND ts.merchant_geo_level = 'CensusCoreBasedStatisticalArea'
    AND states.related_level = 'State'
    AND states.related_geo_name = 'California'
ORDER BY 
    ts.merchant_geo_name, 
    ts.date

Study demographic factors impacting consumer activity

Determine how age impacted dining spend for different consumer groups.

SELECT 
    member_age,
    aggregation_name,
    measure,
    frequency,
    date,
    value
FROM 
    cybersyn.consumer_spending_industry_timeseries
WHERE 
    aggregation_type = 'MARTS Segment'
    AND aggregation_name = 'Retail Sales: Food Services and Drinking Places'
    AND measure = 'Sales_YoY'
    AND frequency = 'Month'
    AND member_income = 'All Income Brackets'
    AND member_age IN (
        '18-24',
        '25-34',
        '35-44',
        '45-54',
        '55-64',
        '65+'
    )
    AND date >= '2020-01-01'
    AND consumer_geo_id = 'country/USA'
    AND merchant_geo_id = 'country/USA'

Study demographic factors impacting consumer activity in different geographies

Determine how age impacted dining spend for different consumer groups from the states of New York and Florida.

SELECT 
    ts.consumer_geo_name,
    ts.member_age,
    ts.aggregation_name,
    ts.measure,
    ts.frequency,
    ts.date,
    ts.value
FROM 
    cybersyn.consumer_spending_industry_timeseries AS ts
JOIN 
    cybersyn.geography_relationships AS states
    ON ts.consumer_geo_id = states.geo_id
WHERE 
    ts.aggregation_type = 'MARTS Segment'
    AND ts.aggregation_name = 'Retail Sales: Food Services and Drinking Places'
    AND ts.measure = 'Sales_YoY'
    AND ts.frequency = 'Month'
    AND ts.member_income = 'All Income Brackets'
    AND ts.member_age IN (
        '18-24',
        '25-34',
        '35-44',
        '45-54',
        '55-64',
        '65+'
    )
    AND ts.date >= '2020-01-01'
    AND ts.merchant_geo_id = 'country/USA'
    AND ts.consumer_geo_level = 'State'
    AND ts.consumer_geo_name IN ('New York', 'Florida')
    AND ts.channel = 'All Channels'
    AND states.related_level = 'Country'
    AND states.related_geo_name = 'United States'
ORDER BY 
    ts.consumer_geo_name, 
    ts.date

Identify pricing trends across industries

Determine which types of merchants are seeing the largest year-over-year increases in average order value (AOV) this quarter.

SELECT 
    aggregation_name,
    date,
    value,
    measure,
    frequency
FROM 
    cybersyn.consumer_spending_industry_timeseries
WHERE 
    aggregation_type = 'MCC'
    AND aggregation_name IN (
        'Fast Food Restaurants',
        'Eating Places And Restaurants',
        'Men’s And Women’s Clothing Stores',
        'Grocery Stores, Supermarkets',
        'Drug Stores And Pharmacies',
        'Sporting Goods Stores',
        'Department Stores',
        'Hardware Stores',
        'Furniture, Home Furnishings, And Equipment Stores, Except appliances'
    )
    AND measure = 'AOV_YoY'
    AND frequency = 'Quarter'
    AND member_income = 'All Income Brackets'
    AND member_age = 'All Age Brackets'
    AND consumer_geo_id = 'country/USA'
    AND merchant_geo_id = 'country/USA'
QUALIFY 
    ROW_NUMBER() OVER (PARTITION BY aggregation_name ORDER BY date DESC) = 1
ORDER BY 
    value DESC

Merchant Level Insights

Overall Merchant Benchmarking

Understand monthly sales for a specific set of merchants

Analyze sales at West Elm, Ikea, and Crate & Barrel across the total population in the United States.

SELECT 
    merchant_name,
    date,
    measure,
    value,
    unit
FROM 
    cybersyn.consumer_spending_merchant_timeseries
WHERE 
    merchant_name IN ('WEST ELM', 'IKEA', 'CRATE & BARREL')
    AND frequency = 'Month'
    AND channel = 'All Channels'
    AND member_age = 'All Age Brackets'
    AND member_income = 'All Income Brackets'
    AND consumer_geo_name = 'United States'
    AND merchant_geo_name = 'United States'
ORDER BY 
    date DESC, 
    merchant_name

Benchmark company performance against industries and competitors

Compare Chipotle’s sales performance to that of McDonald’s and of the broader fast food category

SELECT 
    merchant_name AS series_name,
    measure,
    frequency,
    member_age,
    member_income,
    channel,
    consumer_geo_level,
    consumer_geo_name,
    merchant_geo_level,
    merchant_geo_name,
    date,
    value,
    unit
FROM 
    cybersyn.consumer_spending_merchant_timeseries
WHERE 
    merchant_name IN ('CHIPOTLE MEXICAN GRILL', 'MCDONALD''S')

UNION

SELECT 
    aggregation_name AS series_name,
    measure,
    frequency,
    member_age,
    member_income,
    channel,
    consumer_geo_level,
    consumer_geo_name,
    merchant_geo_level,
    merchant_geo_name,
    date,
    value,
    unit
FROM 
    cybersyn.consumer_spending_industry_timeseries
WHERE 
    aggregation_type = 'MCC'
    AND aggregation_name = 'Fast Food Restaurants'

Benchmark online only performance against competitors

Compare Chipotle’s and McDonald’s transaction performance online across the United States

SELECT 
    merchant_name, 
    measure, 
    frequency, 
    channel, 
    date, 
    value, 
    unit
FROM 
    cybersyn.consumer_spending_merchant_timeseries
WHERE 
    merchant_name IN ('CHIPOTLE MEXICAN GRILL', 'MCDONALDS')
    AND measure = 'Transactions'
    AND frequency = 'Week - Monday Start'
    AND member_income = 'All Income Brackets'
    AND member_age = 'All Age Brackets'
    AND consumer_geo_id = 'country/USA'
    AND merchant_geo_id = 'country/USA'
    AND date > '2022-12-31'
    AND channel = 'Online'
ORDER BY 
    date, 
    merchant_name

Compare average transaction sizes across companies

Compare and contrast how Kroger and Aldi’s average order values (AOV) evolved before and after the height of the Covid-19 pandemic.

WITH spend_data AS (
    SELECT 
        merchant_name, 
        date, 
        value
    FROM 
        marketplace_cybersyn.cybersyn.consumer_spending_merchant_timeseries
    WHERE 
        member_age = 'All Age Brackets'
        AND member_income = 'All Income Brackets'
        AND channel = 'All Channels'
        AND consumer_geo_level = 'Country'
        AND consumer_geo_name = 'United States'
        AND merchant_geo_level = 'Country'
        AND merchant_geo_name = 'United States'
        AND frequency = 'Month'
        AND measure = 'AOV'
), 
aldi AS (
    SELECT 
        date, 
        value AS aldi_aov
    FROM 
        spend_data
    WHERE 
        merchant_name = 'ALDI / HOFER'
), 
kroger AS (
    SELECT 
        date, 
        value AS kroger_aov
    FROM 
        spend_data
    WHERE 
        merchant_name = 'KROGER'
)
SELECT 
    aldi.date,
    aldi_aov,
    kroger_aov
FROM 
    aldi
JOIN 
    kroger ON aldi.date = kroger.date
WHERE 
    aldi.date >= '2019-01-01'
ORDER BY 
    date

Hyperlocal Insights

Find the top performing zip codes in a city for a particular merchant

Determine which zip codes in Manhattan had the highest amount of consumer spending at Taco Bell in 2023

SELECT 
    consumer_geo_name AS zip_code, 
    SUM(value) AS sales
FROM 
    cybersyn.consumer_spending_merchant_timeseries AS ts
JOIN 
    cybersyn.geography_relationships AS geo
    ON ts.consumer_geo_id = geo.geo_id
    AND geo.related_level = 'County'
    AND geo.related_geo_name = 'Manhattan'
WHERE 
    ts.member_age = 'All Age Brackets'
    AND ts.member_income = 'All Income Brackets'
    AND ts.channel = 'All Channels'
    AND ts.consumer_geo_level = 'CensusZipCodeTabulationArea'
    AND ts.merchant_geo_level = 'Country'
    AND ts.merchant_geo_name = 'United States'
    AND ts.measure = 'Sales'
    AND ts.frequency = 'Month'
    AND ts.merchant_name = 'TACO BELL'
    AND ts.date >= '2023-01-01'
    AND ts.date <= '2023-12-31'
GROUP BY 
    zip_code
ORDER BY 
    sales DESC
LIMIT 20

Find the top zip codes in a city where consumers spend the most at a given merchant

Determine which zip codes in the Denver area had the highest amount of consumer spending at DoorDash in 2023

SELECT 
    ts.consumer_geo_name AS zip_code, 
    SUM(ts.value) AS sales
FROM 
    cybersyn.consumer_spending_merchant_timeseries AS ts
JOIN 
    cybersyn.geography_relationships AS geo
    ON ts.consumer_geo_id = geo.geo_id
    AND geo.related_level = 'City'
    AND geo.related_geo_name = 'Denver'
WHERE 
    ts.member_age = 'All Age Brackets'
    AND ts.member_income = 'All Income Brackets'
    AND ts.channel = 'All Channels'
    AND ts.consumer_geo_level = 'CensusZipCodeTabulationArea'
    AND ts.merchant_geo_level = 'Country'
    AND ts.merchant_geo_name = 'United States'
    AND ts.measure = 'Sales'
    AND ts.frequency = 'Month'
    AND ts.merchant_name = 'DOORDASH'
    AND ts.date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    ts.consumer_geo_name
ORDER BY 
    sales DESC
LIMIT 20

Analyze the growth of all restaurants in a particular zip

Determine how fast food restaurants have grown in terms of number of transactions for a specific zip code in Los Angeles.

SELECT
    poi.poi_id,
    poi.poi_street_address,
    poi.poi_city_name,
    poi.poi_state_name,
    poi.poi_zip,
    poi.merchant_name,
    poi.measure,
    poi.frequency,
    poi.date,
    poi.value,
    poi.unit,
    idx.latitude,
    idx.longitude,
    idx.estimated_open_month,
    idx.estimated_close_month
FROM 
    cybersyn.consumer_spending_poi_timeseries AS poi
JOIN 
    cybersyn.poi_index AS idx
    ON poi.poi_id = idx.poi_id
WHERE 
    poi.merchant_category_code_description = 'FAST FOOD RESTAURANTS'
    AND poi.poi_zip = 91604
    AND poi.measure = 'Transactions_YoY'
    AND poi.frequency = 'Month'
    AND poi.date = '2023-06-30'

Understand sales for a specific store location

Analyze sales of a Nike store in Fresno, California

SELECT
    poi.poi_id,
    poi.poi_street_address,
    poi.poi_city_name,
    poi.poi_state_name,
    poi.poi_zip,
    poi.merchant_name,
    poi.merchant_category_code_description,
    poi.measure,
    poi.frequency,
    poi.date,
    poi.value,
    poi.unit,
    idx.latitude,
    idx.longitude,
    idx.estimated_open_month,
    idx.estimated_close_month
FROM 
    cybersyn.consumer_spending_poi_timeseries AS poi
JOIN 
    cybersyn.poi_index AS idx
    ON poi.poi_id = idx.poi_id
WHERE 
    poi.merchant_name = 'NIKE'
    AND poi.poi_state_name = 'CA'
    AND poi.poi_city_name = 'FRESNO'
    AND poi.poi_street_address = '7650 N BLACKSTONE AVE'
    AND poi.measure IN ('Sales', 'AOV')
    AND poi.frequency = 'Week - Monday Start'

Demographic Insights

Analyze the demographic profile for a particular merchant

Look at the proportion of sales at Nike in June 2023 broken down by income segments

SELECT 
    member_income,
    SUM(value) AS income_segment_sales,  
    SUM(value) * 1.0 / SUM(SUM(value)) OVER () AS proportion_of_total
FROM 
    cybersyn.consumer_spending_merchant_timeseries
WHERE 
    merchant_name = 'NIKE'
    AND measure = 'Sales'
    AND frequency = 'Month'
    AND channel = 'All Channels'
    AND consumer_geo_name = 'United States'
    AND merchant_geo_name = 'United States'
    AND member_age = 'All Age Brackets'
    AND member_income != 'All Income Brackets'
    AND date = '2023-06-30'
GROUP BY 
    member_income

Analyze market share changes between two companies for a specific customer demographic

Compare market share of Chipotle vs. McDonald’s for the 18-24 age demographic.

WITH spend_data AS (
    SELECT 
        merchant_name, 
        date, 
        value
    FROM 
        cybersyn.consumer_spending_merchant_timeseries
    WHERE 
        member_age = '18-24'
        AND member_income = 'All Income Brackets'
        AND frequency = 'Month'
        AND measure = 'Sales'
        AND channel = 'All Channels'
        AND consumer_geo_level = 'Country'
        AND consumer_geo_name = 'United States'
        AND merchant_geo_level = 'Country'
        AND merchant_geo_name = 'United States'
), 
mcd AS (
    SELECT 
        date, 
        value AS mcdonalds_sales
    FROM 
        spend_data
    WHERE 
        merchant_name = 'MCDONALDS'
), 
cmg AS (
    SELECT 
        date, 
        value AS chipotle_sales
    FROM 
        spend_data
    WHERE 
        merchant_name = 'CHIPOTLE MEXICAN GRILL'
)
SELECT 
    mcd.date,
    mcdonalds_sales,
    chipotle_sales,
    mcdonalds_sales / (mcdonalds_sales + chipotle_sales) AS mcdonalds_market_share,
    chipotle_sales / (mcdonalds_sales + mcdonalds_sales) AS chipotle_market_share
FROM 
    mcd
JOIN 
    cmg ON mcd.date = cmg.date
ORDER BY 
    mcd.date

Analyze a specific demographic segment within a specific geography

What was the monthly sales trend in 2023 amongst older consumers in the Dallas metro area at Walmart and Target

SELECT 
    merchant_name, 
    SUM(value) AS sales, 
    date
FROM 
    cybersyn.consumer_spending_merchant_timeseries
WHERE 
    consumer_geo_name = 'Dallas-Fort Worth-Arlington, TX Metro Area'
    AND merchant_name IN ('WALMART', 'TARGET')
    AND measure = 'Sales'
    AND frequency = 'Month'
    AND channel = 'All Channels'
    AND merchant_geo_name = 'United States'
    AND member_income = 'All Income Brackets'
    AND member_age IN ('55-64', '65+')
    AND date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    merchant_name, 
    date
ORDER BY 
    date, 
    merchant_name

Retention Analysis

Understand retention for a specific merchant

Understand Wayfair’s retention across various metrics like sales, transactions, and customers

SELECT 
    merchant_name, 
    measure, 
    periods_since_first_purchase, 
    value
FROM 
    cybersyn.consumer_spending_merchant_retention_timeseries
WHERE 
    merchant_name = 'WAYFAIR'
    AND consumer_geo_name = 'United States'
    AND merchant_geo_name = 'United States'
    AND member_age = 'All Age Brackets'
    AND member_income = 'All Income Brackets'
ORDER BY 
    measure, 
    periods_since_first_purchase

Analyze customer retention in a given demographic

Find the 2 year spend retention for Uber Eats and DoorDash customers between 25-34.

SELECT 
    merchant_name, 
    member_age, 
    member_income, 
    consumer_geo_name,
    periods_since_first_purchase, 
    value
FROM 
    cybersyn.consumer_spending_merchant_retention_timeseries
WHERE 
    merchant_name IN ('UBEREATS', 'DOORDASH')
    AND measure = 'User Retention'
    AND member_age ILIKE '25-34'
    AND member_income = 'All Income Brackets'
    AND consumer_geo_name = 'United States'
    AND periods_since_first_purchase <= 24

Product Insights

Analyze market share changes between specific products for a demographic

Compare marketshare of OpenAI vs Anthropic products

WITH spend_data AS (
    SELECT 
        merchant_name || ' x ' || product_name AS series_name, 
        date, 
        value AS sales
    FROM 
        cybersyn.consumer_spending_merchant_product_timeseries
    WHERE 
        member_age = '18-24'
        AND member_income = 'All Income Brackets'
        AND frequency = 'Month'
        AND measure = 'Sales'
        AND channel = 'All Channels'
        AND consumer_geo_level = 'Country'
        AND consumer_geo_name = 'United States'
        AND merchant_geo_level = 'Country'
        AND merchant_geo_name = 'United States'
        AND merchant_name IN ('OPENAI', 'ANTHROPIC')
)

SELECT 
    series_name, 
    date,
    sales / SUM(sales) OVER (PARTITION BY date) AS market_share
FROM 
    spend_data
ORDER BY 
    date

Analyze retention across specific products

Compare retention across different streaming services like Disney Plus, Netflix Standard, and Netflix Premium

SELECT 
    merchant_name, 
    product_name, 
    measure, 
    value, 
    unit, 
    periods_since_first_purchase
FROM 
    cybersyn.consumer_spending_merchant_product_retention_timeseries
WHERE 
    (
        (merchant_name = 'NETFLIX' AND product_name IN ('PREMIUM', 'STANDARD')) 
        OR (merchant_name = 'DISNEY' AND product_name = 'DISNEY PLUS')
    )
    AND consumer_geo_name = 'United States'
    AND merchant_geo_name = 'United States'
    AND member_age = 'All Age Brackets'
    AND member_income = 'All Income Brackets'
ORDER BY 
    measure, 
    periods_since_first_purchase