Modelling Customers in a Pizza Delivery operation

[dkalyan]

Would like to pick your brains on what you all would recommend as the ideal model for this situation.

I am enhancing an existing Orders Datamart for a Pizza Delivery business.
The complexities(or rather the uncertainties) are to do with representing the customer dimension(s).

In this business, the following customer details are captured depending on the order method:
FOR Phone Orders - Name & Phone No, + Delivery Address if the order is a home delivery
FOR Online Orders - Name, Email & PhoneNo + Delivery Address if the order is a home delivery
FOR Walkin Orders - Name, (Phone No is optional)

No additional customer details are captured.

One of the aims of this exercise is to be able to gain an understanding of the customer/household buying patterns.
To identify a household, the delivery address would need to be matched to like Emails & Phone Nos.

How would I model the customer dimension(s)?
Option 1
Delivery Address Dimension
Customer Phone Dimension
Customer Email Dimension
Customer Name Dimension
This option splits the key attributes in diff dimensions to allow the fact table to build the history.

Option 2
Customer Address Dimension (Type 2)
Customer Phone Dimension (Type 2)
Customer Email Dimension (Type 2)
Here the history is captured in the dimension, however it could have a data explosion, e.g. the same phone no, could be used by 4 family members to order pizzas,
or one delivery address used by multiple email addresses to order to, e.g. Wife & Husband ordering.

The above two options would allow me to analyse customer behavior by Phone, Email or Address but not combined, however we cannot identify what a household behavior is? For that I am thinking of introducing an additional Household dimension, that shall link the above dimension combinations to a household dimension based on some other smarts we yet have to figure out!!

I would very much appreciate your comments / assistance in modelling the above scenario.


Regards
Dharmesh

[BoxesAndLines]

I've never been a fan of putting address data into the customer dimension. It's needed in some form or another on many fact tables. That does necessitate snowflaking dims to the address dim, but at least you have a handle on all the different types of addresses across the enterprise. Another benefit is that your address FK essentially becomes your household ID.

[dkalyan]

thanks for the reply.
Our current DW design has Customer Delivery, Customer Email, and Customer Phone dimensions as type 2 dimensions, which are becoming quite large and not providing much value in the history that is being created.
I am leaning towards separating them to
DeliveryAddress,
Email and
Phone as Type 1 dimensions all linked to the facts, however these dimensions would then only store the current customer name and not history.
For history of Customer Names, would it be better to create a Customer Name dimension or rather store the Customer Name as a degenerate dimension in the Fact table (this is what I am leaning towards).

On to the other issue of identifing households, the problem we have is orders are placed by multiple people in a household using different mediums, phone, online, walkin. And for each medium they could be using different email accounts, phones (landline, mobiles),

See example below of the buying patterns of a typical household. Note, offline orders do not capture an email address. Also Delivery Address is only available for Delivery Orders.
The Customer identifiers are: email or phone or delivery address.

Order Channel	OrderType	NAME Used	EMAIL	PHONE	DELIVERY ADDRESS		order placed by
online	delivery	dad	dad@hotmail.com	3326 5443	123 Hawaiian Road, Albion		dad
online	pickup	dad	dad@hotmail.com	3326 5443			dad
online	delivery	mum	mum@hotmail.com	0411 222 333	123 Hawaiian Road, Albion		mum
offline	pickup	mum		3326 5443			mum
online	delivery	son	son@hotmail.com	3326 5443	123 Hawaiian Road, Albion		son
online	delivery	daddyO	dad@hotmail.com	0411 555 666	456 Friends place		dad @ friends house
online	delivery	son	son@hotmail.com	0411 111 000	123 Hawaiian Road, Albion		son using his mobile number
offline	pickup	dad		0411 555 666			dad

In the above scenario, there are really only one household, however each person in the household is using a different identity to order. The only exception being the friends address, which could be classified as a different household, however the email address could than place it back to the original household??

Does anyone have any experience in performing a SINGLE CUSTOMER VIEW (Households - what we would refer to) in the above scenario?

My thoughts so far are to create a Customer occurence table, that aggregates by Customer Key combination email/phone/address and counts the occurence for that combination. Then when looking up an email/phone/address, select the entry with the most occurrence... which would then give us the Houshold.
This would also require the creation of a household dimension , which would be linked to the Customer Occurence fact and to the orders fact as well.

Any suggestions on the single customer view would be very much appreciated.

[Sponsored content]