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!
|