{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["MDD SECONDAIRE","PROFILES","ORDERS","ORDER LINES","ABANDONED CARTS","ABANDONED CART LINES","liste valeur"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":0,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"MDD SECONDAIRE","index":0,"n_rows":63,"n_cols":9,"rows_kept":63,"cut":false,"hidden":true,"merged":[[15,1,15,8],[26,1,26,8],[38,1,38,8],[50,1,50,8],[59,1,59,8]]}
[]
["DESCRIPTION DU MODÈLE DE DONNÉES SECONDAIRE"]
[]
[null,null,"Colonne à l'usage du client uniquement"]
[null,null,"Colonne à l'usage d'ACTITO uniquement"]
[]
[null,"Attention : chaque fichier doit être zippé individuellement en format .zip ou .gzip"]
[null,"LISTE DES TABLES"]
[null,null,"Nom technique de la table","Nom de la table dans l'interface ","Type de table","Si FTP : fichier obligatoire / optionnel","Si FTP : nom de fichier"]
[null,1,"Secondaire","Secondaire","Profils","Obligatoire","<YYYYMMDD>_<CLIENT>_SECONDAIRE.csv"]
[null,2]
[]
["TABLE DE PROFILS"]
["Toutes les personnes devant recevoir une communication marketing sont à importer dans la table de profil"]
[]
[null,"CONTACTS"]
[]
[null,null,"Nom technique / libellé de colonne dans le fichier","Provenance (colonne à l'usage du client / partenaire)","Type","Contraintes","Lien table de référence / Champ standard ACTITO","Nom du champ dans ACTITO","Description"]
[null,1,"idContact",null,"INTEGER","Unique et obligatoire",null,"Contact ID","Identifiant unique et obligatoire du profil dans ACTITO"]
[null,2,"email",null,"emailAddress",null,"emailAddress","Adresse email"]
[null,3]
[null,4]
[]
["TABLES DE DONNÉES LIÉES"]
["Relations 1-1 ou 1-N entre Profil et la table"]
[]
[null,"XXX"]
[]
[null,null,"Nom technique / libellé de colonne dans le fichier","Provenance (colonne à l'usage du client / partenaire)","Type","Contraintes","Lien table de référence / Champ standard ACTITO","Nom du champ dans ACTITO","Description"]
[null,1,"idUniqueXXX",null,"INTEGER","Unique et obligatoire",null,"ID XX","Identifiant unique et obligatoire dans la table"]
[null,2,"idContact",null,"INTEGER","Obligatoire",null,"Contact ID","Identifiant du profil, lien avec la table de profils"]
[null,3]
[null,4]
[]
["TABLES DES INTERACTIONS"]
["Relations 1-1 ou 1-N entre Profil et la table"]
["Un champ obligatoire au format date et heure doit être à minima présent dans cette table"]
[]
[null,"XXX"]
[]
[null,null,"Nom technique / libellé de colonne dans le fichier","Provenance (colonne à l'usage du client / partenaire)","Type","Contraintes","Lien table de référence / Champ standard ACTITO","Nom du champ dans ACTITO","Description"]
[null,1,"idUniqueXXX",null,"INTEGER","Unique et obligatoire",null,"ID XX","Identifiant unique et obligatoire dans la table"]
[null,2,"idContact",null,"INTEGER","Obligatoire",null,"Contact ID","Identifiant du profil, lien avec la table de profils"]
[null,3]
[null,4]
[]
["TABLES DE RÉFÉRENCE"]
["Relations N-1 entre une table de profils et/ou de données liées et/ou des interactions et la table de référence"]
["Maximum 10000 lignes"]
[]
[null,"XXX"]
[]
[null,null,"Nom technique / libellé de colonne dans le fichier","Provenance (colonne à l'usage du client / partenaire)","Type","Contraintes","Lien table de référence / Champ standard ACTITO","Nom du champ dans ACTITO","Description"]
[null,1,"idUniqueXXX",null,"INTEGER","Unique et obligatoire",null,"ID XX","Identifiant unique et obligatoire dans la table"]
[null,2]
[]
[]
["FICHIERS TECHNIQUES"]
[]
[null,"Suppression de profils"]
[]
[null,null,"Nom technique / libellé de colonne dans le fichier","Provenance (colonne à l'usage du client / partenaire)","Type","Contraintes","Lien table de référence / Champ standard ACTITO","Nom du champ dans ACTITO","Description"]
[null,1,"idContact",null,"INTEGER","Obligatoire",null,"ID Contact"]

{"sheet":"PROFILES","index":1,"n_rows":30,"n_cols":5,"rows_kept":30,"cut":false,"hidden":false,"merged":[[18,0,18,4],[21,0,21,4]]}
["#","Technical name on Actito table","CMS concept","CMS Field","Rules apply during the injection"]
[1,"accountId","customers","id"," -"]
[2,"firstName","customers","firstname"," -"]
[3,"lastName","customers","lastname"," -"]
[4,"emailAddress","customers","email"," -"]
[5,"sex","customers","gender\nprefix","Convert the gender : '1' is M and '2' is F. \nIf no gender, the prefix is used : 'M'/'Mr' is 'M' and 'Mrs'/'Mlle'/'Mme'  is 'F'\""]
[6,"birthDate","customers","dob"," -"]
[7,"addressCompany","customers","addresses[DEFAULT_BILLING].company","Fetch the first customer's address with flag address.default_billing"]
[8,"VATNumber","customers","-","Not synchronized"]
[9,"addressStreet","customers","addresses[DEFAULT_BILLING].street","Fetch the first customer's address with flag address.default_billing"]
[10,"addressLocality","customers","addresses[DEFAULT_BILLING].city","Fetch the first customer's address with flag address.default_billing"]
[11,"addressCountry","customers","addresses[DEFAULT_BILLING].country_id","Fetch the first customer's address with flag address.default_billing"]
[12,"addressRegion","customers","addresses[DEFAULT_BILLING].addressRegion.region","Fetch the first customer's address with flag address.default_billing"]
[13,"addressPostalCode","customers","addresses[DEFAULT_BILLING].postcode","Fetch the first customer's address with flag address.default_billing"]
[14,"mobilePhoneNumber","addresses","addresses[DEFAULT_BILLING].telephone","Fetch the first customer's address with flag address.default_billing"]
[15,"customerGroup","customers","group_id"," -"]
[16,"source","customers","created_in","Set with the value 'MAGENTO2X' with suffix '({created_in})' if not empty"]
[17,"motherLanguage","storeConfigs","locale","Find the storeConfig related to the customer based on the customer.website_id and customer.store_id"]
["Subscription"]
[1,"Newsletter","customers","extension_attributes[is_subscribed]"," -"]
[18,"Alerte","Alerte","BOOLEAN"," - true/false"]
["Segmentation"]
[1,"Clients Churn risks","-","-","compute on Actito with a predefined rule"]
[2,"Potential purchasers","-","-","compute on Actito with a predefined rule"]
[3,"New buyers","-","-","compute on Actito with a predefined rule"]
[4,"VIP customer","-","-","compute on Actito with a predefined rule"]
[5,"Unengaged","-","-","compute on Actito with a predefined rule"]
[6,"New subscribers","-","-","compute on Actito with a predefined rule"]
[7,"Engaged 3 months","-","-","compute on Actito with a predefined rule"]
[8,"Unengaged 1 year","-","-","compute on Actito with a predefined rule"]

{"sheet":"ORDERS","index":2,"n_rows":17,"n_cols":5,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["#","Technical name on Actito table","CMS concept","CMS Field","Rules apply during the injection"]
[1,"orderId","orders","entity_id","-"]
[2,"reference","orders","entity_id","-"]
[3,"currency","orders","order_currency_code","-"]
[4,"date","orders","created_at","-"]
[5,"total","orders","base_grand_total","-"]
[6,"totalInclTax","orders","base_grand_total","-"]
[7,"totalExclTax","orders","base_grand_total\nbase_tax_amount","\nCompute (order.base_grand_total - order.base_tax_amount)"]
[8,"shipping","orders","base_shipping_incl_tax","-"]
[9,"shippingOption","orders","shipping_description","-"]
[10,"paymentMethod","orders","payment.method","-"]
[11,"couponCode","orders","coupon_code","-"]
[12,"status","orders","status","-"]
[13,"shopId","orders","store_id","-"]
[14,"shopName","orders\nstoreViews","order.store_name\nstoreView.name","Fetch storeView based on order.store_id\n\nUse the order.store_name, if empty use the storeView.name"]
[15,"emailAddress","orders","customer_email","-"]
[16,"accountId","orders","customer_id","-"]

{"sheet":"ORDER LINES","index":3,"n_rows":21,"n_cols":5,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["#","Technical name on Actito table","CMS concept","CMS Field","Rules apply during the injection"]
[1,"orderId","orders","entity_id"]
[2,"orderDetailId","orders","items[SIMPLE].item_id"]
[3,"orderReference","orders","entity_id"]
[4,"sku","orders","items[SIMPLE].sku"]
[5,"description","products","custom_attributes[DESCRIPTION].value","Fetch simpleProduct based on order.items[SIMPLE].sku\nFetch configurableProduct based on order.items[PARENT_SIMPLE].product_id\n\nIf there is a configurableProduct, use his description. Otherwise, use the description of the simpleProduct. \nThis description is then sanitized.\n\nThe description for a product is found based on a custom_attributes.attribute_code with value 'description'. If found, use his first available value."]
[6,"name","orders","items[SIMPLE].name"]
[7,"quantity","orders","items[SIMPLE].qty_ordered"]
[8,"unitPrice","orders","items[SIMPLE].base_price"]
[9,"unitPriceExclTax","orders","items[SIMPLE].base_price_incl_tax"]
[10,"unitPriceInclTax","orders","items[SIMPLE].base_price_incl_tax\nitems[SIMPLE].base_tax_amount","Compute (items[SIMPLE].base_price_incl_tax - items[SIMPLE].base_tax_amount)"]
[11,"productUrl","\nproducts","\ncustom_attributes[URL_KEY].value","Fetch simpleProduct based on order.items[SIMPLE].sku\nFetch configurableProduct based on order.items[PARENT_SIMPLE].product_id\n\nValue derived from '{ECOM_CONFIG_API_URL}/{RELEVANT_PRODUCT_URL_KEY}.html'\n\nRELEVANT_PRODUCT_URL_KEY\n-----------------\nThe urlKey for a product is found based on a custom_attributes.attribute_code with value 'url_key'. If found, use his first available value. \nIf the simpleProduct.visibility is different from 1 (NOT_VISIBLE), the relevantProductUrlKey is the urlKey of the simpleProduct. Otherwise, use the urlKey of the configurableProduct (if any)."]
[12,"pictureUrl","storeConfig\nproducts",null,"STARTING FROM 22.5.1 (BUGFIX)\n\nFetch storeConfig based on order.store_id\nFetch simpleProduct based on order.items[SIMPLE].sku\nFetch configurableProduct based on order.items[PARENT_SIMPLE].product_id\n\nValue derived from '{STORE_CONFIG_BASE_MEDIA_URL}/catalog/product/{RELEVANT_IMAGE_PATH}'\n\nRELEVANT_IMAGE_PATH\n----------------\nThe imagePath for a product is found based first on the media_gallery_entries then the custom_attributes if empty.\nSearch for a media_gallery_entry.media_type with value 'image', if found, use media_gallery_entry.file\nSearch for a custom_attributes.attribute_code with value 'image', if found, use this custom_attributes.value[0] (ignored if this value is 'no_selection').\nThe relevantImagePath is the imagePath of the simpleProduct. If no value and there is a configurableProduct, we use the imagePath of the configurable.\n\nBEFORE 22.5.1 (BUGFIX)\n\nFetch storeConfig based on order.store_id\nFetch product based on items[SIMPLE].sku\n\nValue derived from '{STORE_CONFIG_BASE_MEDIA_URL}/catalog/product/{RELEVANT_IMAGE_PATH}'\n\nRELEVANT_IMAGE_PATH\n----------------\nThe relevantImagePath is computed based on the product, first with the media_gallery_entries then with the custom_attributes if empty.\nSearch for a media_gallery_entry.media_type with value 'image', if found, use media_gallery_entry.file\nSearch for a custom_attributes.attribute_code with value 'image', if found, use this custom_attributes.value[0] (ignored if this value is 'no_selection')"]
[13,"shopId","orders"]
[14,"shopName","\norders\nstoreViews",null,"Fetch storeView based on order.store_id\n\nUse the order.store_name, if empty use the storeView.name"]
[15,"emailAddress","orders","customer_email","-"]
[16,"accountId","orders","customer_id","-"]
[]
[]
[null,"The items related to a 'configurable' product are retrieved by the API in the items but only the 'simple' are stored in actito table"]
[null,"The sku of the 'configurable' order item is in fact the sku of the simple product "]

{"sheet":"ABANDONED CARTS","index":4,"n_rows":13,"n_cols":5,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["#","Technical name on Actito table","CMS concept","CMS Field","Rules apply during the injection"]
[1,"abandonedCartId","carts","entity_id","Value is derived from '{CART_ID}_{TODAY_AS_YYYYMMDD}'"]
[2,"reference","carts","entity_id","-"]
[3,"currency","carts","quote_currency_code\ncurrency.quote_currency_code","Use quote_currency_code, if empty use currency.quote_currency_code"]
[4,"total","carts","cartsTotals.base_grand_total"]
[5,"totalInclTax","carts","cartsTotals.base_grand_total"]
[6,"totalExclTax","carts","cartsTotals.base_grand_total - cartTotals.base_tax_amount","\nCompute (cartsTotals.base_grand_total - cartTotals.base_tax_amount)"]
[7,"status","carts","is_active","If set, value is 'active'. Otherwise value is 'inactive'"]
[8,"abandonedSinceMoment","carts","updated_at","-"]
[9,"shopId","carts","\nstore_id","-"]
[10,"shopName","carts","store_name","-"]
[11,"emailAddress","carts","customer_email","-"]
[12,"accountId","carts","customer_id\ncustomer.entity_id","Use customer_id, if empty use customer.entity_id"]

{"sheet":"ABANDONED CART LINES","index":5,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["#","Technical name on Actito table","CMS concept","CMS Field","Rules apply during the injection"]
[1,"abandonedCartId","carts","entity_id","Value is derived from '{CART_ID}_{TODAY_AS_YYYYMMDD}'"]
[2,"abandonedCartDetailId","carts","entity_id\nitems[X].item_id","\nValue is derived from '{CART_ID}_{TODAY_AS_YYYYMMDD}_{CART_ITEM_ID}"]
[3,"abandonedCartReference","carts","entity_id"]
[4,"sku","carts","items[X].sku"]
[5,"description","\nproducts","custom_attributes[DESCRIPTION].value","\nFetch simpleProduct based on cart.items[X].sku\nFetch configurableProduct based on cart.items[X].name\n\nIf there is a configurableProduct, use his description. Otherwise, use the description of the simpleProduct. \nThis description is then sanitized.\n\nThe description for a product is found based on a custom_attributes.attribute_code with value 'description'. If found, use his first available value. "]
[6,"name","carts","items[X].name"]
[7,"quantity","carts","items[X].qty"]
[8,"unitPrice","carts","items[X].price"]
[9,"unitPriceExclTax","\ncartstotals","items[X].base_price_incl_tax\nitems[X].base_tax_amount","Fetch cartTotal based on cart.entity_id\n\nFind a cartTotal.items related to the given cart.items[X].item_id"]
[10,"unitPriceInclTax","\ncartstotals","items[X].base_price_incl_tax","Fetch cartTotal based on cart.entity_id\n\nFind a cartTotal.items related to the given cart.items[X].item_id\nThen compute (base_price_incl_tax - base_tax_amount)"]
[11,"productUrl","\nproducts","custom_attributes[URL_KEY].value","Fetch simpleProduct based on cart.items[X].sku\nFetch configurableProduct based on cart.items[X].name\n\nValue derived from '{ECOM_CONFIG_API_URL}/{RELEVANT_PRODUCT_URL_KEY}.html'\n\nRELEVANT_PRODUCT_URL_KEY\n-----------------\nThe urlKey for a product is found based on a custom_attributes.attribute_code with value 'url_key'. If found, use his first available value. \nIf the simpleProduct.visibility is different from 1 (NOT_VISIBLE), the relevantProductUrlKey is the urlKey of the simpleProduct. Otherwise, use the urlKey of the configurableProduct (if any)."]
[12,"pictureUrl","storeConfig\nproducts","storeConfig.base_media_url\nproduct.media_gallery_entries[IMAGE].file\nproduct.custom_attributes[IMAGE].value","Fetch storeConfig based on cart.store_id\nFetch simpleProduct based on cart.items[X].sku\n\nValue derived from '{STORE_CONFIG_BASE_PATH}/catalog/product/{RELEVANT_IMAGE_PATH}'\n\nRELEVANT_IMAGE_PATH\n----------------\nThe relevantImagePath is computed based on the simpleProduct, first with the media_gallery_entries then with the custom_attributes if empty.\nSearch for a media_gallery_entry.media_type with value 'image', if found, use media_gallery_entry.file\nSearch for a custom_attributes.attribute_code with value 'image', if found, use this custom_attributes.value[0] (ignored if this value is 'no_selection')"]
[13,"shopId","carts","store_id","-"]
[14,"shopName","carts","store_name","-"]
[15,"emailAddress","carts","customer_email","-"]
[16,"accountId","carts","customer_id\ncustomer.entity_id","Use customer_id, if empty use customer.entity_id"]
[]
["Unlike the orders, there is only one line in the items for a simple product and his configurable (if any)."]
["Cart item with configurable : the product_type is 'configurable', the sku is the simple product value and the name is the configurable one."]

{"sheet":"liste valeur","index":6,"n_rows":22,"n_cols":4,"rows_kept":22,"cut":false,"hidden":true,"merged":[]}
["STRING",null,null,"firstName"]
["INTEGER",null,null,"lastName"]
["BOOLEAN (True / False)",null,null,"birthDate"]
["DATE (yyyy-mm-dd)",null,null,"sex"]
["TIMESTAMP (yyyy-mm-dd hh:mm:ss)",null,null,"motherLanguage"]
["NUMERIC (xx.xx)",null,null,"emailAddress"]
["birthdate",null,null,"addressStreet"]
["emailAddress",null,null,"addressNulber"]
["gsmNumber",null,null,"addressBox"]
["codeLangue",null,null,"addressPostalCode"]
["sex",null,null,"addressLocality"]
["telephoneNumber",null,null,"addressCountry"]
["civilité (M., Mlle, Mme)",null,null,"gsmNumber"]
["codePays",null,null,"telephoneNumber"]
[null,null,null,"civilState"]
[null,null,null,"nbChildren"]
[null,null,null,"profession"]
[null,null,null,"marketingConsent"]
[null,null,null,"-"]
[null,null,null,"abonnement email"]
[null,null,null,"abonnement sms"]
[null,null,null,"abonnement postal"]