Integration of Mirakl with BigQuery
Connect a Mirakl-powered marketplace to BigQuery with WitCloud - orders, offers, transactions, returns and shop details of your seller shop, collected with a seller API key.
Updated
Introduction
This integration collects the data of your seller shop on a Mirakl-powered marketplace (for example Empik, Decathlon or Leroy Merlin) through the Mirakl Seller API and stores it in Google Cloud BigQuery. You get orders with their lines, offers, transaction lines (commissions, fees, refunds and payouts), returns and the details of your shop, so you can analyze your marketplace sales next to your other data.
Configuration guide
Follow the steps below to connect Mirakl with BigQuery. Once the setup is complete, your marketplace data is transferred to your BigQuery project automatically.
You need the address of your marketplace's Mirakl instance (given by the marketplace operator, for example https://your-marketplace.mirakl.net) and an API key from the marketplace's seller back office. You generate the key there, in Mirakl's own interface, not in WitCloud.
Module setup
Go to Integrations in the menu and click Add integration.

Select Mirakl from the list of available integrations.

Enter the Collect name, for example "Mirakl PL". A good name helps you recognize the data source later.

Enter the Mirakl instance URL, the address of your marketplace's Mirakl instance. It has to start with https://. A trailing slash or /api at the end is removed automatically.

Authorization
If you have already added an API key for this marketplace, you can pick it from the Mirakl authorization drop-down and skip the rest of this step.
Click Authorize. The button stays disabled until the Mirakl instance URL is filled in with a valid address, because the key is checked against that instance.

In the Authorize window paste the API Key generated in the marketplace's seller back office, under your user settings, and click Create. The key is hidden while you type. Only members with the Editor role or higher can add a key.

WitCloud checks the key straight away. If it is rejected, you see the reason: the key is invalid, it does not have sufficient permissions, the instance cannot be reached, or the address does not point to a Mirakl Seller API.
The saved key now appears in the Mirakl authorization drop-down. It is labelled with the shop name, the instance address and the first characters of the key, so several keys stay easy to tell apart. Pick the one you want to use.

Data is collected for the shop the API key belongs to, so there is no shop to select. To collect several shops or marketplaces, add a separate key and a separate integration for each.
Advanced settings
Click Show advanced settings to adjust:
- Data freshness (offers, shop): how often, in hours, the offers table is refreshed. Minimum 4, maximum 12, default 4. The shop table is refreshed once a day whatever you set here.

- Static IP: when enabled, all requests come from the fixed IP address 34.118.32.227, which you can give to your marketplace operator to add to the list of allowed addresses.

- Tables to collect: narrow down the tables if you don't need all of them. By default all five are selected (orders, offers, transactions, returns and shop), and at least one is required.

Click Next to move on to the data collection settings.

Historical data and scheduling
Enter the number of days of Historical Data you want to fetch, up to 365.

A data synchronization schedule is created automatically. You can read more about it here.
Click Create to complete the configuration.

