Amazon Ads

info

Phần hướng dẫn tạo source data từ Amazon Ads này sẽ bao gồm hai phần:

1. Hướng dẫn thiết lập Source từ Amazon Ads.
2. Hướng dẫn điền các trường thông tin khi setup Connection với Source là từ Amazon Ads

Phần 1: Hướng dẫn thiết lập Source từ Amazon Ads

note

Sau khi đã thao tác các bước nền là chọn source và vào được trang setup source chi tiết, bạn tiến hành thao tác điền các trường thông tin cần thiết như hướng dẫn dưới đây. Hướng dẫn các bước nền để tạo source trên Elton bạn có thể xem ở phần Getting Started - Mục Thiết lập Source.

Bước 1: Điền các trường thông tin cần thiết

Tại giao diện setup data source của Amazon Ads, bạn thao tác điền các thông tin định danh cho source chuẩn bị tạo:

• Display name: Tên hiển thị của source. Elton khuyến khích bạn đặt tên có mối liên hệ với source mà bạn sắp sửa tạo.
• Description: Mô tả của source
• Region Chọn khu vực mà tài khoản Ads của bạn đang tồn tại.

Lưu ý: Phần ghi chú của source với nội dung cho biết rằng sau khi nhấn Save thì bạn sẽ còn cần một bước nữa là phân quyền (authorize) cho Elton truy cập vào source data đích của bạn. Chi tiết cách phân quyền sẽ được mô tả ở Bước 2.

Sau khi hoàn tất điền các thông tin của source, bạn nhấn lưu => hệ thống sẽ đưa về trang thông tin chi tiết của source bạn vừa tạo.

Bước 2: Trao quyền từ source đã thiết lập

• Tại trang thông tin chi tiết của source bạn vừa tạo => Nhấn chọn nút Authorize để tiền hành trao quyền cho phép Elton truy cập vào dữ liệu của source.

• Đối với trường hợp Source là Amazon thì hệ thống sẽ tự động đưa bạn qua trang đăng nhập của Amazon. Bạn tiến hành đăng nhập với account Ads của mình.

• Tiếp theo, Amazon sẽ hiển thị các dữ liệu cụ thể của page để tiến hành trao quyền cho Elton. Bạn nhấn xác nhận đồng ý bằng việc nhấn "Allow".

• Sau khi thực hiện trao quyền xong, hệ thống sẽ đưa bạn về tab "Data Sources", bạn sẽ thấy source mới tạo nằm ở trên đầu danh sách các source đã có, bạn thao tác nhấn chọn source và có thể xem được thông tin chi tiết của source đó

• Tại chi tiết của source, bạn có thể tao tác edit, delete, disable hoặc re-authorize trang Facebook Page Insight như hình

Phần 2: Hướng dẫn điền các trường thông tin khi setup Connection với Source là từ Amazon Ads

General info

1. Name: Tên của cài đặt connection bạn sắp tạo.

2. Description: Mô tả của cài đặt connection bạn sắp tạo

3. Sync Frequency: Chọn khoản thời gian định kỳ mà bạn muốn dữ liệu của connection sắp tạo được cập nhật. Ví dụ: Nếu bạn chọn option là Every 1 hour, thì cứ mỗi 1 tiếng hệ thống sẽ tự chạy và sync dữ liệu một lần.

Source config

Tại mục "Source Config" của màn hình setup chi tiết connection có source là Amazon Ads, các trường thông tin bạn cần điền sẽ có ý nghĩa như sau:

1. Select Ads profile

• Chọn amazon ads profile mà bạn muốn tải dữ liệu về

2. Select 1 or many type of data

• Sau khi bạn trao quyền datasource của Amazon Ads thành công, hệ thống sẽ nhận diện được các bảng dữ liệu mà page của bạn đang sở hữu để đề xuất trong trường thông tin này.

• Bạn chọn các bảng dữ liệu liên quan để thực hiện tạo kết nối và tải lên.

Phần 3: Giải thích các trạng thái của Connection sau khi tạo xong và những thao tác vận hành của Connection

Sau khi tạo xong, tại trang listing của các Connection và trang chi tiết của Connection (như hình) sẽ có những trạng thái và những thao tác vận hành.

Ý nghĩa của những trường thông tin đó được giải thích ở hai mục sau:

1. Add-on 1: Giải thích các nút thao tác vận hành ở trang chi tiết của từng Connection
2. Add-on 2: Giải thích các trạng thái của Connection sau khi tạo xong s

Add-on: Cách tính Billable Pipeline cho source

• Khi sử dụng Elton, bạn sẽ được thông báo về cách mà hệ thống sẽ tính phí sử dụng dịch vụ. Cách tính hiện tại chủ yếu dựa trên số pipeline bạn tạo.

• Tuỳ từng loại source mà cách tính số lượng pipeline giới hạn sẽ khác nhau. Để biết thêm chi tiết, bạn vui lòng tham khảo cách tính ở link ở đây.

Phụ lục 1: Hướng dẫn thực hành trích xuất (query) dữ liệu từ các bảng dữ liệu đã được truyền từ source Amazon Ads trên Google BigQuery

Sau khi đã thiết lập thành công connection có source đến từ Amazon Ads và destination là Google BigQuery, bạn có thể tiến hành query dữ liệu của account Amazon Ads của bạn trên Google BigQuery.

Thông thường các metrics và schema của các bảng dữ liệu đến từ source Amazon Ads khá tương đồng với nhau và được hệ thống tiêu chuẩn. Vì vậy, Elton cung cấp cho bạn một cậu lệnh Query mẫu đễ thực hiện trích xuất dữ liệu của connection bạn mới tạo trên Google BigQuery như sau:

-- Tạo 1 view sản phẩm lấy tên từ thị trường US, do bản product này mỗi sản phẩm sẽ có 1 dòng theo từng marketplace
-- Tạm gọi là vw_product_info_us
-- Sửa lại tên project và dataset tùy theo việc sử dụng của bạn

SELECT
  asin,
  marketplace_id,
  item_name,
  sku
FROM `eltondata-data-platform.amazon_dwh.amazon_item`
WHERE marketplace_id = 'ATVPDKIKX0DER'

-- Tạo 1 view để chuẩn bị thông tin về sales details vw_sales_details
-- Lưu ý:
-- Nếu đơn vị tiền của đơn hàng là USD và trạng thái Shipped, Giá trị sản phẩm = Giá bán * Số lượng sản phẩm
-- Nếu đơn vị tiền của đơn hàng không phải USD và đơn hàng có trạng thái là Pending, Giá trị sản phẩm = Giá bán của ngày gần nhất của ASIN theo USD * Số lượng
-- Logic này chỉ nên áp dụng khi bạn chỉ bán tại thị trường Mỹ là chủ yếu
-- Nếu bạn bán tại nhiều thị trường khác nhau, bạn nên áp dụng logic khác, ví dụ lấy tỉ giá theo ngày của từng loại tiền tệ, quy ra USD rồi nhân với số luộng

-- Base CTE: explode order_items JSON and extract item-level fields
WITH base AS (
  SELECT
    o.*,
    JSON_VALUE(items_json,'$.order_item_id') AS order_item_id,

    JSON_VALUE(items_json,'$.asin') AS product_asin,
    JSON_VALUE(items_json,'$.title') AS product_name,
    JSON_VALUE(items_json,'$.seller_sku') AS product_sku,
    JSON_VALUE(items_json,'$.promotion_ids') AS promotion_ids,

    COALESCE(CAST(JSON_VALUE(items_json,'$.product_info.NumberOfItems') AS FLOAT64), 0) AS item_quantity,
    COALESCE(CAST(JSON_VALUE(items_json,'$.quantity_shipped') AS FLOAT64), 0) AS quantity_shipped,
    COALESCE(CAST(JSON_VALUE(items_json,'$.quantity_ordered') AS FLOAT64), 0) AS quantity_ordered,

    COALESCE(CAST(JSON_VALUE(items_json,'$.item_tax.CurrencyCode') AS STRING)) AS item_tax_currency_code,
    COALESCE(CAST(JSON_VALUE(items_json,'$.item_tax.Amount') AS FLOAT64), 0) AS item_tax,

    COALESCE(CAST(JSON_VALUE(items_json,'$.item_price.CurrencyCode') AS STRING)) AS item_price_currency_code,
    COALESCE(CAST(JSON_VALUE(items_json,'$.item_price.Amount') AS FLOAT64), 0) AS item_price,

    COALESCE(CAST(JSON_VALUE(items_json,'$.promotion_discount.CurrencyCode') AS STRING)) AS item_discount_currency_code,
    COALESCE(CAST(JSON_VALUE(items_json,'$.promotion_discount.Amount') AS FLOAT64), 0) AS item_discount_amount,

    COALESCE(CAST(JSON_VALUE(items_json,'$.promotion_discount_tax.CurrencyCode') AS STRING)) AS item_discount_tax_currency_code,
    COALESCE(CAST(JSON_VALUE(items_json,'$.promotion_discount_tax.Amount') AS FLOAT64), 0) AS item_discount_tax_amount,
    
    JSON_VALUE(order_total,'$.CurrencyCode') AS order_total_currency_code,
    CAST(JSON_VALUE(order_total,'$.Amount') AS FLOAT64) AS order_total_amount
  FROM `eltondata-data-platform.amazon_dwh.amazon_order` o,
  UNNEST(order_items) AS items_json
),

-- raw_orders CTE: add timestamps and compute base item_unit_price
raw_orders AS (
  SELECT
    o.*,

    DATETIME(purchase_date, "America/Los_Angeles") AS purchase_date_pst,
    DATETIME(last_update_date, "America/Los_Angeles") AS last_update_date_pst,
    DATETIME(earliest_ship_date, "America/Los_Angeles") AS earliest_ship_date_pst,
    DATETIME(latest_ship_date, "America/Los_Angeles") AS latest_ship_date_pst,

    -- Base unit price = raw item_price / ordered quantity
    SAFE_DIVIDE(item_price, o.quantity_ordered) AS item_unit_price

  FROM base o
),

-- latest_shipped_unit_price:
-- For each ASIN, find the most recently shipped order line,
-- and use its item_unit_price as the reference value.
latest_shipped_unit_price AS (
  SELECT
    product_asin,
    item_unit_price AS latest_shipped_item_unit_price
  FROM (
    SELECT
      product_asin,
      item_unit_price,
      last_update_date,
      ROW_NUMBER() OVER (
        PARTITION BY product_asin ORDER BY last_update_date DESC
      ) AS rn
    FROM raw_orders
    WHERE order_status = 'Shipped'
      AND item_unit_price IS NOT NULL
  )
  WHERE rn = 1
),

-- final_orders:
-- Compute the adjusted item price & line totals when:
--   - Currency ≠ USD  OR
--   - Order is Pending
-- Adjusted price = latest shipped unit price × ordered quantity
final_orders AS (
  SELECT
    r.*,
    l.latest_shipped_item_unit_price,

    -- Adjusted item_price (we keep original item_price unchanged)
    CASE
      WHEN r.item_price_currency_code <> 'USD'
           OR r.order_status = 'Pending'
        THEN COALESCE(l.latest_shipped_item_unit_price, r.item_unit_price)
             * r.quantity_ordered
      ELSE r.item_price
    END AS adj_item_price,

    -- Adjusted line total WITHOUT discount
    CASE
      WHEN r.item_price_currency_code <> 'USD'
           OR r.order_status = 'Pending'
        THEN COALESCE(l.latest_shipped_item_unit_price, r.item_unit_price)
             * r.quantity_ordered
      ELSE r.item_price
    END AS adj_item_line_total_no_discount

  FROM raw_orders r
  LEFT JOIN latest_shipped_unit_price l USING (product_asin)
)

SELECT *
FROM final_orders;

