Title: Manually update view count
Last modified: August 8, 2017

---

# Manually update view count

 *  Resolved [Jim Reekes](https://wordpress.org/support/users/reekes/)
 * (@reekes)
 * [8 years, 10 months ago](https://wordpress.org/support/topic/manually-update-view-count/)
 * I’m considering adding this plugin to my site, which has hundreds of articles
   that have had thousands of page views. Is there a way to update the counter for
   this plugin by pulling the page count from Google Analytics? Is there a way to
   manually set the count?

Viewing 4 replies - 1 through 4 (of 4 total)

 *  Plugin Author [Ajay](https://wordpress.org/support/users/ajay/)
 * (@ajay)
 * [8 years, 10 months ago](https://wordpress.org/support/topic/manually-update-view-count/#post-9392543)
 * 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.
 *  Thread Starter [Jim Reekes](https://wordpress.org/support/users/reekes/)
 * (@reekes)
 * [8 years, 10 months ago](https://wordpress.org/support/topic/manually-update-view-count/#post-9393258)
 * 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!
 *  Thread Starter [Jim Reekes](https://wordpress.org/support/users/reekes/)
 * (@reekes)
 * [8 years, 10 months ago](https://wordpress.org/support/topic/manually-update-view-count/#post-9396149)
 * 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.
 *  Plugin Author [Ajay](https://wordpress.org/support/users/ajay/)
 * (@ajay)
 * [8 years, 10 months ago](https://wordpress.org/support/topic/manually-update-view-count/#post-9396182)
 * 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.

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘Manually update view count’ is closed to new replies.

 * ![](https://ps.w.org/top-10/assets/icon-256x256.png?rev=2986432)
 * [WebberZone Top 10 — Popular Posts](https://wordpress.org/plugins/top-10/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/top-10/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/top-10/)
 * [Active Topics](https://wordpress.org/support/plugin/top-10/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/top-10/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/top-10/reviews/)

 * 4 replies
 * 2 participants
 * Last reply from: [Ajay](https://wordpress.org/support/users/ajay/)
 * Last activity: [8 years, 10 months ago](https://wordpress.org/support/topic/manually-update-view-count/#post-9396182)
 * Status: resolved