How to Analyze Customer Orders by Market and Order Channel

Learn how to gather and analyze customer order data from various markets using a step-by-step approach. This guide covers accessing reports, manipulating data, and identifying key customers.

March 7, 2025 By Melisa Bishop

In this guide, we'll learn how to extract and analyze order data from a report designed for different markets and order channels. Although the report is not specifically intended for this purpose, it can be adapted to meet your needs. You'll need a BWP login to access the report, after which you can select the desired months and market codes.

Once the data is extracted, we'll cover how to manipulate it in Microsoft Excel by copying it into a new file, adding column headings, and adjusting formats. We'll also create a pivot table to organize and analyze the data, allowing you to identify key customers and their order volumes.

Let's get started

I use this report to gather data on customer orders from various markets by order channel. It's not specifically designed for your intended purpose, but it serves the same function. To use it, click on "Analysis" and then "Prompts."

Step #1: Click on "Analysis" — Click on "Analysis"

Step #2: Click on "Prompts?" — Click on "Prompts?"

You need a BWP login to access it. If you haven't logged in yet, do so. Once logged in, you can select the months for which you want to pull reports and add them here, along with the company code for the market you're examining.

I will leave this set to Denmark, change the view to two months, and click OK.

This will all update.

It's not easy to manipulate in the original file, so I always copy it and paste it into a different file to work on.

Step #9: Open a new file and Ctrl + V to paste the information in — Open a new file and Ctrl + V to paste the information in

Two columns lack headings, so I add them so that I can create a pivot table.

Highlight everything and insert a pivot table.

Step #10: Click on "Insert" — Click on "Insert"

Here, I can include the account numbers and the order count.

Step #13: Drag "Sold - to Party" to Rows — Drag "Sold - to Party" to Rows

I then want to pull in the customer's name from the original file

I will highlight the 1st two columns.

Step #14: Click here and give that a name then enter — Click here and give that a name then enter

Step #15: Go back to the pivot table and click on the first empty cell in column B next to the account number — Go back to the pivot table and click on the first empty cell in column B next to the account number

Step #16: Type "=vlookup(A4,name,2,0) + Enter" — Type "=vlookup(A4,name,2,0) + Enter"

Step #17: The name from the 2nd column will be pulled through. — The name from the 2nd column will be pulled through.

Step #18: Copy the formula down the list of account numbers — Copy the formula down the list of account numbers

That will give you the account numbers and names that have ordered during the period you chose.

Avoid running the report for too many months at once, as it can become quite large depending on the market. In smaller markets, running a six-month report should be fine without any issues.