Solved

Using Date in Custom Property

  • 10 June 2021
  • 3 replies
  • 95 views

Badge +2

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!

icon

Best answer by app_analytics_rocks 10 June 2021, 11:37

View original

3 replies

Badge +2

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 4
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? 

How can I use the event timestamp to create a user property? For e.g. “purchased within 48 hours?’ yes/no

Reply