Skip to main content
LayersQL provides three datasets for building metrics: products for product-level performance, search for search analytics, and collections for collection browsing analytics.

​
Products dataset

  • Label: Products
  • Description: Catalog and performance metrics for products.
  • Default group key: product_id

​
Metrics

  • total_sales (USD)
    • Sales after discounts and before taxes/returns.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased.
    • Example: SUM(quantity_purchased)
  • view_sessions (sessions)
    • Sessions where the product was viewed.
    • Example: COUNT_DISTINCT(view_sessions)
  • cart_sessions (sessions)
    • Sessions where the product was added to cart.
  • quantity_added_to_cart (items)
    • Items added to cart from online store sessions.

​
Dimensions

  • geo_country: Two-letter country code.
  • geo_state: Up to three-letter province code.
  • geo_city: City name as captured.
  • marketing_source: UTM source.
  • marketing_medium: UTM medium.
  • marketing_campaign: UTM campaign.

​
Product attributes (dynamic)

  • Usage: Use product_attributes.* to group or segment by dynamic product attributes defined in your catalog.
  • Examples: 
    GROUP BY product_attributes.color
GROUP BY product_attributes.size
SEGMENT BY product_attributes.material

​
Examples

Sales by product (last 7 days) 
FROM products
SHOW SUM(total_sales)
GROUP BY product_id
SINCE -7d
Views by campaign (this month) 
FROM products
SHOW COUNT_DISTINCT(view_sessions)
GROUP BY marketing_campaign
SINCE this_month
Sales by color (past 30 days) 
FROM products
SHOW SUM(total_sales)
GROUP BY product_attributes.color
SINCE past_30_days

​
Search dataset

  • Label: Search
  • Description: Analytics for search requests and conversions.
  • Default group key: id

​
Metrics

  • impressions (requests)
    • Number of search requests.
    • Example: COUNT_DISTINCT(impressions)
  • zero_results_count (requests)
    • Search requests that returned no results.
    • Example: COUNT_DISTINCT(zero_results_count)
  • avg_results_count (results)
    • Average number of results per search request.
    • Example: AVG(avg_results_count)
  • clicks (events)
    • Product views from search results.
    • Example: COUNT_DISTINCT(clicks)
  • add_to_cart (events)
    • Add to cart events from search results.
    • Example: COUNT_DISTINCT(add_to_cart)
  • purchases (transactions)
    • Purchases attributed to search.
    • Example: COUNT_DISTINCT(purchases)
  • revenue (USD)
    • Revenue attributed to search.
    • Example: SUM(revenue)

​
Dimensions

  • query_text_string: The search query string.
  • query_type: Type of search query.
  • num_results: Number of results returned.
  • shopping_channel: The shopping channel (e.g., online_store).

​
Examples

Search impressions by query (last 7 days) 
FROM search
SHOW COUNT_DISTINCT(impressions)
GROUP BY query_text_string
SINCE -7d
Zero results searches (last 7 days) 
FROM search
SHOW COUNT_DISTINCT(zero_results_count)
GROUP BY query_text_string
SINCE -7d
Search revenue by query type (this month) 
FROM search
SHOW SUM(revenue)
GROUP BY query_type
SINCE this_month
Search performance by shopping channel 
FROM search
SHOW COUNT_DISTINCT(impressions), COUNT_DISTINCT(clicks)
GROUP BY shopping_channel
SINCE -7d

​
Collections dataset

  • Label: Collections
  • Description: Analytics for collection browsing and conversions.
  • Default group key: id

​
Metrics

  • impressions (requests)
    • Number of collection browse requests.
    • Example: COUNT_DISTINCT(impressions)
  • clicks (events)
    • Product views from collection browsing.
    • Example: COUNT_DISTINCT(clicks)
  • add_to_cart (events)
    • Add to cart events from collection browsing.
    • Example: COUNT_DISTINCT(add_to_cart)
  • purchases (transactions)
    • Purchases attributed to collection browsing.
    • Example: COUNT_DISTINCT(purchases)
  • revenue (USD)
    • Revenue attributed to collection browsing.
    • Example: SUM(revenue)

​
Dimensions

  • collection_handle: The collection handle.
  • sort_order_id: The sort order used for the collection.
  • rule_id: The merchandising rule applied.
  • shopping_channel: The shopping channel (e.g., online_store).

​
Examples

Collection impressions by handle (last 7 days) 
FROM collections
SHOW COUNT_DISTINCT(impressions)
GROUP BY collection_handle
SINCE -7d
Collection revenue by sort order (this month) 
FROM collections
SHOW SUM(revenue)
GROUP BY sort_order_id
SINCE this_month
Collection performance by merchandising rule 
FROM collections
SHOW COUNT_DISTINCT(impressions), COUNT_DISTINCT(clicks)
GROUP BY rule_id
SINCE -7d
Collection analytics by shopping channel 
FROM collections
SHOW COUNT_DISTINCT(impressions), SUM(revenue)
GROUP BY shopping_channel
SINCE -30d

​
Dates (all datasets)

  • Relative offsets: -24h, -48h, -7d, -14d, -30d, -90d, -12m, -1y
  • Keywords: today, yesterday, this_week, last_week, this_month, last_month, this_quarter, last_quarter, this_year, last_year
  • Rolling windows: past_7_days, past_14_days, past_30_days, past_60_days, past_90_days, past_180_days, past_365_days
  • ISO timestamps: 2025-03-15 or 2025-03-15T10:30:00Z