nice-insights-metrics（很好的洞察指标）

Nice Insights Metrics

Use these tools to answer analytics questions about advertising performance, sales, order economics, and customer acquisition costs.

Data freshness: Data is imported once daily for the *previous* day. Do not query today's date — results will be incomplete. Yesterday is always the most recent complete date.

Available Tools

Step 1: Identify the Company

If company_id is already known from context, use it directly.

Otherwise, call list_companies to get the list of companies the user has access to. If multiple companies are returned and the question does not clearly indicate which one, ask the user to confirm before proceeding.

Step 2: Confirm Refund Handling (BLOCKING — required before any order query)

STOP. Before calling query_order_metrics, query_order_line_metrics, or query_customer_cohort_metrics, you MUST ask the user whether to include or exclude refunds. Do NOT proceed with the tool call until they answer.

Ask: "Should I include or exclude refunds?"

• Exclude refunds → transaction_types: ["Order"] (typical for marketing/sales activity views)
• Include refunds → transaction_types: ["Order", "Refund"] (typical for finance/net revenue views)
• No filter → omit transaction_types entirely (returns everything)

Note: If you add TRANSACTION_TYPE as a *dimension*, refund rows appear as separate rows rather than being filtered. Use this when the user wants orders and refunds broken out side by side.

Skip this step for query_ad_metrics, query_timeseries_metrics, query_email_profile_metrics, and query_email_event_metrics — they have no transaction type concept.

Step 2b: Customer Type Filter (optional)

Use customer_types to segment by buyer history. Available on query_order_line_metrics and query_order_metrics.

• ["New"] — customers placing their first-ever order
• ["Repeat"] — customers who have ordered before
• Omit to include all customer types

You can also add CUSTOMER_TYPE as a dimension to break results out side by side instead of filtering.

Step 2c: Cohort Filters (optional)

Use these filters to narrow results to specific customer acquisition cohorts. Both are optional.

• first_order_date_range — available on query_order_metrics and query_customer_cohort_metrics (required on query_customer_cohort_metrics — it defines which cohorts to include). Filters to orders where the customer's first-ever order date falls within the given range. Uses the same {start, end} format as date_range.
• max_days_since_first_order — available on query_order_metrics, query_order_line_metrics, and query_customer_cohort_metrics. Filters to orders placed within N days of the customer's first order. Useful for analyzing early customer behavior (e.g., repeat purchases within 30 days).

Step 3: Choose the Right Tool and Metrics

Use the definitions below to infer which metrics to request based on the user's question. query_ad_metrics defaults to SPEND, IMPRESSIONS, CLICKS if no metrics are specified. All other tools require explicit metric selection.

Ad Metrics — query_ad_metrics

Available channels: Additional Ad Spend, Tatari TV, TikTok, Amazon, AppLovin, Google, Meta.

Order Line Metrics — query_order_line_metrics

Product-level sales data. Use for revenue, discounts, refunds, units, and product mix analysis.

Order Metrics — query_order_metrics

Order-level costs and margins. Use for profitability, fee analysis, and acquisition economics.

Customer Cohort Metrics — query_customer_cohort_metrics

Pivoted cohort matrix with rows per cohort (first order period) and columns per period since first order. All values are cumulative through the period. Use for retention analysis, LTV curves, and cohort comparisons. Accepts a single measure per query.

Parameters:

• cohort_grain: week or month (default: month) — time grain for cohort bucketing

Additional filters (unique to this tool):

• first_order_product_name — filter to customers whose first order included this product name
• first_order_sku — filter to customers whose first order included this SKU
• sales_channels — filter by sales channel: Amazon, Shopify, TikTok
• subscription_types — filter by subscription type: First, Recurring, Unknown

Timeseries Metrics — query_timeseries_metrics

Blended CAC over time. Use for trend analysis of customer acquisition efficiency. date_range is required.

Email Profile Metrics — query_email_profile_metrics

Email list and profile-level conversion. Use for list growth, email profile activity, and marketing-attributed sales analysis. Metrics ending in _MAR are the *marketing* version of net sales and exclude refunds by construction.

Additional filters (unique to this tool):

• first_event_date_range — filter by the date of the profile's first email event
• profile_statuses — filter by profile status
• profile_creation_types — filter by how the profile was created
• profile_creation_flow_or_lists — filter by originating flow or list name

Email Event Metrics — query_email_event_metrics

Email event activity counts (sends, opens, clicks, etc.). Use for engagement volume and trends.

Additional filters (unique to this tool):

• event_names — filter to specific event names
• profile_statuses, profile_creation_types, profile_creation_flow_or_lists — same semantics as in query_email_profile_metrics

Note on context-dependent metrics: ORDER_COUNT, CUSTOMER_COUNT, and average metrics (AVERAGE_ORDER_VALUE, AVERAGE_CONTRIBUTION_MARGIN, etc.) produce different SQL depending on whether TRANSACTION_TYPE is included as a dimension. When TRANSACTION_TYPE is a dimension, these metrics account for both order and refund rows separately. When it is not, they automatically filter to orders only.

