Integration of PrestaShop with BigQuery

Learn how to connect PrestaShop to Google BigQuery using WitCloud to collect orders, customers, products, stock and other shop data for analysis.

Updated

Introduction

This module connects your PrestaShop store to Google Cloud BigQuery. WitCloud reads your orders, order lines, customers, addresses, products, combinations, stock levels, categories, manufacturers, currencies, cart rules and other shop data through the PrestaShop web service (API) and stores them in your project's BigQuery dataset. You can then analyze sales and stock together with your advertising data and use the tables in the All in One solution.

Configuration guide

Follow the steps below to connect PrestaShop with BigQuery. WitCloud needs the address of your shop and a PrestaShop web service key that is allowed to read your data.

Create the web service key before you start: you will paste it into WitCloud in the Authorization step. Read-only access is enough, because WitCloud only reads data from your shop.

Create the web service key in PrestaShop

This step takes place in your PrestaShop back office, which is not part of WitCloud. The menu names below come from PrestaShop and may differ between its versions; see the PrestaShop documentation if you cannot find them.

  1. Log in to your PrestaShop back office as an administrator.
  2. Go to Advanced Parameters > Webservice and make sure the web service is enabled.
  3. Click Add new webservice key and generate a key.
  4. Give the key read access (View, the GET method) to the resources you want to collect, for example orders, customers, products and stock.
  5. Save the key and copy it. Keep it private: anyone who has it can read the data it gives access to.

Module setup

Go to Integrations and click Add integration.

Integrations list with the Add integration button

Select PrestaShop from the list of available integrations.

Integration catalog filtered to PrestaShop

Enter the Collect name, e.g. "PrestaShop PL". A clear name helps you recognise the data source later.

Collect name field filled in with the example name PrestaShop PL

Enter the Shop url: the address of your shop, including https://, e.g. https://your-shop.com. The Authorize button stays inactive until you enter a valid address.

Shop url field filled in with the example address https://demo-shop.example

Authorization

If you have already authorized this PrestaShop shop in WitCloud, you can pick the existing token from the Prestashop authorization drop-down and skip the next two steps.

Click Authorize.

Authorize button next to the PrestaShop authorization field, active once the shop address is filled in

In the Authorize pop-up paste the web service key into the Access token field and click Create. WitCloud checks the key against the shop address you entered. If the shop cannot be reached, the address or the key is wrong, or the key has no permission to read data, the reason is shown under the form.

Authorize pop-up with the Access token field filled with an example value

The saved token is selected automatically. It is listed by the shop address and a masked part of the key. You can also choose a token from the Prestashop authorization drop-down.

PrestaShop authorization drop-down open with one example token

Advanced settings

Click Show advanced settings to see three more fields.

In Data freshness (products, stocks) enter how often, in hours, tables that hold the whole catalogue (such as products and stocks) are refreshed. The value must be between 4 and 12 hours; the default is 4.

Advanced settings open with the Data freshness field set to 4 hours

Turn on Static IP if your shop only accepts requests from allowed IP addresses, for example behind a firewall or Cloudflare. All data requests are then sent from one fixed IP address, 34.118.32.227, which you can add to the allowed list of your shop.

Static IP switch in the advanced settings

In Tables to collect you can remove the tables you do not need. All 18 tables described in the data schema below are selected by default, and at least one must stay selected. Collecting fewer tables makes the data retrieval faster.

Tables to collect field with all offered PrestaShop tables selected

The customers and addresses tables contain personal data of your shoppers, such as names, email addresses, postal addresses and phone numbers. Remove them from Tables to collect if you do not need them.

Click Next.

Collect settings filled in with the Next button

Historical data and scheduling

In Historical Data enter the number of days to fetch on the first run, up to 365.

Historical Data field filled in with 365 days

A data synchronization schedule is created automatically. More information on this topic can be found here.

Click Create to complete the configuration.

Data collection settings with the Create button

