A Short Guide to PivotTables for Beginner SEMs
Most people getting into SEM dive head first into the Google Ads Dashboard assuming it’s all they would need to be successful, but most fail to realize the importance of also having a deep working knowledge of Microsoft Excel. One key area of Excel that we will focus on today is PivotTables. Below we share with you a short overview with use cases of PivotTables and some of their most commonly used features.
PivotTables – A SEM Marketer’s Best Friend
A PivotTable is a feature in Excel that allows you to reorganize and summarize selected columns and rows of data in a spreadsheet to obtain a new condensed or “filtered” report. A PivotTable doesn’t actually change the spreadsheet data itself, it simply allows you to reorganize the data in a new worksheet, allowing you take a large data set and plug-and-play with which values should be in which rows and columns. PivotTables also allow you to sum, avg, or create custom functions and add these values as new rows or columns.
Below is an example of data that at first glance would appear hard to decipher:
By using a PivotTable, we can choose which values will be presented, and organize the data into a simplified summary (in this case, by channel source):
As you can see, the PivotTable helped reformat and summarize the information so that we could easily assess the ad content performance by channel source. By changing what values we insert into the rows we can manipulate the information to help us make data driven decisions.
PivotTable use cases:
PivotTables allow you to organize large amounts of data from the Google ads UI or lead tracking software like Salesforce and HubSpot, by using PivotTables you can efficiently organize your data to create simple reports. Below are a 3 instances where we, as SEM’s, have found PivotTables to be extremely useful:
- PivotTables can take by-day reporting and quickly turn it into a by week, by month or by quarter report with metrics updating dynamically
- PivotTables can be used to aggregate performance of specific ad headlines across an account to see how each headline performs collectively – or even which headline + description combinations perform best
- Aggregate placement reports so we can see a collective view of how placements are performing across multiple display campaigns
The PivotTable is a fundamental tool that every SEM should be very familiar with.
Calculated Fields – Customize Your PivotTable
A calculated field is a function that you can create for a PivotTable. This allows you to solve for equations with your data within your PivotTable, not just reorganize information. For example, if I have a spreadsheet containing ad performance that I’ve put into a PivotTable to identify which headlines work best, I can use a calculated field to tally click-through rate by headline. In other words, calculated fields allow you to evaluate a value relative to any other value.
Creating a calculated column in your PivotTable is easy: simply go to “analyze’ and select ‘fields, items, & sets’ and then select calculated fields.
Calculated Field use case:
Calculated fields allow you to create formulas like Click through rate, and conversion rate. This is valuable for reporting metrics because it allows you to efficiently calculate functions with large amounts of data. These functions also update dynamically when you make adjustments to your PivotTable, so additional work is needed.
It’s important to note that PivotTables have limited ability to sum, count, or average your data, so any rate data such as CTR, CVR or CPA should be setup as a calculated field. That way data is accurate and dynamically updates as you make changes to the PivotTable.
PivotChart – Make Visuals
A PivotChart uses the data in a PivotTable to make a chart in Excel. PivotCharts can help you and your clients analyze trends in data more effectively by creating a simple visual representation of your data.
To create a PivotChart, first click anywhere within your PivotTable. After that, simply click on the Analyze tab and then the click on the PivotChart button. Finally choose what type of chart you need from the menu on the right. The result should be a visual representation of the data in your PivotTable.
Your output will be a chart similar to those from the standard Insert function in Excel:
PivotChart use case:
PivotCharts are the most efficient way to compare data with many variables over time. You can use this to spot trends in ad performance over time, or even to compare lead generation by source over time.
This by no means is an exhaustive tutorial of all of the PivotTable’s features and uses but it is certainly an excellent starting point for new SEM professionals to expose themselves to a very useful tool in Excel.
If you want to study Microsoft programs further make sure to check out their blog at: https://techcommunity.microsoft.com/t5/custom/page/page-id/Blogs
