Documentation Index

Fetch the complete documentation index at: https://docs.nekt.com/llms.txt

Use this file to discover all available pages before exploring further.

​

Configuring Magalu as a Source

​

1. Add account access

• Start Date: (Optional) Lower bound for incremental syncs (updated_at for Orders, issued_at for Deliveries invoices). If omitted, records are synced from the full history available in the API. Other streams are full-table extractions relative to the tap configuration.

​

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. Order Status Overview

• Monitor order fulfillment rates
• Identify issues with specific order statuses
• Track cancellation rates and reasons

WITH order_stats AS (
   SELECT
      status,
      COUNT(*) AS order_count,
      SUM(amounts.total / COALESCE(NULLIF(amounts.normalizer, 0), 1)) AS total_revenue,
      AVG(amounts.total / COALESCE(NULLIF(amounts.normalizer, 0), 1)) AS avg_order_value
   FROM
      nekt_raw.magalu_orders
   WHERE
      DATE(created_at) >= CURRENT_DATE - INTERVAL '30' DAY
   GROUP BY
      status
),
total AS (
   SELECT SUM(order_count) AS total_orders FROM order_stats
)
SELECT
   os.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
      status,
      COUNT(*) AS order_count,
      SUM(amounts.total / COALESCE(NULLIF(amounts.normalizer, 0), 1)) AS total_revenue,
      AVG(amounts.total / COALESCE(NULLIF(amounts.normalizer, 0), 1)) AS avg_order_value
   FROM
      `nekt_raw.magalu_orders`
   WHERE
      DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      status
),
total AS (
   SELECT SUM(order_count) AS total_orders FROM order_stats
)
SELECT
   os.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

​

2. Top Selling Products

• Understand which products drive the most revenue
• Optimize inventory for high-demand items
• Inform marketing and promotional strategies

WITH item_sales AS (
   SELECT
      i.info.sku AS sku,
      i.info.name AS product_name,
      SUM(i.quantity) AS total_quantity,
      SUM(i.amounts.total / COALESCE(NULLIF(i.amounts.normalizer, 0), 1)) AS total_revenue,
      COUNT(DISTINCT o.id) AS order_count
   FROM
      nekt_raw.magalu_orders o
      CROSS JOIN UNNEST(o.deliveries) AS t(d)
      CROSS JOIN UNNEST(d.items) AS t2(i)
   WHERE
      o.status NOT IN ('cancelled')
      AND DATE(o.created_at) >= CURRENT_DATE - INTERVAL '30' DAY
   GROUP BY
      i.info.sku,
      i.info.name
)
SELECT
   sku,
   product_name,
   total_quantity,
   order_count,
   ROUND(total_revenue, 2) AS total_revenue,
   ROUND(total_revenue / NULLIF(total_quantity, 0), 2) AS avg_unit_price
FROM
   item_sales
ORDER BY
   total_revenue DESC
LIMIT 20