Done! Your PrestaShop data will now be synchronized with BigQuery. Orders, order lines, order cart rules, customers and the delivery addresses of orders are collected day by day, by the date they were created. The other tables (products, combinations, stock, categories, manufacturers, countries, currencies, order states, product options, shop URLs and cart rules) are collected in full on each run.

Data schema

The tables below show the data collected from PrestaShop. Their names in BigQuery follow the pattern prestashop_<table>_<collect ID>, for example prestashop_orders_<collect ID>.

Data schema for prestashop_addresses
Field NameModeType
idNULLABLESTRING
id_customerNULLABLESTRING
id_manufacturerNULLABLESTRING
id_supplierNULLABLESTRING
id_warehouseNULLABLESTRING
id_countryNULLABLESTRING
id_stateNULLABLESTRING
aliasNULLABLESTRING
companyNULLABLESTRING
lastnameNULLABLESTRING
firstnameNULLABLESTRING
vat_numberNULLABLESTRING
address1NULLABLESTRING
address2NULLABLESTRING
postcodeNULLABLESTRING
cityNULLABLESTRING
otherNULLABLESTRING
phoneNULLABLESTRING
phone_mobileNULLABLESTRING
dniNULLABLESTRING
deletedNULLABLEBOOLEAN
date_addNULLABLEDATETIME
date_updNULLABLEDATETIME
Data schema for prestashop_cart_rules
Field NameModeType
idNULLABLESTRING
nameNULLABLEJSON
descriptionNULLABLESTRING
codeNULLABLESTRING
quantityNULLABLESTRING
quantity_per_userNULLABLESTRING
activeNULLABLEBOOLEAN
date_fromNULLABLEDATETIME
date_toNULLABLEDATETIME
priorityNULLABLESTRING
partial_useNULLABLEBOOLEAN
minimum_amountNULLABLEFLOAT
minimum_amount_taxNULLABLEBOOLEAN
minimum_amount_currencyNULLABLESTRING
minimum_amount_shippingNULLABLEBOOLEAN
country_restrictionNULLABLEBOOLEAN
carrier_restrictionNULLABLEBOOLEAN
group_restrictionNULLABLEBOOLEAN
cart_rule_restrictionNULLABLEBOOLEAN
product_restrictionNULLABLEBOOLEAN
shop_restrictionNULLABLEBOOLEAN
free_shippingNULLABLEBOOLEAN
reduction_percentNULLABLEFLOAT
reduction_amountNULLABLEFLOAT
reduction_taxNULLABLEBOOLEAN
reduction_currencyNULLABLESTRING
reduction_productNULLABLESTRING
reduction_exclude_specialNULLABLEBOOLEAN
gift_productNULLABLESTRING
gift_product_attributeNULLABLESTRING
highlightNULLABLEBOOLEAN
date_addNULLABLEDATETIME
date_updNULLABLEDATETIME
Data schema for prestashop_categories
Field NameModeType
idNULLABLESTRING
id_parentNULLABLESTRING
activeNULLABLEBOOLEAN
id_shop_defaultNULLABLESTRING
is_root_categoryNULLABLEBOOLEAN
positionNULLABLESTRING
date_addNULLABLEDATETIME
date_updNULLABLEDATETIME
nameNULLABLEJSON
link_rewriteNULLABLEJSON
descriptionNULLABLEJSON
meta_titleNULLABLEJSON
meta_descriptionNULLABLEJSON
meta_keywordsNULLABLEJSON
associationsNULLABLERECORD
associations.categoriesREPEATEDRECORD
associations.categories.idNULLABLESTRING
associations.productsREPEATEDRECORD
associations.products.idNULLABLESTRING
Data schema for prestashop_combinations
Field NameModeType
idNULLABLESTRING
id_productNULLABLESTRING
locationNULLABLESTRING
ean13NULLABLESTRING
isbnNULLABLESTRING
upcNULLABLESTRING
mpnNULLABLESTRING
quantityNULLABLEINTEGER
referenceNULLABLESTRING
supplier_referenceNULLABLESTRING
wholesale_priceNULLABLEFLOAT
priceNULLABLEFLOAT
ecotaxNULLABLEFLOAT
weightNULLABLEFLOAT
unit_price_impactNULLABLEFLOAT
minimal_quantityNULLABLEINTEGER
low_stock_thresholdNULLABLEINTEGER
low_stock_alertNULLABLEBOOLEAN
default_onNULLABLEBOOLEAN
available_dateNULLABLESTRING
associationsNULLABLERECORD
associations.product_option_valuesREPEATEDRECORD
associations.product_option_values.idNULLABLESTRING
associations.imagesREPEATEDRECORD
associations.images.idNULLABLESTRING
Data schema for prestashop_countries
Field NameModeType
idNULLABLESTRING
id_zoneNULLABLESTRING
id_currencyNULLABLESTRING
call_prefixNULLABLESTRING
iso_codeNULLABLESTRING
activeNULLABLEBOOLEAN
contains_statesNULLABLEBOOLEAN
need_identification_numberNULLABLEBOOLEAN
need_zip_codeNULLABLEBOOLEAN
zip_code_formatNULLABLESTRING
display_tax_labelNULLABLEBOOLEAN
nameNULLABLEJSON
Data schema for prestashop_currencies
Field NameModeType
idNULLABLESTRING
namesNULLABLEJSON
nameNULLABLESTRING
symbolNULLABLEJSON
iso_codeNULLABLESTRING
numeric_iso_codeNULLABLESTRING
precisionNULLABLEFLOAT
conversion_rateNULLABLEFLOAT
deletedNULLABLEBOOLEAN
activeNULLABLEBOOLEAN
unofficialNULLABLESTRING
modifiedNULLABLEBOOLEAN
patternNULLABLEJSON
Data schema for prestashop_customers
Field NameModeType
idREQUIREDSTRING
id_default_groupNULLABLESTRING
id_langNULLABLESTRING
newsletter_date_addNULLABLESTRING
ip_registration_newsletterNULLABLESTRING
secure_keyNULLABLESTRING
deletedNULLABLEBOOLEAN
lastnameNULLABLESTRING
firstnameNULLABLESTRING
emailNULLABLESTRING
id_genderNULLABLESTRING
birthdayNULLABLESTRING
newsletterNULLABLEBOOLEAN
optinNULLABLEBOOLEAN
websiteNULLABLESTRING
companyNULLABLESTRING
siretNULLABLESTRING
apeNULLABLESTRING
outstanding_allow_amountNULLABLEFLOAT
show_public_pricesNULLABLEBOOLEAN
id_riskNULLABLESTRING
max_payment_daysNULLABLEINTEGER
activeNULLABLEBOOLEAN
noteNULLABLESTRING
is_guestNULLABLEBOOLEAN
id_shopNULLABLESTRING
id_shop_groupNULLABLESTRING
date_addNULLABLEDATETIME
date_updNULLABLEDATETIME
associationsNULLABLERECORD
associations.groupsREPEATEDRECORD
associations.groups.idNULLABLESTRING
Data schema for prestashop_manufacturers
Field NameModeType
idNULLABLEINTEGER
activeNULLABLEBOOLEAN
link_rewriteNULLABLESTRING
nameNULLABLESTRING
date_addNULLABLEDATETIME
date_updNULLABLEDATETIME
descriptionNULLABLEJSON
short_descriptionNULLABLEJSON
meta_titleNULLABLEJSON
meta_descriptionNULLABLEJSON
meta_keywordsNULLABLEJSON
Data schema for prestashop_order_cart_rules
Field NameModeType
idNULLABLESTRING
id_orderNULLABLESTRING
id_cart_ruleNULLABLESTRING
id_order_invoiceNULLABLESTRING
nameNULLABLESTRING
valueNULLABLEFLOAT
value_tax_exclNULLABLEFLOAT
free_shippingNULLABLEBOOLEAN
Data schema for prestashop_order_details
Field NameModeType
idNULLABLESTRING
id_orderNULLABLESTRING
product_idNULLABLESTRING
product_attribute_idNULLABLESTRING
product_quantity_reinjectedNULLABLESTRING
group_reductionNULLABLEFLOAT
discount_quantity_appliedNULLABLESTRING
download_hashNULLABLESTRING
download_deadlineNULLABLESTRING
id_order_invoiceNULLABLESTRING
id_warehouseNULLABLESTRING
id_shopNULLABLESTRING
id_customizationNULLABLESTRING
product_nameNULLABLESTRING
product_quantityNULLABLESTRING
product_quantity_in_stockNULLABLESTRING
product_quantity_returnNULLABLESTRING
product_quantity_refundedNULLABLESTRING
product_priceNULLABLEFLOAT
reduction_percentNULLABLEFLOAT
reduction_amountNULLABLEFLOAT
reduction_amount_tax_inclNULLABLEFLOAT
reduction_amount_tax_exclNULLABLEFLOAT
product_quantity_discountNULLABLEFLOAT
product_ean13NULLABLESTRING
product_isbnNULLABLESTRING
product_upcNULLABLESTRING
product_mpnNULLABLESTRING
product_referenceNULLABLESTRING
product_supplier_referenceNULLABLESTRING
product_weightNULLABLEFLOAT
tax_computation_methodNULLABLESTRING
id_tax_rules_groupNULLABLESTRING
ecotaxNULLABLEFLOAT
ecotax_tax_rateNULLABLEFLOAT
download_nbNULLABLESTRING
unit_price_tax_inclNULLABLEFLOAT
unit_price_tax_exclNULLABLEFLOAT
total_price_tax_inclNULLABLEFLOAT
total_price_tax_exclNULLABLEFLOAT
total_shipping_price_tax_exclNULLABLEFLOAT
total_shipping_price_tax_inclNULLABLEFLOAT
purchase_supplier_priceNULLABLEFLOAT
original_product_priceNULLABLEFLOAT
original_wholesale_priceNULLABLEFLOAT
total_refunded_tax_exclNULLABLEFLOAT
total_refunded_tax_inclNULLABLEFLOAT
associationsNULLABLERECORD
associations.taxesREPEATEDRECORD
associations.taxes.idNULLABLESTRING
Data schema for prestashop_order_states
Field NameModeType
idNULLABLESTRING
unremovableNULLABLESTRING
deliveryNULLABLESTRING
hiddenNULLABLEBOOLEAN
send_emailNULLABLEBOOLEAN
module_nameNULLABLESTRING
invoiceNULLABLEBOOLEAN
colorNULLABLESTRING
logableNULLABLEBOOLEAN
shippedNULLABLEBOOLEAN
paidNULLABLEBOOLEAN
pdf_deliveryNULLABLEBOOLEAN
pdf_invoiceNULLABLEBOOLEAN
deletedNULLABLEBOOLEAN
nameNULLABLEJSON
templateNULLABLEJSON
Data schema for prestashop_orders
Field NameModeType
idNULLABLESTRING
id_address_deliveryNULLABLESTRING
id_address_invoiceNULLABLESTRING
id_cartNULLABLESTRING
id_currencyNULLABLESTRING
id_langNULLABLESTRING
id_customerNULLABLESTRING
id_carrierNULLABLESTRING
current_stateNULLABLESTRING
moduleNULLABLESTRING
invoice_numberNULLABLESTRING
invoice_dateNULLABLESTRING
delivery_numberNULLABLESTRING
delivery_dateNULLABLESTRING
validNULLABLEBOOLEAN
date_addNULLABLEDATETIME
date_updNULLABLEDATETIME
shipping_numberNULLABLESTRING
noteNULLABLESTRING
id_shop_groupNULLABLESTRING
id_shopNULLABLESTRING
secure_keyNULLABLESTRING
paymentNULLABLESTRING
recyclableNULLABLEBOOLEAN
giftNULLABLEBOOLEAN
gift_messageNULLABLESTRING
mobile_themeNULLABLEBOOLEAN
total_discountsNULLABLEFLOAT
total_discounts_tax_inclNULLABLEFLOAT
total_discounts_tax_exclNULLABLEFLOAT
total_paidNULLABLEFLOAT
total_paid_tax_inclNULLABLEFLOAT
total_paid_tax_exclNULLABLEFLOAT
total_paid_realNULLABLEFLOAT
total_productsNULLABLEFLOAT
total_products_wtNULLABLEFLOAT
total_shippingNULLABLEFLOAT
total_shipping_tax_inclNULLABLEFLOAT
total_shipping_tax_exclNULLABLEFLOAT
carrier_tax_rateNULLABLEFLOAT
total_wrappingNULLABLEFLOAT
total_wrapping_tax_inclNULLABLEFLOAT
total_wrapping_tax_exclNULLABLEFLOAT
round_modeNULLABLESTRING
round_typeNULLABLESTRING
conversion_rateNULLABLEFLOAT
referenceNULLABLESTRING
associationsNULLABLERECORD
associations.order_rowsREPEATEDRECORD
associations.order_rows.idNULLABLESTRING
associations.order_rows.product_idNULLABLESTRING
associations.order_rows.product_attribute_idNULLABLESTRING
associations.order_rows.product_quantityNULLABLESTRING
associations.order_rows.product_nameNULLABLESTRING
associations.order_rows.product_referenceNULLABLESTRING
associations.order_rows.product_ean13NULLABLESTRING
associations.order_rows.product_isbnNULLABLESTRING
associations.order_rows.product_upcNULLABLESTRING
associations.order_rows.product_priceNULLABLESTRING
associations.order_rows.id_customizationNULLABLESTRING
associations.order_rows.unit_price_tax_inclNULLABLESTRING
associations.order_rows.unit_price_tax_exclNULLABLESTRING
Data schema for prestashop_product_option_values
Field NameModeType
idNULLABLESTRING
id_attribute_groupNULLABLESTRING
colorNULLABLESTRING
positionNULLABLESTRING
nameNULLABLEJSON
Data schema for prestashop_product_options
Field NameModeType
idNULLABLESTRING
is_color_groupNULLABLEBOOLEAN
group_typeNULLABLESTRING
positionNULLABLESTRING
nameNULLABLEJSON
public_nameNULLABLEJSON
associationsNULLABLERECORD
associations.product_option_valuesREPEATEDRECORD
associations.product_option_values.idNULLABLESTRING
Data schema for prestashop_products
Field NameModeType
idNULLABLESTRING
id_manufacturerNULLABLESTRING
id_supplierNULLABLESTRING
id_category_defaultNULLABLESTRING
newNULLABLESTRING
cache_default_attributeNULLABLESTRING
id_default_imageNULLABLESTRING
id_default_combinationNULLABLESTRING
id_tax_rules_groupNULLABLESTRING
position_in_categoryNULLABLESTRING
typeNULLABLESTRING
id_shop_defaultNULLABLESTRING
referenceNULLABLESTRING
supplier_referenceNULLABLESTRING
locationNULLABLESTRING
widthNULLABLEFLOAT
heightNULLABLEFLOAT
depthNULLABLEFLOAT
weightNULLABLEFLOAT
quantity_discountNULLABLEBOOLEAN
ean13NULLABLESTRING
isbnNULLABLESTRING
upcNULLABLESTRING
mpnNULLABLESTRING
cache_is_packNULLABLEBOOLEAN
cache_has_attachmentsNULLABLEBOOLEAN
is_virtualNULLABLEBOOLEAN
stateNULLABLESTRING
additional_delivery_timesNULLABLESTRING
delivery_in_stockNULLABLEJSON
delivery_out_stockNULLABLEJSON
product_typeNULLABLESTRING
on_saleNULLABLEBOOLEAN
online_onlyNULLABLEBOOLEAN
ecotaxNULLABLEFLOAT
minimal_quantityNULLABLEINTEGER
low_stock_thresholdNULLABLESTRING
low_stock_alertNULLABLEBOOLEAN
priceNULLABLEFLOAT
wholesale_priceNULLABLEFLOAT
unityNULLABLESTRING
unit_price_ratioNULLABLESTRING
additional_shipping_costNULLABLEFLOAT
customizableNULLABLESTRING
text_fieldsNULLABLESTRING
uploadable_filesNULLABLESTRING
activeNULLABLEBOOLEAN
redirect_typeNULLABLESTRING
id_type_redirectedNULLABLESTRING
available_for_orderNULLABLESTRING
available_dateNULLABLESTRING
show_conditionNULLABLEBOOLEAN
conditionNULLABLESTRING
show_priceNULLABLEBOOLEAN
indexedNULLABLEBOOLEAN
visibilityNULLABLESTRING
advanced_stock_managementNULLABLEBOOLEAN
date_addNULLABLEDATETIME
date_updNULLABLEDATETIME
pack_stock_typeNULLABLESTRING
meta_descriptionNULLABLEJSON
meta_keywordsNULLABLEJSON
meta_titleNULLABLEJSON
link_rewriteNULLABLEJSON
nameNULLABLEJSON
descriptionNULLABLEJSON
description_shortNULLABLEJSON
available_nowNULLABLEJSON
available_laterNULLABLEJSON
associationsNULLABLERECORD
associations.categoriesREPEATEDRECORD
associations.categories.idNULLABLESTRING
associations.imagesREPEATEDRECORD
associations.images.idNULLABLESTRING
associations.combinationsREPEATEDRECORD
associations.combinations.idNULLABLESTRING
associations.product_option_valuesREPEATEDRECORD
associations.product_option_values.idNULLABLESTRING
associations.product_featuresREPEATEDRECORD
associations.product_features.idNULLABLESTRING
associations.product_features.id_feature_valueNULLABLESTRING
associations.tagsREPEATEDRECORD
associations.tags.idNULLABLESTRING
associations.stock_availablesREPEATEDRECORD
associations.stock_availables.idNULLABLESTRING
associations.stock_availables.id_product_attributeNULLABLESTRING
associations.attachmentsREPEATEDRECORD
associations.attachments.idNULLABLESTRING
associations.accessoriesREPEATEDRECORD
associations.accessories.idNULLABLESTRING
associations.product_bundleREPEATEDRECORD
associations.product_bundle.idNULLABLESTRING
associations.product_bundle.id_product_attributeNULLABLESTRING
associations.product_bundle.quantityNULLABLEINTEGER
Data schema for prestashop_shop_urls
Field NameModeType
idNULLABLESTRING
id_shopNULLABLESTRING
activeNULLABLEBOOLEAN
mainNULLABLEBOOLEAN
domainNULLABLESTRING
domain_sslNULLABLESTRING
physical_uriNULLABLESTRING
virtual_uriNULLABLESTRING
Data schema for prestashop_stock_availables
Field NameModeType
idREQUIREDSTRING
id_productREQUIREDSTRING
id_product_attributeNULLABLESTRING
id_shopREQUIREDSTRING
id_shop_groupREQUIREDSTRING
quantityNULLABLEINTEGER
depends_on_stockNULLABLEBOOLEAN
out_of_stockNULLABLEINTEGER
locationNULLABLESTRING
Data schema for prestashop_stocks
Field NameModeType
idNULLABLESTRING
id_warehouseNULLABLESTRING
id_productNULLABLESTRING
id_product_attributeNULLABLESTRING
referenceNULLABLESTRING
ean13NULLABLESTRING
isbnNULLABLESTRING
upcNULLABLESTRING
mpnNULLABLESTRING
physical_quantityNULLABLEINTEGER
usable_quantityNULLABLEINTEGER
price_teNULLABLEFLOAT