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.

Integrations list with the Add integration button

Select Mirakl from the list of available integrations.

Integration catalog filtered to Mirakl

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

Collect name field filled in with the example name Mirakl PL

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.

Mirakl instance URL field filled in with the example address https://your-marketplace.mirakl.net

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.

Authorize button next to the Mirakl authorization field, active once the instance URL is filled in

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.

Authorize pop-up with the API Key field filled with an example value

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.

Mirakl authorization drop-down open with one example key

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.

Advanced settings open with the Data freshness (offers, shop) field set to 4 hours

  • 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.

Static IP switch in the advanced settings

  • 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.

Tables to collect field with all five offered tables selected

Click Next to move on to the data collection settings.

Collect settings filled in with the Next button

Historical data and scheduling

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

Historical Data field filled in with 365 days

A data synchronization schedule is created automatically. You can read more about it here.

Click Create to complete the configuration.

Data collection settings with the Create button

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 NameModeType
order_idNULLABLESTRING
commercial_idNULLABLESTRING
created_dateNULLABLETIMESTAMP
last_updated_dateNULLABLETIMESTAMP
acceptance_decision_dateNULLABLETIMESTAMP
customer_debited_dateNULLABLETIMESTAMP
shipping_deadlineNULLABLETIMESTAMP
transaction_dateNULLABLETIMESTAMP
transaction_numberNULLABLESTRING
order_stateNULLABLESTRING
order_state_reason_codeNULLABLESTRING
order_state_reason_labelNULLABLESTRING
currency_iso_codeNULLABLESTRING
order_tax_modeNULLABLESTRING
priceNULLABLEFLOAT
shipping_priceNULLABLEFLOAT
total_priceNULLABLEFLOAT
total_commissionNULLABLEFLOAT
fully_refundedNULLABLEBOOLEAN
has_incidentNULLABLEBOOLEAN
has_invoiceNULLABLEBOOLEAN
customer_directly_pays_sellerNULLABLEBOOLEAN
payment_typeNULLABLESTRING
payment_workflowNULLABLESTRING
payment_durationNULLABLEINTEGER
quote_idNULLABLESTRING
channelNULLABLERECORD
channel.codeNULLABLESTRING
channel.labelNULLABLESTRING
referencesNULLABLERECORD
references.order_reference_for_customerNULLABLESTRING
references.order_reference_for_sellerNULLABLESTRING
customerNULLABLERECORD
customer.customer_idNULLABLESTRING
customer.localeNULLABLESTRING
customer.billing_addressNULLABLERECORD
customer.billing_address.cityNULLABLESTRING
customer.billing_address.zip_codeNULLABLESTRING
customer.billing_address.stateNULLABLESTRING
customer.billing_address.countryNULLABLESTRING
customer.billing_address.country_iso_codeNULLABLESTRING
customer.shipping_addressNULLABLERECORD
customer.shipping_address.cityNULLABLESTRING
customer.shipping_address.zip_codeNULLABLESTRING
customer.shipping_address.stateNULLABLESTRING
customer.shipping_address.countryNULLABLESTRING
customer.shipping_address.country_iso_codeNULLABLESTRING
shipping_type_codeNULLABLESTRING
shipping_type_labelNULLABLESTRING
shipping_type_standard_codeNULLABLESTRING
shipping_zone_codeNULLABLESTRING
shipping_zone_labelNULLABLESTRING
shipping_carrier_codeNULLABLESTRING
shipping_carrier_standard_codeNULLABLESTRING
shipping_companyNULLABLESTRING
delivery_dateNULLABLERECORD
delivery_date.earliestNULLABLETIMESTAMP
delivery_date.latestNULLABLETIMESTAMP
fulfillmentNULLABLERECORD
fulfillment.centerNULLABLERECORD
fulfillment.center.codeNULLABLESTRING
promotionsNULLABLERECORD
promotions.total_deduced_amountNULLABLEFLOAT
order_taxesREPEATEDRECORD
order_taxes.codeNULLABLESTRING
order_taxes.rateNULLABLEFLOAT
order_taxes.total_amountNULLABLEFLOAT
order_linesREPEATEDRECORD
order_lines.order_line_idNULLABLESTRING
order_lines.order_line_indexNULLABLEINTEGER
order_lines.order_line_stateNULLABLESTRING
order_lines.order_line_state_reason_codeNULLABLESTRING
order_lines.order_line_state_reason_labelNULLABLESTRING
order_lines.offer_idNULLABLEINTEGER
order_lines.offer_skuNULLABLESTRING
order_lines.offer_state_codeNULLABLESTRING
order_lines.product_skuNULLABLESTRING
order_lines.product_shop_skuNULLABLESTRING
order_lines.product_titleNULLABLESTRING
order_lines.category_codeNULLABLESTRING
order_lines.category_labelNULLABLESTRING
order_lines.quantityNULLABLEINTEGER
order_lines.price_unitNULLABLEFLOAT
order_lines.origin_unit_priceNULLABLEFLOAT
order_lines.priceNULLABLEFLOAT
order_lines.shipping_priceNULLABLEFLOAT
order_lines.total_priceNULLABLEFLOAT
order_lines.commission_feeNULLABLEFLOAT
order_lines.total_commissionNULLABLEFLOAT
order_lines.commission_taxesREPEATEDRECORD
order_lines.commission_taxes.codeNULLABLESTRING
order_lines.commission_taxes.rateNULLABLEFLOAT
order_lines.commission_taxes.amountNULLABLEFLOAT
order_lines.taxesREPEATEDRECORD
order_lines.taxes.codeNULLABLESTRING
order_lines.taxes.rateNULLABLEFLOAT
order_lines.taxes.amountNULLABLEFLOAT
order_lines.shipping_taxesREPEATEDRECORD
order_lines.shipping_taxes.codeNULLABLESTRING
order_lines.shipping_taxes.rateNULLABLEFLOAT
order_lines.shipping_taxes.amountNULLABLEFLOAT
order_lines.feesREPEATEDRECORD
order_lines.fees.codeNULLABLESTRING
order_lines.fees.amountNULLABLEFLOAT
order_lines.promotionsREPEATEDRECORD
order_lines.promotions.idNULLABLESTRING
order_lines.promotions.deduced_amountNULLABLEFLOAT
order_lines.promotions.offered_quantityNULLABLEINTEGER
order_lines.promotions.promo_codeNULLABLESTRING
order_lines.created_dateNULLABLETIMESTAMP
order_lines.debited_dateNULLABLETIMESTAMP
order_lines.shipped_dateNULLABLETIMESTAMP
order_lines.received_dateNULLABLETIMESTAMP
order_lines.last_updated_dateNULLABLETIMESTAMP
order_lines.refundsREPEATEDRECORD
order_lines.refunds.idNULLABLESTRING
order_lines.refunds.amountNULLABLEFLOAT
order_lines.refunds.shipping_amountNULLABLEFLOAT
order_lines.refunds.quantityNULLABLEINTEGER
order_lines.refunds.commission_amountNULLABLEFLOAT
order_lines.refunds.commission_total_amountNULLABLEFLOAT
order_lines.refunds.commission_taxesREPEATEDRECORD
order_lines.refunds.commission_taxes.codeNULLABLESTRING
order_lines.refunds.commission_taxes.rateNULLABLEFLOAT
order_lines.refunds.commission_taxes.amountNULLABLEFLOAT
order_lines.refunds.taxesREPEATEDRECORD
order_lines.refunds.taxes.codeNULLABLESTRING
order_lines.refunds.taxes.rateNULLABLEFLOAT
order_lines.refunds.taxes.amountNULLABLEFLOAT
order_lines.refunds.shipping_taxesREPEATEDRECORD
order_lines.refunds.shipping_taxes.codeNULLABLESTRING
order_lines.refunds.shipping_taxes.rateNULLABLEFLOAT
order_lines.refunds.shipping_taxes.amountNULLABLEFLOAT
order_lines.refunds.reason_codeNULLABLESTRING
order_lines.refunds.created_dateNULLABLETIMESTAMP
order_lines.refunds.order_refund_idNULLABLESTRING
order_lines.refunds.refund_stateNULLABLESTRING
order_lines.refunds.transaction_dateNULLABLETIMESTAMP
order_lines.refunds.transaction_numberNULLABLESTRING
order_lines.cancelationsREPEATEDRECORD
order_lines.cancelations.idNULLABLESTRING
order_lines.cancelations.amountNULLABLEFLOAT
order_lines.cancelations.shipping_amountNULLABLEFLOAT
order_lines.cancelations.quantityNULLABLEINTEGER
order_lines.cancelations.commission_amountNULLABLEFLOAT
order_lines.cancelations.commission_total_amountNULLABLEFLOAT
order_lines.cancelations.commission_taxesREPEATEDRECORD
order_lines.cancelations.commission_taxes.codeNULLABLESTRING
order_lines.cancelations.commission_taxes.rateNULLABLEFLOAT
order_lines.cancelations.commission_taxes.amountNULLABLEFLOAT
order_lines.cancelations.taxesREPEATEDRECORD
order_lines.cancelations.taxes.codeNULLABLESTRING
order_lines.cancelations.taxes.rateNULLABLEFLOAT
order_lines.cancelations.taxes.amountNULLABLEFLOAT
order_lines.cancelations.shipping_taxesREPEATEDRECORD
order_lines.cancelations.shipping_taxes.codeNULLABLESTRING
order_lines.cancelations.shipping_taxes.rateNULLABLEFLOAT
order_lines.cancelations.shipping_taxes.amountNULLABLEFLOAT
order_lines.cancelations.reason_codeNULLABLESTRING
order_lines.cancelations.created_dateNULLABLETIMESTAMP
Data schema for mirakl_offers
Field NameModeType
offer_idNULLABLEINTEGER
shop_skuNULLABLESTRING
product_skuNULLABLESTRING
product_titleNULLABLESTRING
product_brandNULLABLESTRING
product_tax_codeNULLABLESTRING
product_referencesREPEATEDRECORD
product_references.referenceNULLABLESTRING
product_references.reference_typeNULLABLESTRING
category_codeNULLABLESTRING
category_labelNULLABLESTRING
currency_iso_codeNULLABLESTRING
priceNULLABLEFLOAT
total_priceNULLABLEFLOAT
msrpNULLABLEFLOAT
applicable_pricingNULLABLERECORD
applicable_pricing.priceNULLABLEFLOAT
applicable_pricing.unit_origin_priceNULLABLEFLOAT
applicable_pricing.unit_discount_priceNULLABLEFLOAT
applicable_pricing.discount_start_dateNULLABLETIMESTAMP
applicable_pricing.discount_end_dateNULLABLETIMESTAMP
quantityNULLABLEINTEGER
min_quantity_alertNULLABLEINTEGER
min_order_quantityNULLABLEINTEGER
max_order_quantityNULLABLEINTEGER
package_quantityNULLABLEINTEGER
state_codeNULLABLESTRING
activeNULLABLEBOOLEAN
inactivity_reasonsREPEATEDSTRING
channelsREPEATEDSTRING
favorite_rankNULLABLEINTEGER
leadtime_to_shipNULLABLEINTEGER
shipping_deadlineNULLABLETIMESTAMP
min_shipping_priceNULLABLEFLOAT
min_shipping_price_additionalNULLABLEFLOAT
min_shipping_typeNULLABLESTRING
min_shipping_zoneNULLABLESTRING
available_start_dateNULLABLETIMESTAMP
available_end_dateNULLABLETIMESTAMP
Data schema for mirakl_transactions
Field NameModeType
idNULLABLESTRING
typeNULLABLESTRING
amountNULLABLEFLOAT
amount_creditedNULLABLEFLOAT
amount_debitedNULLABLEFLOAT
balanceNULLABLEFLOAT
psp_balanceNULLABLEFLOAT
currency_iso_codeNULLABLESTRING
date_createdNULLABLETIMESTAMP
last_updatedNULLABLETIMESTAMP
payment_stateNULLABLESTRING
pay_out_psp_codeNULLABLESTRING
seller_billing_cycle_idNULLABLESTRING
accounting_document_idNULLABLESTRING
accounting_document_numberNULLABLESTRING
accounting_document_creation_dateNULLABLETIMESTAMP
entitiesNULLABLERECORD
entities.domainNULLABLESTRING
entities.orderNULLABLERECORD
entities.order.idNULLABLESTRING
entities.order.commercial_idNULLABLESTRING
entities.order.creation_dateNULLABLETIMESTAMP
entities.order.referencesNULLABLERECORD
entities.order.references.order_reference_for_customerNULLABLESTRING
entities.order.references.order_reference_for_sellerNULLABLESTRING
entities.order_lineNULLABLERECORD
entities.order_line.idNULLABLESTRING
entities.refundNULLABLERECORD
entities.refund.idNULLABLESTRING
entities.shipmentNULLABLERECORD
entities.shipment.idNULLABLESTRING
entities.debitNULLABLERECORD
entities.debit.idNULLABLESTRING
entities.channelNULLABLERECORD
entities.channel.codeNULLABLESTRING
entities.feeNULLABLERECORD
entities.fee.codeNULLABLESTRING
entities.order_feeNULLABLERECORD
entities.order_fee.codeNULLABLESTRING
entities.order_taxNULLABLERECORD
entities.order_tax.codeNULLABLESTRING
entities.order_tax.rateNULLABLEFLOAT
entities.shop_taxNULLABLERECORD
entities.shop_tax.codeNULLABLESTRING
entities.shop_tax.rateNULLABLEFLOAT
entities.transaction_infoNULLABLERECORD
entities.transaction_info.dateNULLABLETIMESTAMP
entities.transaction_info.numberNULLABLESTRING
Data schema for mirakl_returns
Field NameModeType
idNULLABLESTRING
rmaNULLABLESTRING
stateNULLABLESTRING
order_idNULLABLESTRING
order_commercial_idNULLABLESTRING
reason_codeNULLABLESTRING
rejection_reason_codeNULLABLESTRING
method_codeNULLABLESTRING
date_createdNULLABLETIMESTAMP
last_updatedNULLABLETIMESTAMP
return_addressNULLABLERECORD
return_address.cityNULLABLESTRING
return_address.zip_codeNULLABLESTRING
return_address.stateNULLABLESTRING
return_address.country_iso_codeNULLABLESTRING
return_linesREPEATEDRECORD
return_lines.order_line_idNULLABLESTRING
return_lines.quantityNULLABLEINTEGER
return_lines.reason_codeNULLABLESTRING
trackingNULLABLERECORD
tracking.carrier_codeNULLABLESTRING
tracking.carrier_nameNULLABLESTRING
tracking.carrier_standard_codeNULLABLESTRING
Data schema for mirakl_shop
Field NameModeType
shop_idNULLABLEINTEGER
shop_nameNULLABLESTRING
shop_stateNULLABLESTRING
suspension_typeNULLABLESTRING
currency_iso_codeNULLABLESTRING
modelNULLABLESTRING
shipping_countryNULLABLESTRING
channelsREPEATEDSTRING
is_professionalNULLABLEBOOLEAN
premiumNULLABLEBOOLEAN
free_shippingNULLABLEBOOLEAN
gradeNULLABLEFLOAT
approval_rateNULLABLEFLOAT
approval_delayNULLABLEINTEGER
evaluations_countNULLABLEINTEGER
offers_countNULLABLEINTEGER
orders_countNULLABLEINTEGER
order_messages_response_delayNULLABLEINTEGER
date_createdNULLABLETIMESTAMP
last_updated_dateNULLABLETIMESTAMP
closed_fromNULLABLETIMESTAMP
closed_toNULLABLETIMESTAMP
payment_detailsNULLABLERECORD
payment_details.paid_balanceNULLABLEFLOAT
payment_details.payable_balanceNULLABLEFLOAT
payment_details.pending_balanceNULLABLEFLOAT
payment_details.reserve_balanceNULLABLEFLOAT
payment_details.reserve_targetNULLABLEFLOAT