Time interval between two tables

[Talso]

Hi,

I have two tables: events table and campaign table. 

I want to calculate the time interval between the rows on the tables in order to measure the effectiveness of the campaigns.

I want to do that on the script so that it wouldn't have an effect on the performance.

Table examples:
        1. Events that a user did

date and time	event name	user email
12/15/2021 8:19:20 AM	registered	a@a.com
12/15/2021 8:20:20 AM	login	a@a.com
12/19/2021 8:17:20 AM	paid	b@b.com

        2. Campaigns information 

date and time	campaign name	campaign event	user email
12/13/2021 2:10:20 AM	aaa	sent	a@a.com
12/15/2021 8:16:20 AM	aaa	view	a@a.com
12/15/2021 8:17:20 AM	aaa	click	a@a.com
12/17/2021 2:10:20 AM	bbb	sent	a@a.com
12/17/2021 2:10:20 AM	bbb	sent	b@b.com

I don't want it to be on the same table because it will multiply the data.

The goal is that the user will be able to change the time period on the UI and see the percentage of 'login' users  (for example) after he opens/views the campaign.

Thanks,
Tal.

[MarcoWedel]

given your sample data, what would be your expected result?

[Talso]

Hi,

I want to be able to create a new field that will calculate the time difference between the events in table 1 and events in table two.

if those fields were on the same table, I would do this calc:

Interval( (event_date_time) - (campaign_date_time), 'hh:mm') as hour_difference

Thanks!

[MarcoWedel]

could you please post your sample data including this new field to demonstrate where it should be created and which values you would expect to see?

[kef]

Hi,

You can use the ApplyMap function but i don't understand how join event information and campaign information (only with user email ?).