Set Up Your AI Data Analyst

Configure the AI analyst

1) Enhance all assets with AI metadata

Run bruin ai enhance to auto-generate descriptions, column metadata, and quality check suggestions for every asset:

bruin ai enhance ecommerce/

This reads your SQL, figures out what each asset does, and writes descriptions into the asset files. Review the output - it's usually accurate but sometimes suggests unique checks on columns that aren't actually unique.

2) Create your AGENTS.md

Create an AGENTS.md file at the root of your Bruin project - the directory where .bruin.yml lives. Claude Code reads this file automatically when it starts a session in your project directory.

You can ask Claude Code to help draft it:

Create an AGENTS.md file for this ecommerce analytics project. Include data access rules using bruin query with the warehouse connection name from our pipeline, a glossary of ecommerce terms (AOV, ROAS, LTV, etc.), and data caveats like Stripe amounts being in cents and guest checkouts having null customer_email.

Here's what the file should look like. Replace the connection name with the one you set up in Step 1:

# AGENTS.md

## Data access
- Use `bruin query --connection clickhouse-default --query "<SQL>"` for all data access
- Always show the SQL query and explain your reasoning before executing it
- Add `LIMIT 10` to your SQL when exploring unfamiliar tables or testing complex queries
- Read the `assets/` directory to understand available tables and their schemas before querying
- This is a **read-only** environment - never run INSERT, UPDATE, DELETE, or DROP statements

<!-- Customize this list to match the sources you actually connected in Step 1.
     The example below includes Shopify, Stripe, Klaviyo, Facebook Ads, and GA4.
     Remove any you don't use and add any additional sources (e.g., TikTok Ads, Pinterest, Zendesk). -->
## Project overview
This is an ecommerce analytics pipeline ingesting data from Shopify (orders,
customers, products, inventory), Stripe (payments, refunds, payouts),
Klaviyo (email campaigns, flows), Facebook Ads (campaigns, ad insights),
and GA4 (sessions, events) into your data warehouse.

The pipeline has three layers:
- **raw.*** - ingested data from SaaS sources (do not query directly for reporting)
- **staging.*** - cleaned, deduplicated, joined tables
- **reports.*** - aggregated business metrics and KPIs

## Domain glossary
- **AOV** - Average Order Value: net revenue divided by number of paid orders
- **GMV** - Gross Merchandise Value: total order value before returns and discounts
- **NMV** - Net Merchandise Value: GMV minus returns, cancellations, and discounts
- **ROAS** - Return on Ad Spend: attributed revenue divided by ad spend
- **CAC** - Customer Acquisition Cost: total marketing spend divided by new customers
- **LTV** - Lifetime Value: total revenue from a customer across all orders
- **Conversion Rate** - percentage of website sessions resulting in a completed purchase
- **Cart Abandonment** - percentage of sessions with add-to-cart but no purchase
- **Repeat Purchase Rate** - percentage of customers with more than one order
- **Churn** - customers with no order in the past 90 days
- **SKU** - Stock Keeping Unit: unique identifier for a product variant
- **Basket Size** - number of items in a single order

## Data caveats
- All timestamps are in **UTC**
- Stripe amounts are in **cents** - divide by 100 for dollar values
- `customer_email` is `NULL` for guest checkouts (~15-20% of orders)
- Refund window is **30 days** - recent revenue figures may be revised downward
- Inventory data syncs every **4 hours** - not real-time
- Shopify `order_total` includes tax and shipping; use `subtotal` for product revenue only
- Facebook Ads data has a **24-48 hour attribution delay**
- Klaviyo campaigns have no direct spend - they appear with $0 spend in marketing reports

## Query guidelines
- For revenue, always use **NMV** (net of returns) unless explicitly asked for GMV
- Exclude test orders: `WHERE customer_email NOT LIKE '%@test.%'`
- Use a **12-month lookback window** for LTV calculations by default
- For marketing attribution, use `reports.rpt_marketing_roi` - do not attempt to join raw ad data with orders directly
- For daily KPIs, use `reports.rpt_daily_kpis` as the single source of truth
- Prefer `staging.*` tables over `raw.*` for any analysis

# AGENTS.md

## Data access
- Use `bruin query --connection bigquery-default --query "<SQL>"` for all data access
- Always show the SQL query and explain your reasoning before executing it
- Add `LIMIT 10` to your SQL when exploring unfamiliar tables or testing complex queries
- Read the `assets/` directory to understand available tables and their schemas before querying
- This is a **read-only** environment - never run INSERT, UPDATE, DELETE, or DROP statements

<!-- Customize this list to match the sources you actually connected in Step 1.
     The example below includes Shopify, Stripe, Klaviyo, Facebook Ads, and GA4.
     Remove any you don't use and add any additional sources (e.g., TikTok Ads, Pinterest, Zendesk). -->
## Project overview
This is an ecommerce analytics pipeline ingesting data from Shopify (orders,
customers, products, inventory), Stripe (payments, refunds, payouts),
Klaviyo (email campaigns, flows), Facebook Ads (campaigns, ad insights),
and GA4 (sessions, events) into your data warehouse.

The pipeline has three layers:
- **raw.*** - ingested data from SaaS sources (do not query directly for reporting)
- **staging.*** - cleaned, deduplicated, joined tables
- **reports.*** - aggregated business metrics and KPIs

## Domain glossary
- **AOV** - Average Order Value: net revenue divided by number of paid orders
- **GMV** - Gross Merchandise Value: total order value before returns and discounts
- **NMV** - Net Merchandise Value: GMV minus returns, cancellations, and discounts
- **ROAS** - Return on Ad Spend: attributed revenue divided by ad spend
- **CAC** - Customer Acquisition Cost: total marketing spend divided by new customers
- **LTV** - Lifetime Value: total revenue from a customer across all orders
- **Conversion Rate** - percentage of website sessions resulting in a completed purchase
- **Cart Abandonment** - percentage of sessions with add-to-cart but no purchase
- **Repeat Purchase Rate** - percentage of customers with more than one order
- **Churn** - customers with no order in the past 90 days
- **SKU** - Stock Keeping Unit: unique identifier for a product variant
- **Basket Size** - number of items in a single order

## Data caveats
- All timestamps are in **UTC**
- Stripe amounts are in **cents** - divide by 100 for dollar values
- `customer_email` is `NULL` for guest checkouts (~15-20% of orders)
- Refund window is **30 days** - recent revenue figures may be revised downward
- Inventory data syncs every **4 hours** - not real-time
- Shopify `order_total` includes tax and shipping; use `subtotal` for product revenue only
- Facebook Ads data has a **24-48 hour attribution delay**
- Klaviyo campaigns have no direct spend - they appear with $0 spend in marketing reports

## Query guidelines
- For revenue, always use **NMV** (net of returns) unless explicitly asked for GMV
- Exclude test orders: `WHERE customer_email NOT LIKE '%@test.%'`
- Use a **12-month lookback window** for LTV calculations by default
- For marketing attribution, use `reports.rpt_marketing_roi` - do not attempt to join raw ad data with orders directly
- For daily KPIs, use `reports.rpt_daily_kpis` as the single source of truth
- Prefer `staging.*` tables over `raw.*` for any analysis

# AGENTS.md

## Data access
- Use `bruin query --connection snowflake-default --query "<SQL>"` for all data access
- Always show the SQL query and explain your reasoning before executing it
- Add `LIMIT 10` to your SQL when exploring unfamiliar tables or testing complex queries
- Read the `assets/` directory to understand available tables and their schemas before querying
- This is a **read-only** environment - never run INSERT, UPDATE, DELETE, or DROP statements

<!-- Customize this list to match the sources you actually connected in Step 1.
     The example below includes Shopify, Stripe, Klaviyo, Facebook Ads, and GA4.
     Remove any you don't use and add any additional sources (e.g., TikTok Ads, Pinterest, Zendesk). -->
## Project overview
This is an ecommerce analytics pipeline ingesting data from Shopify (orders,
customers, products, inventory), Stripe (payments, refunds, payouts),
Klaviyo (email campaigns, flows), Facebook Ads (campaigns, ad insights),
and GA4 (sessions, events) into your data warehouse.

The pipeline has three layers:
- **raw.*** - ingested data from SaaS sources (do not query directly for reporting)
- **staging.*** - cleaned, deduplicated, joined tables
- **reports.*** - aggregated business metrics and KPIs

