Pivot Tables will allow you to both group and pivot data.
To build a Pivot Table in Google, highlight all the data you wish to pivot including the header columns (i.e. the column/field names). From here, click Data → Pivot Table.
Here's what it looks like when we pivot the Getting Started: Users dataset:
Once clicked, a modal will pop-up asking you where you want to put the pivot table – on a "New sheet" or and "Existing sheet". Where you put the pivot table is really a matter of personal preference, but we typically select "New sheet", and then give that sheet a pivot-related name. For instance, after pivoting the Getting Started: Users dataset to a new sheet we'd name it "Users, Pivot".
Using a Pivot Table to Pivot Data
A pivot table is made up of four elements:
|Item||Description||Example (see screenshot below)|
|Row||The field you want to see as rows||Place each "Country" value in a row|
|Column||The field you want to see as columns||Place each "Acquisition Source" value as a column|
|Value||The field you want to aggregate||Aggregate for the Count of Unique Users|
|Filter||The data you want to exclude||Exclude any User that's deleted their account|
We use the Pivot table editor to specify the layout and what data to exclude. The screenshot below follows the "Example" described in the table above – how many unique users (Value), that haven't deleted their account (Filter), have signed up via each acquisition channel (Column) for each country (Row):
You'll notice there are a selection options inside each element of the Pivot Table Editor:
|Row / Column||Order||How to order values: ascending or descending|
|Row / Column||Sort by||Which field you want to sort by|
|Row / Column
||Show totals||On/off toggle for showing totals|
|Value||Summarize by||How to aggregate values (e.g. sum, average, count, ...)|
|Value||Show as||How to show aggregate output (e.g. default, % of row, ...)|
|Filter||Status||What and how to filter: by condition or values|
Using a Pivot Table to Group Data
Not all four elements are required to build a pivot table. For instance – how many unique users (Value) have signed up in each country (Row):
In the above screenshot we exemplify the use of a Pivot Table without actually pivoting any data (i.e we aren't turning any cell values into new fields).
Instead, we're using a Pivot Table to Group our data! This is why these two processes are together in one lesson.