mail_outline

Looker Studio connector
for PrestaShop

Orders

Order IDid_order
(NUMBER)		* select = 'o.`id_order`';
Cart IDid_cart
(NUMBER)		* select = 'o.`id_cart`';
Referencereference
(TEXT)		* select = 'o.`reference`';
Totaltotal_paid_tax_incl
(NUMBER)		* select = 'o.`total_paid_tax_incl`';
Total HTtotal_paid_tax_excl
(NUMBER)		* select = 'o.`total_paid_tax_excl`';
Products TTCtotal_products_wt
(NUMBER)		* select = 'o.`total_products_wt`';
Products HTtotal_products
(NUMBER)		* select = 'o.`total_products`';
Shipping TTCtotal_shipping_tax_incl
(NUMBER)		* select = 'o.`total_shipping_tax_incl`';
Shipping HTtotal_shipping_tax_excl
(NUMBER)		* select = 'o.`total_shipping_tax_excl`';
Discount TTCtotal_discounts_tax_incl
(NUMBER)		* select = 'o.`total_discounts_tax_incl`';
Discount HTtotal_discounts_tax_excl
(NUMBER)		* select = 'o.`total_discounts_tax_excl`';
Wrapping TTCtotal_wrapping_tax_incl
(NUMBER)		* select = 'o.`total_wrapping_tax_incl`';
Wrapping HTtotal_wrapping_tax_excl
(NUMBER)		* select = 'o.`total_wrapping_tax_excl`';
Promotional codes code
(TEXT)		* select = 'COALESCE(GROUP_CONCAT(cr.code SEPARATOR ','), '') as `code`';
               'LEFT JOIN `ps_order_cart_rule` ocr
                ON ocr.id_order = o.id_order
               LEFT JOIN `ps_cart_rule` cr
               ON cr.id_cart_rule = ocr.id_cart_rule
               ';
Order datedate_add
(YEAR_MONTH_DAY_HOUR)		* select = 'DATE_FORMAT(o.date_add, '%Y%m%d%H') as date_add';
Payment methodpayment
(TEXT)		* select = 'o.`payment`';
Payment module module
(TEXT)		* select = 'o.`module`';
Currencycurrency
(TEXT)		* select = 'COALESCE(`currency_lang`.name, '') as `currency`';
* join = 'LEFT JOIN `ps_currency` `currency`
               ON `currency`.id_currency = o.id_currency
                LEFT JOIN `ps_currency_lang` `currency_lang`
               ON `currency_lang`.id_currency = o.id_currency
               AND `currency_lang`.id_lang = 1';
Currency codecurrency_code
(TEXT)		* select = 'COALESCE(`currency_code`.iso_code, '') as `currency_code`';
* join = 'LEFT JOIN `ps_currency` `currency_code`
               ON `currency_code`.id_currency = o.id_currency';
Statusstatus
(TEXT)		* select = 'COALESCE(osl.name, '') as `status`';
* join = 'LEFT JOIN `ps_order_state_lang` osl
               ON (osl.id_order_state = o.current_state AND osl.id_lang = 1)';
Newnew
(BOOLEAN)		* select = 'IF ((
               SELECT so.id_order FROM `'. _DB_PREFIX_.'orders` so
               WHERE so.id_customer = o.id_customer
               AND so.id_order < o.id_order LIMIT 1) > 0, 'false', 'true') as `new`';
Giftgift
(BOOLEAN)		* select = 'IF (o.gift > 0, 'true', 'false') as `gift`';
Customer IDid_customer
(TEXT)		* select = 'o.`id_customer`';
Customer namecustomer
(TEXT)		* select = 'CONCAT(
               IFNULL(`customer`.firstname, ''), ' ', IFNULL(`customer`.lastname, '')
               ) as `customer`';
* join = 'LEFT JOIN `ps_customer` `customer`
               ON (`customer`.id_customer = o.id_customer)';
