Modelling Customers in a Pizza Delivery operation

View previous topic View next topic Go down

Modelling Customers in a Pizza Delivery operation

Post  dkalyan on Sun Jul 10, 2011 9:28 am

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

dkalyan

Posts : 5
Join date : 2009-10-08
Location : Australia

View user profile

Back to top Go down

Re: Modelling Customers in a Pizza Delivery operation

Post  BoxesAndLines on Mon Jul 11, 2011 8:49 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modelling Customers in a Pizza Delivery operation

Post  dkalyan on Tue Jul 12, 2011 3:07 am

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 ChannelOrderTypeNAME UsedEMAILPHONEDELIVERY ADDRESSorder placed by
onlinedeliverydaddad@hotmail.com3326 5443123 Hawaiian Road, Albiondad
onlinepickupdaddad@hotmail.com3326 5443dad
onlinedeliverymummum@hotmail.com0411 222 333123 Hawaiian Road, Albionmum
offlinepickupmum3326 5443mum
onlinedeliverysonson@hotmail.com3326 5443123 Hawaiian Road, Albionson
onlinedeliverydaddyOdad@hotmail.com0411 555 666456 Friends placedad @ friends house
onlinedeliverysonson@hotmail.com0411 111 000123 Hawaiian Road, Albionson using his mobile number
offlinepickupdad0411 555 666dad

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.

dkalyan

Posts : 5
Join date : 2009-10-08
Location : Australia

View user profile

Back to top Go down

Re: Modelling Customers in a Pizza Delivery operation

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum