Forum Discussion
Filter Two Fact Tables
It's still not very clear. Can I consider that in 'Campaign Clicks' table, we need to get the latest time of a click for each customer. If a customer made a call/calls after that time, we need to extract the earliest call time and calculate whether the duration between "latest click time" and "earliest call time" is less than 7 days? If it is less than 7 days, then we count this customer and count how many calls he made from 'Inbound Calls' table?
If my above understanding is correct, is it possible that a customer would click campaigns again after he had made a call? If so, only getting the latest click time is not appropriate as it would miss calls happened earlier than that.
Can you provide some sample data to help me understand the problem better?
Best Regards,
Jing
Hi v-jingzhang below is the data samples from the campaign and call tables, I hope it makes sense.
The business problem I'm trying to solve is a reduction of incoming calls. If a customer sees an online campaign they should ideally resolve their issue online instead of calling in. But some campaigns drive up call traffic instead.
So the questions I'm trying to answer are these:
- How many customers called within 7 days of clicking on a campaign.
- How many calls did they make in total (that's a straight row count on the calls table).
- Which campaign codes are successful (i.e. clicks resulting in the least call traffic).
I hope it makes sense, thank you for your help.
CAMPAIGN CLICKS
| Customer ID | Campaign ID | Date | Campaign Clicks |
| 12345 | ABCDE | 2021-12-01 | 0 |
| 16582 | ABCDE | 2021-12-01 | 2 |
| 12345 | AEEEE | 2021-12-02 | 1 |
| 12345 | ABCDE | 2021-12-03 | 5 |
| 17777 | EEFFF | 2021-12-04 | 8 |
INCOMING CALLS
| Customer ID | Date | Call Topic | Resolved |
| 12345 | 2021-12-07 | Support | TRUE |
| 99598 | 2021-12-07 | Complaint | TRUE |
| 12345 | 2021-12-08 | Sales | TRUE |