I have the integration between hubspot to mixpanel. it happens that the date is coming in a different format, from hubspot to mixpanel. the field in mixpanel, can I format it as I need so it interprets the right format?
Hi Juan I., Please stand by as I search the Mixpanel knowledge base for an AI-powered answer. I normally take about 15 seconds or so. In case AI cannot answer your question, feel free to reach out to Mixpanel support at mixpanel.com/get-support. If you have follow-up questions for this thread, please tag me again with your question: @kapa.ai <follow-up question> đ
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
Based on the knowledge sources provided, I can see that Mixpanel allows you to handle date formatting issues through typecasting. When data comes from an integration like HubSpot to Mixpanel in a different format than expected, you can use Mixpanel's typecasting feature to ensure it's interpreted correctly.
Mixpanel allows you to force properties into another data type using the "Data Type" option in the overflow menu for property filters and breakdowns. This is particularly useful when one of your properties is a timestamp but is sent to Mixpanel in a different format than expected.
As mentioned in the documentation: "Typecasting is helpful when one of your properties is a number but is sent to Mixpanel as a string and you want to see histogram data for that property. Or if one of your properties is a unix timestamp and got sent as a number, and you want it to be treated as a date Property instead." Mixpanel Reports Documentation
For date properties specifically, Mixpanel recommends using an ISO-formatted date string (YYYY-MM-DDTHH:mm:ss) for optimal use in your reports. If your date from HubSpot is coming in a different format, you can use typecasting to convert it to the proper date format.
Remember that Mixpanel records all events in Coordinated Universal Time (UTC) at intake, so ensure your dates are properly formatted in UTC for consistency.
I hope this helps with your HubSpot to Mixpanel integration issue. If you need more specific guidance on implementing the typecasting for your particular date format, you might want to check Mixpanel's support resources for more detailed instructions.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
âšī¸Â Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Let me check the knowledge base for you again. One moment! đ
Based on the knowledge sources provided, I don't see specific information about changing the date format interpretation from DD/MM to MM/DD for datetime fields in Mixpanel.
However, from what I understand about Mixpanel's date handling, there are a few important points to consider:
1. Mixpanel recommends using ISO-formatted date strings (YYYY-MM-DDTHH:mm:ss) for date properties to ensure proper interpretation in reports.
2. As mentioned in the documentation: "Mixpanel records all events in Coordinated Universal Time (UTC) at intake. Changing the timezone for any project under Project Settings only affects the timezone in which Mixpanel outputs results." Manage Timezones for Projects
The documentation doesn't specifically address changing the date format interpretation from DD/MM to MM/DD. This might be because Mixpanel typically expects standardized date formats rather than offering format customization at the display level.
For your specific issue with HubSpot integration, you might need to:
1. Transform the date format before it reaches Mixpanel (at the source or through a middleware) 2. Contact Mixpanel support for specific guidance on handling this date format issue
The knowledge sources don't contain enough information to provide a definitive solution for changing date format interpretation from DD/MM to MM/DD within Mixpanel itself.
I'd recommend reaching out to Mixpanel support for specific guidance on this issue, as they would have more detailed information about handling date format interpretations in integrations.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
âšī¸Â Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Let me check the knowledge base for you again. One moment! đ
Yes, you can use Mixpanel's Custom Properties feature with a formula to convert your datetime format. This is a great solution for your date format issue.
Based on the documentation, you can create a custom property with a formula to transform your date format from DD/MM to MM/DD. Here's how you can approach it:
First, you'll need to create a custom property:
1. Click the plus button to the right of the search bar 2. Select Event Property or User Property to open the property builder 3. Give your property a name (e.g., "Formatted Date") 4. Click into the formula bar to define your transformation Custom Properties: Calculate new properties on the fly
For the formula itself, you'll likely need to use string manipulation functions to rearrange the date parts. While the documentation doesn't provide a specific example for DD/MM to MM/DD conversion, you could use functions like SPLIT to separate the date components and then recombine them in the desired order.
For example, if your date is in a string format like "DD/MM/YYYY", you might use something like:
```
SPLIT(your_date_property, "/")[1] + "/" + SPLIT(your_date_property, "/")[0] + "/" + SPLIT(your_date_property, "/")[2]
LBKSThis would take the second part (month), add a slash, then the first part (day), add another slash, and finally the third part (year).
You can press Ctrl + Space while writing your formula to see all available functions and their descriptions, which will help you construct the appropriate formula for your specific date format.
After creating the formula, you can click "Apply" to use it locally in your report, or "Save" to make it available for reuse across reports [Custom Properties: Calculate new properties on the fly](https://docs.mixpanel.com/docs/features/custom-properties#creating-a-custom-property).
kapa.ai is still learning and improving, please let me know how I did by reacting below.
âšī¸Â Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Let me check the knowledge base for you again. One moment! đ
I'm sorry to hear that your custom property formula is resulting in blank values. Based on your previous attempts to convert date formats from DD/MM to MM/DD, there could be several reasons why your formula isn't working:
1. The date format in your source data might not be exactly what you expected 2. The SPLIT function might not be working as intended with your specific date format 3. There might be null or undefined values in your date property
Here are some troubleshooting steps you could try:
1. First, check if your original date property has values by creating a simple custom property that just returns the original property without any transformation 2. Verify the exact format of your date strings (they might be in a different format than expected) 3. Try using simpler string functions first to see if they work with your data
If you're dealing with a more complex date format or if the date is stored as a timestamp rather than a string, you might need to use different functions.
For debugging purposes, you could also try creating a custom property that just returns a part of your date string to see if that works:
```
SPLIT(your_date_property, "/")[0]
LBKSIf you continue to experience issues, you might want to reach out to [Mixpanel Support](https://docs.mixpanel.com/guides/onboarding-overview#getting-help) or join the [Mixpanel Community Slack](https://www.mixpanel.com/community-slack) where you can ask other experienced Mixpanel users for help with specific formula examples.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
âšī¸Â Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.