Category: E-commerce

So you decided to integrate your shop with Shopzilla. If you are reading this in March 2014 then come back in a month to learn is it worth it. If you are decided then read on.

There are some commercial solutions available that will connect your shop to Shopzilla. The cost roughly 100 GBP. It’s not a huge sum, but I would advise you to spend the money on the Shopzilla ads instead to see if it is worth it. Which causes a small problem of uploading the products to Shopzilla.

Shopzilla’s interface seems to be stuck between 1999 and 2005. The only reasonable way they offer is to upload a CSV file for which they provide a documentation with some infuriating errors. And their system does not provide feedback on what you are doing wrong. Are you ready?

In order to create csv file they require I used SQL script and run it through PHPMySQL to save the result in csv format (please note you need to change it to .txt to upload to Shopzilla). Please save it as tab (\t) separated file without quotes around columns and with new line removal.

The script is below. You will have to adjust:
* case section to provide your category mapping
* change yourshop to base url of your shopin product and image url
* adjust id_lang in where statement to change the language of the export. I’m using 1 for English
* quantity in stock is hardcoded to 1000. That will be fine in most cases.
* Leave the shipping cost and bid empty, because Shopzilla suggests setting those parameters by the admin panel, not the feed itself.

The resulting txt file works fine with SEO optimized urls.


SELECT
case
when cat.name like 'Earrings' then '14138'
when cat.name like 'Bracelets' then '14135'
else '14157'
end as Category,
m.name AS Manufacturer,
pl.name AS Title,
replace(pl.description,'\n','
') AS 'Product Description',
concat('http://yourshop/',cat.link_rewrite,'/',cast(p.id_product as char),'-',pl.link_rewrite,'.html') as Link,
concat('http://yourshop/',cast(img.id_image as char),'-thickbox_default/',cast(img.id_image as char),'.jpg') as Image,
concat(m.name,'_',cast(p.id_product as char)) as SKU,
'1000' as Stock,
'New' AS 'Condition',
p.weight AS 'Shipping Weight',
'' as 'Shipping Cost',
'' as Bid,
'9 14' as 'Promotional Description',
'' as 'EAN / UPC',
p.price as Price

FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_manufacturer m ON p.id_manufacturer = m.id_manufacturer LEFT JOIN
ps_image img on p.id_product = img.id_product LEFT JOIN
ps_category_lang cat on p.id_category_default = cat.id_category
where img.position=1
and pl.id_lang=1
and cat.id_lang=1
and p.active=1


Please let me know what your results in using Shopzilla are. Was it worth for you?