CSV report export shows incorrect date values

Last updated:

This article explains how to troubleshoot and resolve an exported report in CSV format when every row for a date metadata field contains the same incorrect value (for example, 0001-01-01 16:10:58), even though the Pendo UI shows the correct value.

Product area: Any report that you can export as a CSV file, including exports requested through the Pendo UI and API (web, mobile)

Issue

When sending metadata to Pendo, it must match one of the supported date formats, as described in Configure visitor and account metadata. If metadata is sent in the date format MM/DD/YYYY or any other unsupported date formats, Pendo can’t process the date value into milliseconds, which is required for Pendo to have a standardized time format in the backend. It also causes two major issues:

  • The metadata field can't use any of the segmentation rules for dates.
  • The metadata field can’t be converted correctly for report exports in CSV format. This means that all rows in the report will have the same incorrect value in the format YYYY-MM-DD HH:MM:SS (for example, 0001-01-01 16:10:58), even if the values in the Pendo UI are unique and correct. 

Solution

There are two options for resolving incorrect date values in this scenario.

Option 1. Change metadata type in Data Mapping settings

The first option fixes the issue with report exports, but it still doesn't allow you to use segmentation rules for dates.

  1. In Settings > Data Mappings, change the Type for the metadata field from Date to Text
  2. Wait at least 6 hours for caching to expire, then try exporting the report as a CSV file again.

Option 2. Change date format in install script (recommended)

The second option resolves both the report export issue and not being able to use segmentation rules for dates.

  1. Ask your developers to make changes as needed to send date values to Pendo in a supported date format, as described in Configure visitor and account metadata. We recommend the ISO8601 W3C format (for example, 2006-01-02T15:04:05.999-05:00) for dates, which is the default in Settings > Data Mappings. Contact Pendo Support if you need to choose a different supported date format.
  2. We recommend sending the corrected date values as a new metadata field instead of reusing the existing metadata field. This ensures that all values in the new metadata field are using the supported date format and processing correctly.
Was this article helpful?
2 out of 5 found this helpful