Google Data Feed - Google Product Categories

Hello,

Since Google Product Categories have been implemented into Google Feeds and Product Cart does not currently export products with the option of adding Google Product Categories most of us with a large amount of products have had to manually open up these .txt files and figure out a way to add them.

I have figured out a good process for doing this along with a great excel formula to automatically generate the proper Google category. I hope this helps people who have this problem.

• Download the .txt Google feed file from the back end of your website
• Import that .txt into a new Microsoft Access Program - follow import steps.
• Now Export to a .xlsx or .xls
  
• Here you can sort your fields, delete access's ID field, and add A google_product_category field. 

Note: If you are having problems with duplicate items, make sure your item #'s are in column F, hit alt+F11, copy the following code, Save, then hit file, close and return to excel workbook. Run that macro and your duplicates will be gone.

Sub fewww()
Dim iRow As Integer: iRow = Range("F:F").Find("*", , , , 1, 2).Row
Range("f1:F" & iRow) = Evaluate(Replace("IF(MATCH(F1:F@,F1:F@,0)=ROW(F1:F@),F1:F@,""#DIV/0!"")", "@", iRow))
On Error Resume Next
Range("f1:F" & iRow).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

• For the following formula to work, make sure your product_type field is in field C.
  
• Depending on the amount of categories you have & google product categories your products need to go into, the following formula will need to be tweaked.

Note: our store deals with medical products & categories alike so for these rules I took the title text of categories in the product_type field to use for the rule. Your price field should also be in field H for this formula to work.

• If you copy and paste the following formula into line 2 and your google_product_category field, then drag the formula to your last product, your google_product_category will automatically generate after tweaking the formula to your product_type & google_product_category. Without further delay here is the formula.

=IF(ISNUMBER(SEARCH("*Physical Therapy*",C2)),"Health & Beauty > Health Care > Physical Therapy Equipment",IF(ISNUMBER(SEARCH("*Bathroom*",C2)),"Home & Garden > Bathroom Accessories",IF(ISNUMBER(SEARCH("*Respiratory*",C2)),"Health & Beauty > Health Care > Respiratory Care",IF(ISNUMBER(SEARCH("*Walking Aids*",C2)),"Health & Beauty > Health Care > Mobility & Accessibility > Walking Aids",IF(ISNUMBER(SEARCH("*Wheelchairs*",C2)),"Health & Beauty > Health Care > Mobility & Accessibility > Accessibility Equipment > Wheelchairs",IF(ISNUMBER(SEARCH("*Scales*",C2)),"Health & Beauty > Health Care > Biometric Monitors > Body Weight Scales",IF(ISNUMBER(SEARCH("*Otoscopes*",C2)),"Business & Industrial > Medical > Medical Equipment > Otoscopes & Ophthalmoscopes",IF(ISNUMBER(SEARCH("*Thermometers*",C2)),"Health & Beauty > Health Care > Biometric Monitors > Medical Thermometers",IF(ISNUMBER(SEARCH("*Carts*",C2)),"Business & Industrial > Medical > Medical Furniture > Medical Carts",IF(ISNUMBER(SEARCH("*Cubicle*",C2)),"Furniture > Office Furniture > Workstations & Cubicles",IF(ISNUMBER(SEARCH("*Incontinence*",C2)),"Health & Beauty > Health Care > Incontinence Aids",IF(ISNUMBER(SEARCH("*Pediatric Equipment*",C2)),"Business & Industrial > Medical > Medical Equipment",IF(ISNUMBER(SEARCH("*Patient Room*",C2)),"Business & Industrial > Medical > Medical Supplies",IF(ISNUMBER(SEARCH("*Diagnostics*",C2)),"Business & Industrial > Medical > Medical Supplies",IF(ISNUMBER(SEARCH("*Daily Living*",C2)),"Business & Industrial > Medical > Medical Supplies",IF(ISNUMBER(SEARCH("*MRI Equipment*",C2)),"Business & Industrial > Medical > Medical Supplies",IF(ISNUMBER(SEARCH("*Pill Management*",C2)),"Business & Industrial > Medical > Medical Supplies",IF(ISNUMBER(SEARCH("*Risk Management*",C2)),"Business & Industrial > Medical > Medical Supplies",IF(ISNUMBER(SEARCH("*Curtain Track*",C2)),"Business & Industrial > Medical > Medical Supplies",IF(COUNTIF(H2,">0"),"Health & Beauty > Health Care",""))))))))))))))))))))

• After that code is entered and dropped down, save as a .txt file and upload to Google.
  

Now most excel experts will call it nesting and tell you to use a VLOOKUP formula, but that forces you to create a table inside of the XL file. That table will then show text in your .txt file and create discrepancies within your feed, more errors and warnings that you don't need.

• All of these are IF functions except the very last rule, the same last rule most of you will want to create. Once almost all of your categories have been mapped and there are few products but they have many separate categories, you will want to make the rule saying: If this item has a price greater than 0, (which all should) it goes in google_product_category x. If you make this rule last, then it will only apply to the items with categories that there is no current rule for.

 - I hope this helps!

Edited by PCBean - 14-April-2014 at 4:35pm

Somehow I was logged in as my other profile when I posted that.  Anyone interested in the above solution can contact us.  It's not a big deal to do what I described in the prior post.

No.  This was simply a quick solution for getting the category assignments stuffed into the database.