## Domain glossary
- **AOV** - Average Order Value: net revenue divided by number of paid orders
- **GMV** - Gross Merchandise Value: total order value before returns and discounts
- **NMV** - Net Merchandise Value: GMV minus returns, cancellations, and discounts
- **ROAS** - Return on Ad Spend: attributed revenue divided by ad spend
- **CAC** - Customer Acquisition Cost: total marketing spend divided by new customers
- **LTV** - Lifetime Value: total revenue from a customer across all orders
- **Conversion Rate** - percentage of website sessions resulting in a completed purchase
- **Cart Abandonment** - percentage of sessions with add-to-cart but no purchase
- **Repeat Purchase Rate** - percentage of customers with more than one order
- **Churn** - customers with no order in the past 90 days
- **SKU** - Stock Keeping Unit: unique identifier for a product variant
- **Basket Size** - number of items in a single order

## Data caveats
- All timestamps are in **UTC**
- Stripe amounts are in **cents** - divide by 100 for dollar values
- `customer_email` is `NULL` for guest checkouts (~15-20% of orders)
- Refund window is **30 days** - recent revenue figures may be revised downward
- Inventory data syncs every **4 hours** - not real-time
- Shopify `order_total` includes tax and shipping; use `subtotal` for product revenue only
- Facebook Ads data has a **24-48 hour attribution delay**
- Klaviyo campaigns have no direct spend - they appear with $0 spend in marketing reports

## Query guidelines
- For revenue, always use **NMV** (net of returns) unless explicitly asked for GMV
- Exclude test orders: `WHERE customer_email NOT LIKE '%@test.%'`
- Use a **12-month lookback window** for LTV calculations by default
- For marketing attribution, use `reports.rpt_marketing_roi` - do not attempt to join raw ad data with orders directly
- For daily KPIs, use `reports.rpt_daily_kpis` as the single source of truth
- Prefer `staging.*` tables over `raw.*` for any analysis

3) Ask Claude Code real business questions

With the Bruin MCP connected and AGENTS.md in place, Claude Code is now your ecommerce data analyst. Try these:

What was our revenue last week compared to the week before? Break it down by day.

Which marketing channel had the best ROAS in the past 30 days?

Show me the customer cohort retention curve for customers acquired in January.

What are our top 10 products by revenue this month, and what's their cancellation rate?

Note: The rpt_product_performance report currently shows catalog data from stg_products only. To get revenue-per-product metrics, expand the pipeline with a staging.stg_order_line_items asset that flattens Shopify's nested line item JSON, then ask Claude Code to rebuild this report with order-level data.

What's our conversion rate trend over the past 3 months?

Claude Code uses the Bruin MCP to query your warehouse directly, reading the AGENTS.md for context on your domain terms and data quirks before writing SQL.

4) Iterate on AGENTS.md

When the agent gets something wrong - wrong column, wrong metric definition, expensive query - add a correction to AGENTS.md. For example:

## Known issues
- The `stg_orders.order_total` includes tax. For product-only revenue, use `subtotal` instead.
- When computing repeat purchase rate, count by `customer_email`, not by `order_id`.

The more you use it, the better your AGENTS.md gets. When the agent makes a mistake, that's a sign something is missing from the context file.

5) Deploy to Bruin Cloud

Now let's make this available to the whole team - in Slack or Teams, not just locally.

Push your project to GitHub:

Note: .bruin.yml contains your connection credentials. bruin init adds it to .gitignore by default - verify it's there before pushing.

git add -A -- ':!.bruin.yml'
git commit -m "ecommerce analytics pipeline"
git remote add origin https://github.com/your-org/ecommerce-analytics.git
git push -u origin main

Add the repo to Bruin Cloud:

Go to Bruin Cloud, open Team Settings > Projects
Add your GitHub repository
Enable the ecommerce pipeline
Configure your connections (warehouse, Shopify, Stripe, etc.) in the Cloud environment
Run the pipeline to confirm it works

Create the Slack/Teams AI agent:

Go to Agents > Create Agent
Select your repo and the ecommerce pipeline
Add your Slack (or Teams) credentials
Name the agent, e.g. "Ecommerce Analyst"
Select the target channel where your team asks data questions

Test in Slack:

Mention the agent in your channel:

@Ecommerce Analyst What was our revenue yesterday?

The agent queries your warehouse using the same AGENTS.md context and responds with an answer. Anyone on the team can ask questions without writing SQL.

Shopify Data Pipeline