Unable to distinguish keywords by locale

#1

In the table “product_keyword_new” (created by scipio), it would be helpful to add a “locale” column. As it is now, it is great for product searches but not useful for generating relevant SEO meta tags for a particular page. Another possibility is to use the “status_id” column, which seems to be not used.

ofbiz=# \d product_keyword_new
Table "public.product_keyword_new"
Column | Type | Modifiers
-----------------------±-------------------------±----------
product_id | character varying(255) | not null
keyword | character varying(60) | not null
keyword_type_id | character varying(255) | not null
relevancy_weight | numeric(20,0) |
status_id | character varying(255) |
locale ------------------------------ NEW -----------------
last_updated_stamp | timestamp with time zone |
last_updated_tx_stamp | timestamp with time zone |
created_stamp | timestamp with time zone |
created_tx_stamp | timestamp with time zone |

#2

I totally agree in principle for that entity (ProductKeyword) as it is/was. But it would require changes to different update/indexing services and possibly solr to make it work. However at this time I’m unsure in what direction the ProductKeyword entity is headed (in some cases we are simply not using it), so might have to get back to you.

#3

IMHO, solr should always be built from contents of the DB. Solr today, in the future another, better indexing platform. Also, I am currently building my own sitemap.xml by querying the database external to the DB. It would be awkward to have to query multiple DB platforms because data is stored elsewhere.

#4

OK, so I have a problem with keywords… Again with locale. I was focusing on creating keywords in the section for SEO purposes. At first I tried this:

<#assign productKeywords = product.getRelated(“ProductKeyword”, null, null, true) />
<#list productKeywords as productKeyword >
${productKeyword.keyword}
</#list>
… lots of keywords…

But, because I support multiple languages, the above returns ALL KEYWORDS, for ALL LANGUAGES, which doesn’t help me to make meaningful keyword content. Desperately, I ended up doing this:

<#assign prodNameDescr = Static[‘org.ofbiz.product.product.ProductContentWrapper’].getProductContentAsText
(product, ‘DESCRIPTION’, request, “html”) />

<#assign keystr = product.brandName + " " + prodNameDescr + " " + webSiteName />
<#assign keylist = keystr?split(’\s’, “r”) />

In the above case, the text in ${prodNameDescr} does contain words in the correct language, but it is a shame (and a design defect) that the ofbiz schema designers never added a “locale” column to the ProductKeyword table.

Can scipio tell me how I can query either Solr to get targeted keywords by locale (WITHIN FTL), or can Scipio add a locale column to the ProductKeyword table while it is being populated?

Thanks

#5

Just out of curiosity: what exactly is it that you want to use the keywords for?

I’m asking because meta-tags are useless SEO-wise and I am not even sure i can find an example in which a user would desire to see your keywords for a product.

That doesn’t take away from the points you raise, which i will still have to dig into. I partially agree to what you say, but then again: keywords are a special case, as ours are mostly auto-generated and we even considered ditching them as a whole before.

#6

I now get it after reading the google docs. Thanks for the clarification!

https://support.google.com/webmasters/answer/79812?hl=en

#7

Yeah, it is a common misconception that the keyword meta-tag is still being considered. But google stopped doing that more than a decade ago after websites began abusing it (and there was literally no technical need for it anymore either).

Btw, we pushed the cms component last night. Included is a sitemap generator (which you can access through catalog > product store > Your Website Id > SEO). Once generated you can access the sitemaps from this url:

your.domain.com/sitemaps/sitemap_index.xml

It is a complete sitemap of all categories and products (cms content being added to it soon), clustered in groups of 50k entries max. So if you happen to have large quantities of pages, i suggest you add this to your google webmaster tools (https://www.google.com/webmasters/tools/) - that way google will index your pages sooner, which is probably a much larger boost than you can do in terms of on-page-optimization at this point otherwise…