QComm Masters Playbook

There are a set of steps that need to be followed to update the master data for QComm. The steps are as follows:

1. Download the offtake data from the each platform's respective folder, and upload them to s3.
2. Run the el_qcomm_s3 dag from the Dagster UI. This transfers the data from s3 to redshift.
3. Now, we have to update the masters.
4. After updating the gsheets, we need to ingest the same in the database. For that, materialise the dag named el_qcomm_gsheets.

Query to get the missing item ids

    WITH offtake_base AS (
        SELECT
            DISTINCT
            item_id,
            item_name,
            mrp/qty_sold as mrp
        FROM
            bronze.qcomm_blinkit_offtake
    )
    SELECT
        a.*
    FROM
        offtake_base as a 
    LEFT JOIN
        bronze.qcomm_blinkit_item_pid_mapping as b
    ON a.item_id=b.item_id
    WHERE
        b.product_id is NULL

Note: The above query will give you the missing item ids. You need to add these item ids in the QComm::Blinkit::Item PID Mapping gsheet.

Query to get the missing product ids (own and competitor)

    WITH scrapping_base AS (
        SELECT
            brand,
            product_id,
            title,
            grammage,
            product_type,
            avg(mrp) as mrp,
            avg(price) as price
        FROM
            wholetruth.silver.fact_platform_availability
        WHERE
            gc_crawl_date = CURRENT_DATE - INTERVAL '7 day'
            AND
            product_type IN (SELECT DISTINCT ptype from bronze.qcomm_ptype_bgr_mapping)
            AND
            gc_platform = 'Blinkit'
        GROUP BY 1,2,3,4,5
        )

    SELECT
        a.*
    FROM
        scrapping_base as a
    LEFT JOIN bronze.qcomm_blinkit_pid_master as b 
        ON a.product_id=b.product_id
    WHERE
        b.product_id IS NULL

Note: The above query will give you the missing product ids. You need to add these product ids in 2 sheets

1. QComm::Blinkit::PID Master gsheet, along grammage (gc_grammage)
2. QComm::PID Master gsheet and map its respective BGR (product group)