Skip to content

Order Data Exports Documentation

Menu Location: Reports > Monthly Spreadsheet Export

Access Level: Manager / Administrator

Last Updated: 2026-03-01

Overview

The Order Data Exports page generates monthly Excel spreadsheets containing comprehensive order, sales, and product data. It provides four detailed worksheets for financial reporting, inventory planning, and business analysis.

Primary Functions:

  • Generate multi-sheet Excel (XLSX) files for any month
  • Export sales by sales order (individual order details)
  • Export sales by customer (customer spending totals)
  • Export sales by item summary (add-on revenue by product)
  • Export menu contents by item (subscription box product quantities)
  • Download files organized by month and year

Page Layout

Year Sections

  • Multiple Year Headers: One section per year from current back to earliest data
  • Year Description: Text explaining the year's reports
  • Monthly Tiles: Grid of clickable month tiles (Jan through Dec or partial)
  • Month Labels: Month abbreviation and year
  • Excel Icon: Visual indicator of downloadable file
  • Reverse Chronological: Current year at top, oldest at bottom
  • Month Progression: Within each year, newest month first (right to left)
  • Horizontal Rules: Separate each year section

Generated Worksheets

Sheet 1: Sales By Salesorder

Purpose: Order-by-order revenue detail

Columns:

  • Order# (order ID)
  • Sum - Amount($) (payment_total)
  • Date (deliver_on in m-d-Y format)

Data Source:

  • Query: cust_order table
  • Filter: deliver_on within month, status = 4 (delivered)
  • Sort: Order by ID

Use Cases:

  • Daily revenue tracking
  • Order volume analysis
  • Date-specific revenue verification
  • Accounting reconciliation

Sample Data: 

Order#  | Sum - Amount($) | Date
12345   | $125.50        | 03-01-2026
12346   | $89.75         | 03-01-2026
12347   | $156.00        | 03-02-2026

Sheet 2: Sales By Customer

Purpose: Customer spending totals for month

Columns:

  • Customer Name (first_name + last_name)
  • Total Amount Spent

Data Source:

  • Aggregated from Sales By Salesorder
  • Grouped by customer name
  • Sorted alphabetically by name

Calculation:

  • Sum of all order amounts per customer
  • Includes only delivered orders (status 4)

Use Cases:

  • Top customer identification
  • Customer value analysis
  • Loyalty program qualification
  • VIP tier assignments

Sample Data: 

Customer Name      | Total Spent
Brown, Alice       | $450.25
Johnson, Robert    | $125.50
Smith, Jennifer    | $895.00

Sheet 3: Sales by Item Summary

Purpose: Add-on product revenue and quantity

Columns:

  • Item (product name)
  • Item Desc. (product description - same as name)
  • Qt. Sold (quantity sold)
  • Total Revenue (formatted as $X.XX)

Data Source:

  • Query: cust_order_contents joined with menu
  • Filter: cost > 0 (excludes free subscription items), delivered orders
  • Grouped by classify_id (product ID)
  • Sorted alphabetically by product name

Calculation:

  • Sum of menu_qty for quantity
  • Sum of cost field for revenue
  • Only add-on items, not subscription box contents

Use Cases:

  • Add-on revenue analysis
  • Inventory planning for popular add-ons
  • Product performance comparison
  • Profit margin calculation by item

Sample Data: 

Item              | Item Desc.        | Qt. Sold | Total Revenue
Organic Apples    | Organic Apples    | 145      | $362.50
Free-Range Eggs   | Free-Range Eggs   | 89       | $445.00
Local Honey       | Local Honey       | 67       | $1,005.00

Sheet 4: Menu Contents by Item Summary

Purpose: Subscription box product quantities (no cost)

Columns:

  • Product (item name)
  • Qty (total quantity delivered)

Data Source:

  • Query: cust_order_contents joined with menu
  • Filter: cost = 0 (subscription box items only), delivered orders
  • Grouped by classify_id
  • Sorted alphabetically by product name

Calculation:

  • Sum of menu_qty across all boxes
  • Multiplies by number of boxes if multiple quantities per box
  • Accounts for case_quantity where applicable

Additional Row:

  • "Subscription Box Revenue" total at bottom
  • Sum of box_total from all delivered orders

Use Cases:

  • Inventory planning for box contents
  • Supplier ordering quantities
  • Product usage forecasting
  • Box content popularity analysis

Sample Data: 

Product                | Qty
Carrots, Organic       | 1,250
Lettuce, Mixed Greens  | 890
Tomatoes, Heirloom     | 675

Subscription Box Revenue | $15,450.00

Data Date Ranges

Month Boundaries

Start of Month:

  • First day at 12:00:00 AM (midnight)
  • Calculated: strtotime("Y-m-01", strtotime("$year-$month-01"))

