Chapter 7 · 7 levels
PivotTables
Summarise a table in seconds with a PivotTable: rows, columns, values, Sum, Count and Average, a report filter, and shares of the total, then reuse the results in formulas.
StartA manager rarely wants forty order lines. They want the lines rolled up: revenue per category,
orders per channel, the share of spend that went to paid ads. In chapter 3 you built those
summaries with SUMIFS and COUNTIFS, one formula per cell. A PivotTable builds the same
kind of table from a few choices: which field goes down the side, which goes across the top, and
which numbers to add up.
In this chapter every sheet has a PivotTable button. It opens the same choices as Excel’s PivotTable Fields pane: Rows, Columns, Values, how the values are summarised, “Show values as”, and a Filter. You pick the fields, the table appears in the grid, and the checker reads it cell by cell. You will summarise a day of order lines, count orders by channel and status, average pages per session for one device, compare the marketing mix before and during Thanksgiving week, and finish with a boss level that feeds a PivotTable from your own helper column and then reads its results with formulas.
Levels
- Count orders by channel and status
from Robert Kim, Chief Operating Officer
pivot tables, columns field, count, cross tab
- Average pages per visit, mobile only
from Thomas Long, Growth Marketer
pivot tables, report filter, average, sample size
- The marketing mix as % of each column
from Joshua Scott, Data Analyst
pivot tables, show values as, percent of total, mix
- Returns by reason and month, biggest first
from Mason Jackson, Data Analyst
pivot tables, group dates, helper column, sort by value
- Where does each field go?
from Ashley Miller, Data Analyst
pivot tables, field placement, slicers, calculated field
- Boss: a day's sales, from lines to a pivot to the headline
from Priya Shah, Head of Data
pivot tables, helper column, report filter, large, index match