Integration of Shoper with BigQuery

Learn how to connect Shoper to Google BigQuery using WitCloud to collect orders, order products, products, categories, stock and other shop data from your store.

Updated

Introduction

This module connects your Shoper store to Google Cloud BigQuery. WitCloud reads your orders and the products in them, your product catalogue (products, images, categories, stock levels, producers) and the shop's settings (statuses, currencies, payment and shipping methods, taxes) through the Shoper API and stores them in your project's BigQuery dataset. You can then analyse sales, products and stock together with your advertising data.

Configuration guide

Follow the steps below to connect Shoper with BigQuery. WitCloud needs the address of your store and the username and password of a Shoper account that can use Shoper's REST API (WebAPI).

Prepare the Shoper account before you start: you enter its username and password in the Authorization step. Only data is read from your store; WitCloud does not change anything in it.

Prepare the Shoper account

This step takes place in your Shoper administration panel, which is not part of WitCloud, so it is not shown here. How to create an account with access to the Shoper REST API (WebAPI) is described in Shoper's own documentation.

Make sure you have the username and the password of such an account. Keep them private: anyone who has them can read your store data through the API.

Module setup

Go to Integrations and click Add integration.

Integrations list with the Add integration button

Select Shoper from the list of available integrations.

Integration catalog filtered to Shoper

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

Collect name field filled in with the example name Shoper PL

Enter the Shop url: the address of your store, 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 Shoper store in WitCloud, you can pick the existing token from the Shoper authorization drop-down and skip the next two steps.

Click Authorize.

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

In the Authorize pop-up enter the Username and the password of your Shoper account and click Create. WitCloud checks them against your store and saves them so that it can renew access later. If the password is wrong, the shop address cannot be reached or is not valid, the reason is shown under the form.

Authorize pop-up with the Username and password fields filled with example values

The saved token is selected automatically and is named after your shop address and username. You can also choose a token from the Shoper authorization drop-down.

Shoper 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, the tables that hold the whole catalogue (products, product images and stock) are refreshed. A table that was updated more recently than that is skipped on the next run. 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 store sits behind extra network protection such as 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 store.

Static IP switch in the advanced settings

In Tables to collect you can remove the tables you do not need. The field offers orders, order_products, products, product_images, categories, categories_tree, stock, producers, statuses, currencies, option_values, application_config, payments, shippings and taxes; all of them are selected by default and at least one must stay selected.

Tables to collect field with all offered tables selected

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 Shoper data will now be synchronized with BigQuery. Orders and order products are collected day by day, so the historical days are filled in for them; all the other tables are collected in full on each run.

Data schema

Expand the tables below to view the full data schema collected from Shoper.

