​

Configuring Mercado Livre as a Source

​

1. Add account access

• Client ID: The application ID from your Mercado Livre OAuth app.
• Client Secret: The secret key from your Mercado Livre OAuth app.
• Refresh Token: A refresh token obtained through the Mercado Livre OAuth flow, from a seller account with enough permissions to access the data.
• Metrics Granularity: The time granularity for item-level metrics (visits, ad performance, etc.). Available options:
  • day - Daily metrics (most granular, but slower extraction)
  • week - Weekly metrics
  • month - Monthly metrics (default, fastest extraction)
• Start Date: The earliest date from which records will be synced.

{
  "access_token": "APP_USR-...",
  "token_type": "Bearer",
  "expires_in": 21600,
  "scope": "offline_access read",
  "user_id": 123456789,
  "refresh_token": "TG-..."
}

​

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

​

Data Model

​

Use Cases for Data Analysis

​

1. Product Ads Campaign Performance Overview

• Identify top-performing campaigns by ROAS and ACoS
• Understand which campaigns drive the most conversions
• Optimize budget distribution across campaigns

WITH campaign_summary AS (
   SELECT
      c.id AS campaign_id,
      c.name AS campaign_name,
      c.status AS campaign_status,
      c.strategy,
      c.acos_target,
      SUM(m.prints) AS total_impressions,
      SUM(m.clicks) AS total_clicks,
      SUM(m.cost) AS total_cost,
      SUM(m.total_amount) AS total_revenue,
      SUM(m.units_quantity) AS total_units_sold,
      AVG(m.ctr) AS avg_ctr,
      AVG(m.cvr) AS avg_cvr
   FROM
      nekt_raw.mercadolivre_product_ads_campaigns c
      LEFT JOIN nekt_raw.mercadolivre_product_ads_campaign_metrics m
         ON c.id = m.campaign_id
   WHERE
      m.date >= CURRENT_DATE - INTERVAL '30' DAY
   GROUP BY
      c.id, c.name, c.status, c.strategy, c.acos_target
)
SELECT
   campaign_name,
   campaign_status,
   strategy,
   total_impressions,
   total_clicks,
   ROUND(total_cost, 2) AS total_cost,
   ROUND(total_revenue, 2) AS total_revenue,
   total_units_sold,
   ROUND(avg_ctr * 100, 2) AS ctr_percent,
   ROUND(avg_cvr * 100, 2) AS cvr_percent,
   ROUND(CASE WHEN total_revenue > 0 THEN total_cost / total_revenue * 100 ELSE 0 END, 2) AS acos_percent,
   ROUND(acos_target * 100, 2) AS target_acos_percent,
   ROUND(CASE WHEN total_cost > 0 THEN total_revenue / total_cost ELSE 0 END, 2) AS roas
FROM
   campaign_summary
ORDER BY
   total_revenue DESC

