Pivot table basics are covered in my previous posts: What is a Pivot Table? How to Use It? and Google Data Studio Pivot Table Usage. Following this, I continue with Google Analytics Pivot Table Usage.
As of July 2023, Universal Analytics (UA) is being replaced by Google Analytics 4 (GA4) property format. After this date, UA properties will no longer be able to process new data. They are expected to become inaccessible by year-end. For details on differences between property formats and other operations, please refer to my article titled Universal Analytics (UA) to Google Analytics 4 (GA4). You may also request technical support at (https://calendly.com/dnomia/15min).
Google Analytics Pivot Table Usage
Google Analytics [posts/category:google-analytics] allows standard reports to be viewed in full report view under Data, Percentage, Performance, Comparison, and Pivot formats. By default, data presented in the Data format is expressed through columns and rows based on the Acquisition, Behavior, and Conversions contexts. In contrast, the standard report in Pivot format generates content under the Explore > Summary section, based on the relevant tab.
In the image above, you can compare data and pivot tables. You can access the relevant report views via the path Audience > Geographic > ***Language_. No segments, dimension changes, or filters have been applied in the reports. Now let’s take one more step and customize our pivot table according to our needs1. For the sake of diversity, let’s proceed through visitors’ age information (Audience > Demographics > Age).
Without any filtering, segmentation, dimension, or metric changes, the view we obtain will look like the one above. Thus, we can begin customizing.
When selecting Country as the secondary dimension, alongside Age as the primary dimension, you will observe that the list of countries appears in the second column, and a new row group is created for each age, within the country context. After selecting Gender, you will also notice that groupings (summaries) appear in the columns section. Now, we can also detail the Metric field with Summary Metrics. I would like to see, for the specified countries, the number of Users and the number of New Users within the content, grouped by Gender.
Pivot Table: Segmentation and Filtering
Even further, I’m segmenting the data that flows into the report into three categories: Organic Traffic, Direct Traffic, and Referral Traffic. This enables me to perform numerous analyses from a single table. Of course, at the moment, a table with general metrics and dimensions has already been created based on the ones we’ve specified.
We can further narrow down the provided data by applying filters. For this, the most basic step we need to take is to enter a query that applies a filter to the search section. For example, if we enter 18-24, the table will only present the metrics and dimensions for the age group of 18-24 years2. Now, let’s create our report using Secondary Dimension instead of country, selecting Source / Medium, and then expand upon the basic filtering we performed earlier by applying it across device segments (mobile, tablet, and desktop).
Source / Device data will be filtered based on traffic originating from Google. Since no specific Device (organic, CPC, etc.) has been selected, Google will serve as the primary source, and all devices within this context will be displayed in the table. I’m also specifying the city-based filtering as Istanbul, Ankara, and İzmir, and finally, to define the age groups, I’m entering the numerical values 18 and 34, representing the 18–24 and 25–34 age ranges. As a result, I am evaluating visitors who acquired traffic through Google (regardless of whether organic or CPC) based on the devices they used, specifically within Istanbul, Ankara, and İzmir. The data shows that the 25–34 age group exhibits significant traffic both in organic and CPC traffic. Naturally, I should also evaluate the targeting performance within the CPC context. Additionally, the city-level traffic distribution follows the pattern: Istanbul > Ankara > İzmir. You may also save these tables as custom reports.
Resultingly
I mentioned the benefits offered by the pivot table in the article titled Pivot Table. In this context, when creating pivot tables through Google Analytics, we are also able to make detailed and insightful analyses. On the other hand, we can include various data sources via Looker Studio to obtain a broader perspective. Naturally, as we expand our data sources and incorporate calculated fields, custom metrics, and dimensions, we become capable of making more detailed and accurate interpretations about our visitors. As I frequently emphasize in the relevant articles, ensuring that the data is valid, systematic, and consistent is the top priority. Afterward, we can display the relevant data in various formats according to our needs. The pivot table, in particular, stands out as one of the most detailed and practical options among these formats, being available in numerous tools.