Crosstab Software: The Power of a Crosstab Query – Explanation and Benefits

Crosstab SoftwareHaving tons of data in your database doesn’t mean much unless you can organize and analyze it in a way that’s easy to set up and understand. Crosstab queries get high points in both categories. They’re easy to set up using most crosstab software, which typically walks you through the process. And they’re easy to understand due to their power to present large amounts of data in a logical and compact arrangement.

Crosstab Query Explained

A crosstab query is a specific type of query that summarizes your data in a concise format that appears similar to a spreadsheet. The greatest benefit of crosstab queries is their ability to showcase a large amount of data in a format that is much easier to review and analyze than viewing the information in database form.

Crosstab queries involve three different fields:

  • Column field
  • Row field
  • Aggregated data field

The column field runs across the top of your query, the row fields run down the left-hand side, and the aggregated data is in the center of the table, plotted against the column and rows. You can have only one column field and one value for your aggregated data, but you can have up to three row fields.

A few examples can give you a better idea of the crosstab query concept.

Straightforward Transaction Data

Crosstab queries can be very useful when evaluating sales, so we’ll start with a straightforward table that represents sales from specific customers. You may have a table that looks something like this:

Customer ID Purchase Amount

Items Purchased

Purchase Date

43 $25

1

03/04/14

45 $50

1

06/07/14

45 $70

1

08/09/14

46 $132

1

08/18/14

45 $22

1

11/11/14

Crosstab Query: By Month or Quarter

One way to apply a crosstab query to the data is to organize your earnings from specific customers by month or quarter. Here you could choose months or quarters as your column field and your customer ID as your row field. You can then use your earnings as the aggregate data to be plotted against the rows and columns.

Earnings by Month

Customer ID

J F M A M J J A S O N D TOTAL

43

$25

$25

45

$50

$70

$22

$142

46

$132

$132

TOTAL

$25

$50

$202

$22

$299

Earnings by Quarter

Customer ID

Q1 2014

Q2 2014

Q3 2014

Q4 2014

TOTAL

43

$25

$25

45

$50

$70

$22

$142

46

$132

$132

TOTAL

$25

$50

$202

$22

$299

Once you set up the tables using crosstab software, you’re able to see at a glance how your sales have fared over the year, as well as when specific customers made their purchases. You immediately notice a steady increase over the year until the third quarter, which hit earnings four times higher than your first quarter’s earnings. Your fourth quarter, however, didn’t continue the increase, and instead brought in less than you earned at your lowest point in the first quarter.

Using a crosstab query to breakdown sales by month or quarter can help you pinpoint holidays, activities, marketing efforts or seasonal trends that lead to an increase in sales. You’re also aware of slower periods that may benefit from a boost in your marketing or other efforts to generate more activity during those time frames.

You can also analyze when certain customers were most prone to making a purchase, adding another layer of insight into your findings.

Crosstab Query: By Products

Another crosstab query option would be to break down your earnings by products and customers, resulting in a table that may appear something like this:

Customer ID

Product A

Product B

Product C

Product D

Product E

TOTAL

43

$25

$25

45

$92

$70

$162

46

$132

$132

TOTAL

$122

$132

$70

$299

Here you can see Product C generated the most revenue, although it was not your most popular product. It may have been your most profitable product, but product A was purchased by more than one customer. Another crosstab query, this one using the number of products as your aggregate value, would specifically show you Product A sold the greatest number of units.

Customer ID

Product A

Product B

Product C

Product D

Product E

TOTAL

43

1

1

45

2

1

3

46

1

1

TOTAL

3

1

5

The second crosstab query indicates Product A is the big seller in terms of numbers, even if it’s not the greatest generator of revenue. It also shows your most profitable and repeat customer bought two units of Product A, another interesting fact that could lead to deeper insights on the customer as well as the product.

You could continue your research with various other crosstab queries that use a different column header, row headers and value to be summarized. Options could include:

  • Number of products sold to different customers across different months or quarters
  • Amount of purchases sold to different locations across different months or quarters

How Crosstab Queries Work

Crosstab software makes it simple to set up a crosstab query, provided you choose a value that can be summarized and plotted against different fields. That means your summary field needs to be a number, such as a dollar amount, number of items, number of pounds or other field with a numeric value that can be calculated across other fields.

While all crosstab software may operate a bit differently, most are going to ask you to define the fields you want to use in your crosstab query. As a reminder, crosstab queries allow one field as your column heading and up to three rows to use as your row headings. The final step is often to pick a field for which the values will be summarized.

Once you’ve selected the fields you want to use and plugged them into your crosstab software, the program does the rest. The end result is a detailed table of data that is easy to read, straightforward to understand and packed with insights you can use to increase and enhance your business.

6-Steps-to-Understand-Survey-Data-whitepaper