During a boot-camp about online businesses back in 2013, I mentioned one important aspect of search optimization marketing:
What does this mean? As an entrepreneur flirting with data science, let me explain: you can’t do proper search optimization unless you look at the facts and figures. Code. Analytics. Traffic. Metrics. Click-through rates. Visitor behavior. Search patterns. Search queries (briefly covered a few years ago, here and here). Which brings me to today’s topic: Pivot Tables. Seems to me people have stopped talking about the importance of pivot tables in search marketing. But they still matter in 2017. Learn why.
Working with Pivot Tables in Excel
If your technical background is not the most advanced one, and terms such as SPSS, AMOS, R, Python sound extraterrestrial to you, start with the basics: Microsoft Excel. There are plenty of courses out there which tap into Pivot Tables and Business Intelligence, but if you were to ask me, I recommend this one from Acuity Training. The course consists of an introduction to power pivot and walks you through pivot interface, formulas, and KPIs, which is enough to build a solid foundation for data analysis.
Data Sources for Pivot Tables
Pivot tables work with any type of data source, such as:
- Google Analytics, Bing, Yandex;
- Customer/visitor surveys;
- SQL databases;
- Reports from Internet marketing tools (i.e. Moz, Majestic, Raven);
- Social media analytics in-house or external tools (Twitter APIs, Facebook, Instagram analytics, Pinterest);
- Ads stats or reports.
Case Study: Working with Data from Your Website
Let’s say I want to work with data from one of my Google Analytics accounts. I’m interested in what has happened in the past 12 months (reference points: July 2016, October 2016, January 2017, April 2017) for two scenarios: All Users and Mobile and Tablet Users. I’ll be comparing Sessions versus Pageviews, on a Weekly basis. Judging by the numbers, you can see this is a website that doesn’t have a lot of traffic. However, I want to make the best out of the data I have. I can simply export the numbers into a .xlsx or .cvs format and work with that data in Excel, SPSS, Amos etc.
(screenshot GA account. Metric: Weekly)
This is the end result. Google combines all the available data into a nicely looking graph, which explains what happened to this web property in the past 12 months, based on selected/filtered information, such as traffic from users who only use tablets and mobile devices, versus users who use any type of device (code name: all users). But what if you’re just presented with an excel datasheet, and you need to come up with graphs and data reports? Here is where knowing how Pivot Tables work can save you:
- They are easy to use and to present findings to you, your partners, your clients (who might not have a solid data science background)
- No formulas are required, so beginners can use it from day 1
- Works with simple drag & drop commands
- You can use Excel add-ins that work well with Pivot Tables (i.e. PivotPal)
Below, you can see a very simple Pivot Table I created from the GA data exported to a .xlsx datasheet. Specifically, in this example, I generated a Pivot Table using the Sum of Sessions and Sum of Pageviews (overall) from All Users and Mobile and Tablet Users combined. There were 4701 total sessions (blue), and 14,068 total page views (orange).
If I want to visualize the total (53 weeks) of traffic that came from All Users versus Mobile and Tablet Users, I can also create a pivot table and pivot chart to serve that purpose. (See image below). Which will show me that more than a third (1353) of traffic came was from mobile and tablet users.
This is valuable insight for my website (or any, for that matter), in a way that I can now prepare my strategy by:
- Creating a mobile-only website; 30% of traffic comes from mobile devices, so it’s worth the investment.
- Targeting mobile and tablet theme designs to meet the needs of potential customers.
- Creating mobile ads and banners, for a more stable marketing/remarketing strategy.
- Displaying mobile ads on my website, as an additional revenue stream.
This is just a simple insight you can gain from a simple analysis. Export everything into an Excel spreadsheet. Run Pivot Tables and Pivot Charts to get insights for your blogging strategy, online business, and more. Take it further and combine data from your website and your social media channels to see where you are at.