-- Tạo 1 view để chứa thông tin sales theo ASIN vw_product_sales_by_date
SELECT
  DATE(purchase_date_pst) AS purchase_date_pst,
  product_asin,
  -- Tên sản phẩm sẽ lấy theo US cho thống nhất với Ads
  COALESCE(product_in_us.item_name, sales.product_name) AS product_name,
  product_sku,
  SUM(item_quantity) AS item_quantity,
  SUM(quantity_shipped) AS quantity_shipped,
  SUM(quantity_ordered) AS quantity_ordered,
  SUM(adj_item_line_total_no_discount) AS item_line_total_no_discount

FROM `eltondata-data-platform.amazon_dwh.vw_sales_details` sales
LEFT JOIN `eltondata-data-platform.amazon_ads_dwh.vw_product_info_us` product_in_us ON product_in_us.asin = sales.product_asin

GROUP BY ALL


-- Amazon Ads có 3 loại ads: Sponsored Brand Campaign, Sponsored Display Campaign, Sponsored Product Campaign
-- Tạo view chung để gộp lại cho dễ xử lý (có thể dùng transformation nếu data lớn)

-- Tạo 1 view dành cho Sponsored Brand Campaign Daily Report, có chứa ASIN trích từ tên campaign
WITH ads_with_product AS (
  SELECT
    *,
    -- SPLIT(campaign_name, ' ')[SAFE_OFFSET(0)] AS product_asin,
    REGEXP_EXTRACT(campaign_name, r'([A-Z0-9]{10})') AS product_asin
  FROM `eltondata-data-platform.amazon_ads_dwh.amazon_ads_sponsored_brand_campaign_daily_report` r
)

SELECT
  ads_with_product.*,
  product.item_name AS product_name
FROM ads_with_product
LEFT JOIN `eltondata-data-platform.amazon_ads_dwh.vw_product_info_us` product ON product.asin = ads_with_product.product_asin

-- Tương tự cho view sponsored dislay
WITH ads_with_product AS (
  SELECT
    *,
    -- SPLIT(campaign_name, ' ')[SAFE_OFFSET(0)] AS product_asin,
    REGEXP_EXTRACT(campaign_name, r'([A-Z0-9]{10})') AS product_asin
  FROM `eltondata-data-platform.amazon_ads_dwh.amazon_ads_sponsored_display_campaign_daily_report` r
)

SELECT
  ads_with_product.*,
  product.item_name AS product_name
FROM ads_with_product
LEFT JOIN `eltondata-data-platform.amazon_ads_dwh.vw_product_info_us` product ON product.asin = ads_with_product.product_asin

-- Tương tự cho view sponsored product

WITH ads_with_product AS (
  SELECT
    *,
    -- SPLIT(campaign_name, ' ')[SAFE_OFFSET(0)] AS product_asin,
    REGEXP_EXTRACT(campaign_name, r'([A-Z0-9]{10})') AS product_asin
  FROM `eltondata-data-platform.amazon_ads_dwh.amazon_ads_sponsored_product_campaign_daily_report` r
)

SELECT
  ads_with_product.*,
  product.item_name AS product_name,
  camp.targeting_type
FROM ads_with_product
LEFT JOIN `eltondata-data-platform.amazon_ads_dwh.vw_product_info_us` product ON product.asin = ads_with_product.product_asin
LEFT JOIN `eltondata-data-platform.amazon_ads_dwh.amazon_ads_sponsored_product_campaign` camp ON camp.campaign_id = ads_with_product.campaign_id

-- Tạo view gộp data của 3 loại ads
SELECT 
    'Sponsored Product' AS ad_type,
    date,
    profile_id,
    campaign_id,
    campaign_name,
    campaign_status,
    campaign_budget_amount,
    campaign_budget_currency_code,
    product_asin,
    product_name,
    impressions,
    clicks,
    cost,
    spend,                                  -- Dedicated spend column for SP
    sales7d AS sales,                       -- 7-day attribution
    purchases7d AS purchases,               -- 7-day attribution
    units_sold_clicks7d AS units_sold       -- 7-day attribution