End of Month:

  • Last day at 11:59:59 PM
  • Calculated: strtotime(date("Y-m-t g:i:sa", strtotime("$year-$month-01 11:59:59pm")))

Inclusive:

  • Both start and end boundaries included
  • Filters by deliver_on field
  • Based on when order was delivered, not when placed

Example: March 2026 export includes:

  • Start: March 1, 2026 12:00:00 AM
  • End: March 31, 2026 11:59:59 PM
  • Any deliver_on timestamp within this range

File Naming Convention

Format: 

Sales_[MonthAbbr]_[CompanyNameNoSpaces]_[Timestamp].xlsx

Components:

  • Sales_: Fixed prefix
  • MonthAbbr: Three-letter month (Jan, Feb, Mar, etc.)
  • CompanyName: From COMPANY constant with spaces removed
  • Timestamp: Unix timestamp when generated

Example: 

Sales_Mar_FarmBoxOrganics_1709251200.xlsx

Benefits:

  • Sortable by name (month visible)
  • Company identification for multi-company systems
  • Unique timestamp prevents overwrites
  • Extension indicates Excel format

Common Use Cases

Use Case 1: Monthly Financial Reporting

Goal: Generate end-of-month revenue report for accounting

Steps:

  1. Wait until all month's orders delivered (typically 2-3 days after month end)
  2. Navigate to Reports > Monthly Spreadsheet Export
  3. Find current or target year section
  4. Click tile for desired month
  5. File downloads automatically
  6. Open in Excel
  7. Review "Sales By Salesorder" sheet for total revenue
  8. Verify against accounting system
  9. Save file in accounting records

Example: March 2026 closes. On April 3, download March export. Total shows $45,230.50 across 420 orders. Matches accounting software revenue.

Tips:

  • Wait a few days after month end for late deliveries
  • Cross-reference with Payment Transactions report
  • Save in consistent folder structure
  • Include in monthly close procedures

Use Case 2: Customer Segmentation Analysis

Goal: Identify high-value customers for retention programs

Steps:

  1. Download last 3 months exports
  2. Open each file's "Sales By Customer" sheet
  3. Copy data into master spreadsheet
  4. Sum totals per customer across months
  5. Sort by total descending
  6. Identify top 10% of spenders
  7. Export list for marketing campaign

Example: Q1 analysis shows 45 customers spent $500+ each. Total from these 45 is 62% of quarterly revenue. Target for VIP program.

Tips:

  • Use pivot tables to aggregate multi-month
  • Look for consistent high spenders vs one-time large orders
  • Cross-reference with customer retention rates
  • Consider frequency + amount, not just amount

Use Case 3: Inventory Planning for Add-Ons

Goal: Forecast inventory needs based on sales trends

Steps:

  1. Download last 6 months exports
  2. Open "Sales by Item Summary" sheet for each
  3. Create trend analysis spreadsheet
  4. Track Qt. Sold for each product monthly
  5. Calculate average monthly sales
  6. Add seasonal factors
  7. Generate next month purchase orders

Example: "Organic Honey" averaged 75 units/month Jan-Jun. July shows 120 units (seasonal spike). Plan for 130 units in August.

Tips:

  • Look for seasonal patterns (summer vs winter items)
  • Consider new product ramp-up periods
  • Account for promotional impacts
  • Build in safety stock for popular items

Use Case 4: Box Content Optimization

Goal: Understand which box contents are most used

Steps:

  1. Download current month export
  2. Review "Menu Contents by Item Summary" sheet
  3. Compare quantities to number of boxes sent
  4. Calculate usage rate per box
  5. Identify over/under utilized items
  6. Adjust box contents or quantities

Example: Sent 500 boxes. Carrots show 1,250 qty = 2.5 per box average. Lettuce shows 400 qty = 0.8 per box. Consider reducing carrot quantity or increasing lettuce.

Tips:

  • Account for different box sizes
  • Consider item pricing when adjusting
  • Survey customer preferences
  • Test changes with small customer subset first

Use Case 5: Year-Over-Year Growth Analysis

Goal: Compare business growth year over year

Steps:

  1. Download same month from current and prior year
  2. Compare "Sales By Salesorder" totals
  3. Compare order counts
  4. Compare average order values
  5. Review "Sales by Item Summary" for product growth
  6. Calculate percentage changes
  7. Document trends for stakeholders

Example: March 2025: $32,450 across 305 orders ($106.39 avg) March 2026: $45,230 across 420 orders ($107.69 avg) Growth: 39.4% revenue, 37.7% orders, 1.2% avg order

Tips:

  • Account for business days in month differences
  • Consider external factors (economy, weather)
  • Segment by product categories
  • Track both growth and profitability

Troubleshooting

Download Doesn't Start

Symptoms: Clicking month tile doesn't trigger download