Data schema for shoper_application_config
Field NameModeType
is_mobileNULLABLEBOOLEAN
is_facebookNULLABLEBOOLEAN
shop_urlNULLABLESTRING
shop_emailNULLABLESTRING
shop_company_nameNULLABLESTRING
shop_phoneNULLABLESTRING
shop_tax_idNULLABLESTRING
shop_address_1NULLABLESTRING
shop_address_2NULLABLESTRING
shop_cityNULLABLESTRING
shop_zip_codeNULLABLESTRING
shop_provinceNULLABLESTRING
shop_countryNULLABLESTRING
shop_tradeNULLABLESTRING
shop_regonNULLABLESTRING
shop_offNULLABLESTRING
shop_nameNULLABLESTRING
shop_full_addressNULLABLESTRING
shop_trade_codeNULLABLESTRING
product_defaults_tax_idNULLABLESTRING
product_defaults_codeNULLABLESTRING
product_defaults_stockNULLABLESTRING
product_defaults_warn_levelNULLABLESTRING
product_defaults_orderNULLABLESTRING
product_defaults_activeNULLABLESTRING
product_defaults_weightNULLABLESTRING
product_defaults_unit_idNULLABLESTRING
product_defaults_availability_idNULLABLESTRING
product_defaults_delivery_idNULLABLESTRING
product_defaults_bundle_unit_idNULLABLESTRING
license_idNULLABLESTRING
shopping_vat_eu_enableNULLABLESTRING
shopping_allow_product_different_currencyNULLABLESTRING
shopping_price_levelsNULLABLESTRING
shopping_save_basketNULLABLESTRING
shopping_update_stock_on_buyNULLABLESTRING
shopping_basket_addingNULLABLESTRING
shopping_allow_to_buy_not_regNULLABLESTRING
shopping_order_via_tokenNULLABLESTRING
shopping_confirm_orderNULLABLESTRING
shopping_min_prod_quantityNULLABLESTRING
shopping_min_order_valueNULLABLESTRING
shopping_offNULLABLESTRING
shopping_products_allow_zeroNULLABLESTRING
shopping_allow_oversellingNULLABLESTRING
shopping_parcel_create_status_idNULLABLESTRING
shopping_parcel_send_status_idNULLABLESTRING
shopping_change_status_payment_made_idNULLABLESTRING
shopping_change_status_refund_made_idNULLABLESTRING
shopping_shipping_extra_stepNULLABLESTRING
shopping_newproducts_daysNULLABLESTRING
shopping_newproducts_modeNULLABLESTRING
shopping_bestseller_daysNULLABLESTRING
shopping_bestseller_modeNULLABLESTRING
shopping_bestseller_algorithmNULLABLESTRING
shopping_price_comparision_fieldNULLABLESTRING
shopping_volumetric_weight_enableNULLABLESTRING
shopping_promo_codes_enableNULLABLESTRING
shopping_peselNULLABLESTRING
product_notifies_enableNULLABLESTRING
registration_enableNULLABLESTRING
registration_confirmNULLABLESTRING
registration_login_to_see_priceNULLABLESTRING
registration_new_cust_discountNULLABLESTRING
registration_require_addressNULLABLESTRING
shipping_add_payment_cost_to_free_shippingNULLABLESTRING
shipping_volumetric_weight_enableNULLABLESTRING
product_search_typeNULLABLESTRING
product_search_all_tokensNULLABLESTRING
product_search_codeNULLABLESTRING
product_search_short_descriptionNULLABLESTRING
product_search_descriptionNULLABLESTRING
digital_product_unlocking_status_idNULLABLESTRING
digital_product_link_expiration_timeNULLABLESTRING
digital_product_number_of_downloadsNULLABLESTRING
comment_enableNULLABLESTRING
comment_for_usersNULLABLESTRING
comment_moderationNULLABLESTRING
loyalty_enableNULLABLESTRING
blog_items_per_pageNULLABLESTRING
blog_comments_enableNULLABLESTRING
blog_comments_for_usersNULLABLESTRING
blog_comments_moderationNULLABLESTRING
blog_download_enableNULLABLESTRING
blog_download_for_usersNULLABLESTRING
blog_news_default_url_formatNULLABLESTRING
blog_category_default_url_formatNULLABLESTRING
blog_use_new_url_namespaceNULLABLESTRING
locale_timezoneNULLABLESTRING
locale_default_weightNULLABLESTRING
default_language_nameNULLABLESTRING
default_language_idNULLABLESTRING
default_currency_idNULLABLESTRING
default_currency_nameNULLABLESTRING
warehouses_enabledNULLABLEBOOLEAN
Data schema for shoper_categories
Field NameModeType
category_idNULLABLESTRING
rootNULLABLEBOOLEAN
orderNULLABLEINTEGER
translationsREPEATEDRECORD
├─ translations.languageNULLABLESTRING
├─ translations.trans_idNULLABLESTRING
├─ translations.category_idNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
├─ translations.nameNULLABLESTRING
├─ translations.descriptionNULLABLESTRING
├─ translations.description_bottomNULLABLESTRING
├─ translations.seo_titleNULLABLESTRING
├─ translations.seo_descriptionNULLABLESTRING
├─ translations.seo_keywordsNULLABLESTRING
├─ translations.seo_urlNULLABLESTRING
├─ translations.permalinkNULLABLESTRING
├─ translations.activeNULLABLEBOOLEAN
├─ translations.isdefaultNULLABLEBOOLEAN
├─ translations.itemsNULLABLEINTEGER
Data schema for shoper_categories_tree
Field NameModeType
parent_idNULLABLESTRING
category_idNULLABLESTRING
category_pathREPEATEDSTRING
Data schema for shoper_currencies
Field NameModeType
currency_idNULLABLESTRING
nameNULLABLESTRING
orderNULLABLESTRING
activeNULLABLEBOOLEAN
rate_syncNULLABLESTRING
rate_dateNULLABLEDATE
defaultNULLABLEBOOLEAN
Data schema for shoper_option_values
Field NameModeType
ovalue_idNULLABLESTRING
option_idNULLABLESTRING
orderNULLABLESTRING
total_productsNULLABLEINTEGER
change_price_typeNULLABLESTRING
change_price_valueNULLABLEFLOAT
percentNULLABLESTRING
translationsREPEATEDRECORD
├─ translations.translation_idNULLABLESTRING
├─ translations.ovalue_idNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
├─ translations.valueNULLABLESTRING
Data schema for shoper_order_products
Field NameModeType
idNULLABLESTRING
order_idNULLABLESTRING
product_idNULLABLESTRING
typeNULLABLESTRING
stock_idNULLABLESTRING
priceNULLABLEFLOAT
discount_percNULLABLEFLOAT
quantityNULLABLEINTEGER
delivery_timeNULLABLEINTEGER
delivery_time_hoursNULLABLEFLOAT
nameNULLABLESTRING
codeNULLABLESTRING
pkwiuNULLABLESTRING
taxNULLABLESTRING
tax_valueNULLABLEFLOAT
unitNULLABLESTRING
optionNULLABLESTRING
unit_fpNULLABLEBOOLEAN
weightNULLABLEFLOAT
loyaltyNULLABLERECORD
├─ loyalty.exchangedNULLABLEBOOLEAN
├─ loyalty.scoreNULLABLEINTEGER
├─ loyalty.ratioNULLABLESTRING
childrenREPEATEDRECORD
├─ children.idNULLABLESTRING
├─ children.order_idNULLABLESTRING
├─ children.stock_idNULLABLESTRING
├─ children.priceNULLABLEFLOAT
├─ children.discount_percNULLABLEFLOAT
├─ children.quantityNULLABLEINTEGER
├─ children.delivery_timeNULLABLEINTEGER
├─ children.nameNULLABLESTRING
├─ children.codeNULLABLESTRING
├─ children.pkwiuNULLABLESTRING
├─ children.taxNULLABLESTRING
├─ children.tax_valueNULLABLEFLOAT
├─ children.unitNULLABLESTRING
├─ children.optionNULLABLESTRING
├─ children.unit_fpNULLABLEBOOLEAN
├─ children.weightNULLABLEFLOAT
Data schema for shoper_orders
Field NameModeType
order_idNULLABLESTRING
user_idNULLABLESTRING
dateNULLABLETIMESTAMP
status_dateNULLABLETIMESTAMP
confirm_dateNULLABLETIMESTAMP
delivery_dateNULLABLETIMESTAMP
status_idNULLABLESTRING
sumNULLABLEFLOAT
payment_idNULLABLESTRING
user_orderNULLABLEBOOLEAN
shipping_idNULLABLESTRING
shipping_costNULLABLEFLOAT
emailNULLABLESTRING
codeNULLABLESTRING
confirmNULLABLEBOOLEAN
notesNULLABLESTRING
notes_privNULLABLESTRING
notes_pubNULLABLESTRING
currency_idNULLABLESTRING
currency_rateNULLABLEFLOAT
paidNULLABLEFLOAT
discount_clientNULLABLEFLOAT
discount_groupNULLABLEFLOAT
discount_levelsNULLABLEFLOAT
discount_codeNULLABLEFLOAT
code_idNULLABLESTRING
lang_idNULLABLESTRING
originNULLABLESTRING
registeredNULLABLEBOOLEAN
promo_codeNULLABLESTRING
is_paidNULLABLEBOOLEAN
is_overpaymentNULLABLEBOOLEAN
is_underpaymentNULLABLEBOOLEAN
total_parcelsNULLABLEINTEGER
total_productsNULLABLEINTEGER
loyalty_costNULLABLEINTEGER
loyalty_scoreNULLABLEINTEGER
shipping_tax_nameNULLABLESTRING
shipping_tax_valueNULLABLEFLOAT
shipping_tax_idNULLABLESTRING
delivery_addressNULLABLERECORD
├─ delivery_address.address_idNULLABLESTRING
├─ delivery_address.order_idNULLABLESTRING
├─ delivery_address.typeNULLABLESTRING
├─ delivery_address.cityNULLABLESTRING
├─ delivery_address.postcodeNULLABLESTRING
├─ delivery_address.stateNULLABLESTRING
├─ delivery_address.countryNULLABLESTRING
├─ delivery_address.phoneNULLABLESTRING
├─ delivery_address.country_codeNULLABLESTRING
├─ delivery_address.tax_identification_numberNULLABLESTRING
billing_addressNULLABLERECORD
├─ billing_address.address_idNULLABLESTRING
├─ billing_address.order_idNULLABLESTRING
├─ billing_address.typeNULLABLESTRING
├─ billing_address.cityNULLABLESTRING
├─ billing_address.postcodeNULLABLESTRING
├─ billing_address.stateNULLABLESTRING
├─ billing_address.countryNULLABLESTRING
├─ billing_address.phoneNULLABLESTRING
├─ billing_address.country_codeNULLABLESTRING
├─ billing_address.tax_identification_numberNULLABLESTRING
Data schema for shoper_payments
Field NameModeType
payment_idNULLABLESTRING
nameNULLABLESTRING
orderNULLABLESTRING
installNULLABLEBOOLEAN
visibleNULLABLEBOOLEAN
currenciesREPEATEDSTRING
translationsREPEATEDRECORD
├─ translations.languageNULLABLESTRING
├─ translations.translation_idNULLABLESTRING
├─ translations.payment_idNULLABLESTRING
├─ translations.titleNULLABLESTRING
├─ translations.descriptionNULLABLESTRING
├─ translations.vendor_descriptionNULLABLESTRING
├─ translations.activeNULLABLESTRING
├─ translations.notifyNULLABLESTRING
├─ translations.notify_mailNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
Data schema for shoper_producers
Field NameModeType
producer_idNULLABLESTRING
nameNULLABLESTRING
webNULLABLESTRING
gfxNULLABLESTRING
isdefaultNULLABLEBOOLEAN
translationsREPEATEDRECORD
├─ translations.languageNULLABLESTRING
├─ translations.translation_idNULLABLESTRING
├─ translations.producer_idNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
├─ translations.nameNULLABLESTRING
├─ translations.descriptionNULLABLESTRING
├─ translations.seo_titleNULLABLESTRING
├─ translations.seo_descriptionNULLABLESTRING
├─ translations.seo_keywordsNULLABLESTRING
├─ translations.seo_urlNULLABLESTRING
Data schema for shoper_product_images
Field NameModeType
gfx_idNULLABLESTRING
product_idNULLABLESTRING
mainNULLABLEBOOLEAN
orderNULLABLESTRING
unic_nameNULLABLESTRING
hiddenNULLABLEBOOLEAN
extensionNULLABLESTRING
translationsREPEATEDRECORD
├─ translations.gfx_idNULLABLESTRING
├─ translations.nameNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
Data schema for shoper_products
Field NameModeType
product_idNULLABLESTRING
typeNULLABLESTRING
producer_idNULLABLESTRING
group_idNULLABLESTRING
tax_idNULLABLESTRING
category_idNULLABLESTRING
unit_idNULLABLESTRING
add_dateNULLABLETIMESTAMP
edit_dateNULLABLETIMESTAMP
other_priceNULLABLEFLOAT
promo_priceNULLABLEFLOAT
codeNULLABLESTRING
dimension_wNULLABLEFLOAT
dimension_hNULLABLEFLOAT
dimension_lNULLABLEFLOAT
eanNULLABLESTRING
pkwiuNULLABLESTRING
is_product_of_dayNULLABLEBOOLEAN
loyalty_scoreNULLABLEINTEGER
loyalty_priceNULLABLEINTEGER
in_loyaltyNULLABLEBOOLEAN
bestsellerNULLABLEBOOLEAN
newproductNULLABLEBOOLEAN
vol_weightNULLABLEFLOAT
gauge_idNULLABLESTRING
currency_idNULLABLESTRING
additional_isbnNULLABLESTRING
additional_kgoNULLABLESTRING
additional_bloz7NULLABLESTRING
additional_bloz12NULLABLESTRING
additional_gtuNULLABLESTRING
additional_producerNULLABLESTRING
relatedREPEATEDSTRING
optionsREPEATEDSTRING
categoriesREPEATEDSTRING
unit_price_calculationNULLABLEBOOLEAN
main_imageNULLABLERECORD
├─ main_image.gfx_idNULLABLESTRING
├─ main_image.orderNULLABLEINTEGER
├─ main_image.nameNULLABLESTRING
├─ main_image.unic_nameNULLABLESTRING
├─ main_image.hiddenNULLABLEBOOLEAN
stockNULLABLERECORD
├─ stock.stock_idNULLABLESTRING
├─ stock.product_idNULLABLESTRING
├─ stock.extendedNULLABLEBOOLEAN
├─ stock.priceNULLABLEFLOAT
├─ stock.activeNULLABLEBOOLEAN
├─ stock.defaultNULLABLEBOOLEAN
├─ stock.stockNULLABLEFLOAT
├─ stock.warn_levelNULLABLEFLOAT
├─ stock.soldNULLABLEFLOAT
├─ stock.codeNULLABLESTRING
├─ stock.eanNULLABLESTRING
├─ stock.weightNULLABLEFLOAT
├─ stock.weight_typeNULLABLESTRING
├─ stock.availability_idNULLABLESTRING
├─ stock.delivery_idNULLABLESTRING
├─ stock.gfx_idNULLABLESTRING
├─ stock.packageNULLABLEFLOAT
├─ stock.price_wholesaleNULLABLEFLOAT
├─ stock.price_specialNULLABLEFLOAT
├─ stock.calculation_unit_idNULLABLESTRING
├─ stock.calculation_unit_ratioNULLABLEFLOAT
special_offerNULLABLERECORD
├─ special_offer.promo_idNULLABLESTRING
├─ special_offer.date_fromNULLABLETIMESTAMP
├─ special_offer.date_toNULLABLETIMESTAMP
├─ special_offer.discountNULLABLEFLOAT
├─ special_offer.discount_wholesaleNULLABLEFLOAT
├─ special_offer.discount_specialNULLABLEFLOAT
childrenREPEATEDRECORD
├─ children.idNULLABLESTRING
├─ children.bundle_idNULLABLESTRING
├─ children.stock_idNULLABLESTRING
├─ children.product_idNULLABLESTRING
├─ children.stockNULLABLEFLOAT
├─ children.orderNULLABLEINTEGER
translationsREPEATEDRECORD
├─ translations.languageNULLABLESTRING
├─ translations.translation_idNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
├─ translations.nameNULLABLESTRING
├─ translations.descriptionNULLABLESTRING
├─ translations.short_descriptionNULLABLESTRING
├─ translations.activeNULLABLEBOOLEAN
├─ translations.seo_titleNULLABLESTRING
├─ translations.seo_descriptionNULLABLESTRING
├─ translations.seo_keywordsNULLABLESTRING
├─ translations.seo_urlNULLABLESTRING
├─ translations.permalinkNULLABLESTRING
Data schema for shoper_shippings
Field NameModeType
shipping_idNULLABLESTRING
costNULLABLEFLOAT
depend_on_wNULLABLESTRING
tax_idNULLABLESTRING
max_weightNULLABLEFLOAT
min_weightNULLABLEFLOAT
free_shippingNULLABLEFLOAT
orderNULLABLESTRING
pkwiuNULLABLESTRING
max_costNULLABLEFLOAT
visibleNULLABLEBOOLEAN
mobileNULLABLEBOOLEAN
engineNULLABLESTRING
zone_idNULLABLESTRING
callback_urlNULLABLESTRING
warehouse_idNULLABLESTRING
nameNULLABLESTRING
urlNULLABLESTRING
descriptionNULLABLESTRING
vendor_descriptionNULLABLESTRING
activeNULLABLEBOOLEAN
is_defaultNULLABLEBOOLEAN
lang_idNULLABLESTRING
countriesREPEATEDSTRING
rangesREPEATEDRECORD
├─ ranges.range_idNULLABLESTRING
├─ ranges.fromNULLABLESTRING
├─ ranges.toNULLABLESTRING
├─ ranges.priceNULLABLESTRING
translationsREPEATEDRECORD
├─ translations.languageNULLABLESTRING
├─ translations.translation_idNULLABLESTRING
├─ translations.shipping_idNULLABLESTRING
├─ translations.nameNULLABLESTRING
├─ translations.urlNULLABLESTRING
├─ translations.descriptionNULLABLESTRING
├─ translations.vendor_descriptionNULLABLESTRING
├─ translations.activeNULLABLESTRING
├─ translations.is_defaultNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
paymentsREPEATEDRECORD
├─ payments.costNULLABLEFLOAT
├─ payments.percentNULLABLESTRING
├─ payments.payment_idNULLABLESTRING
Data schema for shoper_statuses
Field NameModeType
status_idNULLABLESTRING
activeNULLABLEBOOLEAN
defaultNULLABLEBOOLEAN
colorNULLABLESTRING
typeNULLABLESTRING
orderNULLABLESTRING
translationsREPEATEDRECORD
├─ translations.trans_idNULLABLESTRING
├─ translations.status_idNULLABLESTRING
├─ translations.nameNULLABLESTRING
├─ translations.lang_idNULLABLESTRING
├─ translations.messageNULLABLESTRING
├─ translations.message_htmlNULLABLESTRING
Data schema for shoper_stock
Field NameModeType
stock_idNULLABLESTRING
product_idNULLABLESTRING
extendedNULLABLEBOOLEAN
priceNULLABLEFLOAT
activeNULLABLEBOOLEAN
defaultNULLABLEBOOLEAN
stockNULLABLEFLOAT
warn_levelNULLABLEFLOAT
soldNULLABLEFLOAT
codeNULLABLESTRING
eanNULLABLESTRING
weightNULLABLEFLOAT
weight_typeNULLABLESTRING
availability_idNULLABLESTRING
delivery_idNULLABLESTRING
gfx_idNULLABLESTRING
packageNULLABLEFLOAT
price_wholesaleNULLABLEFLOAT
price_specialNULLABLEFLOAT
price_type_specialNULLABLEINTEGER
price_type_wholesaleNULLABLEINTEGER
price_buyingNULLABLEFLOAT
calculation_unit_idNULLABLESTRING
calculation_unit_ratioNULLABLEFLOAT
warehousesREPEATEDRECORD
├─ warehouses.idNULLABLESTRING
├─ warehouses.stockNULLABLEFLOAT
optionsREPEATEDRECORD
├─ options.option_idNULLABLESTRING
├─ options.value_idNULLABLESTRING
Data schema for shoper_taxes
Field NameModeType
tax_idNULLABLESTRING
valueNULLABLEINTEGER
nameNULLABLESTRING
classNULLABLESTRING