Skip to content

Customer Totals Documentation

Menu Location: Reports > Customers > Customer Totals

Access Level: Manager / Administrator

Last Updated: 2026-03-01

Overview

The Customer Totals report generates a QuickBooks-compatible CSV export called "Sales by Customer Details" that shows all payment history for all customers within a specified date range. This report pulls from the payment records table (cust_order_payment) and formats the data for easy import into accounting software.

Primary Functions:

  • Export comprehensive payment history by customer
  • Generate QuickBooks-compatible CSV format
  • Filter by date range (default: last 3 weeks)
  • Include box totals, addon totals, and running balances
  • Track credits, debits, invoices, and all payment types

Page Layout

Header Section

  • Info Alert: Explains the report generates QuickBooks-style CSV from payment records table

Filter Form

  • From Date: Starting date for payment history (defaults to 3 weeks ago)
  • To Date: Ending date for payment history (defaults to today)
  • Download CSV Button: Generate and download the report

Main Content Area

No table display - clicking Download CSV immediately generates file and starts download.

Report Data & Columns

The CSV export includes these columns:

Column Description Source
Type Payment type (Invoice, Credit, Debit, Check, etc.) cust_order_payment.type converted to text
Date Date payment was created cust_order_payment.date_created
Num Payment record ID cust_order_payment.id
Memo Payment note/description cust_order_payment.note
Name Customer full name custy.first_name + custy.last_name
Item Order description (box name + order ID) Constructed from order details
Qty Quantity (always 1 for aggregated payments) Fixed value: 1
Box Total Subscription box cost cust_order.box_total
Addons Add-on items cost cust_order.extras_total
Sales Price Individual payment amount cust_order_payment.amount
Amount Individual payment amount (duplicate of Sales Price) cust_order_payment.amount
Balance Running balance for customer Calculated cumulative total

Additional Summary Rows:

  • After each customer's payment list: "Total [Customer Name]" row with line count and balance
  • Subtotals for each customer showing total box costs and addon costs

Payment Type Handling

The report consolidates payment types for QuickBooks compatibility:

Database Type CSV Output Notes
Credit Card (1) Invoice Includes box_total and extras_total
Debit (3) Invoice Includes box_total and extras_total
Credit (2) Credit Amount shown as negative
Check (4) Check Manual payment
Invoice (5) Invoice Standard invoice
Fundraise (6) Fundraise Special payment type

Credit Handling: Credits (type 2) have amounts converted to negative values for proper QuickBooks import.

Common Use Cases

Use Case 1: Monthly Accounting Reconciliation

Goal: Export all payment data for the month to import into QuickBooks

Steps:

  1. Navigate to Reports > Customers > Customer Totals
  2. Set From date to first day of month (e.g., "03/01/2026")
  3. Set To date to last day of month (e.g., "03/31/2026")
  4. Click "Download CSV"
  5. Save file as "customer_totals_march_2026.csv"
  6. Import into QuickBooks using standard CSV import process

Tips:

  • Run this on the first business day of the new month
  • Keep monthly exports organized in accounting folder
  • Verify totals in QuickBooks match expected revenue

Use Case 2: Quarter-End Financial Review

Goal: Generate quarterly payment summary for financial reporting

Steps:

  1. Set From date to first day of quarter (e.g., "01/01/2026" for Q1)
  2. Set To date to last day of quarter (e.g., "03/31/2026")
  3. Click "Download CSV"
  4. Open in Excel for analysis
  5. Create pivot table showing totals by customer
  6. Calculate quarterly revenue, credits, and net payments

Analysis:

  • Sum all positive amounts for total revenue
  • Sum all negative amounts (credits) for total refunds
  • Calculate net revenue (positive - negatives)
  • Identify top paying customers for the quarter

Use Case 3: Customer Payment History Audit

Goal: Review all payment activity for specific date range for auditing purposes

Steps:

  1. Set date range covering audit period
  2. Download CSV
  3. Open in Excel
  4. Filter by customer name to review individual payment patterns
  5. Check for unusual patterns (excessive credits, missed payments, etc.)
  6. Verify all payments have corresponding orders (Item column)

Red Flags to Look For:

  • Customers with many credits (potential quality issues)
  • Missing order IDs in Item column (payment not linked to order)
  • Unusual payment amounts compared to box totals
  • Gaps in payment dates for active subscribers

Use Case 4: Year-End Tax Preparation

Goal: Generate complete annual payment data for tax reporting

Steps:

  1. Set From date to "01/01/2025"
  2. Set To date to "12/31/2025"
  3. Download CSV
  4. Provide to accountant for tax preparation
  5. Keep copy in permanent tax records

Important:

  • File separately for each tax year
  • Include in business records for 7 years
  • Verify total matches revenue reported in other systems

Use Case 5: Customer-Specific Payment Report

Goal: Generate payment history for a single customer

Steps:

  1. Determine date range needed (or use wide range to get all history)
  2. Download CSV
  3. Open in Excel
  4. Filter Name column to specific customer
  5. Review their payment timeline and balance
  6. Export filtered data to separate file if needed for customer