WITH item_sales AS (
   SELECT
      i.info.sku AS sku,
      i.info.name AS product_name,
      SUM(i.quantity) AS total_quantity,
      SUM(i.amounts.total / COALESCE(NULLIF(i.amounts.normalizer, 0), 1)) AS total_revenue,
      COUNT(DISTINCT o.id) AS order_count
   FROM
      `nekt_raw.magalu_orders` o,
      UNNEST(o.deliveries) AS d,
      UNNEST(d.items) AS i
   WHERE
      o.status NOT IN ('cancelled')
      AND DATE(o.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      i.info.sku,
      i.info.name
)
SELECT
   sku,
   product_name,
   total_quantity,
   order_count,
   ROUND(total_revenue, 2) AS total_revenue,
   ROUND(SAFE_DIVIDE(total_revenue, total_quantity), 2) AS avg_unit_price
FROM
   item_sales
ORDER BY
   total_revenue DESC
LIMIT 20

​

3. Delivery Performance Analysis

• Track delivery success rates
• Identify slow shipping providers
• Optimize fulfillment processes

WITH delivery_metrics AS (
   SELECT
      d.shipping.provider.name AS provider_name,
      d.status AS delivery_status,
      COUNT(*) AS delivery_count,
      AVG(d.shipping.deadline.value) AS avg_deadline_days,
      SUM(CASE WHEN d.status = 'delivered' THEN 1 ELSE 0 END) AS delivered_count,
      SUM(CASE WHEN d.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
   FROM
      nekt_raw.magalu_deliveries d
   WHERE
      DATE(d.purchased_at) >= CURRENT_DATE - INTERVAL '30' DAY
   GROUP BY
      d.shipping.provider.name,
      d.status
)
SELECT
   provider_name,
   SUM(delivery_count) AS total_deliveries,
   SUM(delivered_count) AS total_delivered,
   SUM(cancelled_count) AS total_cancelled,
   ROUND(SUM(delivered_count) * 100.0 / NULLIF(SUM(delivery_count), 0), 2) AS delivery_success_rate,
   ROUND(AVG(avg_deadline_days), 1) AS avg_deadline_days
FROM
   delivery_metrics
GROUP BY
   provider_name
ORDER BY
   total_deliveries DESC

WITH delivery_metrics AS (
   SELECT
      d.shipping.provider.name AS provider_name,
      d.status AS delivery_status,
      COUNT(*) AS delivery_count,
      AVG(d.shipping.deadline.value) AS avg_deadline_days,
      SUM(CASE WHEN d.status = 'delivered' THEN 1 ELSE 0 END) AS delivered_count,
      SUM(CASE WHEN d.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
   FROM
      `nekt_raw.magalu_deliveries` d
   WHERE
      DATE(d.purchased_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      d.shipping.provider.name,
      d.status
)
SELECT
   provider_name,
   SUM(delivery_count) AS total_deliveries,
   SUM(delivered_count) AS total_delivered,
   SUM(cancelled_count) AS total_cancelled,
   ROUND(SAFE_DIVIDE(SUM(delivered_count) * 100.0, SUM(delivery_count)), 2) AS delivery_success_rate,
   ROUND(AVG(avg_deadline_days), 1) AS avg_deadline_days
FROM
   delivery_metrics
GROUP BY
   provider_name
ORDER BY
   total_deliveries DESC

​

Implementation Notes

​

Data normalization

-- Example: Get actual order total
SELECT
    amounts.total / COALESCE(NULLIF(amounts.normalizer, 0), 1) AS actual_total
FROM orders

• 100 for values stored as cents
• 1 for values already in the base currency unit

​

Order status flow

1. new - Order just placed
2. approved - Payment confirmed
3. invoiced - Invoice generated
4. shipped - Order dispatched
5. delivered - Order delivered to customer
6. finished - Order completed

• cancelled - Order was cancelled
• returned - Customer returned the order

​

Brazilian context

• Currency: Values are in BRL (Brazilian Real)
• Documents: Customer documents are CPF (individuals) or CNPJ (companies)
• Addresses: Brazilian address format with CEP (postal code), state abbreviations
• Shipping: Includes Brazilian carriers like Correios, Jadlog, and Magalu’s own logistics

​

Incremental sync and replication keys

• Orders and Deliveries invoices: Incremental when configured. Orders uses updated_at and Deliveries invoices uses issued_at as the replication key (API requests include date filters from bookmark or Start Date).
• SKUs, Deliveries, Prices, Stocks: Full table sync in the tap (no replication key on those streams). Start Date still applies where the shared client passes date filters only for streams that define a replication key.

​

Child streams and API volume

• Prices and Stocks are children of SKUs: one extra API request per SKU per stream when enabled.
• Deliveries invoices is a child of Deliveries: one extra API request per delivery when enabled. Includes paginated extraction to safely fetch high volumes.

​

Nested data structures

• Use UNNEST (or CROSS JOIN UNNEST in Athena) to flatten arrays
• Access nested objects using dot notation (e.g., customer.name)
• Handle NULL values in nested fields with COALESCE

-- AWS Athena
SELECT d.items
FROM orders o
CROSS JOIN UNNEST(o.deliveries) AS t(d)

-- GCP BigQuery
SELECT i.*
FROM orders o, UNNEST(o.deliveries) AS d, UNNEST(d.items) AS i

​

Skills for agents