Join with GroupBy

Hi all, I have two tables called users and campaign, attached sample .xlsx below.
users shows the users created through the campaigns.
campaign shows the cost for each running campaign per day.
sample_dataset.xlsx (11.5 KB)

I want to find the cost for each user created per day and per campaign. Note that, not every day there is a user created from a campaign.

Here is the desired outcome:
image

How can I achieve this?

Hello @ksenkaya,

check my answer in your previous topic. Why did you withdrawn it?

https://forum.knime.com/t/join-with-groupby/30544

Br,
Ivan

Hey @ipazin, the screenshot was wrong and that was already late to Edit the topic.
Do you mind if you write your answer and attach the Knime workflow here?

Thanks,
Kadir

Hello @ksenkaya,

under assumption campaign table is left and user table is right start with left outer join, count number of same campaigns on same date, then perform inner join, divide to get proper cost and do a little bit of cleaning.

If I got it right only the last row was incorrect but ok. Give it a try and if you’ll have issues I can share example.

Br,
Ivan

2 Likes

I did what you wrote here.
CPC_calculation.knwf (16.3 KB)

If I do $cost$/$Unique count(campaign_id (#1))$ I get wrong results.
i.e. I only need to divide the cost by users for the same campaign_id as the desired output above.

Can you help me with that please?

Hello @ksenkaya,

not exactly what I said :smiley:

If I got it right your grouping columns are date and id so you should always used them (both in GroupBy node and joins) to get what you need. Also GroupBy should return number to divide cost so you shouldn’t use Unique count but rather Count?

Br,
Ivan

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.