Convert Qlik load scripts to BigQuery SQL

// App: Customer_Revenue_Segmentation
SET vMinAmount = 1000;

Tiers:
MAPPING LOAD CUST_ID, TIER_CODE
FROM [lib://QVD/customer_master.qvd] (qvd);

Txns:
LOAD
    CUST_ID,
    AMOUNT,
    TOTAL_SPEND,
    ApplyMap('Tiers', CUST_ID, 'Standard') as SEGMENT
FROM [lib://QVD/transactions.qvd] (qvd)
WHERE AMOUNT > $(vMinAmount);

Seg:
LOAD
    if(TOTAL_SPEND > 50000, 'Platinum',
      if(TOTAL_SPEND > 10000, 'Gold', SEGMENT)) as SEGMENT,
    Sum(AMOUNT) as SEGMENT_REVENUE,
    Count(CUST_ID) as CUSTOMER_COUNT
RESIDENT Txns
GROUP BY SEGMENT, TOTAL_SPEND;

STORE Seg INTO [lib://QVD/customer_segments.qvd] (qvd);

-- ApplyMap + RESIDENT → BigQuery SQL
WITH segmented AS (
    SELECT
        t.cust_id,
        CASE
            WHEN t.total_spend > 50000 THEN 'Platinum'
            WHEN t.total_spend > 10000 THEN 'Gold'
            ELSE COALESCE(c.tier_code, 'Standard')
        END AS segment,
        t.amount
    FROM `project.dataset.customer_transactions` t
    LEFT JOIN `project.dataset.customer_master` c
        ON t.cust_id = c.cust_id
    WHERE t.amount > 1000
),
agg AS (
    SELECT
        segment,
        SUM(amount) AS segment_revenue,
        COUNT(*) AS customer_count
    FROM segmented
    GROUP BY segment
)
MERGE `project.dataset.customer_segments` tgt
USING agg src
    ON tgt.segment = src.segment
WHEN MATCHED THEN UPDATE SET
    tgt.segment_revenue = src.segment_revenue,
    tgt.customer_count = src.customer_count
WHEN NOT MATCHED THEN INSERT
    (segment, segment_revenue, customer_count)
    VALUES (src.segment, src.segment_revenue,
            src.customer_count);