Migrating SEO urls from Woocommerce to Opencart

One of our customers moved their webshop from Woocommerce with Wordpress to Opencart. One of the issues that had to be solved for one of the shops in a multi-shop environment were the SEO friendly URL's of the to be migrated shop.

Requirement

The url's of the Woocommerce shop were not very compatible with the Opencart webshop, but nevertheless the customer would love to keep the old url's valid, for the Woocommerce shop was already existing for quite some time and the site was very well indexed by search engines.

We assume that the shop url is: https://www.example.com.

How the Woocommerce url looks

https://www.example.com/product/clean-x-glasreiniger-professional-cream-cleanser-m-880-457666

How the Opencart url looks

https://www.example.com/457666-cleanx-glasreiniger

How the non SEO friendly url of Opencart looks

https://www.example.com/index.php?route=product/product&product_id=1747

Using .htaccess

In case of our customer's store, it were less than 1000 products. So this solution requires a different approach if you have lots (1000+) of products. In that case you have to write a modification (OCMOD or VCMOD).

RewriteCond %{HTTP_HOST} ^www.example.com$  [OR]
RewriteCond %{HTTP_HOST} ^your.shop.nl$
RewriteRule ^product/iq-2-positioner-880302 index.php?route=product/product&product_id=111
RewriteRule ^product/9v-battery-box-370689 index.php?route=product/product&product_id=110
RewriteRule ^product/batterijbox-zie-led-uv-lamp-770624-370689 index.php?route=product/product&product_id=110

• 1-2. Condition with an [OR] to make these rules only valid from some of the stores (domains)
• 3-5. Some rewrite samples. The first segment (^product/....) is the Woocommerce part and the second segment is the Opencart (index.php?route....)

How we did it, some tips

Step 1. Woocommerce Sitemaps to Excel

I imported the sitemap.xml from Woocommerce into Excel. We require only 2 columns:

1. url (from the sitemap)
2. model or sku (the article number)

With a small function in VBA we have extracted as much article numbers as possible. Please notice that we required for Opencart the product_id's and not the SKU field values. But the SKU field value will bring us to the product_id.

To distille as many articlecodes (SKU) as possible I used a small VBA macro GetSkuFromString. This does nothing else then starting at the end en distille the first number it is finding. This is no guarantee though that it is an existing SKU in the new shop, or even an valid article number. So you have to check the outcome.

Function GetNumericFromString(CellRef As String)

Dim StringLength As Integer
Dim hasStarted, hasStopped As Boolean
Dim StringValue As String

StringLength = Len(CellRef)
hasStarted = False
hasStopped = False

Result = ""

For i = StringLength To 1 Step -1

    StringValue = Mid(CellRef, i, 1)

    If IsNumeric(StringValue) And hasStopped = False Then
        Result = Result & StringValue
        hasStarted = True
    Else
        If hasStarted = True Then
           hasStopped = True
        End If
    End If

Next i

GetNumericFromString = Strings.StrReverse(Result)

End Function

How to use

Result

Step 2. Upload Excel document to Opencart located database

Upload the Excel with your DBA software to the MySQL database where Opencart is located. You can name the table sitemap_xls. Only the first columns are relevant.

After that is done, run the following SQL script:

select 
    concat('RewriteRule ^product/', sm.keyword, ' ', 'index.php?route=product/product&product_id=', prod.product_id) as url_rewrite
from sitemap_xls sm 
left join master_product prod on (
    prod.sku = sm.model
)
where sm.model in (
    select prod.sku
    from master_product prod 
    where prod.sku = sm.model
)

The outcome of this SQL should be a list with the proper .htaccess rules for these rewrites.

Export these to the clipboard or a text file and copy the contents in your .htaccess file.

Step 3. Update the Opencart frontend .htaccess

# 1.To use URL Alias you need to be running apache with mod_rewrite enabled.
# 2. In your opencart directory rename htaccess.txt to .htaccess.
# For any support issues please visit: http://www.opencart.com

#The DirectoryIndex is index.php
DirectoryIndex index.php index.html

Options +FollowSymlinks

# Prevent Directory listing
Options -Indexes

# Prevent Direct Access to files
<FilesMatch "(?i)((\.tpl|\.twig|\.ini|\.log|(?<!robots)\.txt))">
 Require all denied
</FilesMatch>

RewriteBase /

# SEO URL Settings
RewriteEngine On
# If your opencart installation does not run on the main web folder make sure you folder it does run in ie. / becomes /shop/

# Force HTTPS
RewriteCond %{HTTP:X-Forwarded-Proto} !https
SetEnvIf X-Forwarded-Proto "https" HTTPS=on

RewriteCond %{HTTPS} !=on
RewriteRule ^ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

# Woocommerce -> Opencart rewrites
RewriteCond %{HTTP_HOST} ^www.yourshop.nl$  [OR]
RewriteCond %{HTTP_HOST} ^www.anyothershop.com$
RewriteRule ^product/iq-2-positioner-880302 index.php?route=product/product&product_id=111
RewriteRule ^product/9v-battery-box-370689 index.php?route=product/product&product_id=110
#... all the others ...
RewriteRule ^product/panel-removal-tool-v-shape-m-608v index.php?route=product/product&product_id=1894
RewriteRule ^product/sponsjes-8x2x2-voor-het-verwijderen-van-siliconen-voor-het-primeren-457528 index.php?route=product/product&product_id=1903
RewriteRule ^product/pro-wipes-emmer-457610 index.php?route=product/product&product_id=1954

RewriteRule ^googlebase.xml$ index.php?route=extension/feed/google_base [L]
RewriteRule ^system/download/(.*) index.php?route=error/not_found [L]

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_URI} !.*\.(ico|gif|jpg|jpeg|png|js|css)
RewriteRule ^([^?]*) index.php?_route_=$1 [L,QSA]

### Additional Settings that may need to be enabled for some servers

... rest of the .htaccess content

Tip

On the research how the url's for the .htaccess should look like I used the following web service: htaccess tester.

Conclusion

• This only works if you have not changed the article numbers after the conversion
• It helps when the original Woocommerce URL has the article number included. Otherwise find a way of linking the SEO Url from Woocommerce to the article number in Woocommmerce. This could be done with a little SQL in PHPMyAdmin or any other DBA software.
• Not very useful if you have loads and loads of SEO-Urls and make .htaccess ultra heavy.
• Let the former url's be temporary. Maintain the SEO in Opencart. Request a reindex once your SEO url's in Opencart are well maintained. After a while the old url's will drop in the index and in the end vanish from sight. Then you could decide to remove the links from the .htaccess file.