christmas-bundle-order-counter

name

Christmas Bundle Order Counter

description

Count and report on Christmas bundle orders (XMAS prefix AND Status='Processed' only). Includes day-of-week analysis. Use when user asks about Christmas bundle orders, holiday bundles, XMAS orders, most popular day of week for orders, or wants to know how many Christmas gift boxes have been ordered. DO NOT use for breast cancer awareness bundles (BCA prefix). ONLY count Processed orders, NOT Archived or Open orders.

Christmas Bundle Order Counter Skill

This skill queries the Caspio database to retrieve and count Christmas bundle orders specifically.

What This Skill Does

Fetches quote sessions from the database that meet BOTH criteria:

1. QuoteID starts with "XMAS"
2. Status = "Processed"

Provides:

• Total count of PROCESSED Christmas bundle orders only
• List of processed orders with key details (QuoteID, customer name, quantity, amount)
• Total revenue from processed Christmas bundles
• Total quantity of bundles in processed orders
• Breakdown by sales representative (Processed orders only)
• Top selling products by category (Jackets, Hoodies, Beanies, Gloves) with style numbers and percentages
• Day-of-week analysis showing which days receive the most orders with weekday vs weekend breakdown

When to Use This Skill

Use this skill when the user asks questions like:

• "How many Christmas bundle orders do I have?"
• "Show me my Christmas bundle orders"
• "What's the status of holiday bundles?"
• "How many XMAS orders came in?"
• "Which sales rep has the most Christmas bundle orders?"
• "Show me Christmas bundles by sales rep"
• "What are the top selling products in Christmas bundles?"
• "What jacket/hoodie is selling best?"
• "Which jacket style is most popular?"
• "What day of the week do we get the most Christmas bundle orders?"
• "Which day is most popular for XMAS orders?"
• "Show me Christmas orders by day of week"

Implementation

Use this exact working code pattern to query and process Christmas bundle orders.

Step 1: Fetch Quote Sessions from API

# Save all quote sessions to a file
curl -s "https://caspio-pricing-proxy-ab30a049961a.herokuapp.com/api/quote_sessions" -o /c/Users/erik/Downloads/quotes_full.json

Step 2: Fetch Quote Items from API

# Save all quote items to analyze product selections
curl -s "https://caspio-pricing-proxy-ab30a049961a.herokuapp.com/api/quote_items" -o /c/Users/erik/Downloads/all_items.json

Step 3: Process Data with Python

cd /c/Users/erik/Downloads && python3 << 'PYEOF'
import json
from collections import defaultdict, Counter

# Load quote sessions
with open('quotes_full.json', 'r') as f:
    sessions = json.load(f)

# Load quote items
with open('all_items.json', 'r') as f:
    all_items = json.load(f)

# CRITICAL: Apply TWO filters (BOTH must be true)
processed_xmas = [
    order for order in sessions
    if order.get('QuoteID', '').startswith('XMAS')     # Filter 1: XMAS prefix
    and order.get('Status') == 'Processed'              # Filter 2: Processed status
]

print(f'Total Processed XMAS Orders: {len(processed_xmas)}')
print()

# Calculate totals
total_revenue = sum(float(order.get('TotalAmount', 0)) for order in processed_xmas)
total_quantity = sum(int(order.get('TotalQuantity', 0)) for order in processed_xmas)

print(f'Total Revenue: ${total_revenue:,.2f}')
print(f'Total Bundles: {total_quantity}')
print()

# Group by sales rep
by_rep = defaultdict(lambda: {'count': 0, 'revenue': 0, 'quantity': 0})

for order in processed_xmas:
    # Priority: SalesRepName, then SalesRep, then Unassigned
    rep = order.get() or order.get() or 
    by_rep[rep][] += 1
    by_rep[rep][] += (order.get(, 0))
    by_rep[rep][] += int(order.get(, 0))


()
 rep  sorted(by_rep.keys(), key=lambda r: by_rep[r][], reverse=True):
    stats = by_rep[rep]
    pct = (stats[] / len(processed_xmas) * 100)  processed_xmas  0
    avg = stats[] / stats[]  stats[] > 0  0
    (f)

()
( * 60)
()
( * 60)
()


processed_xmas_ids = (order[]  order  processed_xmas)


xmas_items = [
    item  item  all_items
     item.get()  processed_xmas_ids
]


jackets = []
hoodies = []
beanies = []
gloves = []

 item  xmas_items:
    bundle_config = item.get()
     bundle_config:
        try:
            config = json.loads(bundle_config)

            
            
            
            
            

               config:
                jacket_full = config[]
                jacket_style = jacket_full.split()[0]    jacket_full  jacket_full
                jackets.append(jacket_style)  

               config:
                hoodie_full = config[]
                hoodie_style = hoodie_full.split()[0]    hoodie_full  hoodie_full
                hoodies.append(hoodie_style)  

               config:
                beanie_full = config[]
                beanie_style = beanie_full.split()[0]    beanie_full  beanie_full
                beanies.append(beanie_style)  

               config:
                gloves_full = config[]
                gloves_style = gloves_full.split()[0]    gloves_full  gloves_full
                gloves.append(gloves_style)  

        except Exception as e:
            pass


 jackets:
    jacket_counter = Counter(jackets)
    ()
     style, count  jacket_counter.most_common(10):
        pct = (count / len(jackets) * 100)  jackets  0
        (f)
    ()

 hoodies:
    hoodie_counter = Counter(hoodies)
    ()
     style, count  hoodie_counter.most_common(10):
        pct = (count / len(hoodies) * 100)  hoodies  0
        (f)
    ()

 beanies:
    beanie_counter = Counter(beanies)
    ()
     style, count  beanie_counter.most_common(10):
        pct = (count / len(beanies) * 100)  beanies  0
        (f)
    ()

 gloves:
    gloves_counter = Counter(gloves)
    ()
     style, count  gloves_counter.most_common(10):
        pct = (count / len(gloves) * 100)  gloves  0
        (f)