Zip codepostcode
(TEXT)		* select = 'COALESCE(`address_postcode`.postcode, '') as `postcode`';
* join = 'LEFT JOIN `ps_address` `address_postcode`
               ON (`address_postcode`.id_address = o.id_address_delivery)';
Citycity
(TEXT)		* select = 'COALESCE(`address_city`.city, '') as `city`';
* join = 'LEFT JOIN `ps_address` `address_city`
               ON (`address_city`.id_address = o.id_address_delivery)';
Statestate
(TEXT)		* select = 'COALESCE(`state`.name, '') as `state`';
* join = 'LEFT JOIN `ps_address` `address_state`
               ON `address_state`.id_address = o.id_address_delivery
                LEFT JOIN `ps_state` `state`
               ON `state`.id_state = `address_state`.id_state';
State codestate_code
(REGION_CODE)		* select = 'COALESCE(`state_code`.iso_code, '') as `state_code`';
* join = 'LEFT JOIN `ps_address` `address_state_code`
               ON (`address_state_code`.id_address = o.id_address_delivery)
               LEFT JOIN `ps_state` `state_code`
               ON (`state_code`.id_state = `address_state_code`.id_state)';
Countrycountry
(TEXT)		* select = 'COALESCE(`country_lang`.name, '') as `country`';
* join = 'LEFT JOIN `ps_address` `address_country`
               ON (`address_country`.id_address = o.id_address_delivery)
               LEFT JOIN `ps_country_lang` `country_lang`
               ON (`country_lang`.id_country = `address_country`.id_country
               AND `country_lang`.id_lang = 1)';
Country codecountry_code
(COUNTRY_CODE)		* select = 'COALESCE(`country`.iso_code, '') as `country_code`';
* join = 'LEFT JOIN `ps_address` `address_country_code`
               ON (`address_country_code`.id_address = o.id_address_delivery)
               LEFT JOIN `ps_country` `country`
               ON (`country`.id_country = `address_country_code`.id_country)';
Invoice Numinvoice_number
(NUMBER)		* select = 'o.`invoice_number`';
Invoice dateinvoice_date
(YEAR_MONTH_DAY_HOUR)		* select = 'DATE_FORMAT(o.invoice_date, '%Y%m%d%H') as invoice_date';
Shippingshipping
(TEXT)		* select = 'COALESCE(`carrier_shipping`.name, '') as `shipping`';
* join = 'LEFT JOIN `ps_carrier` `carrier_shipping`
               ON (`carrier_shipping`.id_carrier = o.id_carrier)';
Tracking numbershipping_number
(TEXT)		* select = 'COALESCE(o.shipping_number, '') as `shipping_number`';
Delivery datedelivery_date
(YEAR_MONTH_DAY_HOUR)		* select = 'DATE_FORMAT(o.delivery_date, '%Y%m%d%H') as delivery_date';
Products'order slips TTCproducts_oslip_tax_incl
(NUMBER)		* select = 'COALESCE((
               SELECT SUM(`product_refund_ti`.total_products_tax_incl)
               FROM `ps_order_slip` `product_refund_ti`
               WHERE `product_refund_ti`.id_order = o.id_order
               ), 0) as `products_oslip_tax_incl`';
Products'order slips HTproducts_oslip_tax_excl
(NUMBER)		* select = 'COALESCE((
               SELECT SUM(`product_refund_te`.total_products_tax_excl)
               FROM `ps_order_slip` `product_refund_te`
               WHERE `product_refund_te`.id_order = o.id_order
               ), 0) as `products_oslip_tax_excl`';
Shipping's order slips TTCshipping_oslip_tax_incl
(NUMBER)		* select = 'COALESCE((
               SELECT SUM(`shipping_refund_ti`.total_shipping_tax_incl)
               FROM `ps_order_slip` `shipping_refund_ti`
               WHERE `shipping_refund_ti`.id_order = o.id_order
               ), 0) as `shipping_oslip_tax_incl`';
Shipping's order slips HTshipping_oslip_tax_excl
(NUMBER)		* select = 'COALESCE((
               SELECT SUM(`shipping_refund_te`.total_shipping_tax_excl)
               FROM `ps_order_slip` `shipping_refund_te`
               WHERE `shipping_refund_te`.id_order = o.id_order
               ), 0) as `shipping_oslip_tax_excl`';
Total credit notes TTCtotal_oslip_tax_incl
(NUMBER)		* select = '(COALESCE((
               SELECT SUM(`product_refund_tit`.total_products_tax_incl)
               FROM `ps_order_slip` `product_refund_tit`
               WHERE `product_refund_tit`.id_order = o.id_order
               ), 0) + COALESCE((
               SELECT SUM(`shipping_refund_tit`.total_shipping_tax_incl)
               FROM `ps_order_slip` `shipping_refund_tit`
               WHERE `shipping_refund_tit`.id_order = o.id_order
               ), 0)) as `total_oslip_tax_incl`';
Total credit notes HTtotal_oslip_tax_excl
(NUMBER)		* select = '(COALESCE((
               SELECT SUM(`product_refund_tet`.total_products_tax_excl)
               FROM `ps_order_slip` `product_refund_tet`
               WHERE `product_refund_tet`.id_order = o.id_order
               ), 0) + COALESCE((
               SELECT SUM(`shipping_refund_tet`.total_shipping_tax_excl)
               FROM `ps_order_slip` `shipping_refund_tet`
               WHERE `shipping_refund_tet`.id_order = o.id_order
               ), 0)) as `total_oslip_tax_excl`';
Total TTC - Total Credit notes TTCturnover_tax_incl
(NUMBER)		* select = '(o.total_paid_tax_incl - COALESCE((
               SELECT SUM(`product_refund_tit`.total_products_tax_incl)
               FROM `ps_order_slip` `product_refund_tit`
               WHERE `product_refund_tit`.id_order = o.id_order
               ), 0) - COALESCE((
               SELECT SUM(`shipping_refund_tit`.total_shipping_tax_incl)
               FROM `ps_order_slip` `shipping_refund_tit`
               WHERE `shipping_refund_tit`.id_order = o.id_order
               ), 0)) as `turnover_tax_incl`';
Total HT - Total Credit notes HTturnover_tax_excl
(NUMBER)		* select = '(o.total_paid_tax_excl - COALESCE((
               SELECT SUM(`product_refund_tet`.total_products_tax_excl)
               FROM `ps_order_slip` `product_refund_tet`
               WHERE `product_refund_tet`.id_order = o.id_order
               ), 0) - COALESCE((
               SELECT SUM(`shipping_refund_tet`.total_shipping_tax_excl)
               FROM `ps_order_slip` `shipping_refund_tet`
               WHERE `shipping_refund_tet`.id_order = o.id_order
               ), 0)) as `turnover_tax_excl`';
Paid and not canceled ?valid
(BOOLEAN)		* select = 'IF (o.valid > 0, 'true', 'false') as `valid`';
Validated ? (logable)logable
(BOOLEAN)		* select = 'IF (oslg.logable > 0, 'true', 'false') as `logable`';
* join = 'LEFT JOIN `ps_order_state` `oslg`
               ON `oslg`.id_order_state = o.current_state';
Shopshop
(TEXT)		* select = 'COALESCE(`shop`.name, '') as `shop`';
* join = 'LEFT JOIN `ps_shop` `shop`
               ON `shop`.id_shop = o.id_shop';
Market placemarketplace
(TEXT)		* select = 'COALESCE(`lengow`.`marketplace`, ''.
               $this->module->l('website').'') as `marketplace`';
* join = 'LEFT JOIN `ps_lengow_orders` lengow
               ON lengow.id_order = o.id_order';
Loyalty pointsloyalty_points
(NUMBER)		* select = 'COALESCE(`loyalty`.points, '') as `loyalty_points`';
* join = 'LEFT JOIN `ps_loyalty` `loyalty`
               ON loyalty.id_order = o.id_order';
Orders
SELECT [ select ]
FROM `ps_orders` o
[ join ]
Available data sources