Explore

Context

Old context -

To mark as hero product we use last 30 days order of the products and provide sort rank to the product which can be read from a model and then persisted in taxonomy DB

gold.plp_sorting_logic_logs_v1_backend (metabase model)

This table stores ranking score for each product ids(relative rank of product of catalogs) based on last 30 days orders for those product via metabase model

This the presto table/model which is persist sort rank of a product for catalogs

Fields used

rank (constant which is 1)

score - used as sort rank while persisting in db

audience id - since it was ab experiment thus we use to persist it at audience level but currently we are keeping it at audience 14 only

created_at - use to filter records on daily cron run

CRON:

Query during daily (cron every 2 hours) which persist records in taxonomy table product_sorting

select catalog_id, product_id, audience_id, score gold.plp_sorting_logic_logs_v1_backend where rank = 1 AND created >= timestamp <current timestamp hour - 2 hour>' order by catalog_id ASC

product_sorting (taxonomy DB) (metabase silver.taxonomy__product_sorting)

This is the table where actual sort order persist in taxonomy DB, we take records from above table and persist or update them in this table in taxonomy DB

This is the source of truth from sort ranking

Mapping

Let’s assume above table as GOLD (gold.plp_sorting_logic_logs_v1_backend) and taxonomy table as SORTING(product_sorting) then mapping is like

Table 39

Table 39

SORTING(product_sorting)

GOLD(gold.plp_sorting_logic_logs_v1_backend)

productId

product_id

audienceId

audience_id

rank

score

There are no rows in this table

⁠

How we provide sort rank to product Id in catalogs

We have different priorities to provide sort rank, it started from 0 and then increment by 1

Priorities

valid flag

record in product_sorting

product id

invalid flag

Let’s take an example of few products

Table 40

Table 40

ProductId

Valid

Rank(from product_sorting table)

SORT RANK

1100

1000

NULL

900

NULL

1000

There are no rows in this table

⁠

Logic:

Since product id 1 and 6 are valid and have sort rank/score, we will priorities them based on sort rank and will given then rank 0 (score 1000, pid 6) and rank 1 (score 1100,pid1)

Since product ids 3 and 5 are valid but don’t have sort rank/scorescore, then we will given them priority based on Ids i.e. rank 2 (pid 3) and rank 3(pid 4)

Since product ids 2 and 4 are invalid, then we will prioritise them in the end and based on sort rank/scoresort score i.e. rank 4 (pid 4) and rank 5(pid 2)

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.