()
( * 60)
()
( * 60)
()


from datetime import datetime

days_of_week = []

 order  processed_xmas:
    created_at = order.get()
     created_at:
        try:
            
            dt = datetime.strptime(created_at, )

            
            day_num = dt.weekday()
            day_names = [, , , , , , ]
            day_name = day_names[day_num]

            days_of_week.append(day_name)
        except Exception as e:
            pass


day_counter = Counter(days_of_week)

()
()


day_order = [, , , , , , ]
 day  day_order:
    count = day_counter.get(day, 0)
    pct = (count / len(days_of_week) * 100)  days_of_week  0
    bar =  * int(pct / 2)  
    (f)

()
 day_counter:
    most_popular = day_counter.most_common(1)[0]
    (f)

()
()
weekday_count = (day_counter.get(day, 0)  day  [, , , , ])
weekend_count = (day_counter.get(day, 0)  day  [, ])

 days_of_week:
    weekday_pct = (weekday_count / len(days_of_week) * 100)
    weekend_pct = (weekend_count / len(days_of_week) * 100)
    (f)
    (f)
PYEOF

Expected Output

Running this code should return approximately:

Order Statistics:

• 80 Processed XMAS orders
• Total Revenue: ~$23,657.00
• Total Bundles: ~320

Sales Rep Breakdown:

• Nika: ~47 orders (59%)
• Taneisha: ~33 orders (41%)

Top Selling Products:

• Top Jacket: CT104670 (~46 orders, 56.8%)
• Top Hoodie: CTK121 (~61 orders, 75.3%)
• Top Beanie: CT104597 (100% - standard option)
• Top Gloves: CTGD0794 (100% - standard option)

Day of Week Analysis:

• Most Popular Day: Thursday with ~26 orders (32.5%)
• Second Place: Friday with ~16 orders (20.0%)
• Weekdays: ~77 orders (96.2%)
• Weekends: ~3 orders (3.8%)

Usage in Response

When responding to the user:

1. Call the API using the fetch pattern above
2. Present the summary with key statistics:
   • Total number of Christmas bundle orders
   • Total revenue
   • Total quantities
   • Breakdown by status
   • Breakdown by sales representative (count, revenue, quantity per rep)
3. List recent orders (limit to 10 most recent unless user asks for more)
4. Format currency with $ and 2 decimal places
5. Include dates in readable format (e.g., "January 15, 2025")
6. Sort sales rep breakdown by order count (highest first)

Example Response Format

📊 Christmas Bundle Order Summary (PROCESSED ONLY)

Total PROCESSED Orders: 20
Total Revenue: $5,712.00
Total Bundles: 80

Sales Rep Breakdown (Processed Orders Only):
👤 Taneisha: 11 orders (55%) | $3,181.00 revenue | 44 bundles | Avg: $289.18/order
👤 Nika: 9 orders (45%) | $2,531.00 revenue | 36 bundles | Avg: $281.22/order

Recent Processed Orders:
1. XMAS1020-1128 - Kelsie Stroud (Nika) - 4 bundles - $283.00 - Processed
2. XMAS1017-2560 - April M Edwards (Taneisha) - 4 bundles - $238.00 - Processed
[... continue list ...]

Note: This report ONLY includes orders with Status="Processed".
Archived and Open orders are excluded from all totals.

Important Notes

• CRITICAL: TWO-PART FILTER REQUIRED
  1. QuoteID must start with "XMAS" (not BCA, not any other prefix)
  2. Status must equal "Processed" (not "Archived", not "Open")
  • Both conditions must be true - do not count orders that don't meet both criteria
• Exclude Archived orders - Even if they have XMAS prefix, if Status="Archived" they should NOT be counted
• Exclude Open orders - Even if they have XMAS prefix, if Status="Open" they should NOT be counted
• Check QuoteID field for the prefix, not other fields
• Handle missing data gracefully - Some fields may be null or empty strings
• Always show currency with 2 decimals - Format as $X,XXX.XX
• Sort by date - Most recent first when showing lists (use CreatedAt field)
• Sales rep field priority: Use SalesRepName first, fall back to SalesRep, then "Unassigned"
• Use Bash tool with curl to fetch the data from API
• Count manually from the JSON response or use simple grep/awk commands
• Show percentages when displaying sales rep breakdown to show distribution
• Calculate averages: Average order value, average bundles per order