A proof-of-concept dashboard that monitors channel conflicts and competitor promotional activity by combining pricing data with competitive intelligence.

This POC demonstrates an intelligent pricing analytics platform that:

• Detects channel conflicts - Identifies when wholesale customers undercut your direct prices
• Tracks competitor promos - Monitors competitive promotional activity
• AI-powered insights - Ask natural language questions about your pricing data
• Visual analytics - Charts and tables for quick insights

User Question
     ↓
AI Chat (OpenAI GPT-4)
     ↓
Generate SQL Query
     ↓
BigQuery (Your Data)
     ↓
Results + AI Interpretation
     ↓
Beautiful Dashboard

Tech Stack:

• Frontend: Next.js 14 + React + TypeScript + Tailwind CSS
• Database: Google BigQuery
• AI: OpenAI GPT-4 (for SQL generation + insights)
• Charts: Recharts

• 10 competitor promos across 4 competitors
• 26 price points across 8 SKUs and 4 channels
• Realistic pricing scenarios with conflicts

1. Dashboard Tab:

   • Channel conflicts table
   • Active competitor promos
   • Price comparison chart
   • Detailed pricing table

2. AI Assistant Tab:

   • Natural language queries
   • Auto-generated SQL
   • Formatted results
   • Business insights

• Node.js 18+ installed
• Google Cloud Platform account
• OpenAI API account
• Basic familiarity with terminal/command line

1. Create GCP Project (if you don't have one):

   • Go to https://console.cloud.google.com
   • Create a new project or select existing

2. Enable BigQuery API:

   • In GCP Console, search for "BigQuery API"
   • Click "Enable"

3. Create Service Account:

   • Go to IAM & Admin > Service Accounts
   • Click "Create Service Account"
   • Name it "pricing-intelligence"
   • Grant role: "BigQuery Admin"
   • Click "Create Key" → JSON
   • Download the JSON key file

4. Load Sample Data:

   • Open BigQuery Console
   • Copy and paste the contents of bigquery-setup.sql
   • Run each section in order:
     1. Create dataset
     2. Create competitor_promos table + insert data
     3. Create sku_pricing table + insert data

1. Clone/Download this project:

   cd pricing-intelligence-poc

2. Install dependencies:

   npm install

3. Configure environment variables:

   cp .env.local.example .env.local

   Edit .env.local and add:

   GCP_PROJECT_ID=your-actual-project-id
   GOOGLE_APPLICATION_CREDENTIALS=/absolute/path/to/your-service-account-key.json
   OPENAI_API_KEY=sk-your-openai-api-key

4. Get OpenAI API Key:

   • Go to https://platform.openai.com/api-keys
   • Create new secret key
   • Copy and paste into .env.local

npm run dev

Open http://localhost:3000 in your browser.

• View all channel conflicts at a glance
• See active competitor promos
• Compare prices across channels visually
• Drill into detailed pricing data

Try these example questions:

• "Which SKUs have the biggest channel conflict?"
• "Show me all competitor promos from December"
• "What products are cheaper on Amazon than our site?"
• "Which wholesale customer has the lowest prices?"
• "Are there any active competitor promos right now?"

The AI will:

1. Generate a SQL query
2. Run it against BigQuery
3. Show you the data
4. Provide business insights

Replace the sample data in bigquery-setup.sql with your actual data dumps:

Competitor Promos Format:

INSERT INTO pricing_intelligence.competitor_promos VALUES
  ('PROMO-XXX', 'Competitor Name', 'Promo Description', 'promo_type', 'YYYY-MM-DD', 'YYYY-MM-DD', 'TIMESTAMP');

SKU Pricing Format:

INSERT INTO pricing_intelligence.sku_pricing VALUES
  ('PRICE-XXX', 'SKU-XXX', 'Product Name', 'Category', 'channel_name', 42.00, 'TIMESTAMP');

If you prefer Claude over OpenAI:

1. Install Anthropic SDK:

   npm install @anthropic-ai/sdk

2. Update lib/openai.ts to use Claude instead

3. Add ANTHROPIC_API_KEY to .env.local

pricing-intelligence-poc/
├── app/
│   ├── api/
│   │   ├── query/route.ts      # BigQuery API endpoint
│   │   └── chat/route.ts       # AI chat endpoint
│   ├── page.tsx                # Main page with tabs
│   ├── layout.tsx              # App layout
│   └── globals.css             # Global styles
├── components/
│   ├── Dashboard.tsx           # Dashboard with charts/tables
│   └── AIChat.tsx              # AI chat interface
├── lib/
│   ├── bigquery.ts             # BigQuery client & queries
│   └── openai.ts               # OpenAI client & prompts
├── bigquery-setup.sql          # Database schema + sample data
└── README.md                   # This file

"Cannot find module '@google-cloud/bigquery'"

• Run npm install again

"Permission denied" for BigQuery

• Make sure your service account has "BigQuery Admin" role
• Verify GOOGLE_APPLICATION_CREDENTIALS path is correct

"Invalid API key" for OpenAI

• Check that your API key starts with sk-
• Make sure you have credits in your OpenAI account

"No data showing in dashboard"

• Verify you ran all SQL statements in bigquery-setup.sql
• Check BigQuery Console to confirm tables have data
• Check browser console for API errors

Charts not rendering

• Make sure recharts is installed: npm install recharts
• Clear browser cache and reload

To turn this POC into a production system:

1. Automated Data Loading:

   • Set up scheduled Cloud Functions to load data dumps
   • Use Cloud Storage + BigQuery data transfer service

2. Authentication:

   • Add NextAuth.js for user login
   • Restrict access to authorized users

3. Alerts:

   • Set up email/Slack alerts for critical conflicts
   • Use BigQuery scheduled queries + Cloud Functions

4. Enhanced Analytics:

   • Add historical trending
   • Predictive pricing recommendations
   • Competitor response suggestions

5. Better AI:

   • Fine-tune prompts for your specific business
   • Add conversation memory
   • Multi-step reasoning for complex questions

Channel Conflict Queries:

• "Show me all products where wholesale customers are undercutting us"
• "Which SKU has the worst channel conflict?"
• "What's the average discount wholesale customers are offering?"

Competitive Intelligence:

• "What types of promos do competitors run most often?"
• "Which competitor runs the most promotions?"
• "Show me all BOGO promos"

Price Analysis:

• "What's our average price compared to Amazon?"
• "Which products are we overpriced on?"
• "Show me Kitchen category pricing"

Questions? Issues?

• Check the troubleshooting section above
• Review the sample queries in bigquery-setup.sql
• Verify your .env.local configuration

This is a POC/demo project. Use freely for your business needs.

Built with ❤️ using Next.js, BigQuery & OpenAI