How to Export Popular Pages from Google Analytics to Sheets (Excel) to WordPress Widget

For statistics of all my websites, I use Google Analytics. It is fast, reliable, free and comprehensive. Recently, on one of my website, I wanted to show the site’s visitors the popular posts and pages. The first option was to use a WordPress plugin. But, honestly speaking, I avoid such plugins.

All those plugins which store their queries into the database for each pageview are really memory hog. This is the main reason I avoid them. I don’t want to compromise on the speed of the site.

The other type of WordPress plugins is that which uses the third-party statistics, like Google analytics. These plugins extract the data from these sites and then show it in the sidebar as a widget or in a post/page. The main drawback of these types of plugins is that they are often not updated regularly or they may sniff the confidential data which don’t want to share otherwise.

To bypass all these hurdles, I finally decided to export the popular pages/posts from Google analytics and use them anywhere on my site.

This whole process looks very tedious and laborious, but in fact, it is very easy and straightforward. After about one month, or whenever I have time, I follow this simple steps, export the data and update my popular posts list.

If you also want to show the popular posts from Google analytics on your WordPress website on or any other site, just repeat these steps. After learning this simple how-to guide, you’ll repent why you never explored the Analytics stats this way.

How to Show Google Analytics Popular Pages in (WordPress) Sidebar as Widget or in any page?

Step 1: Sign into your Google Analytics account. From the left sidebar, select Behavior > Site Content > All Pages.

Pages-Analytics

Step 2: In the next step, select the date range accordingly. If you want to show the popular posts from last 30 days, choose that option.

select-date-range

Step 3: You will see the top 10 pages of your website. If you want to show more that 10 pages, click on “Show Rows” at the bottom of this page and select either 25, 50 or 100. By default, this tool shows only the page URLs. We also need Page titles. For this purpose, click on “Secondary dimension > Behavior”, scroll down and choose “Page title”.

page-titles

Step 4: The first part to show Google analytics popular pages in wordpress site without plugin is almost completed as you are now ready to export this data. By using the “Export” option, either transfer your data into Google Sheets or save locally as Excel file. Because I use Google Docs in Chrome, I will use this option. Google Docs is free and can be used offline too. You can install it from Chrome Store (Extensions) if it is not already available in your Chrome.

export-stats

Step 5: In Google Sheets, or in Microsoft Excel, delete all the rows and columns except the actual 10 Page URLS in Colum A and Page Titles in Colum B.

top-posts-spreadsheet

Step 6: The URLs are relative URLS which may cause problems when copy-pasting on the site. So, we need to add our site’s domain address prior to them. Highlight first row of Colum C and enter this formula in functions bar. Replace “www.mydomain.com” with your domain name address.

=CONCATENATE(“http://www.mydomain.com”, A1)

contanement-formula

Press Enter and drag down the first row of Colum C and you’ll get all the URLs with your domain name address.

Step 7: Now we want to convert these URL into clickable URLs with Link Labels. For this purpose, select the first row of Colum D and enter this formula into functions bar.

=HYPERLINK(C1, B1)

hyperlink-formula

As you did above, drag down the formula to get all the hyperlinks.

Step 8: Now copy only column D contents, open Google Document or Microsoft Work and paste there.

Step 9: Select the Table, go to Table > Merge Cells to remove all the formatting of Spread Sheets’ columns and rows.

merge-cells

Step 10: Now copy only the links and paste them wherever you want to show them.

Isn’t it great? You have successfully shown the Google Analytics Top Pages in your site’s sidebar widget or in any post/page without using WordPress plugin.

Tip: You can repeat these steps when you want to update your popular posts list. If only one article is gaining popularity, you can either include it manually without following this process or you can re-generate your data.

Bonus Tip: When we copy anything from Google’s Documents and paste it in WordPress Editor, we usually see some weird classes like “<span style=”font-weight: 400;”>” with each link or paragraph. To avoid it, if we make the text bold in Google Docs and after pasting in the WordPress editor, remove the boldness, these weird classes don’t show up.

Leave a Reply

Your email address will not be published. Required fields are marked *