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. This 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.