# Google Sheets pivot tables for marketing data

Pivot tables are one of the easiest and quickest tools to analyze marketing data and to draw some first actionable insights. As such they shouldn’t be missing in the basic skill set of every marketer. This is an introductory session to pivot tables.

#### What you’ll learn in this lesson:

Basics of pivot tables, different aggregation options (SUM, AVERAGE, %, etc.), pivot groupings, calculated fields.

## Examining marketing channel performance with pivot tables

First of all **make a copy of this workbook**. It contains the raw data the below example is based on as well as the solution sheets. Obviously you can just read through this guide. However I highly recommend to make a copy and work along!

For this case we will use the reported data for three paid advertising channels (Google Ads, Facebook, Twitter) from a t-shirt ecommerce store as a basis for the analysis. Obviously this is only an example and you could use pivot tables for analyzing other data such as sales revenue from different regions, customer orders or cost by location.

The example data contains the last three years and includes: *Average Order Value, Impressions, Clicks, CPC, Conversions, Revenue, Advertising Costs *and* Other Costs *segmented by month and advertising channel.

Disclaimer: The values for the advertising channels are completely random and should not be seen as representative for one channel or the other.

In this lesson you’ll do the following things:

- Examine absolute revenue and revenue share by channel and month
- Describe the influence of seasonality and overall trend
- Analyze profitability and order values based advertising channel

## Analyzing absolute revenue and revenue shares

In this first part you’ll learn how to get some first insights on how each advertising channel is performing during the respective months. We’ll start by looking into absolute revenue numbers and then analyze what months and channels drive most of the revenue.

### How much revenue does each channel generate?

Click anywhere in one of the cells containing data in the *Worksheet – Raw Data *sheet. Afterwards click in the menu on *Data* and then *Pivot table…* to prompt the pivot table pop-up. Google Sheets should have correctly guessed the range which contains data. So simply click on create to create the pivot table in a new sheet.

In the new sheet you’ll see the pivot table as well as the table editor on the right site, which you can use to build the table. We’ll start by clicking on *Add* next to *Rows* and adding *Channel* there. Now click on *Values* to add *Revenue*. We already know now what the lifetime revenue of each channel is:

However it would also be interesting to know how much revenue on average each channel does each month. For this simply click on the dropdown *SUM* below *Summarize** by *on the right side and choose *AVERAGE* instead.

Next we want to know what the total revenue is per months. So set *AVERAGE* back to *SUM*, click on *Add* next to *Columns* and add *Month**.*

### What’s the revenue share of each channel compared with advertising costs?

Even though knowing the absolute revenue of each channel is already helpful to get a general idea of the channel performance, looking at shares or percentages is often more insightful.

So remove *Month *by clicking on the* X* next to it. In the *Revenue* tab click on the* Show** as* dropdown next to the *Summarize by* dropdown and chose *% of column* instead of *Default*. Next add another *Value* called* Advertising Costs* and do the same.

You just unlocked your first small marketing insight!

While Facebook ads account for roughly 30% of advertising costs they only account for 18% of revenue. The other two channel do a lot better here and as such there is definitely room for optimization or even a shift of budget. But we’ll look more into this in the later sessions.

## The influence of seasonality and overall time trends

While we looked into channel performance above we’ll now examine the performance of individual years and months more closely. As preparation switch the pivot table back to *Month* as *Columns* and *Revenue* as *Values*.

### Which months are on average the highest grossing?

Right click on any month in the month header and choose *Create pivot date group…* –> *Month**. T*his will group the months of each year (e.g. February ‘19 with February ‘16, February ‘17 and February ‘18).

Next in the *Revenue* tab switch *Summarize by* from *SUM* to *AVERAGE*. This gives us the average revenue for each kind of month. Obviously this would already be enough to answer above questions. However it’s a lot easier, if we sort the months by revenue descending. This can easily be done by choosing *Descending* in the drop down menu below *Month* and *Order *as well as *AVERAGE* of* Revenue* in the* Sort by* dropdown.

This gives us our second little marketing insight: Not surprisingly for a t-shirt retailer, summer months are the strongest revenue wise.

### How is the revenue performance year-over-year?

Ungroup the months and create a pivot group by *Year* instead. Also switch the fields back to default (*Columns*: sorted by *Month*, *Values*: *SUM* of *Revenue*):

Since we want do look at year-over-year growth and 2019 is not done yet, we are going to filter it out. Simply click on *Add* next to *Filter*, choose *Month* and un-select all 2019 months (January, February, March, April).

Write =C3/B3-1 in cell C4 and =D3/C3-1 into cell D4 respectively to calculate the growth rates.

This results into our next insight: revenue growth rates are actually dropping!

## Examining profitability and order values

Last thing we want to do is to look at the profits and average order values of each advertising channel.

### What is the profit per channel for each year?

First unfilter the 2019 months since we also want to have a look at the most recent months. Also click the *X* next to *Revenue* to delete the field. Luckily Google Sheets pivot tables allow us to add calculated fields. And since profit =revenue-cost we can simply click on the *Add* button next to *Values *choose* Calculated field *and add the following formula (each item in the formula equals the column names of the raw data):

=’Revenue’-‘Advertising Costs’-‘Other Costs’

This will give us a profit field and another insight: In addition to the year-over-year drop in revenue, Facebook Ads are dropping in profitability.

### How are the average order values per advertising channel distributed?

First of all delete the calculated profit field and add* Average Order Value* as row as well as *Value* and *Channel* as column. For the later one choose *% of column* as *Show as*.

Above we already grouped by date, however it is actually also possible to choose custom groupings. Just click on one of the* Average Order Value* values and click* Create pivot group rule…** . Set **Interval size* to $10. This leads to dividing *Average Order Value* into 10$ buckets. E.g. this means in our case 23% of all months Twitter Ads had an average order value of $40 – $50.

As such our last insight is, that Facebook as well as Twitter Ads have a significantly lower average order value than Google Ads.

That’s it, you are done! You learned all important pivot table functions and how to use them to gain some first insights from marketing raw data. In the next lesson we will further analyze the data with Google Sheets formulas and functions.

## Trackbacks & Pingbacks

[…] and only select Facebook Ads (If you are not sure what to do here or need a recap check out my first lesson on pivot tables) Select cells A21 to C60 and insert a chart via the top menu (Insert → Chart). Now on the […]

## Comments are closed.