​

Configuring Shopee Ads 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 ad performance 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 you want to keep historical performance trends.
  • Full table: every time the extraction happens, we’ll get the current state of the data, which is good for campaign configuration snapshots.

​

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. Campaign Performance Overview

• Identify which campaigns generate the highest ROAS
• Optimize ad spend allocation across campaigns
• Track advertising efficiency trends over time

SELECT
   c.campaign_id,
   ci.ad_name,
   ci.campaign_status,
   ci.ad_type,
   ci.campaign_placement,
   SUM(p.impression) AS total_impressions,
   SUM(p.clicks) AS total_clicks,
   ROUND(AVG(p.ctr), 2) AS avg_ctr,
   ROUND(SUM(p.expense), 2) AS total_spend,
   ROUND(SUM(p.broad_gmv), 2) AS total_revenue,
   ROUND(SUM(p.broad_gmv) / NULLIF(SUM(p.expense), 0), 2) AS roas
FROM
   nekt_raw.shopee_ads_ads_product_campaigns c
   CROSS JOIN UNNEST(ARRAY[c.common_info]) AS t(ci)
   LEFT JOIN nekt_raw.shopee_ads_ads_daily_product_campaign_performance p
      ON c.campaign_id = p.campaign_id
GROUP BY
   c.campaign_id, ci.ad_name, ci.campaign_status, ci.ad_type, ci.campaign_placement
ORDER BY
   total_revenue DESC
LIMIT 20

SELECT
   c.campaign_id,
   c.common_info.ad_name,
   c.common_info.campaign_status,
   c.common_info.ad_type,
   c.common_info.campaign_placement,
   SUM(p.impression) AS total_impressions,
   SUM(p.clicks) AS total_clicks,
   ROUND(AVG(p.ctr), 2) AS avg_ctr,
   ROUND(SUM(p.expense), 2) AS total_spend,
   ROUND(SUM(p.broad_gmv), 2) AS total_revenue,
   ROUND(SAFE_DIVIDE(SUM(p.broad_gmv), SUM(p.expense)), 2) AS roas
FROM
   `nekt_raw.shopee_ads_ads_product_campaigns` c
   LEFT JOIN `nekt_raw.shopee_ads_ads_daily_product_campaign_performance` p
      ON c.campaign_id = p.campaign_id
GROUP BY
   c.campaign_id, c.common_info.ad_name, c.common_info.campaign_status,
   c.common_info.ad_type, c.common_info.campaign_placement
ORDER BY
   total_revenue DESC
LIMIT 20

​

2. Daily Ad Spend and Revenue Trend

• Monitor daily ROAS trends
• Detect sudden drops in ad performance
• Plan budget adjustments based on historical patterns

SELECT
   date,
   SUM(impression) AS total_impressions,
   SUM(clicks) AS total_clicks,
   ROUND(SUM(expense), 2) AS total_spend,
   ROUND(SUM(broad_gmv), 2) AS total_revenue,
   ROUND(SUM(broad_gmv) / NULLIF(SUM(expense), 0), 2) AS daily_roas,
   SUM(broad_order) AS total_orders
FROM
   nekt_raw.shopee_ads_ads_daily_product_campaign_performance
WHERE
   date >= DATE_FORMAT(CURRENT_DATE - INTERVAL '30' DAY, '%Y-%m-%d')
GROUP BY
   date
ORDER BY
   date DESC

SELECT
   date,
   SUM(impression) AS total_impressions,
   SUM(clicks) AS total_clicks,
   ROUND(SUM(expense), 2) AS total_spend,
   ROUND(SUM(broad_gmv), 2) AS total_revenue,
   ROUND(SAFE_DIVIDE(SUM(broad_gmv), SUM(expense)), 2) AS daily_roas,
   SUM(broad_order) AS total_orders
FROM
   `nekt_raw.shopee_ads_ads_daily_product_campaign_performance`
WHERE
   date >= FORMAT_DATE('%Y-%m-%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY
   date
ORDER BY
   date DESC

​

Implementation Notes

​

Skills for agents