Ramp Vendor Spend Analysis

Ramp Vendor Spend Analysis

Overview

This skill extracts comprehensive vendor data from Ramp, enriches it with owner/department information and contract data from purchase orders, and outputs structured results. Data can be displayed as text/tables or exported to connected integrations (Notion, Google Sheets, etc.).

When to Use This Skill

• User asks to analyze vendor spend or build a vendor database
• User wants to see top vendors by spend (L365, L30, YTD, all-time)
• User asks about vendor owners or which departments own which vendors
• User asks about contract end dates, renewals, or purchase order status
• User wants vendor data exported to Notion, spreadsheets, or other systems
• User asks ad-hoc questions like "vendors with spend over $50k" or "upcoming renewals"

Prerequisites

• Ramp MCP server must be connected
• For exports: Target integration (Notion, Google Sheets, etc.) should be connected

Data Collection Workflow

Step 1: Load Core Vendor Data

Tool: ramp-demo:load_vendors

This is the primary data source. Call with default parameters to load all vendors.

Parameters: {}

Data extracted per vendor:

• Vendor ID
• Vendor Name
• Vendor Owner ID
• Vendor Contacts (email, phone)
• Total spend L365 (last 365 days)
• Total spend L30 (last 30 days)
• Total spend YTD (year to date)
• Total spend all-time
• Billing frequency
• Tax information (W-9 status, tax ID)
• Active status

Step 2: Load Users for Owner Details

Tool: ramp-demo:load_users

Required to resolve Vendor Owner IDs to names and get their department assignments.

Parameters: {}

Data extracted per user:

• User ID
• First name, Last name
• Email
• Department ID
• Location ID
• Manager ID

Step 3: Load Departments

Tool: ramp-demo:load_departments

Required to map Department IDs to department names.

Parameters: {}

Data extracted:

• Department ID
• Department Name

Step 4: Load Bills for Payment Method Analysis

Tool: ramp-demo:load_spend_export

Query bills to determine preferred payment methods per vendor.

Parameters:
  spend_export_type: "bills"
  from_date: [365 days ago, YYYY-MM-DD format]
  to_date: [today, YYYY-MM-DD format]

Data extracted per bill:

• Vendor ID (to join with vendor data)
• Payment method used
• Bill amount
• Bill date

Step 5: Load Purchase Orders for Contract Data

Tool: ramp-demo:load_purchase_orders

Purchase orders contain contract/renewal information. The spend end date on a PO maps to the contract end date for that vendor relationship.

Parameters:
  from_date: [365 days ago, YYYY-MM-DD format]
  to_date: [today, YYYY-MM-DD format]

Data extracted per purchase order:

• PO ID
• PO Number
• Vendor ID (to join with vendor data)
• Spend end date → maps to contract end date
• Total amount
• Amount paid/billed
• Receipt status (FULLY_RECEIVED, PARTIALLY_RECEIVED, OVER_RECEIVED, NOT_RECEIVED)
• Three-way match enabled flag
• Created date

Contract status logic:

• If vendor has PO with spend_end_date → "Contract in place", use date as contract end
• If vendor has multiple POs → "Multiple contracts", use earliest upcoming end date
• If vendor has no POs → "No contract in place"
• If PO is nearing full billing (amount_paid approaching total_amount) → flag for renewal attention

Step 6: Join and Enrich Data

After loading all data sources, perform the following joins using SQL queries:

Tool: ramp-demo:execute_query

-- Join vendors with owner names, departments, and contract info from POs
SELECT 
  v.vendor_name,
  v.total_spend_l365,
  v.total_spend_l30,
  v.total_spend_ytd,
  v.total_spend_all_time,
  v.billing_frequency,
  v.tax_status,
  u.first_name || ' ' || u.last_name AS vendor_owner_name,
  u.email AS vendor_owner_email,
  d.name AS owner_department,
  po.spend_end_date AS contract_end_date,
  po.total_amount AS po_total,
  po.amount_paid AS po_paid,
  po.receipt_status,
  CASE 
    WHEN po.id IS NULL THEN 'No contract in place'
    WHEN po_count.cnt > 1 THEN 'Multiple contracts'
    ELSE 'Contract'
  END AS contract_status
FROM vendors v
LEFT JOIN users u ON v.vendor_owner_id = u.id
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN purchase_orders po ON v.id = po.vendor_id
LEFT JOIN (
  SELECT vendor_id, COUNT(*) as cnt 
  FROM purchase_orders 
  GROUP BY vendor_id
) po_count ON v.id = po_count.vendor_id
ORDER BY v.total_spend_l365 DESC;

Note: Actual column names may vary. After loading data, query the schema:

PRAGMA table_info(vendors);
PRAGMA table_info(users);
PRAGMA table_info(departments);
PRAGMA table_info(purchase_orders);
PRAGMA table_info(bills);

Output Schema

The final enriched dataset should include these fields per vendor:

Handling Ad-Hoc Queries

For filtered queries like "show vendors over $50k spend":

1. Load data using Steps 1-5 above
2. Apply filters in the SQL query:

SELECT * FROM vendors 
WHERE total_spend_l365 > 50000
ORDER BY total_spend_l365 DESC;

Common filter patterns:

• Spend thresholds: WHERE total_spend_l365 > [amount]
• Department filter: WHERE owner_department = '[dept_name]'
• Active only: WHERE is_active = 1
• Top N vendors: LIMIT [n]
• Upcoming renewals: WHERE contract_end_date BETWEEN date('now') AND date('now', '+90 days')
• Missing contracts: WHERE contract_status = 'No contract in place' AND total_spend_l365 > 25000
• POs nearing completion: WHERE po_paid / po_total > 0.8

Output Options

After data collection, ask the user how they want to receive the results:

Option 1: Text/Table Display (Default)

Display results as a formatted markdown table directly in the conversation.

Option 2: Export to Connected Integration

Check for available integrations and offer export:

For Notion:

• Create or update a database with the output schema
• Map fields to Notion properties:
  • Vendor Name → Title
  • Spend fields → Number ($ format)
  • Contract Status → Select (Contract | No contract in place | Multiple contracts)
  • Contract End Date → Date
  • Tax Status → Select (Tax details verified by Ramp | Missing | N/A)
  • Payment Method → Select (Pay by card | Pay by Bill pay (ACH) | Mixed)

For Google Sheets:

• Create a new sheet or append to existing
• Include headers matching the output schema

For other integrations:

• Adapt the output schema to the target system's format

Example Usage

User: "Build me a vendor database with our top vendors"

Workflow:

1. Call load_vendors → loads vendor table
2. Call load_users → loads users table
3. Call load_departments → loads departments table
4. Call load_spend_export with type="bills" → loads bills table
5. Call load_purchase_orders → loads purchase orders table
6. Execute join query to create enriched dataset with contract info
7. Ask user: "I've compiled data on [X] vendors. Would you like me to display this as a table, or export to a connected system like Notion?"
8. Output based on user preference

User: "Show me vendors with contracts expiring in the next 90 days"

Workflow:

1. Load all data sources (Steps 1-5)
2. Execute filtered query:

SELECT vendor_name, vendor_owner_name, owner_department, 
       total_spend_l365, contract_end_date,
       julianday(contract_end_date) - julianday('now') AS days_until_expiry
FROM enriched_vendors
WHERE contract_end_date BETWEEN date('now') AND date('now', '+90 days')
ORDER BY contract_end_date ASC;

3. Display results as table

User: "Which high-spend vendors don't have contracts?"

Workflow:

1. Load all data sources (Steps 1-5)
2. Execute filtered query:

SELECT vendor_name, vendor_owner_name, total_spend_l365
FROM enriched_vendors
WHERE contract_status = 'No contract in place'
AND total_spend_l365 > 25000
ORDER BY total_spend_l365 DESC;

3. Display results with recommendation to establish contracts

User: "Show me purchase orders that are almost fully billed"

Workflow:

1. Load purchase orders data
2. Execute query:

SELECT vendor_name, po_number, po_total, po_paid,
       ROUND(po_paid * 100.0 / po_total, 1) AS percent_used,
       contract_end_date
FROM enriched_vendors
WHERE po_paid / po_total > 0.8
ORDER BY percent_used DESC;

3. Display results - these vendors may need renewal attention

Error Handling

MCP Connection Failed

• Verify Ramp MCP is connected in Settings > Extensions
• Check API credentials are valid
• Try reconnecting the integration

No Vendors Returned

• Confirm the Ramp account has vendor data
• Check if filters are too restrictive
• Try loading without filters first

No Purchase Orders Found

• Not all vendors will have purchase orders
• Mark these vendors as "No contract in place"
• This is expected for many card-based or self-serve SaaS vendors

Join Failures

• Query table schemas first to verify column names
• Check for NULL values in join keys
• Use LEFT JOIN to preserve vendors without owners or POs

Export Failures

• Verify target integration is connected
• Check permissions on target database/sheet
• Confirm field mapping is valid for target system