Step 4: Choose Output Mode

Use inline when result sets are relatively small and you need to reason over or summarize the data directly.

Use s3_csv as the output mode when you expect more than 40 rows of data, or when the resulting data is complicated. Analyze the data with a script on your side for s3_csv output.

Available Dimensions

Time: DATE, WEEK, MONTH

Ad-specific: CHANNEL, PLATFORM, CAMPAIGN_ID, CAMPAIGN_NAME, AD_GROUP_ID, AD_GROUP_NAME, AD_ID, AD_NAME

Order/line: SALES_CHANNEL, CUSTOMER_TYPE, TRANSACTION_TYPE, PRODUCT_ID, PRODUCT_NAME, PRODUCT_VARIANT_ID, PRODUCT_VARIANT_NAME, SKU, IS_SUBSCRIPTION, SUBSCRIPTION_TYPE, ORDER_ID, CUSTOMER_ID

Cohort analysis (order line only): FIRST_ORDER_DATE, FIRST_ORDER_WEEK, FIRST_ORDER_MONTH, DAYS_SINCE_FIRST_ORDER, WEEKS_SINCE_FIRST_ORDER, MONTHS_SINCE_FIRST_ORDER

Email profile: PROFILE_CREATED_DATE, PROFILE_STATUS, PROFILE_CREATION_TYPE, PROFILE_CREATION_FLOW_OR_LIST, DAYS_FROM_CREATION_TO_FIRST_ORDER, WEEKS_FROM_CREATION_TO_ORDER_DATE, MONTHS_FROM_CREATION_TO_ORDER_DATE

Email event: EVENT_DATE, EVENT_NAME, PROFILE_STATUS, PROFILE_CREATION_TYPE, PROFILE_CREATION_FLOW_OR_LIST, CLICKED_EMAIL_SOURCE

Cohort period values: For DAYS/WEEKS/MONTHS_SINCE_FIRST_ORDER, 0 always represents the first order date itself. 1 is the 1st full day/week/month after that, 2 is the 2nd, and so on.

Note: For cohort retention/LTV analysis, prefer query_customer_cohort_metrics which returns a pre-pivoted matrix directly.

Common Query Patterns

Ad spend by channel for a date range:

query_ad_metrics(query={
  filters: { company_id: 123, date_range: { start: "2025-01-01", end: "2025-01-31" } },
  dimensions: ["CHANNEL"],
  metrics: ["SPEND", "IMPRESSIONS", "CLICKS", "CPM", "CTR"]
})

Net sales by product (excluding refunds):

query_order_line_metrics(query={
  filters: {
    company_id: 123,
    date_range: { start: "2025-01-01", end: "2025-01-31" },
    transaction_types: ["Order"]
  },
  dimensions: ["PRODUCT_NAME"],
  metrics: ["GROSS_SALES", "TOTAL_DISCOUNT", "NET_SALES", "UNIT_COUNT", "ORDER_COUNT"]
})

Monthly contribution margin by sales channel (including refunds):

query_order_metrics(query={
  filters: {
    company_id: 123,
    date_range: { start: "2025-01-01", end: "2025-03-31" },
    transaction_types: ["Order", "Refund"]
  },
  dimensions: ["MONTH", "SALES_CHANNEL"],
  metrics: ["CONTRIBUTION_MARGIN", "ORDER_COUNT"]
})

Monthly cumulative LTV by customer cohort:

query_customer_cohort_metrics(query={
  filters: {
    company_id: 123,
    first_order_date_range: { start: "2025-01-01", end: "2025-03-31" },
    transaction_types: ["Order"]
  },
  measure: "sales_per_customer",
  cohort_grain: "month"
})

Weekly blended CAC trend:

query_timeseries_metrics(query={
  filters: { company_id: 123, date_range: { start: "2025-01-01", end: "2025-03-31" } },
  dimensions: ["WEEK"],
  metrics: ["DTC_BLENDED_CAC", "AMZN_BLENDED_CAC", "COMBINED_BLENDED_CAC"]
})

Monthly email-attributed sales by profile status:

query_email_profile_metrics(query={
  filters: { company_id: 123, date_range: { start: "2025-01-01", end: "2025-03-31" } },
  dimensions: ["MONTH", "PROFILE_STATUS"],
  metrics: ["PROFILE_COUNT", "ORDER_COUNT", "TOTAL_NET_SALES_MAR", "CONVERSION_RATE"]
})

Weekly email event volume by event name:

query_email_event_metrics(query={
  filters: { company_id: 123, date_range: { start: "2025-01-01", end: "2025-03-31" } },
  dimensions: ["WEEK", "EVENT_NAME"],
  metrics: ["TOTAL_EVENT_COUNT", "UNIQUE_EVENT_COUNT"]
})