Use Cases:

  • Customer requests payment history
  • Investigating billing disputes
  • Customer service inquiries about charges
  • Preparing for customer account review

Export & Download Options

Export Format:

  • CSV Only: Comma-separated values compatible with Excel and QuickBooks

Filename Format: 

[CompanyName]_SalesByCustomerDetails_MM_DD_YYYY_to_MM_DD_YYYY_KivaLogic.csv

Example: FarmBox_SalesByCustomerDetails_01_01_2026_to_01_31_2026_KivaLogic.csv

Export Process:

  1. Set date range using From/To fields
  2. Click "Download CSV" button
  3. File downloads automatically to your browser's download folder
  4. File is ready to open in Excel or import to QuickBooks

Troubleshooting

Issue: CSV File Won't Open Properly in Excel

Symptoms: Excel shows garbled data or all data in one column

Solutions:

  1. Open Excel first (blank workbook)
  2. Go to Data > From Text/CSV
  3. Select the downloaded CSV file
  4. Use "Comma" as delimiter
  5. Click "Load"

OR:

  1. Right-click CSV file
  2. Choose "Open with > Excel"
  3. If still problems, change file extension to .txt and import as delimited text

Issue: Balance Column Shows Unexpected Values

Symptoms: Running balance doesn't match what you expect

Check:

  1. Remember: Balance is cumulative within customer (resets for each customer)
  2. Credits show as negative and reduce balance
  3. Balance reflects all payment types, not just completed orders
  4. Verify date range includes all relevant payments

This is Normal: Balance is calculated sequentially for each customer's payments in date order.

Issue: Missing Customer Payments

Symptoms: You know customer made payments but they're not in export

Check:

  1. Verify date range includes when payments were created (date_created, not order delivery date)
  2. Payments with amount = 0 are excluded from report
  3. Check if payment was voided or deleted
  4. Verify it exists in cust_order_payment table

If Problem Persists: Payment may not have been properly recorded. Check customer's payment history on their account page.

Issue: Duplicate Entries for Same Customer

This is Normal: Customers appear multiple times if they have payments on different dates. Each payment gets its own row. The "Total" row at the end of each customer's section summarizes all their activity.

  • Customer Info (customer_info.php) - Individual customer account showing payment history
  • Payment Records (payment-records.php) - Detailed payment transaction listing
  • Customer Engagement (customer-engagement.php) - Customer activity tracking

Typical Workflow:

  1. Customer Totals → Export CSV → Excel/QuickBooks for accounting
  2. Customer Info → Credit/Payment History link → View individual customer details

Permissions & Access

Required Access Level: Manager

Access Level Capabilities:

  • Customer Service: Cannot access
  • Manager: Full access to export
  • Administrator: Full access to export
  • Kiva Admin: Full access to export

Best Practices

Export Frequency

  1. Monthly: Export on first business day of month for prior month
  2. Quarterly: Export for quarter-end financial reporting
  3. Annually: Export for tax preparation and year-end accounting
  4. As Needed: For customer inquiries, audits, or disputes

File Organization

  • Create folder structure: Accounting/Customer_Totals/[Year]/[Month]
  • Use consistent naming: customer_totals_YYYY_MM.csv
  • Keep exports for at least 7 years for tax purposes
  • Back up exports to cloud storage

Data Verification

  1. After export, open in Excel and spot-check data
  2. Compare total revenue to dashboard or other reports
  3. Verify date range exported correctly (check first and last dates)
  4. Ensure all expected customers appear
  5. Check that totals match QuickBooks after import

Things to Avoid

  • ❌ Exporting overlapping date ranges (causes double-counting in accounting)
  • ❌ Deleting exports after importing to QuickBooks (keep for records)
  • ❌ Modifying CSV data before importing (keep original, create separate analysis file)
  • ❌ Using excessively long date ranges (slows down export)

Quick Reference Card

Task Action/Location
Export this month's payments Set From to first of month, To to today, Download CSV
Export last month for accounting Set From to 1st of last month, To to last day of last month
Get full year for taxes Set From to 1/1/YYYY, To to 12/31/YYYY
Find specific customer payments Export wide range, open in Excel, filter by customer name
Quick 3-week export (default) Just click "Download CSV" without changing dates
Verify export succeeded Check Downloads folder for [Company]_SalesByCustomerDetails*.csv

FAQs

What's the difference between Box Total, Addons, and Amount?

  • Box Total: Cost of subscription box for that order
  • Addons: Cost of add-on items purchased with that order
  • Amount: Actual payment amount (may not match box + addons for credits/partial payments)

Why do some payments show as "Invoice" type?

Credit card and debit payments are converted to "Invoice" type for QuickBooks compatibility. This is standard accounting practice.

Can I filter by specific customer before exporting?

No - this report exports all customers' data. After downloading, open in Excel and filter by customer name.

How do I import this into QuickBooks?

Use QuickBooks' "Import Sales Receipts" or "Import Invoices" feature, selecting CSV as the file type. Map columns as needed.

Why are some amounts negative?

Credits (refunds/adjustments) show as negative amounts so they properly reduce the customer's balance.

End of Documentation

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