Hi All,
I have a classic marketing problem: we send out campaign messages (outbounds) and coupons to customers and they can use coupons to order the products, either the received coupon or another one. Coupons can be handed over to another person as well. I want to see all combinations of how they ordered the product.
So I have the following data:
- Campaigns (A1,A2,A3)
- Campaign outbounds to contact IDs: A1-C1, A1-C2, A2-C1, A2-C3, A3-C4
- Discount coupons for campaigns: A1-D1, A1-D2, A2-D3
- Actual orders by customer and coupon IDs: O1-C1[D1,D2], O2-C3[D3]
I have the following tables (simplified):
- Campaigns->Contacts(outbounds)->Orders
- Campaigns->Coupons->Orders
The first problem was, that it is a kind of circular reference from Campaigns to Orders, so I separated the Campaign to CampaignOutbound and CampaignCoupon and need to select the same campaign twice, but I can live with it.
I would need a kind of "Has outbound" and "Has coupon" type of field in the orders table, to be able to see and further analyse how and in which combination the given order was achieved. I tried many different relation order, combinations of USERELATIONSHOP and KEEPFILTERS, but none of them worked. For example in the "Has outbound" field it always gives the total number of records in the CampaignOutbound table instead of a 1/0 if it is in the selected campaign.
I have millions of records and based on my experience it is faster to solve the problem with relations, rather than M joins, which take hours to refresh. Also, I have other helper tables for filtering, I can not do a join for each of them, so I prefer a good relationship structure.
Any ideas?