FROM `eltondata-data-platform.amazon_ads_dwh.vw_amazon_ads_sponsored_product`

UNION ALL

SELECT 
    'Sponsored Brand' AS ad_type,
    date,
    profile_id,
    campaign_id,
    campaign_name,
    campaign_status,
    campaign_budget_amount,
    campaign_budget_currency_code,
    product_asin,
    product_name,
    impressions,
    clicks,
    cost,
    cost AS spend,                          -- Using cost as alias for spend
    sales,                                  -- 14-day attribution (default)
    purchases,                              -- 14-day attribution (default)
    units_sold                              -- 14-day attribution (default)
FROM `eltondata-data-platform.amazon_ads_dwh.vw_amazon_ads_sponsored_brand`

UNION ALL

SELECT 
    'Sponsored Display' AS ad_type,
    date,
    profile_id,
    campaign_id,
    campaign_name,
    campaign_status,
    campaign_budget_amount,
    campaign_budget_currency_code,
    product_asin,
    product_name,
    impressions,
    clicks,
    cost,
    cost AS spend,                          -- Using cost as alias for spend
    sales,                                  -- 14-day attribution (default)
    purchases,                              -- 14-day attribution (default)
    units_sold                              -- 14-day attribution (default)
FROM `eltondata-data-platform.amazon_ads_dwh.vw_amazon_ads_sponsored_display`;

-- Tạo 1 view / bảng để chứa data ads kết hợp sales trong 1 bảng để tính toán
-- Bảng này nên lưu vào datamart của MKT
WITH date_range AS (
  SELECT
    DATE_SUB(DATE(CURRENT_DATETIME("America/Los_Angeles")), INTERVAL 365 DAY) AS start_date,
    DATETIME(CURRENT_DATETIME("America/Los_Angeles")) AS end_date
),

dim_date AS (
  SELECT
    DATE_ADD(start_date, INTERVAL day_offset DAY) AS date
  FROM date_range,
  UNNEST(
    GENERATE_ARRAY(
      0,
      DATE_DIFF(
        end_date,
        start_date,
        DAY
      )
    )
  ) AS day_offset
),

ads_by_date AS (
  SELECT
    date AS ads_date,
    product_asin,
    
    -- SPEND Breakdown
    SUM(CASE WHEN ad_type = 'Sponsored Brand' THEN spend ELSE 0 END) AS ads_spend_sponsored_brand,
    SUM(CASE WHEN ad_type = 'Sponsored Display' THEN spend ELSE 0 END) AS ads_spend_sponsored_display,
    SUM(CASE WHEN ad_type = 'Sponsored Product' THEN spend ELSE 0 END) AS ads_spend_sponsored_product,
    
    -- REVENUE Breakdown (using conditional aggregation / PIVOT)
    SUM(CASE WHEN ad_type = 'Sponsored Brand' THEN sales ELSE 0 END) AS ads_revenue_sponsored_brand,
    SUM(CASE WHEN ad_type = 'Sponsored Display' THEN sales ELSE 0 END) AS ads_revenue_sponsored_display,
    SUM(CASE WHEN ad_type = 'Sponsored Product' THEN sales ELSE 0 END) AS ads_revenue_sponsored_product,

    -- UNIT SOLD Breakdown (using conditional aggregation / PIVOT)
    SUM(CASE WHEN ad_type = 'Sponsored Brand' THEN units_sold ELSE 0 END) AS ads_units_sold_sponsored_brand,
    SUM(CASE WHEN ad_type = 'Sponsored Display' THEN units_sold ELSE 0 END) AS ads_units_sold_sponsored_display,
    SUM(CASE WHEN ad_type = 'Sponsored Product' THEN units_sold ELSE 0 END) AS ads_units_sold_sponsored_product,

    -- ADS METRICS
    SUM(CASE WHEN ad_type = 'Sponsored Brand' THEN clicks ELSE 0 END) AS ads_clicks_sponsored_brand,
    SUM(CASE WHEN ad_type = 'Sponsored Display' THEN clicks ELSE 0 END) AS ads_clicks_sponsored_display,
    SUM(CASE WHEN ad_type = 'Sponsored Product' THEN clicks ELSE 0 END) AS ads_clicks_sponsored_product,

    
    SUM(CASE WHEN ad_type = 'Sponsored Brand' THEN impressions ELSE 0 END) AS ads_impressions_sponsored_brand,
    SUM(CASE WHEN ad_type = 'Sponsored Display' THEN impressions ELSE 0 END) AS ads_impressions_sponsored_display,
    SUM(CASE WHEN ad_type = 'Sponsored Product' THEN impressions ELSE 0 END) AS ads_impressions_sponsored_product
    
  FROM `eltondata-data-platform.eltondata_mkt_datamart.vw_amazon_ads_all_types_by_campaign_daily`
  GROUP BY 1, 2
),

