adityac7/vdata-mcp-server

Vdata MCP Server

A Model Context Protocol (MCP) server that provides access to HUL e-commerce funnel analytics data stored in PostgreSQL/Supabase.

Overview

This MCP server provides programmatic access to the hul_combined_data table containing e-commerce funnel events (ads, search, product views, cart) with demographics and weighted user representation.

Features

• Progressive Context Loading: Get schema and metadata on demand
• SQL Query Execution: Run SELECT queries with automatic weighting
• NCCS Merging: Automatic socioeconomic class merging (A/A1→A, B→B, C/D/E→C/D/E)
• Weighted Analysis: All user counts automatically weighted to represent population
• HTTP API: RESTful endpoints for testing and integration

Database Schema

hul_combined_data Table

Main table containing HUL e-commerce funnel data (629,890 rows):

User Identifiers:

• vtionid: User identifier
• id: Primary key

Event Data:

• event_ad: Ad view events count
• event_search: Search events count
• event_product: Product view events count
• event_cart: Cart events count
• event_category: Category events count

Product/Brand Information:

• brand: Brand name
• sub_brand: Sub-brand name
• parent_company: Parent company
• cat: Category
• Cat_1: Category level 1
• tag: Product tag
• Keyword: Search keyword

Event Flags:

• Seen Ad: Whether user saw ad (Yes/No)
• Searched Product: Whether user searched (Yes/No)
• Seen Product: Whether user viewed product (Yes/No)
• Browsed Product: Whether user browsed (Yes/No)
• Added to Cart: Whether user added to cart (Yes/No)
• Category Interaction: Category interaction flag

Demographics:

• age: Age bucket
• gender: User gender
• nccs_code: Socioeconomic class code
• nccs_actual: Actual NCCS classification
• State: State/region
• zone: Geographic zone
• City_Classification: City classification
• Population: Population segment

Platform:

• app: E-commerce app/platform
• apps_for_ads: Apps where ads were shown

Metrics:

• time_spent: Time spent (seconds)
• Homepage_to_Cart: Homepage to cart conversion metric
• Search_to_Cart: Search to cart conversion metric
• Product_to_Cart: Product to cart conversion metric
• Browse_to_Cart: Browse to cart conversion metric
• Weights: Representativeness weight (user represents N others)

Temporal:

• date_f: Event date
• Month: Month name
• Year: Year
• created_at: Record creation timestamp

Deployment

Render (Recommended)

1. Push this repository to GitHub
2. Connect to Render
3. Deploy using the included render.yaml configuration
4. Environment variables are pre-configured in render.yaml

Manual Deployment

# Install dependencies
pip install -r requirements.txt

# Set environment variables
export DATASET_1_NAME=hul_combined_data
export DATASET_1_DESC="HUL e-commerce funnel data"
export DATASET_1_CONNECTION="postgresql://postgres:Vtion%402023%23@db.yjiotntmzaukbmgxeqvq.supabase.co:5432/postgres?sslmode=require"
export DATASET_1_DICTIONARY='{"hul_combined_data": "HUL e-commerce funnel events with demographics and weights"}'

# Run server
python server.py

API Endpoints

Health Check

GET /
GET /health

List Datasets

GET /datasets

Execute Query (Testing)

POST /query
Content-Type: application/json

{
  "dataset_id": 1,
  "query": "SELECT brand, SUM(weights) as users FROM hul_combined_data WHERE \"Added to Cart\" = 'Yes' GROUP BY brand ORDER BY users DESC LIMIT 10"
}

MCP Tools

The server provides these MCP tools:

1. get_context: Progressive context loading (levels 0-3)
2. list_available_datasets: List all configured datasets
3. get_dataset_schema: Get schema for specific dataset
4. run_query: Execute SQL queries with automatic weighting

Usage with MCP Clients

Claude Desktop

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "vdata-analytics": {
      "command": "python",
      "args": ["/path/to/indian_analytics_mcp.py"],
      "env": {
        "DATASET_1_NAME": "hul_combined_data",
        "DATASET_1_DESC": "HUL e-commerce funnel data",
        "DATASET_1_CONNECTION": "postgresql://postgres:Vtion%402023%23@db.yjiotntmzaukbmgxeqvq.supabase.co:5432/postgres?sslmode=require",
        "DATASET_1_DICTIONARY": "{\"hul_combined_data\": \"HUL e-commerce funnel events with demographics and weights\"}"
      }
    }
  }
}

Example Queries

-- Top brands by cart additions
SELECT brand, SUM(weights) as weighted_users 
FROM hul_combined_data 
WHERE "Added to Cart" = 'Yes' 
GROUP BY brand 
ORDER BY weighted_users DESC 
LIMIT 10;

-- Conversion funnel by NCCS
SELECT nccs_code, 
       SUM(CASE WHEN "Seen Ad" = 'Yes' THEN weights ELSE 0 END) as saw_ad,
       SUM(CASE WHEN "Searched Product" = 'Yes' THEN weights ELSE 0 END) as searched,
       SUM(CASE WHEN "Added to Cart" = 'Yes' THEN weights ELSE 0 END) as added_cart
FROM hul_combined_data 
GROUP BY nccs_code;

-- App performance by state
SELECT app, "State", SUM(weights) as users, AVG(time_spent) as avg_time
FROM hul_combined_data 
GROUP BY app, "State" 
ORDER BY users DESC 
LIMIT 20;

Security

• Read-only queries (SELECT only)
• Dangerous SQL keywords blocked
• Connection pooling prevents resource exhaustion
• SSL mode required for Supabase connection

Data Statistics

• Total rows: 629,890
• Table: hul_combined_data
• Source: Supabase PostgreSQL
• Update frequency: As per data pipeline

Support

For issues or questions, check the logs:

# On Render
View logs in Render dashboard

# Local
Check console output

Version

1.0 - Initial deployment with Supabase connection and HUL data schema

License

MIT