Done! Your Mirakl data is now synchronized with BigQuery and updated on a schedule.
Data synchronization
Orders, transaction lines and returns are collected by the day they were created. Recent days are fetched again on later runs, so status changes, refunds and payment states from roughly the last 30 days are picked up. Offers and shop are snapshots of their current state, saved on the day of the run: to read current values, use the latest day.
Transactions and returns depend on optional Mirakl modules. If your marketplace operator has not enabled seller payments or returns, the corresponding table stays empty.
Data schema
Expand the tables below to view the full data schema collected from Mirakl. Customers are limited to an ID, a locale and the city, zip code, state and country of their addresses; names, streets, phone numbers and e-mails are not collected.
Data schema for mirakl_orders
| Field Name | Mode | Type |
|---|---|---|
| order_id | NULLABLE | STRING |
| commercial_id | NULLABLE | STRING |
| created_date | NULLABLE | TIMESTAMP |
| last_updated_date | NULLABLE | TIMESTAMP |
| acceptance_decision_date | NULLABLE | TIMESTAMP |
| customer_debited_date | NULLABLE | TIMESTAMP |
| shipping_deadline | NULLABLE | TIMESTAMP |
| transaction_date | NULLABLE | TIMESTAMP |
| transaction_number | NULLABLE | STRING |
| order_state | NULLABLE | STRING |
| order_state_reason_code | NULLABLE | STRING |
| order_state_reason_label | NULLABLE | STRING |
| currency_iso_code | NULLABLE | STRING |
| order_tax_mode | NULLABLE | STRING |
| price | NULLABLE | FLOAT |
| shipping_price | NULLABLE | FLOAT |
| total_price | NULLABLE | FLOAT |
| total_commission | NULLABLE | FLOAT |
| fully_refunded | NULLABLE | BOOLEAN |
| has_incident | NULLABLE | BOOLEAN |
| has_invoice | NULLABLE | BOOLEAN |
| customer_directly_pays_seller | NULLABLE | BOOLEAN |
| payment_type | NULLABLE | STRING |
| payment_workflow | NULLABLE | STRING |
| payment_duration | NULLABLE | INTEGER |
| quote_id | NULLABLE | STRING |
| channel | NULLABLE | RECORD |
| channel.code | NULLABLE | STRING |
| channel.label | NULLABLE | STRING |
| references | NULLABLE | RECORD |
| references.order_reference_for_customer | NULLABLE | STRING |
| references.order_reference_for_seller | NULLABLE | STRING |
| customer | NULLABLE | RECORD |
| customer.customer_id | NULLABLE | STRING |
| customer.locale | NULLABLE | STRING |
| customer.billing_address | NULLABLE | RECORD |
| customer.billing_address.city | NULLABLE | STRING |
| customer.billing_address.zip_code | NULLABLE | STRING |
| customer.billing_address.state | NULLABLE | STRING |
| customer.billing_address.country | NULLABLE | STRING |
| customer.billing_address.country_iso_code | NULLABLE | STRING |
| customer.shipping_address | NULLABLE | RECORD |
| customer.shipping_address.city | NULLABLE | STRING |
| customer.shipping_address.zip_code | NULLABLE | STRING |
| customer.shipping_address.state | NULLABLE | STRING |
| customer.shipping_address.country | NULLABLE | STRING |
| customer.shipping_address.country_iso_code | NULLABLE | STRING |
| shipping_type_code | NULLABLE | STRING |
| shipping_type_label | NULLABLE | STRING |
| shipping_type_standard_code | NULLABLE | STRING |
| shipping_zone_code | NULLABLE | STRING |
| shipping_zone_label | NULLABLE | STRING |
| shipping_carrier_code | NULLABLE | STRING |
| shipping_carrier_standard_code | NULLABLE | STRING |
| shipping_company | NULLABLE | STRING |
| delivery_date | NULLABLE | RECORD |
| delivery_date.earliest | NULLABLE | TIMESTAMP |
| delivery_date.latest | NULLABLE | TIMESTAMP |
| fulfillment | NULLABLE | RECORD |
| fulfillment.center | NULLABLE | RECORD |
| fulfillment.center.code | NULLABLE | STRING |
| promotions | NULLABLE | RECORD |
| promotions.total_deduced_amount | NULLABLE | FLOAT |
| order_taxes | REPEATED | RECORD |
| order_taxes.code | NULLABLE | STRING |
| order_taxes.rate | NULLABLE | FLOAT |
| order_taxes.total_amount | NULLABLE | FLOAT |
| order_lines | REPEATED | RECORD |
| order_lines.order_line_id | NULLABLE | STRING |
| order_lines.order_line_index | NULLABLE | INTEGER |
| order_lines.order_line_state | NULLABLE | STRING |
| order_lines.order_line_state_reason_code | NULLABLE | STRING |
| order_lines.order_line_state_reason_label | NULLABLE | STRING |
| order_lines.offer_id | NULLABLE | INTEGER |
| order_lines.offer_sku | NULLABLE | STRING |
| order_lines.offer_state_code | NULLABLE | STRING |
| order_lines.product_sku | NULLABLE | STRING |
| order_lines.product_shop_sku | NULLABLE | STRING |
| order_lines.product_title | NULLABLE | STRING |
| order_lines.category_code | NULLABLE | STRING |
| order_lines.category_label | NULLABLE | STRING |
| order_lines.quantity | NULLABLE | INTEGER |
| order_lines.price_unit | NULLABLE | FLOAT |
| order_lines.origin_unit_price | NULLABLE | FLOAT |
| order_lines.price | NULLABLE | FLOAT |
| order_lines.shipping_price | NULLABLE | FLOAT |
| order_lines.total_price | NULLABLE | FLOAT |
| order_lines.commission_fee | NULLABLE | FLOAT |
| order_lines.total_commission | NULLABLE | FLOAT |
| order_lines.commission_taxes | REPEATED | RECORD |
| order_lines.commission_taxes.code | NULLABLE | STRING |
| order_lines.commission_taxes.rate | NULLABLE | FLOAT |
| order_lines.commission_taxes.amount | NULLABLE | FLOAT |
| order_lines.taxes | REPEATED | RECORD |
| order_lines.taxes.code | NULLABLE | STRING |
| order_lines.taxes.rate | NULLABLE | FLOAT |
| order_lines.taxes.amount | NULLABLE | FLOAT |
| order_lines.shipping_taxes | REPEATED | RECORD |
| order_lines.shipping_taxes.code | NULLABLE | STRING |
| order_lines.shipping_taxes.rate | NULLABLE | FLOAT |
| order_lines.shipping_taxes.amount | NULLABLE | FLOAT |
| order_lines.fees | REPEATED | RECORD |
| order_lines.fees.code | NULLABLE | STRING |
| order_lines.fees.amount | NULLABLE | FLOAT |
| order_lines.promotions | REPEATED | RECORD |
| order_lines.promotions.id | NULLABLE | STRING |
| order_lines.promotions.deduced_amount | NULLABLE | FLOAT |
| order_lines.promotions.offered_quantity | NULLABLE | INTEGER |
| order_lines.promotions.promo_code | NULLABLE | STRING |
| order_lines.created_date | NULLABLE | TIMESTAMP |
| order_lines.debited_date | NULLABLE | TIMESTAMP |
| order_lines.shipped_date | NULLABLE | TIMESTAMP |
| order_lines.received_date | NULLABLE | TIMESTAMP |
| order_lines.last_updated_date | NULLABLE | TIMESTAMP |
| order_lines.refunds | REPEATED | RECORD |
| order_lines.refunds.id | NULLABLE | STRING |
| order_lines.refunds.amount | NULLABLE | FLOAT |
| order_lines.refunds.shipping_amount | NULLABLE | FLOAT |
| order_lines.refunds.quantity | NULLABLE | INTEGER |
| order_lines.refunds.commission_amount | NULLABLE | FLOAT |
| order_lines.refunds.commission_total_amount | NULLABLE | FLOAT |
| order_lines.refunds.commission_taxes | REPEATED | RECORD |
| order_lines.refunds.commission_taxes.code | NULLABLE | STRING |
| order_lines.refunds.commission_taxes.rate | NULLABLE | FLOAT |
| order_lines.refunds.commission_taxes.amount | NULLABLE | FLOAT |
| order_lines.refunds.taxes | REPEATED | RECORD |
| order_lines.refunds.taxes.code | NULLABLE | STRING |
| order_lines.refunds.taxes.rate | NULLABLE | FLOAT |
| order_lines.refunds.taxes.amount | NULLABLE | FLOAT |
| order_lines.refunds.shipping_taxes | REPEATED | RECORD |
| order_lines.refunds.shipping_taxes.code | NULLABLE | STRING |
| order_lines.refunds.shipping_taxes.rate | NULLABLE | FLOAT |
| order_lines.refunds.shipping_taxes.amount | NULLABLE | FLOAT |
| order_lines.refunds.reason_code | NULLABLE | STRING |
| order_lines.refunds.created_date | NULLABLE | TIMESTAMP |
| order_lines.refunds.order_refund_id | NULLABLE | STRING |
| order_lines.refunds.refund_state | NULLABLE | STRING |
| order_lines.refunds.transaction_date | NULLABLE | TIMESTAMP |
| order_lines.refunds.transaction_number | NULLABLE | STRING |
| order_lines.cancelations | REPEATED | RECORD |
| order_lines.cancelations.id | NULLABLE | STRING |
| order_lines.cancelations.amount | NULLABLE | FLOAT |
| order_lines.cancelations.shipping_amount | NULLABLE | FLOAT |
| order_lines.cancelations.quantity | NULLABLE | INTEGER |
| order_lines.cancelations.commission_amount | NULLABLE | FLOAT |
| order_lines.cancelations.commission_total_amount | NULLABLE | FLOAT |
| order_lines.cancelations.commission_taxes | REPEATED | RECORD |
| order_lines.cancelations.commission_taxes.code | NULLABLE | STRING |
| order_lines.cancelations.commission_taxes.rate | NULLABLE | FLOAT |
| order_lines.cancelations.commission_taxes.amount | NULLABLE | FLOAT |
| order_lines.cancelations.taxes | REPEATED | RECORD |
| order_lines.cancelations.taxes.code | NULLABLE | STRING |
| order_lines.cancelations.taxes.rate | NULLABLE | FLOAT |
| order_lines.cancelations.taxes.amount | NULLABLE | FLOAT |
| order_lines.cancelations.shipping_taxes | REPEATED | RECORD |
| order_lines.cancelations.shipping_taxes.code | NULLABLE | STRING |
| order_lines.cancelations.shipping_taxes.rate | NULLABLE | FLOAT |
| order_lines.cancelations.shipping_taxes.amount | NULLABLE | FLOAT |
| order_lines.cancelations.reason_code | NULLABLE | STRING |
| order_lines.cancelations.created_date | NULLABLE | TIMESTAMP |
Data schema for mirakl_offers
| Field Name | Mode | Type |
|---|---|---|
| offer_id | NULLABLE | INTEGER |
| shop_sku | NULLABLE | STRING |
| product_sku | NULLABLE | STRING |
| product_title | NULLABLE | STRING |
| product_brand | NULLABLE | STRING |
| product_tax_code | NULLABLE | STRING |
| product_references | REPEATED | RECORD |
| product_references.reference | NULLABLE | STRING |
| product_references.reference_type | NULLABLE | STRING |
| category_code | NULLABLE | STRING |
| category_label | NULLABLE | STRING |
| currency_iso_code | NULLABLE | STRING |
| price | NULLABLE | FLOAT |
| total_price | NULLABLE | FLOAT |
| msrp | NULLABLE | FLOAT |
| applicable_pricing | NULLABLE | RECORD |
| applicable_pricing.price | NULLABLE | FLOAT |
| applicable_pricing.unit_origin_price | NULLABLE | FLOAT |
| applicable_pricing.unit_discount_price | NULLABLE | FLOAT |
| applicable_pricing.discount_start_date | NULLABLE | TIMESTAMP |
| applicable_pricing.discount_end_date | NULLABLE | TIMESTAMP |
| quantity | NULLABLE | INTEGER |
| min_quantity_alert | NULLABLE | INTEGER |
| min_order_quantity | NULLABLE | INTEGER |
| max_order_quantity | NULLABLE | INTEGER |
| package_quantity | NULLABLE | INTEGER |
| state_code | NULLABLE | STRING |
| active | NULLABLE | BOOLEAN |
| inactivity_reasons | REPEATED | STRING |
| channels | REPEATED | STRING |
| favorite_rank | NULLABLE | INTEGER |
| leadtime_to_ship | NULLABLE | INTEGER |
| shipping_deadline | NULLABLE | TIMESTAMP |
| min_shipping_price | NULLABLE | FLOAT |
| min_shipping_price_additional | NULLABLE | FLOAT |
| min_shipping_type | NULLABLE | STRING |
| min_shipping_zone | NULLABLE | STRING |
| available_start_date | NULLABLE | TIMESTAMP |
| available_end_date | NULLABLE | TIMESTAMP |
Data schema for mirakl_transactions
| Field Name | Mode | Type |
|---|---|---|
| id | NULLABLE | STRING |
| type | NULLABLE | STRING |
| amount | NULLABLE | FLOAT |
| amount_credited | NULLABLE | FLOAT |
| amount_debited | NULLABLE | FLOAT |
| balance | NULLABLE | FLOAT |
| psp_balance | NULLABLE | FLOAT |
| currency_iso_code | NULLABLE | STRING |
| date_created | NULLABLE | TIMESTAMP |
| last_updated | NULLABLE | TIMESTAMP |
| payment_state | NULLABLE | STRING |
| pay_out_psp_code | NULLABLE | STRING |
| seller_billing_cycle_id | NULLABLE | STRING |
| accounting_document_id | NULLABLE | STRING |
| accounting_document_number | NULLABLE | STRING |
| accounting_document_creation_date | NULLABLE | TIMESTAMP |
| entities | NULLABLE | RECORD |
| entities.domain | NULLABLE | STRING |
| entities.order | NULLABLE | RECORD |
| entities.order.id | NULLABLE | STRING |
| entities.order.commercial_id | NULLABLE | STRING |
| entities.order.creation_date | NULLABLE | TIMESTAMP |
| entities.order.references | NULLABLE | RECORD |
| entities.order.references.order_reference_for_customer | NULLABLE | STRING |
| entities.order.references.order_reference_for_seller | NULLABLE | STRING |
| entities.order_line | NULLABLE | RECORD |
| entities.order_line.id | NULLABLE | STRING |
| entities.refund | NULLABLE | RECORD |
| entities.refund.id | NULLABLE | STRING |
| entities.shipment | NULLABLE | RECORD |
| entities.shipment.id | NULLABLE | STRING |
| entities.debit | NULLABLE | RECORD |
| entities.debit.id | NULLABLE | STRING |
| entities.channel | NULLABLE | RECORD |
| entities.channel.code | NULLABLE | STRING |
| entities.fee | NULLABLE | RECORD |
| entities.fee.code | NULLABLE | STRING |
| entities.order_fee | NULLABLE | RECORD |
| entities.order_fee.code | NULLABLE | STRING |
| entities.order_tax | NULLABLE | RECORD |
| entities.order_tax.code | NULLABLE | STRING |
| entities.order_tax.rate | NULLABLE | FLOAT |
| entities.shop_tax | NULLABLE | RECORD |
| entities.shop_tax.code | NULLABLE | STRING |
| entities.shop_tax.rate | NULLABLE | FLOAT |
| entities.transaction_info | NULLABLE | RECORD |
| entities.transaction_info.date | NULLABLE | TIMESTAMP |
| entities.transaction_info.number | NULLABLE | STRING |
Data schema for mirakl_returns
| Field Name | Mode | Type |
|---|---|---|
| id | NULLABLE | STRING |
| rma | NULLABLE | STRING |
| state | NULLABLE | STRING |
| order_id | NULLABLE | STRING |
| order_commercial_id | NULLABLE | STRING |
| reason_code | NULLABLE | STRING |
| rejection_reason_code | NULLABLE | STRING |
| method_code | NULLABLE | STRING |
| date_created | NULLABLE | TIMESTAMP |
| last_updated | NULLABLE | TIMESTAMP |
| return_address | NULLABLE | RECORD |
| return_address.city | NULLABLE | STRING |
| return_address.zip_code | NULLABLE | STRING |
| return_address.state | NULLABLE | STRING |
| return_address.country_iso_code | NULLABLE | STRING |
| return_lines | REPEATED | RECORD |
| return_lines.order_line_id | NULLABLE | STRING |
| return_lines.quantity | NULLABLE | INTEGER |
| return_lines.reason_code | NULLABLE | STRING |
| tracking | NULLABLE | RECORD |
| tracking.carrier_code | NULLABLE | STRING |
| tracking.carrier_name | NULLABLE | STRING |
| tracking.carrier_standard_code | NULLABLE | STRING |
Data schema for mirakl_shop
| Field Name | Mode | Type |
|---|---|---|
| shop_id | NULLABLE | INTEGER |
| shop_name | NULLABLE | STRING |
| shop_state | NULLABLE | STRING |
| suspension_type | NULLABLE | STRING |
| currency_iso_code | NULLABLE | STRING |
| model | NULLABLE | STRING |
| shipping_country | NULLABLE | STRING |
| channels | REPEATED | STRING |
| is_professional | NULLABLE | BOOLEAN |
| premium | NULLABLE | BOOLEAN |
| free_shipping | NULLABLE | BOOLEAN |
| grade | NULLABLE | FLOAT |
| approval_rate | NULLABLE | FLOAT |
| approval_delay | NULLABLE | INTEGER |
| evaluations_count | NULLABLE | INTEGER |
| offers_count | NULLABLE | INTEGER |
| orders_count | NULLABLE | INTEGER |
| order_messages_response_delay | NULLABLE | INTEGER |
| date_created | NULLABLE | TIMESTAMP |
| last_updated_date | NULLABLE | TIMESTAMP |
| closed_from | NULLABLE | TIMESTAMP |
| closed_to | NULLABLE | TIMESTAMP |
| payment_details | NULLABLE | RECORD |
| payment_details.paid_balance | NULLABLE | FLOAT |
| payment_details.payable_balance | NULLABLE | FLOAT |
| payment_details.pending_balance | NULLABLE | FLOAT |
| payment_details.reserve_balance | NULLABLE | FLOAT |
| payment_details.reserve_target | NULLABLE | FLOAT |
