Google Data Feed - Google Product Categories |
Post Reply |
Author | |
PCBean
Newbie Joined: 09-April-2014 Location: MN Status: Offline Points: 4 |
Post Options
Thanks(0)
Posted: 09-April-2014 at 3:40pm |
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.
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()
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(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",""))))))))))))))))))))
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.
- I hope this helps!
Edited by PCBean - 14-April-2014 at 4:35pm |
|
greybeard2
Newbie Joined: 26-March-2013 Status: Offline Points: 3 |
Post Options
Thanks(0)
|
One of our clients exported their products to a CSV, updated that file with the shopping categories, and sent it to us. We loaded that file to a temporary database table and built a script that read the entries in that table, and updated the product table. Now it's just a matter of their continuing to simply assign the correct categories to new products subsequent to that update.
|
|
Greg Dinger
Certified ProductCart Developers Joined: 23-September-2006 Location: United States Status: Offline Points: 238 |
Post Options
Thanks(0)
|
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.
|
|
PCBean
Newbie Joined: 09-April-2014 Location: MN Status: Offline Points: 4 |
Post Options
Thanks(0)
|
This is a solution to the duplicate items?
|
|
Greg Dinger
Certified ProductCart Developers Joined: 23-September-2006 Location: United States Status: Offline Points: 238 |
Post Options
Thanks(0)
|
No. This was simply a quick solution for getting the category assignments stuffed into the database.
As a note, that store happens to be 4.5, which is prior to when the Google category support was distributed in 4.7. We have back-written that support into some earlier-version stores because they are heavily altered and cannot justify the upgrade just to get the new Google stuff. We did not encounter an issue with duplicate SKUs as you have reported. However, I have a gal on my team who is an expert with Office macros. If I had such an issue, I'd probably look to Ellen to just write a macro to make the problem go away.
|
|
PCBean
Newbie Joined: 09-April-2014 Location: MN Status: Offline Points: 4 |
Post Options
Thanks(0)
|
Brilliant.
Here is the Excel VBA code (if your item #'s are in column F) to delete duplicates + the entire row those duplicates are in. 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 - Thanks! Edited by PCBean - 14-April-2014 at 10:26am |
|
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |