Report relationship problem

[theinternetchap]

Report relationship problem

 I have a difficulty in creating a relationship for a report and am thinking that a repeating field my be what I need. I've searched the forum but can't find a similar post but was wondering if a relationship can be based upon a repeating field?

The problem I have is this

We are trying to measure the value of sending out mailers to customer based upon them returning back to one of our stores.

We have the three tables based on this relationship

Mailings = group record that defines mailing activity i.e. letters posted in january

Mailing records = a record for each individual that has been sent a mailing

Invoices = invoices - the proof that a customer has returned back to store

The report I am generating is based upon the mailing records in order to get the totals amongst other things:

Store name          Qty Mailed           Orders

Store 1

January Mailings                200

February Mailings              150   

Store 2 

January Mailings                180

February Mailings              190   

I have the above working fine so far, where I'm having trouble is getting the order count through as the only relationship between a mailing and an order from what I can see is the unique customer_ID.

My thinking was do I create a repeating field in the Mailings table and fill it with all of the customer_ID's then base a relationship on this field to the orders table(as well as some global date stuff) in order to pull through matching orders.

Or is there a better way without using repeating fields? Although I don't even know if this repeating fields method would work anyway and if it did - it sounds like it may be a little intensive!

All help is much appreciated.

[David McQueen]

Report relationship problem

 I have a difficulty in creating a relationship for a report and am thinking that a repeating field my be what I need. I've searched the forum but can't find a similar post but was wondering if a relationship can be based upon a repeating field?

The problem I have is this

We are trying to measure the value of sending out mailers to customer based upon them returning back to one of our stores.

We have the three tables based on this relationship

Mailings = group record that defines mailing activity i.e. letters posted in january

Mailing records = a record for each individual that has been sent a mailing

Invoices = invoices - the proof that a customer has returned back to store

The report I am generating is based upon the mailing records in order to get the totals amongst other things:

Store name          Qty Mailed           Orders

Store 1

January Mailings                200

February Mailings              150   

Store 2 

January Mailings                180

February Mailings              190   

I have the above working fine so far, where I'm having trouble is getting the order count through as the only relationship between a mailing and an order from what I can see is the unique customer_ID.

My thinking was do I create a repeating field in the Mailings table and fill it with all of the customer_ID's then base a relationship on this field to the orders table(as well as some global date stuff) in order to pull through matching orders.

Or is there a better way without using repeating fields? Although I don't even know if this repeating fields method would work anyway and if it did - it sounds like it may be a little intensive!

All help is much appreciated.

 

Somewhere, you have to make an assumption that an order was generated by the mailing. An example would be if the mailing is in January and the order is also in January, they are connected.

 

In your mailings table, you can have a field that is say an indexed date that would be Jan 1 2013 for the January mailing.

 

When the individual mailing record is created an identical date should be created. If there is a field now that captures the date of mailing, you could just have a calculated field that reads the month and year and puts in the day as 1. It would have to be indexed. Then the two are connected.  

 

In orders, you will have an order date.  Use the same strategy to take an order that happens on say Jan 17, 2013 and create an indexed calculated date of Jan 1 2013.

 

In this way the Mailing, the individual mailers and the orders are connected.

 

From there, you can get fancier between mailers and orders by using both the date key and the client id to determine what they are buying when.

 

With regards to repeating fields, my personal opinion: If you do not have to go there, don't. Others may differ.

 

HTH

 

Dave

[theinternetchap]

Somewhere, you have to make an assumption that an order was generated by the mailing. An example would be if the mailing is in January and the order is also in January, they are connected.

 

In your mailings table, you can have a field that is say an indexed date that would be Jan 1 2013 for the January mailing.

 

When the individual mailing record is created an identical date should be created. If there is a field now that captures the date of mailing, you could just have a calculated field that reads the month and year and puts in the day as 1. It would have to be indexed. Then the two are connected.  

 

In orders, you will have an order date.  Use the same strategy to take an order that happens on say Jan 17, 2013 and create an indexed calculated date of Jan 1 2013.

 

In this way the Mailing, the individual mailers and the orders are connected.

 

From there, you can get fancier between mailers and orders by using both the date key and the client id to determine what they are buying when.

 

With regards to repeating fields, my personal opinion: If you do not have to go there, don't. Others may differ.

 

HTH

 

Dave

 

Hmmm, I think your onto something there, the problem is we have multiple mailings going out every week to different sets of customers (but never to the same customer within a 2 month period), we also want to track the relevant sales over the first, second, third and fourth months.

 

I liked your idea of adding the calc field on the invoice side to relate back to a possible mailing, and guess that some how i'd have to create a windows from the invoice date going back each month ( or better yet / week).

 

Unless you can see a problem with this - I'm thinking the best way to get the relationship to work would be to create a field on the customers (customers table) file in the database which is populated with a mailing ID, if an invoice is then generated for that customer, the invoice record has a new field which performs a lookup of the mailing ID which then creates the relationship.

 

To then complicate this even further, they also want to track related sales when the 'invoice customer information' doesn't COMPLETELY match the original mailing information - this could mean:

 

Matching address & name - i.e same customer

Matching address but new customer name - New customer

Matching customer details but new address - May have a new customer account set up by mistake

 

Does anybody have any experience of trying to build relationships on this kind of information?!

[David McQueen]

Take a few steps back and take a look at your architecture.  You are indicating to me that you want to embed mailing information on the customer record.  No.  This is properly requiring a join table between your customer database and the database of mailings (defined as The January Mailing).  

 

The individual mailings sent out are really like the line items of an invoice.  So there is a related record to "The Mailing" for each customer mailed on that mailing. In other words for each mailing you could have a portal showing all customers mailed.

 

You may not be being clear in your post, but a few things there worried me, so I wanted to be clear.

 

Second, you can only be as granular as the information you collect.  You can infer some things, as long as you realize that they are inferences. It sounds as if the higher ups would like more than inference. So, take a few more steps back, away from the machine, and over to the marketing department. You can have them put on a "Promo Code" that a customer would use for that mailing. It can be a two part code ie:   2234-4561  Where 2234 is the ID of the mailing and 4561 is the ID of the customer. When they buy something, they are also giving you an item ID.  So if the marketing department does their work in the initial set up, you would have all information required to be truly granluar - Customer ID, Item ID, Mailing ID.

 

Different approach from what I originally posted, but in the long run it gets rid of inferences. There are a whole lot of other things that could be done in building this, but it depends on what is wanted over the long haul and I am not familiar with your business.

 

Dave

[theinternetchap]

Take a few steps back and take a look at your architecture.  You are indicating to me that you want to embed mailing information on the customer record.  No.  This is properly requiring a join table between your customer database and the database of mailings (defined as The January Mailing).  

 

The individual mailings sent out are really like the line items of an invoice.  So there is a related record to "The Mailing" for each customer mailed on that mailing. In other words for each mailing you could have a portal showing all customers mailed.

 

You may not be being clear in your post, but a few things there worried me, so I wanted to be clear.

 

Second, you can only be as granular as the information you collect.  You can infer some things, as long as you realize that they are inferences. It sounds as if the higher ups would like more than inference. So, take a few more steps back, away from the machine, and over to the marketing department. You can have them put on a "Promo Code" that a customer would use for that mailing. It can be a two part code ie:   2234-4561  Where 2234 is the ID of the mailing and 4561 is the ID of the customer. When they buy something, they are also giving you an item ID.  So if the marketing department does their work in the initial set up, you would have all information required to be truly granluar - Customer ID, Item ID, Mailing ID.

 

Different approach from what I originally posted, but in the long run it gets rid of inferences. There are a whole lot of other things that could be done in building this, but it depends on what is wanted over the long haul and I am not familiar with your business.

 

Dave

 

Correct, the database does have a 'The Mailing' table (Mailing Runs) with related records to each customer.

 

I understand what you are saying about having a 'Promo Code' unfortunately for this business it's just not how the marketing department work - they have tried many different methods and the promo code usage from the past has shown to yield low return rate (as they do not always purchase an item on promo) - yet if questioned - it was the mailing that brought them into store, this why we are required to look at it from an inference standpoint... and yes, in some cases - a little more.

 

In light of trying to be clear - I have the following set up:

 

Customers (the customer)

Vehicles (the customers car, the reason they are being mailed, i.e because they need an MOT / Service etc)

Orders (If they appear in store to make a purchase, an order is created)

Order Line Items (formed in relation to the above)

Products (Items here are inserted into the Order Line Items)

Branches (Where the customer is associated to 'Their branch' and where the orders are created)

Mailing Runs (Run on a weekly basis for each branch, criterior here specifies which vehicles are due to be mailed depending on product / branch / when they were last mailed)

Mailing Records (The record relating a 'Mailing Run' to a vehicle / customer)

 

The mailing records generated are based upon a vehicles needs... which is related to a customer.

 

The problem is that we are creating a solution which was formerly handled by an individual and was presented back as excel documents / reports. This is an expensive way to generate them on a rolling basis so we are trying to automate the solution - it is understood by all parties that the report results will not always match exactly because of the level of inference, however we just want to be as close as possible (sorry for not being clear on this in the first place - I was just trying to fix one element and was planning on tackling the other element afterwards).

 

I am currently digging through some old data used to generate a previous report by the individual, to see exactly what level of inference is needed in the solution, but in the meantime I'll keep an eye on here and if the above helps clear up a few things and you think of another way of relating mailing records to invoices then I appreciate it - I do however feel, that I may be complicating the issue on the level of inference as the individual never had access to customer ID's for some reason so it may be that this area is not as bad as I thought - which is why I'm doing the digging. In this (hopeful) instance it would just be a 'Mailing Record' to 'Order (relating to a customer mailed)' relationship which I still need a solid relationship for.

[David McQueen]

I understand where you are now.

 

Go back to my original ideas.  Each customer is mailed at most once over a 2 month period. The date keys can be over a two month period.  You can also make secondary keys for three and four months.  

 

 

If the customer is mailed between Jan 1 and Feb 28 - mailing key is Jan 1

 

Two month purchase key if they purchase between Jan 1 and Feb 28 is Jan 1 (adjust these to fit your needs)

 

Have a similar three month purchase key.  They purchase on March 10.  Three month purchase key calculates out at Jan 1

 

so the purchaser who purchases on Mar 10 has:

 

Purchase Date - Mar 10

2 Month Key : March 1

3 Month Key: Jan 1

 

That should allow you to relate back to mailings. I am not trying to be exact in any way shape or form, just to jog your thoughts so you can go where you have to go.