Question

Using Date in Custom Property


Badge

Hi! I want to create a couple of Custom Properties related to the Event Timestamp / Date, namely:

  • Day of the week
  • Week number (ISO)

I want to use this properties to do some filters, namely:

  • get all events from last week (and not necessarily the last 7 days)
  • remove events from a particular day of the week
  • etc

I would also need to know which is the current week number.

How can I achieve such thing? I couldn’t manage to:

  • find the event timestamp field while creating a Custom Property
  • extract the day of the week or week number from a date ( example: TODAY() )

Thanks in advance!


2 replies

Badge

I’ve tried the following workaround with no success.

First, I created a custom property TODAY_YYYYMMDD for All Events using a simple function that returns TODAY() in format YYYYMMDD (eg. 20210610). Formula: ` NUMBER(MID(STRING(TODAY()),1,4)+MID(STRING(TODAY()),6,2)+MID(STRING(TODAY()),9,2))`

Then I added a lookup table with all the days from 2019 to 2030, with the key YYYYMMDD and new fields TODAY Week Day and TODAY Week Number. Table “Today Date Table.csv” attached

Lastly, I Mapped this table to the key TODAY_YYYYMMDD. I expected that All Events would now get the new week day and week number fields but nothing is showing.

Do Lookup Tables work when mapping to custom properties like I did? If so, any idea where the issue might be?

 

EDIT:

Creating the csv file from local Windows Excel doesn’t work, but downloading a CSV file from Google Sheets did the trick!

Userlevel 3
Badge +5

@app_analytics_rocks We have “date” as a default property which you can use in filters and breakdowns, I know this won’t solve everything mentioned but would it help? 

Reply


Mixpanel