Check:

  1. Verify browser allows downloads from site
  2. Check if popup blocker is interfering
  3. Look for download prompt in browser
  4. Check Downloads folder for file

Solutions:

  1. Allow popups for this site
  2. Click tile again
  3. Try different browser
  4. Check browser download settings

File Won't Open in Excel

Symptoms: Downloaded file shows error when opening

Check:

  1. Verify file extension is .xlsx
  2. Check file size is >0 bytes
  3. Confirm Excel version supports .xlsx
  4. Look for file corruption

Solutions:

  1. Re-download file
  2. Use LibreOffice or Google Sheets as alternative
  3. Update Excel to recent version
  4. Contact support if file consistently corrupt

Data Seems Incomplete

Symptoms: Order count or totals don't match expectations

Check:

  1. Verify you downloaded correct month/year
  2. Check if all orders for month are status 4 (delivered)
  3. Confirm deliver_on dates are within month
  4. Review if late deliveries occurred after export

Solutions:

  1. Wait until a few days after month end
  2. Re-download if late deliveries completed
  3. Cross-reference with other reports
  4. Query database directly if needed

Wrong Month or Year Downloaded

Symptoms: File contains unexpected date range

Check:

  1. Verify you clicked correct tile
  2. Check filename for month and timestamp
  3. Open file to confirm date column

Solutions:

  1. Download correct month
  2. Delete wrong file to avoid confusion
  3. Check month labels on page carefully
  • Payment Transactions (billing-payments.php) - Detailed payment data by time period
  • Revenue Per Customer (billing-revenue-reports.php) - Customer spending rankings
  • Order Reports (various) - Additional order analysis tools
  • Inventory Management - Use export data for inventory planning

Typical Workflow:

  1. Month end → Order Exports → Download → Accounting reconciliation
  2. Order Exports → Customer sheet → VIP Program → Marketing campaign
  3. Order Exports → Item summary → Inventory planning → Purchase orders

Permissions & Access

Required Access Level: PERMISSION_WEEKLYREPORTS

Access Level Capabilities:

  • Customer Service: Generally no access (financial data)
  • Manager: Download monthly reports
  • Administrator: Full access to all historical months
  • Kiva Admin: All features

Data Sensitivity:

  • Contains financial revenue data
  • Shows customer spending details
  • Includes product performance metrics
  • Should be kept confidential

Best Practices

Regular Exports

  1. Download monthly reports within first week of new month
  2. Archive files in organized folder structure (Year/Month/)
  3. Document any anomalies or special circumstances
  4. Share with accounting by 10th of month
  5. Retain files for minimum 7 years

Data Validation

  1. Cross-reference totals with other reports
  2. Verify order counts match expectations
  3. Check for duplicate or missing orders
  4. Compare to prior month for reasonableness
  5. Investigate significant variances

File Management

  1. Use consistent naming convention
  2. Store in backed-up location
  3. Restrict access to authorized personnel
  4. Don't email files (contains sensitive data)
  5. Use secure file sharing if distribution needed

Things to Avoid

  • ❌ Don't download too early (before all month's deliveries complete)
  • ❌ Don't modify Excel file before archiving original
  • ❌ Don't share files without proper authorization
  • ❌ Don't rely solely on this for financial reconciliation
  • ❌ Don't delete files after use - keep for records

Quick Reference Card

Task Action/Location
Download current month Find this year, click current/last month
Get last quarter Download 3 most recent months
Year-end summary Download all 12 months for year
Compare YoY Download same month from 2 years
Customer spending analysis Open "Sales By Customer" sheet
Add-on performance Open "Sales by Item Summary" sheet
Box contents tracking Open "Menu Contents by Item" sheet
Order detail Open "Sales By Salesorder" sheet
Find specific month Scroll to year, find month tile
Verify month Check filename after download

FAQs

Why are some months missing in older years?

The page only shows months where data exists. If your business started mid-year or had no orders in a month, that tile won't appear.

Can I get weekly exports instead of monthly?

This page only generates monthly reports. For weekly data, use the Payment Transactions report with weekly frequency or export from weekly schedule pages.

What if I need to regenerate a report after late orders were added?

Simply click the month tile again. The system generates the report fresh each time with current database data. The filename timestamp will be different.

Why does "Subscription Box Revenue" only appear on Menu Contents sheet?

This summary row shows total revenue from box subscriptions (not add-ons). It's on the box contents sheet because that sheet focuses on subscription box items.

Can I customize what's included in the export?

This page generates standard reports. For custom exports, contact Kiva Logic support or use the database query tools if you have access.

Change Log

2026-03-01

  • Initial documentation created
  • All sections completed based on monthly-export.php code review
  • Documented four worksheet contents
  • Added date range calculation details
  • Included file naming convention
  • Noted year-based organization structure

End of Documentation

For additional help, contact your system administrator or Kiva Logic support.