Insights: Advanced Order Export Pivot Table Reports

This article walks you through the RoasterTools Sales Pivot Table Template — a Google Sheets

tool that transforms your order export data into flexible, filterable sales reports.

What is the Sales Pivot Table Template?

The Sales Pivot Table Template is a Google Sheets file that connects to your RoasterTools order

export data and gives you a flexible way to analyze sales by product, customer type, reporting

group, and more. Instead of scrolling through a raw export, you can slice and filter your data

to answer questions like: Which product categories are my wholesale accounts ordering most? How

much did a specific customer type spend over a given period.

The video below walks through a google sheets tool to help you create customized pivot table reports from advanced order export CSV files.

• Open and copy this google sheets file to your google drive

Understanding the sheets

The template contains several sheets. Some are for your input; others run automatically in the

background. Here's what each one does:

Insert Data Here— This is where you paste your RoasterTools order export. Do not edit the columns

or add rows outside the data area. This sheet is input-only.

Product Reporting Groups — Optional. Use this to rename specific products to a custom label for reporting purposes. For example, you might group several individual syrup SKUs all under "Syrups." What is entered in Column A and B are examples.

Customer Type Mapping — Optional. Use this to override how specific accounts are categorized. For example, you could relabel a group of accounts as "Cafes" instead of their default wholesale or retail designation. What is entered in Column A and B are examples.

Product Group Mapping — Optional. Use this to simplify how product group combinations appear in your reports. For example, if products come in tagged as "cafe supplies," "cups," "lids," and "paper goods" across different orders, you can map all of them to a single label like "Cafe Supplies." What is entered in Column A and B are examples.

Data Merge — DO NOT EDIT THIS ONE.

This sheet combines your data and all override mappings automatically using

array formulas. Do not edit this sheet. Columns prefixed with "P" (for example, "P Reporting

Group Final") are the ones formatted for use in pivot tables.

Categorized Sales, Product Sales, and Customer Sales Pivot— These are your pivot table views. This is where you'll spend most of your time building and refining reports.

Step 1: Download the Google Sheet

This will be your template for running future reports.

• Open and copy this google sheets file to your google drive

Step 2: Import your data

Each time you want to refresh your report, you'll replace the data on the Data sheet with a

new export from RoasterTools.

1. In RoasterTools, run your Advanced Order Export for the date range you want to analyze and download the file.
2. Open the Sales Pivot Table Template in Google Sheets.
3. Click the Insert Data tab at the bottom of the sheet. This is the first sheet.
4. Click File > Import > Upload >Select the report you just downloaded
5. Chose Replace existing sheet.

Once you replace your data, the Merge sheet automatically recalculates and all pivot tables

update to reflect the new data set.

Step 3: Set up your override mappings

Override mappings let you control how products and customers appear in your reports — without

changing anything in RoasterTools itself. You only need to set these up once; they'll apply

every time you import new data. This is also optional and not needed to run the reports. The data in column A and B are examples on how to use the form, you can delete these once you have set you own.

Remapping products (Product Reporting Groups)

Use this sheet to rename specific line items to a custom reporting label.

1. Click the Product Reporting Groups tab.
2. In the right column, you'll see a reference list of all products currently in your data.
3. Copy the product names you want to remap and paste them into the Full Line Item Name column on the left.
4. In the adjacent column, type the label you want those products to appear under in your reports (for example, "Syrups" or "Holiday Coffees").

Remapping customer types (Customer Type Mapping)

Use this sheet to override how specific accounts are categorized in your reports.

1. Click the Customer Type Mapping tab.
2. Review the list of accounts currently in your data set on the right.
3. Copy the account names you want to relabel into the left column.
4. Enter the new category label in the adjacent column (for example, "Cafes").

Simplifying product groups (Product Group Mapping)

Use this sheet when you want to fold multiple product group combinations into a single,

cleaner label.

1. Click the Product Group Mapping tab.
2. Review the existing product group combinations listed from your data.
3. Copy the combinations you want to simplify into the left column.
4. Enter your simplified label in the adjacent column (for example, map "cups," "lids," and "paper goods" all to "Cafe Supplies").

Step 4: Use the pivot tables

The template includes two pre-built pivot table views: Categorized Sales (sales broken

down by customer type) and Product Sales (sales broken down by product). You can

customize either view by adding, removing, or rearranging rows, columns, and filters.

First: filter out blank rows

When you import new data, the pivot tables may include a blank row in the Order ID Status

filter. Clear this before reading your data.

1. Click the pencil icon under Grand Total in the pivot table.
2. Go to Filters and locate the Order ID Status filter.
3. Click Select All, then uncheck the blank entry.
4. Click OK.

Using "P" columns for pivot fields

When adding fields to your pivot table rows, columns, or values, look for fields prefixed with

P — for example, P Reporting Group Final, P Customer Type Final, or

P Simple Line Item Name. These are the cleaned, final versions of each field that are

ready for reporting. Other columns on the Merge sheet are intermediate calculations and won't

give you clean results if used directly in a pivot.

Customizing your view

You can drag fields in and out of the Rows, Columns, and Filters panels to change what your

pivot table shows.

• To see sales broken down by individual customer name within a category, drag Billing Name under the Rows section below your existing row grouping.
• To filter out a customer type (for example, hide retail and focus on wholesale only), add a filter for P Customer Type Final and uncheck the types you don't need.
• To break out product sizes, add P Simple Line Item Variant as a row below P Simple Line Item Name.

Understanding order subtotals vs. line item totals

Each row in your data represents a line item, not a full order. Many line items share the same

order ID. To prevent order subtotals from being double-counted across line items, the Merge

sheet only shows the order subtotal on the first line item for each order. Use the

P Line Item Total field when you want to sum product-level revenue; use

P Order Subtotal only when you want a count of total order value (and be aware it

won't add up correctly if you sum it directly across multiple line items).

Tips & FAQs

Q: Do I need to redo my override mappings every time I import new data?

A: No. Your mapping sheets carry over automatically. Just replace the data on the Data sheet and your overrides will apply to the new export.

Q: A product isn't showing up under the label I mapped it to. What's wrong?

A: Double-check that the product name in your mapping sheet matches exactly how it appears in the export — including capitalization and any commas. The lookup is exact-match.

Q: Can I see profit margins in the pivot table?

A: Not currently. Profit margin reporting requires a cost-per-line-item value, which isn't included in the standard order export. Revenue, order counts, and discount data are all available.

Q: Can I see how much a customer was discounted over a period?

A: Yes. Add the P Line Item Discount field as a value in your pivot table and filter by customer or billing name to see discount totals.