sales_by_date AS (
  SELECT
    purchase_date_pst,
    product_asin,
    product_name,
    product_sku,
    SUM(item_line_total_no_discount) AS total_product_sales,
    SUM(quantity_ordered) AS total_product_quantity_ordered
  FROM `eltondata-data-platform.amazon_dwh.vw_product_sales_by_date`
  GROUP BY 1, 2, 3, 4
)

SELECT
  d.date,
  COALESCE(s.product_asin, a.product_asin) AS product_asin,
  COALESCE(product_in_us.item_name, ads_product_info.item_name, s.product_name) AS product_name,
  s.product_sku,
  
  -- SPEND Output Columns
  COALESCE(a.ads_spend_sponsored_brand, 0) AS ads_spend_sponsored_brand,
  COALESCE(a.ads_spend_sponsored_display, 0) AS ads_spend_sponsored_display,
  COALESCE(a.ads_spend_sponsored_product, 0) AS ads_spend_sponsored_product,
  
  -- REVENUE Output Columns
  COALESCE(a.ads_revenue_sponsored_brand, 0) AS ads_revenue_sponsored_brand,
  COALESCE(a.ads_revenue_sponsored_display, 0) AS ads_revenue_sponsored_display,
  COALESCE(a.ads_revenue_sponsored_product, 0) AS ads_revenue_sponsored_product,

  -- UNIT SOLDS Output Columns
  COALESCE(a.ads_units_sold_sponsored_brand, 0) AS ads_units_sold_sponsored_brand,
  COALESCE(a.ads_units_sold_sponsored_display, 0) AS ads_units_sold_sponsored_display,
  COALESCE(a.ads_units_sold_sponsored_product, 0) AS ads_units_sold_sponsored_product,
  
  -- ADS METRICS
  COALESCE(a.ads_clicks_sponsored_brand, 0) AS ads_clicks_sponsored_brand,
  COALESCE(a.ads_clicks_sponsored_display, 0) AS ads_clicks_sponsored_display,
  COALESCE(a.ads_clicks_sponsored_product, 0) AS ads_clicks_sponsored_product,

  COALESCE(a.ads_impressions_sponsored_brand, 0) AS ads_impressions_sponsored_brand,
  COALESCE(a.ads_impressions_sponsored_display, 0) AS ads_impressions_sponsored_display,
  COALESCE(a.ads_impressions_sponsored_product, 0) AS ads_impressions_sponsored_product,

  -- Product sales
  COALESCE(s.total_product_sales, 0) AS total_product_sales,
  COALESCE(s.total_product_quantity_ordered, 0) AS total_product_quantity_ordered
FROM dim_date d
LEFT JOIN ads_by_date a
  ON d.date = a.ads_date
LEFT JOIN sales_by_date s
  ON d.date = s.purchase_date_pst AND COALESCE(a.product_asin, s.product_asin) = s.product_asin
LEFT JOIN `eltondata-data-platform.amazon_ads_dwh.vw_product_info_us` product_in_us
  ON product_in_us.asin = s.product_asin
LEFT JOIN `eltondata-data-platform.amazon_ads_dwh.vw_product_info_us` ads_product_info
  ON ads_product_info.asin = a.product_asin
ORDER BY d.date;