Tag Analytics

Overview

Tag analytics provides aggregate metric views across product tags — enabling Creative and Ops teams to understand which design categories, themes, and product lines are performing. It builds on the tag classification system and the mart refresh pipeline.

Goal: Answer questions like “How are all iPhone-floral products performing?” or “Which tag category has the highest 30-day sales?”

API endpoints

Commerce analytics (specced)

Method	Path	Spec	Description
GET	/analytics/commerce/products	analytics.yaml	Product performance ranked by revenue/units
GET	/analytics/commerce/overview	analytics.yaml	Commerce headline KPIs for date range

Tag analytics (internal, not yet specced)

Method	Path	Description
POST	/api/tag-analytics/performance	Aggregated metrics for a set of tags
GET	/api/tag-analytics/distribution/:category	Tag distribution within a category
GET	/api/tag-analytics/group-performance/:groupId	Metrics for a saved tag group
POST	/api/tag-analytics/refresh	Trigger refresh of the tag performance aggregate

Backing catalog endpoints (specced)

Method	Path	Spec	Description
GET	/tags	product.yaml	List product tags (flat labels)
GET	/categories	product.yaml	List product categories

Schema tables

Table	Module	Role
product	Product	Read: product catalog for join
product_tag	Product	Read: tag-to-product links
measurement.sales (R2 Iceberg)	Analytics	Read: sales metrics per product
product_category	Product	Read: tag group storage (rewrite target)

Infrastructure

Resource	Type	Purpose
dashboard-api	CF Worker (internal)	Analytics query endpoints
measurement.sales	R2 Iceberg table	Sales metric source
PlanetScale (Hyperdrive)	Managed Postgres	Product + tag + category data
Mart refresh job	CF Cron Trigger	Rebuilds performance aggregates

Participants

Actor	Role
Staff user (Creative/Ops)	Explores tag metrics, creates tag groups, refreshes views
Dashboard API Worker	Serves analytics endpoints
PlanetScale	Store for tag/product data
Mart refresh job	Rebuilds performance aggregates

Performance aggregate request

// POST /api/tag-analytics/performance
{
  tagNames: string[]   // Tag identifiers, e.g. ["floral", "iphone-15", "summer-2024"]
}

Returns aggregated metrics (total units, 30-day units, product counts) for the given set of tags.

Distribution response

The distribution endpoint returns how tags within a category are distributed across products — useful for understanding coverage (e.g., “80% of products have a season tag, but only 30% have a designer tag”).

Data flow

sequenceDiagram
    participant User as Staff user
    participant API as Dashboard API
    participant DB as PlanetScale

    User->>API: POST /tag-analytics/performance
    API->>DB: Query product + measurement.sales (R2) + product_tag
    DB-->>API: Aggregated metrics
    API-->>User: Tag performance metrics

    User->>API: POST /tag-analytics/refresh
    API->>DB: Rebuild tag performance aggregate
    DB-->>API: Success
    API-->>User: { success: true }

Migration notes

The current system relies on Supabase RPC functions for all tag analytics:

Current RPC	Target implementation
get_tag_performance_aggregate(tagNames)	TypeScript query joining product + measurement.sales (R2) + product_tag, filtered by tag names
get_tag_distribution_by_category(category)	TypeScript query counting products per tag within a category
get_tag_group_performance(groupId)	Lookup tag group -> get tag names -> run aggregate query
refresh_tag_performance_aggregate()	Background job rebuilds aggregate table (runs hourly via cron)

Acceptance criteria

• Performance aggregate returns correct metric totals for a given set of tags
• Distribution endpoint shows accurate product counts per tag within a category
• Tag group performance matches manually querying the group’s constituent tags
• Refresh endpoint triggers a rebuild and the metrics reflect within the next query