WITH campaign_summary AS (
   SELECT
      c.id AS campaign_id,
      c.name AS campaign_name,
      c.status AS campaign_status,
      c.strategy,
      c.acos_target,
      SUM(m.prints) AS total_impressions,
      SUM(m.clicks) AS total_clicks,
      SUM(m.cost) AS total_cost,
      SUM(m.total_amount) AS total_revenue,
      SUM(m.units_quantity) AS total_units_sold,
      AVG(m.ctr) AS avg_ctr,
      AVG(m.cvr) AS avg_cvr
   FROM
      `nekt_raw.mercadolivre_product_ads_campaigns` c
      LEFT JOIN `nekt_raw.mercadolivre_product_ads_campaign_metrics` m
         ON c.id = m.campaign_id
   WHERE
      DATE(m.date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      c.id, c.name, c.status, c.strategy, c.acos_target
)
SELECT
   campaign_name,
   campaign_status,
   strategy,
   total_impressions,
   total_clicks,
   ROUND(total_cost, 2) AS total_cost,
   ROUND(total_revenue, 2) AS total_revenue,
   total_units_sold,
   ROUND(avg_ctr * 100, 2) AS ctr_percent,
   ROUND(avg_cvr * 100, 2) AS cvr_percent,
   ROUND(SAFE_DIVIDE(total_cost, total_revenue) * 100, 2) AS acos_percent,
   ROUND(acos_target * 100, 2) AS target_acos_percent,
   ROUND(SAFE_DIVIDE(total_revenue, total_cost), 2) AS roas
FROM
   campaign_summary
ORDER BY
   total_revenue DESC

​

2. Item Quality Score Analysis

• Identify items with low quality scores that need improvement
• Track quality metrics over time
• Prioritize optimization efforts

SELECT
   i.id AS item_id,
   i.title AS item_title,
   i.price,
   i.available_quantity,
   q.score AS quality_score,
   q.level AS quality_level,
   q.level_wording AS quality_description,
   q.calculated_at AS last_calculated
FROM
   nekt_raw.mercadolivre_items i
   LEFT JOIN nekt_raw.mercadolivre_quality_score_per_item q
      ON i.id = q.entity_id
WHERE
   q.score IS NOT NULL
ORDER BY
   q.score ASC
LIMIT 50

SELECT
   i.id AS item_id,
   i.title AS item_title,
   i.price,
   i.available_quantity,
   q.score AS quality_score,
   q.level AS quality_level,
   q.level_wording AS quality_description,
   q.calculated_at AS last_calculated
FROM
   `nekt_raw.mercadolivre_items` i
   LEFT JOIN `nekt_raw.mercadolivre_quality_score_per_item` q
      ON i.id = q.entity_id
WHERE
   q.score IS NOT NULL
ORDER BY
   q.score ASC
LIMIT 50

​

3. Sales and Revenue Analysis

• Track revenue trends over time
• Understand payment method preferences
• Identify top-selling items

WITH order_items_exploded AS (
   SELECT
      o.id AS order_id,
      o.date_created,
      o.status AS order_status,
      o.total_amount,
      o.paid_amount,
      o.currency_id,
      oi.item.id AS item_id,
      oi.item.title AS item_title,
      oi.quantity,
      oi.unit_price,
      oi.sale_fee
   FROM
      nekt_raw.mercadolivre_orders o
      CROSS JOIN UNNEST(o.order_items) AS oi
   WHERE
      o.date_created >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT
   DATE(date_created) AS order_date,
   COUNT(DISTINCT order_id) AS total_orders,
   SUM(quantity) AS total_units_sold,
   ROUND(SUM(total_amount), 2) AS total_revenue,
   ROUND(SUM(sale_fee), 2) AS total_fees,
   ROUND(AVG(total_amount), 2) AS avg_order_value
FROM
   order_items_exploded
WHERE
   order_status = 'paid'
GROUP BY
   DATE(date_created)
ORDER BY
   order_date DESC

WITH order_items_exploded AS (
   SELECT
      o.id AS order_id,
      o.date_created,
      o.status AS order_status,
      o.total_amount,
      o.paid_amount,
      o.currency_id,
      oi.item.id AS item_id,
      oi.item.title AS item_title,
      oi.quantity,
      oi.unit_price,
      oi.sale_fee
   FROM
      `nekt_raw.mercadolivre_orders` o
      CROSS JOIN UNNEST(o.order_items) AS oi
   WHERE
      DATE(o.date_created) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
   DATE(date_created) AS order_date,
   COUNT(DISTINCT order_id) AS total_orders,
   SUM(quantity) AS total_units_sold,
   ROUND(SUM(total_amount), 2) AS total_revenue,
   ROUND(SUM(sale_fee), 2) AS total_fees,
   ROUND(AVG(total_amount), 2) AS avg_order_value
FROM
   order_items_exploded
WHERE
   order_status = 'paid'
GROUP BY
   DATE(date_created)
ORDER BY
   order_date DESC

​

Implementation Notes

​

Data Quality Considerations

• Advertising Streams: Product Ads and Brand Ads streams are only available if your Mercado Livre account has an advertiser ID. Accounts without advertising access will only have access to Orders, Items, and item-level metrics.
• Quality Score Availability: Quality scores are only available for non-catalog items. Items linked to the Mercado Livre product catalog will be skipped during extraction.
• Metrics Time Window: Campaign metrics are available for the last 90 days only.

​

API Limits & Performance

• Metrics Granularity Impact: The more granular the metrics setting (day vs. month), the longer extractions will take due to increased API calls.
• Visit Data Limit: Visit statistics are limited to the last 2 years of data.
• Pagination: Large seller accounts with many items may experience longer extraction times.

​

Regional Considerations

• Currency: All financial values are in the local currency of the site (BRL for MLB, ARS for MLA, MXN for MLM).
• Timestamps: All timestamps are in UTC.

​

Skills for agents