Loading large coupon CSV files into SAP Marketing Cloud and SAP Marketing using Soap UI
This blog post and the described techniques are based on the great pre-work from my colleague Eileen Koehler back in 2016.
She originally wrote this blog post: https://blogs.sap.com/2016/10/06/loading-csv-data-with-soap-ui-into-sap-s4hana-marketing-cloud-and-sap-hybris-marketing/ which I used as template to provide this guide.
You want to upload large amounts of coupon codes into SAP Marketing Cloud or SAP Marketing (OnPrem) using CSV files.
The coupon maintenance UI allows a maximum file size of only 1MB, which corresponds to roughly 8,000 codes (depending on the number of columns). If you want to upload e.g. 2 million codes, you would have to split the data into a lot of files and upload them manually one after the other. This would obviously take up a large amount of time and would be quite error prone.
This tutorial shows you how the upload can easily be achieved using the open source tool Soap UI and provides you with the necessary files to configure the tool.
We use the OData API for Coupons that can also be used to integrate with other systems, e.g. via CPI.
Preparing the SAP Marketing system
SAP Marketing Cloud
Create a communication arrangement (along with the corresponding communication system and communication user) for the communication scenario SAP_COM_0317 (Marketing – Coupon Integration).
You can find an overview on how this setup is performed in the Integration Guide in the SAP Help Portal.
SAP Marketing (OnPrem 1809+)
The coupon OData API is available in the OnPrem release starting with version 1809.
Here, you copy the PFCG template role SAP_CEI_API_COUPON to a Z-role and maintain the necessary authorization values. Afterwards you create a dedicated service user and assign it the created role.
Checking the OData service
After you have performed the preparation steps, you should try to access the coupon API endpoint in your system with the created user by opening the following URL in a browser or another REST tool (be sure to adjust the server URL and port):
Hint: In the SAP Marketing Cloud, the final URL is displayed in the communication arrangement for easy access.
Preparing your (local) computer
Download the open source tool Soap UI and install it.
Getting the Soap UI project files
The Soap UI project files can be downloaded from here.
Preparing the Soap UI tool
Extract the downloaded file SOAP_UI_Coupon_Codes.zip on your local computer to a known location (e.g. C:\SOAP UI\).
Open the SOAP UI application and import the project file CSV-Upload.xml (menu: File -> Import Project).
Prepare the data files
The data upload requires 2 files, one CSV file containing the data and one text file containing the mapping of the CSV columns to OData properties.
The CSV file requires a header row. Line items must be separated by semicolons. This is an example:
COUPONCODE;COUPONCODESERIALNUMBER;EANCODEIMAGEURL;QRCODEIMAGEURL; 114880625989697; 118578660666135; 114458317677821; 112449487421436; 115433053140189; 111250308851217; 118176105734840; 115193352403397; 113084219563850;
The metadata file contains the mapping from the OData Service property to the CSV column separated by colon. The corresponding metadata file for coupons therefore would look like this:
CouponCode:COUPONCODE CouponCodeSerialNumber:COUPONCODESERIALNUMBER EANCodeImageURL:EANCODEIMAGEURL QRCodeImageURL:QRCODEIMAGEURL
Your CSV file can contain more columns than you want to upload to the system. Only columns mapped in your metadata file will be uploaded.
The SOAP UI.zip file contains examples for both files.
Open the imported project in Soap UI and drill down to the Settings section:
Double click the Settings section:
Double click on the respective values and maintain the necessary values:
Username/password are for the communication/service user created earlier.
The package size of 500 is a good balance between throughput and resource consumption on your PC as well as the SAP Marketing backend. You can change this value to adapt to your needs. If the value is too high, it can result in out-of-memory situations in the Soap UI application or the backend.
The hostname setting contains the hostname and the corresponding HTTP port of your backend system.
CouponUUID contains the UUID of the coupon in to which you want to upload the codes. You copy it from the Coupon app URL:
Ensure that you copy the UUID when in display mode. In edit mode, you’ll have another UUID which then subsequently won’t work for the upload.
DataFileName contains the complete path to the file containing your data.
MetadataFileName contains the complete path to the file containing the column to OData mapping.
Offset is a parameter you can normally ignore. With this parameter you can control from which line the upload should start. This might be helpful in case the upload failed after a certain package. After you fixed the file, you can start from this line again using this parameter.
Now you can start the upload. Double click on Code Upload:
Press the Start/Play button to start the upload.
If the upload was successful it will end with a corresponding success message.
If the upload fails, you can check the logs in Soap UI. You can always easily delete all existing codes in a coupon using the “Delete All Codes” button in the Coupon app and restart from scratch.
Info: the Soap UI log might contain error messages like this:
Thu May 23 14:18:48 CEST 2019:ERROR:An error occurred [Not in GZIP format], see error log for details
You can safely ignore those errors.
You have successfully uploaded coupon codes into SAP Marketing Cloud or SAP Marketing.
You can now distribute the coupon codes to your customers via email campaigns or the offer discovery service.