Values rounded when pushed to Salesforce Marketing Cloud

  • 30Views
  • Last Post 3 days ago
0
votes
Admin Tryke posted this 2 weeks ago

Hi, we are setting up new integrations to push our data from skyvia into Salesforce Marketing Cloud. However, the data is arriving in marketing cloud without decimal precision (rounded to the nearest whole value). This issue never presented itself in our Sales Cloud integration. When we actually look at the value being generated by the error file, it appears that is it some sort of issue with Skyvia as the value is being rounded there (see attached screenshot).

What we think is most likely happening is it thinks the field in Marketing cloud is numeric only and doesn't have scale and therefore just passes the rounded value. However, we tried converting this and passing into a text field and still get the same issue.

For our customer table, we have  ‘AmountSpent’, ‘LastSpent’ & ‘Points’ that need the decimal accuracy, in addition to all the pricing fields in the sales and discount tables.

Thanks

Attached Files

Order By: Standard | Newest | Votes
0
votes
Dmitriy Muzurov posted this 2 weeks ago

Hello,

Thank you for submitting a ticket with us.

We have tested your use case from our side both with PostgreSQL and CSV as a source and do confirm Skyvia passes correct values to Salesforce Marketing Cloud.

Please make sure the custom fields in your SFMC with "decimal" data type have scale not equal to 0. In our example the scale is 4.

Should you have any questions, do not hesitate to contact us back.

0
votes
Admin Tryke posted this 2 weeks ago

We are already using decimal fields and the scale is already set to 2 (not 0).

After your comment, we also tested with scale set to 4 and we are still seeing the issue.

We think there must be something in the specific map. Is there anyone who can take a look at our actual instance to try to assist?

 

0
votes
Dmitriy Muzurov posted this 2 weeks ago

Hi,

Thank you for getting back to us.

Please provide us with the screenshot from your Salesforce Marketing Cloud with the details about the field in question.

If you export the data to CSV and open it with Excel, make sure the Excel does not cut a part after a dot or present values in some optimized format. On your screenshot, the third value does have integers after a dot. You can open a CSV via a simple Notepad and check the values.

Please let us know what data type has a field in your PostgreSQL database.

We are looking forward to hearing from you.

0
votes
Admin Tryke posted this 1 weeks ago

 

Ok, checking from the MC cloud side, we have changed the DataType multiple times to your suggestions and all show no decimal accuracy (See below).

 

We are noticing that the decimal accuracy is getting removed in Skyvia as all the Source tables contain decimal accuracy; however, the target files from what gets successfully integrated have no decimal accuracy (see integration configuration below of our Source tables). Please also feel free to check any of our integration csv files, which will show the loss in decimal accuracy during this integration.

 

Below shows how these variables are being mapped in SkyVia (if this may be the issue).

 

 

0
votes
Dmitriy Muzurov posted this 4 days ago

Hi,

Thank you for the screenshots.

Please create an export package and select decimal fields from your PostgreSQL database for export. Then open a CSV files with Notepad and let us know if there are integers after a dot.

Also, let us know the data type that fields with decimal values have in your PostgreSQL database.

Should you have any questions, do not hesitate to contact us back.

0
votes
Admin Tryke posted this 3 days ago

 

Here are snippets from CSV exports of the pre- and post-integration tables:

 

Pre Integration (nevadalivebiotrack.salesforce_customers table)

 

customerid

amountspent

lastspent

NV00167ba38e

406.25

34.02

 

 

 

Post Integration (Customer_Staging_Integration_NV table)

 

CustomerID

AmountSpent

LastSpent

NV00167ba38e

406

34

 

All of these data types are ‘DT_NUMERIC’ within the integration, as shown in the final screenshot from our previous post.

 

0
votes
Dmitriy Muzurov posted this 3 days ago

Hi,

Thank you for your reply.

DT_NUMERIC from your screenshot is how SSIS, that is in the backend of Skyvia, determines the data type from the target connection.

For decimal-like fields PostgreSQL has several numeric data types, among them: decimal, numeric, real and double precision. Please let us know the data type that is used for fields in question from your PostgreSQL database.

Also, we would highly appreciate if you create an export package here, select the required fields, run the package and send us a CSV file.

We are looking forward to hearing from you.

Close