​

Configuring Shopee as a Source

​

1. Add account access

• API URL: Select the appropriate API endpoint for your region:
  • https://partner.shopeemobile.com - Global (Southeast Asia)
  • https://openplatform.shopee.com.br - Brazil
  • https://openplatform.shopee.cn - China
• Start Date: The earliest date from which records will be synced. If not provided, all available historical data will be extracted.

​

2. Select streams

Tip: The stream can be found more easily by typing its name.

​

3. Configure data streams

• Layer: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: a folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: we suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: you can choose between INCREMENTAL and FULL_TABLE.
  • Incremental: every time the extraction happens, we’ll get only the new data - which is good if, for example, you want to keep every record ever fetched.
  • Full table: every time the extraction happens, we’ll get the current state of the data - which is good if, for example, you don’t want to have deleted data in your catalog.

​

4. Configure data source

• Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource here.
• Determine when to execute an Additional Full Sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

​

5. Check your new source

​

Streams and Fields

​

Use Cases for Data Analysis

​

1. Product Performance Overview

• Identify best-selling and most-viewed products
• Understand conversion from views to sales
• Optimize product listings based on engagement

WITH product_metrics AS (
   SELECT
      p.item_id,
      p.item_name,
      p.item_status,
      p.category_id,
      pa.sale AS total_sales,
      pa.views AS total_views,
      pa.likes AS total_likes,
      pa.rating_star,
      pa.comment_count,
      CASE 
         WHEN pa.views > 0 THEN ROUND(pa.sale * 100.0 / pa.views, 2)
         ELSE 0 
      END AS conversion_rate
   FROM
      nekt_raw.shopee_products p
      LEFT JOIN nekt_raw.shopee_product_analytics pa ON p.item_id = pa.item_id
   WHERE
      p.item_status = 'NORMAL'
)
SELECT
   item_id,
   item_name,
   total_sales,
   total_views,
   total_likes,
   rating_star,
   comment_count,
   conversion_rate
FROM
   product_metrics
ORDER BY
   total_sales DESC
LIMIT 50

WITH product_metrics AS (
   SELECT
      p.item_id,
      p.item_name,
      p.item_status,
      p.category_id,
      pa.sale AS total_sales,
      pa.views AS total_views,
      pa.likes AS total_likes,
      pa.rating_star,
      pa.comment_count,
      CASE 
         WHEN pa.views > 0 THEN ROUND(SAFE_DIVIDE(pa.sale * 100.0, pa.views), 2)
         ELSE 0 
      END AS conversion_rate
   FROM
      `nekt_raw.shopee_products` p
      LEFT JOIN `nekt_raw.shopee_product_analytics` pa ON p.item_id = pa.item_id
   WHERE
      p.item_status = 'NORMAL'
)
SELECT
   item_id,
   item_name,
   total_sales,
   total_views,
   total_likes,
   rating_star,
   comment_count,
   conversion_rate
FROM
   product_metrics
ORDER BY
   total_sales DESC
LIMIT 50

​

2. Order Status Analysis

• Monitor order processing efficiency
• Identify bottlenecks in fulfillment
• Track cancellation rates and reasons

WITH order_stats AS (
   SELECT
      order_status,
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_revenue,
      AVG(total_amount) AS avg_order_value
   FROM
      nekt_raw.shopee_orders
   WHERE
      FROM_UNIXTIME(create_time) >= CURRENT_DATE - INTERVAL '30' DAY
   GROUP BY
      order_status
),
total AS (
   SELECT SUM(order_count) AS total_orders FROM order_stats
)
SELECT
   os.order_status,
   os.order_count,
   ROUND(os.order_count * 100.0 / t.total_orders, 2) AS percentage,
   ROUND(os.total_revenue, 2) AS total_revenue,
   ROUND(os.avg_order_value, 2) AS avg_order_value
FROM
   order_stats os, total t
ORDER BY
   os.order_count DESC

WITH order_stats AS (
   SELECT
      order_status,
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_revenue,
      AVG(total_amount) AS avg_order_value
   FROM
      `nekt_raw.shopee_orders`
   WHERE
      TIMESTAMP_SECONDS(create_time) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
   GROUP BY
      order_status
),
total AS (
   SELECT SUM(order_count) AS total_orders FROM order_stats
)
SELECT
   os.order_status,
   os.order_count,
   ROUND(SAFE_DIVIDE(os.order_count * 100.0, t.total_orders), 2) AS percentage,
   ROUND(os.total_revenue, 2) AS total_revenue,
   ROUND(os.avg_order_value, 2) AS avg_order_value
FROM
   order_stats os, total t
ORDER BY
   os.order_count DESC

​

Implementation Notes

-- AWS Athena
FROM_UNIXTIME(create_time) AS created_at

-- GCP BigQuery
TIMESTAMP_SECONDS(create_time) AS created_at

-- AWS Athena
SELECT o.order_sn, i.item_name, i.model_quantity_purchased
FROM orders o
CROSS JOIN UNNEST(o.item_list) AS t(i)

-- GCP BigQuery
SELECT o.order_sn, i.item_name, i.model_quantity_purchased
FROM orders o, UNNEST(o.item_list) AS i