Manually Importing Products from Commerce Cloud to Optimizely Data Platform (ODP)

ODP has a turn key Integration app called the Commerce Cloud Connector which can be responsible for the synchronisation of Contact, Order and Product data between your Optimizely Commerce Cloud instance and ODP.

However in Europe the Commerce Cloud Connector is not due to be released until the end of June 2022.

In Europe, due to GDPR guideline compliance you may not ever be able to turn on the connector for Customer Contact or Order data . I can cover that in a separate blog post but you will need to make sure that the relevant cookie policies have been selected by the customer.

While waiting for the connectors release in Europe, a manual product data export and import to ODP is a low cost interim solution. The key is to make sure that the schema you choose to save the products in ODP will be maintained when you turn on the connectors product import.

Key Product Data Schema Import Considerations

ODP currently supports 1 currency with that currency in the UI showing as USD. It is on the product roadmap to support additional currencies but for the moment you will have to choose whatever you determine to be the default currency on your website for the product export.

ODP is flexible with regard to your product data structure. However if you intend to use the connector, it’s best to follow a Product->Variant model to match your Commerce catalog so that the consistency of your data will be maintained.

Exporting Product data

Assuming your default market is GB, the following script will export your products from the catalog in GBP£.

SELECT
CN.[Name] as category,
CE.[Name] AS [name],
(
       CASE
              WHEN Charindex('_',CE.Code)> 0 THEN CE.Code
              ELSE CE.Code
       END
)AS sku,
min(PD.UnitPrice)AS price
,
(
       CASE
              WHEN CEChild.Code IS NOT NULL THEN CEChild.Code
              ELSE CE.Code
       END
)AS parent_product_id
FROM [dbo].[CatalogEntry] CE
		INNER JOIN NodeEntryRelation NR ON NR.CatalogEntryId = CE.CatalogEntryId 
		INNER JOIN CatalogNode CN ON CN.CatalogNodeId = NR.CatalogNodeId
       LEFT OUTER JOIN [dbo].[CatalogEntryRelation] ER ON ER.ChildEntryId = CE.CatalogEntryId
       LEFT OUTER JOIN [dbo].[CatalogEntry] CEParent ON ER.ParentEntryId = CE.CatalogEntryId
          LEFT OUTER JOIN [dbo].[CatalogEntry] CEChild ON ER.ParentEntryId = CEChild.CatalogEntryId
       LEFT OUTER JOIN [PriceDetail] PD ON PD.CatalogEntryCode = CE.Code
WHERE (PD.MarketId ='GB'OR PD.MarketId IS NULL)
GROUP BY  CE.CatalogEntryId, NR.CatalogNodeId, CN.[Name], CE.ClassTypeId, CE.Code, CEParent.Code, CE.MetaClassId, CEParent.MetaClassId, CE.Name, CEParent.Name, CEParent.CatalogEntryId, CEChild.Code, CEChild.MetaClassId
Order BY CE.CatalogEntryId

The output of the script can be copied to a CSV file for import to ODP.

Row 1 of the above export is the product with the variants linked by the parent_product_id

Price in this example is pound price for the GB market. You can update the Where clause to isolate the prices for your default market of choice. Support for multi-market & multi-currency pricing is on the roadmap for ODP so i look forward to hearing more on that in the coming months.

Importing to ODP

Copy the output of this script to Excel to convert to a CSV file before doing the following:

• Do a Find/Replace to convert any “NULL” values in the price column to empty
• Update price values to 2 decimal places

The name the file odp_products.csv and drop into the ODP Integration CSV Upload interface

Conclusion

While you’re waiting for the ODP Commerce Cloud connector to be released in Europe, this simple Export/Import process will help you migrate product data into ODP in a way that will be consistent with the connector schema.