Overview

Goal: Marketers want to have access to detailed information from Shopify online shops

In case manual update data to SFMC is needed, one can use below script to call relevant Shopify and generate relevant csv, which can be uploaded to SFMC

ProductDetials_to_csv.py
ProductDetials_to_csv.ipynb (Jupyter Notebook file, RECOMMENDED)

#	Step	Description
1	Shopify	Data should be collected from potentially all possible Business units
2a	Lambda - Collecting product list with details from Shopify and parsing the data	"Get products Lambda" is created for each business unit Authentication: Basic HTTP authentication by using their Admin API key and password as a username and password OR by including to the request header X-Shopify-Access-Token: {access_token}, where {access_token} is replaced by this store Admin API password. More: Shopify API Authorisation Documentation Lambda uses Shopify API: https://help.shopify.com/en/api/reference/products/product?api[version]=2019-07#index-2019-07 Lambda collects all available data from Shopify Lambda collects data twice a day, at: TBD ENDPOINT: GET /admin/api/2019-10/products.json CURL: `curl -X GET https://{username}:{password}@{shop}.myshopify.com/admin/api/2019-10/products.json -H 'Content-Type: application/json'` example request: `https://38b3692b6b48a9712b8d909c46e6208d:c2dc0abbdef5825da69ee1647962ce2d@example.myshopify.com/admin/api/2019-10/products.json` example answer: example respone Expand source HTTP/1.1 200 OK { "products": [ { "id": 632910392, "title": "IPod Nano - 8GB", "body_html": "<p>It's the small iPod with one very big idea: Video. Now the world's most popular music player, available in 4GB and 8GB models, lets you enjoy TV shows, movies, video podcasts, and more. The larger, brighter display means amazing picture quality. In six eye-catching colors, iPod nano is stunning all around. And with models starting at just $149, little speaks volumes.</p>", "vendor": "Apple", "product_type": "Cult Products", "created_at": "2019-09-30T13:47:52-04:00", "handle": "ipod-nano", "updated_at": "2019-09-30T13:47:52-04:00", "published_at": "2007-12-31T19:00:00-05:00", "template_suffix": null, "tags": "Emotive, Flash Memory, MP3, Music", "published_scope": "web", "admin_graphql_api_id": "gid://shopify/Product/632910392", "variants": [ { "id": 808950810, "product_id": 632910392, "title": "Pink", "price": "199.00", "sku": "IPOD2008PINK", "position": 1, "inventory_policy": "continue", "compare_at_price": null, "fulfillment_service": "manual", "inventory_management": "shopify", "option1": "Pink", "option2": null, "option3": null, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "taxable": true, "barcode": "1234_pink", "grams": 567, "image_id": 562641783, "weight": 1.25, "weight_unit": "lb", "inventory_item_id": 808950810, "inventory_quantity": 10, "old_inventory_quantity": 10, "requires_shipping": true, "admin_graphql_api_id": "gid://shopify/ProductVariant/808950810", "presentment_prices": [ { "price": { "currency_code": "USD", "amount": "199.00" }, "compare_at_price": null } ] }, { "id": 49148385, "product_id": 632910392, "title": "Red", "price": "199.00", "sku": "IPOD2008RED", "position": 2, "inventory_policy": "continue", "compare_at_price": null, "fulfillment_service": "manual", "inventory_management": "shopify", "option1": "Red", "option2": null, "option3": null, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "taxable": true, "barcode": "1234_red", "grams": 567, "image_id": null, "weight": 1.25, "weight_unit": "lb", "inventory_item_id": 49148385, "inventory_quantity": 20, "old_inventory_quantity": 20, "requires_shipping": true, "admin_graphql_api_id": "gid://shopify/ProductVariant/49148385", "presentment_prices": [ { "price": { "currency_code": "USD", "amount": "199.00" }, "compare_at_price": null } ] }, { "id": 39072856, "product_id": 632910392, "title": "Green", "price": "199.00", "sku": "IPOD2008GREEN", "position": 3, "inventory_policy": "continue", "compare_at_price": null, "fulfillment_service": "manual", "inventory_management": "shopify", "option1": "Green", "option2": null, "option3": null, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "taxable": true, "barcode": "1234_green", "grams": 567, "image_id": null, "weight": 1.25, "weight_unit": "lb", "inventory_item_id": 39072856, "inventory_quantity": 30, "old_inventory_quantity": 30, "requires_shipping": true, "admin_graphql_api_id": "gid://shopify/ProductVariant/39072856", "presentment_prices": [ { "price": { "currency_code": "USD", "amount": "199.00" }, "compare_at_price": null } ] }, { "id": 457924702, "product_id": 632910392, "title": "Black", "price": "199.00", "sku": "IPOD2008BLACK", "position": 4, "inventory_policy": "continue", "compare_at_price": null, "fulfillment_service": "manual", "inventory_management": "shopify", "option1": "Black", "option2": null, "option3": null, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "taxable": true, "barcode": "1234_black", "grams": 567, "image_id": null, "weight": 1.25, "weight_unit": "lb", "inventory_item_id": 457924702, "inventory_quantity": 40, "old_inventory_quantity": 40, "requires_shipping": true, "admin_graphql_api_id": "gid://shopify/ProductVariant/457924702", "presentment_prices": [ { "price": { "currency_code": "USD", "amount": "199.00" }, "compare_at_price": null } ] } ], "options": [ { "id": 594680422, "product_id": 632910392, "name": "Color", "position": 1, "values": [ "Pink", "Red", "Green", "Black" ] } ], "images": [ { "id": 850703190, "product_id": 632910392, "position": 1, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "alt": null, "width": 123, "height": 456, "src": "https://cdn.shopify.com/s/files/1/0006/9093/3842/products/ipod-nano.png?v=1569865672", "variant_ids": [], "admin_graphql_api_id": "gid://shopify/ProductImage/850703190" }, { "id": 562641783, "product_id": 632910392, "position": 2, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "alt": null, "width": 123, "height": 456, "src": "https://cdn.shopify.com/s/files/1/0006/9093/3842/products/ipod-nano-2.png?v=1569865672", "variant_ids": [ 808950810 ], "admin_graphql_api_id": "gid://shopify/ProductImage/562641783" } ], "image": { "id": 850703190, "product_id": 632910392, "position": 1, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "alt": null, "width": 123, "height": 456, "src": "https://cdn.shopify.com/s/files/1/0006/9093/3842/products/ipod-nano.png?v=1569865672", "variant_ids": [], "admin_graphql_api_id": "gid://shopify/ProductImage/850703190" } }, { "id": 921728736, "title": "IPod Touch 8GB", "body_html": "<p>The iPod Touch has the iPhone's multi-touch interface, with a physical home button off the touch screen. The home screen has a list of buttons for the available applications.</p>", "vendor": "Apple", "product_type": "Cult Products", "created_at": "2019-09-30T13:47:52-04:00", "handle": "ipod-touch", "updated_at": "2019-09-30T13:47:52-04:00", "published_at": "2008-09-25T20:00:00-04:00", "template_suffix": null, "tags": "", "published_scope": "web", "admin_graphql_api_id": "gid://shopify/Product/921728736", "variants": [ { "id": 447654529, "product_id": 921728736, "title": "Black", "price": "199.00", "sku": "IPOD2009BLACK", "position": 1, "inventory_policy": "continue", "compare_at_price": null, "fulfillment_service": "shipwire-app", "inventory_management": "shipwire-app", "option1": "Black", "option2": null, "option3": null, "created_at": "2019-09-30T13:47:52-04:00", "updated_at": "2019-09-30T13:47:52-04:00", "taxable": true, "barcode": "1234_black", "grams": 567, "image_id": null, "weight": 1.25, "weight_unit": "lb", "inventory_item_id": 447654529, "inventory_quantity": 13, "old_inventory_quantity": 13, "requires_shipping": true, "admin_graphql_api_id": "gid://shopify/ProductVariant/447654529", "presentment_prices": [ { "price": { "currency_code": "USD", "amount": "199.00" }, "compare_at_price": null } ] } ], "options": [ { "id": 891236591, "product_id": 921728736, "name": "Title", "position": 1, "values": [ "Black" ] } ], "images": [], "image": null } ] }
2b	Lambda - parsing	Lambda parses nested JSON into flat JSNO: TIPS here JSON parameters used for the process, mapping and final JSON data model: DataModel.xlsx example JSON output after parsing: example JSON for 2 SKU Expand source { "Products": [ { "variant_sku": "example SKU 1" "variant_id": "example string" "id": "example string" "title": "example string" "variant_title": "example string" "variant_price": "example integer" "body_html": "example string" "vendor": "example string" "product_type": "example string" "created_at": "example datatime" "handle": "example string" "updated_at": "example datatime" "published_at": "example datatime" "template_suffix": "example string" "tags": "example string" "published_scope": "example string" "admin_graphql_api_id": "example string" "variant_position": "example integer" "variant_inventory_policy": "example string" "variant_compare_at_price": "example string" "variant_fulfillment_service": "example string" "variant_inventory_management": "example string" "variant_option1": "example string" "variant_option2": "example string" "variant_option3": "example string" "variant_created_at": "example datatime" "variant_updated_at": "example datatime" "variant_taxable": "example string" "variant_barcode": "example string" "variant_grams": "example integer" "variant_image_id": "example string" "variant_weight": "example integer" "variant_weight_unit": "example string" "variant_inventory_item_id": "example string" "variant_inventory_quantity": "example integer" "variant_old_inventory_quantity": "example integer" "variant_requires_shipping": "example boolean" "variant_admin_graphql_api_id": "example string" "variant_currency_code": "example string" }, { "variant_sku": "example SKU 2" "variant_id": "example string" "id": "example string" "title": "example string" "variant_title": "example string" "variant_price": "example integer" "body_html": "example string" "vendor": "example string" "product_type": "example string" "created_at": "example datatime" "handle": "example string" "updated_at": "example datatime" "published_at": "example datatime" "template_suffix": "example string" "tags": "example string" "published_scope": "example string" "admin_graphql_api_id": "example string" "variant_position": "example integer" "variant_inventory_policy": "example string" "variant_compare_at_price": "example string" "variant_fulfillment_service": "example string" "variant_inventory_management": "example string" "variant_option1": "example string" "variant_option2": "example string" "variant_option3": "example string" "variant_created_at": "example datatime" "variant_updated_at": "example datatime" "variant_taxable": "example string" "variant_barcode": "example string" "variant_grams": "example integer" "variant_image_id": "example string" "variant_weight": "example integer" "variant_weight_unit": "example string" "variant_inventory_item_id": "example string" "variant_inventory_quantity": "example integer" "variant_old_inventory_quantity": "example integer" "variant_requires_shipping": "example boolean" "variant_admin_graphql_api_id": "example string" "variant_currency_code": "example string" } ] }
3	Files are uploaded to DynamoDB
4	Lambda - Sending data to dedicated DE	"Send data to DE Lambda" is created for each business unit Lambda is started twice a day: TBD Lambda uses UPSERT SFMC API: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-apis.meta/mc-apis/updateDataExtensionIDAsync.htm example request: example request Expand source Host: https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com PUT /data/v1/async/dataextensions/key:ExternalKey12345/rows Content-Type: application/json Authorization: Bearer YOUR_ACCESS_TOKEN { "items": [{ "FirstName":"Bobby", "LastName" : "Jones", "ZipCode": "23456" }, { "FirstName":"Sam", "LastName" : "Sneed", "ZipCode": "23456" }] } example response example response Expand source HTTP/1.1 202 OK { "requestId": "ed0ec06e-dd78-4f30-80b6-07bfc77db289" } --------- ERROR response: HTTP/1.1 200 OK { "requestId": "524059b7-bf60-4fa6-9271-32aaf3479091", "resultMessages": [ { "resultType": "Operational", "resultClass": "Error", "resultCode": "CustomObjectNotFound", "message": "Failed to resolve the Custom Object from the provided ObjectReferenceIdentifier [Id: ed9b5f8e-ebb7-e711-8103-005056b37304, Key: ]." } ] }

POC

Collecting data from test shop: x-uk-test-store.myshopify.com
Data should be sent to dedicated DE:
- BU: "CDP tests CAN" in Shopify folder
-External Key: CDP_Shopify_Products

Nutrition-Customer Engagement

NUTRINTG Product details DE

Overview

POC