Manually update view count

You can manually set the count on a post by post basis. I don’t have a way of importing the posts automatically from Google Analytics. It’s one thing I’m exploring but more long term.

You can do this manually through this process which needs a mix of mySQL work via phpMyAdmin

1. Get the counts for your posts potentially in an Excel/CSV format for the URLs and their corresponding total hits
2. Get the URLs/permalinks of the posts and the post IDs from your posts table
3. Map the post IDs to the counts – Top 10 table takes three columns mainly : post ID, count, site ID (usually 1)
4. Import this CSV file into this table via phpMyAdmin.

Note that is the theoretical work flow but I haven’t tried that personally myself nor am familiar with how GA exports data.

Thank you Ajay. I had pretty much starting working on that same outline before getting your message. The order was a little different, but I can let you know it did work. For the benefit of your other users, here’s the details. Before you follow these steps, make sure you understand everything before proceeding. It only took me about 20 minutes to complete this. If you make a mistake, it could cost you much more. If you alter the Top 10 table without first exporting it for a backup, you’ll need to restore the entire site from a backup.

You’ll need to be familiar with phpMyAdmin and Excel. If you are not using Excel, you’ll need software that will let you import/export to phpMyAdmin, plus manipulate the tables. The key features will be sorting the table, extracting a string, and most of all a way to merge two tables based on a key value.

Data set #1

Getting the content exported in Google Analytics is pretty straightforward, for anyone familiar with it. You only need GA variables: Page and Pageviews.

Set the Google Analytics date range to what you want. Also apply any other filters you are using. In my case I filter out GA spam. I’m just insane about details like this. It may not be applicable for you, and it may not even matter. If the pageviews are off by a small percent, it’s in the relative noise.

The Google Analytics list needs the slug separated from its path. I used a quick grep tool to create a third column (e.g. replace the last “/” with a tab). It’s also possible to do this in Excel. From there you can filter which pages/posts (or in my case, custom posts) you want. This was straightforward for me, since all of these are in specific directories (reflected by their permalink structure).

The short answer is you only need the list of slugs and pageviews. from GA. This was my Excel table of just those two columns.

Data set #2

The second piece of data needed is the post table from WordPress. Easy enough to export this using phpMyAdmin. Note, you won’t get the required post ID field if you export from the WordPress admin page. Next thing to note, this table is usually called wp_posts but since I’m using security software all tables have a different prefix. Just find the xx_posts table and export that as CSV.

The only portions of the table you need are the ID, and post_name (which is really the slug). But there are other fields in this table that are important for sorting/filtering this list. Set the export type to CSV. Next, set the option to “Remove carriage return/line feed characters within columns.” This will strip them from the content, which you don’t even need but if the CR/LF are including in the export it will mess up the import into Excel.

Once you have the exported table for all posts, use the post_type, post_mime_type, and post_status fields to filter the list. I was able to delete thousands of posts this way.

At this point you have a table in Excel that included the post’s ID and post_name, plus many other columns that can ignored ignored.

Merge

Using Excel’s VLookup() function, use the slug (in tables from #1 and #2) as the index. Then insert the missing column in the other table. You can add the missing ID to table #1, or the Pageviews to table #2. After this, you have a complete data set of the two required columns: ID and Pageviews.

Create Table #3

This is the one you’ll import into WordPress, and replace your current Top 10 table. You only need two columns: ID and Pageviews. Delete all other columns in the Excel table. Add a third column, which will be the blog ID number. You’ll get that in the next step.

Export the Top 10 table

I exported the Top 10 table wp_top_ten (or xx_top_ten for those with software that has changed the prefix). I ignored the table of daily counts. Since I didn’t have any data in that table after installing the plugin, I simply visited a few pages to generate a few page counts. I exported the Top 10 table just to confirm I had the right format, and do a sanity check with real data. You can also confirm the table’s format in phpMyAdmin. It’s postnumber, cntaccess, and blog_id (which is 1 in my case for all posts I was looking for). For your own sanity check, visit at least one page of every type you’re concerned with (blogs, pages, custom posts, etc) just to get this last number.

Another sanity check. Confirm a few of the postnumbers you just exported from the Top 10 list match the IDs and slugs you have in the table #3. You want to double check all your work at this point. You don’t want to import data in to the table and get this wrong, especially if you have been using Top 10 for some time. In my case I am installing it for the first time. Any mistakes can be solved by deleting the entire data. You may not have that option.

If during the final step you get in trouble you can delete all the data from your WordPress site, and then reimport the Top 10 list again. That will restore it, and that’s why you want to export the Top 10 before the final step.

Complete Table #3 and Export

Look at the blog_id column in your Top 10 export. If it’s not all the same number, you’ll need to merge those number with table #3. I suspect they’re all the same. In my case they were all “1”. Going back to table #3 I filled in the third column with a row of 1s.

At this point you have a new table of the Top 10, with the Pageviews from Google. It should be only the three columns, and no header. Export this as a CSV file.

Import into WordPress

Back in phpMyAdmin you’ll select the Top 10 table (same one you exported previously) and import the CSV file you created from Table #3 (just those three columns). Before you do this step, note you’ll be replacing the page counts used by Top 10. In my case, having never used it before, I deleted that table and wasn’t replacing anything. I suspect it’s OK to import over top the existing table, and expect SQL to replace the duplicates you’re adding.

I like to be sure I know what’s happening. I would delete the all entries in the Top 10 table before importing. This is why you saved the Top 10 export in the step above. If there’s any problems, you can restore from this backup.

After you complete the import, you have Top 10 using the page counts you obtained from Google Analytics.

It took me longer to write this up than it did to actually do all the steps above. Mainly I wanted to let you know it can be done, and it worked well. I think if you added this feature into your product, you’d find customers will be very happy! You might even get users to switch from a competing tool to Top 10, because they don’t want to switch and loose this history.

If they have such history, they could export it from phpMyAdmin instead of Google. OK, back to working on my site!

I realize after posting this some may want to keep existing Top 10 history, and add history from another source. If so, you’d have to export the Top 10 table and then merge your changes. Something to think about before doing the steps above.

Thank you for the very detailed instructions. I couldn’t have put this better!

I do hope to develop an import tool some day once I figure out how to deal with the CSV files efficiently particularly for very large blogs.