Ad Versioning Info

[Jeff M]

I have a customer that I build an ad for. Each ad has 6-10 pages and has 10-20 products on each page.

This customer has many stores (identified by a unique store number) and each store could run a special price on any of those products. Typically it's a major project like Coke or Pepsi that changes price, (or product), but it could be any product.

I need to build a database that will help me identify through data how many versions of each page I need to make and what the differences are.

Here's an example:

The beverage lady sends a spreadsheet that says this list of stores gets PEPSI on page 1 at $2.99/12PK, and this other list of stores get's COKE on page 1 at $3.49/12PK. Logically, this means I need at least 2 versions of the page, one for coke, one for pepsi. Then I get a 3rd list of stores that get's PEPSI on page 1 at $2.49/12PK. Now I have at least 3 versions. Then I get a spreadsheet from the beer department saying page 1 has Bud on sale for $8.99/12PK in these stores, and $9.99 in these other stores. So now I have a variety of products that could cause versioning.

Essentially, I want to be able to import these spreadsheets and export a report that tells me I need X versions of page 1. This version is good for these store numbers and they contain Bud at $8.99 and Coke at $3.49. The next would be Bud at $8.99 and PEPSI at $2.49/12PK and it's unique to these stores...

I started with a store list table.

Fields are: store number, store address, etc.

Then I have a version data table where I would import the data.

Fields are: year, week, page, store number, product, size, price, limit

Then I think I'll need a table to store a record for each page of the ad.

Field are: year, week, page

I may be able to get there on my own, but I'm hopeful someone has a more elegant way of getting there, or even a better structure.

Help please!

[comment]

IIUC, there could be (at least in theory) as many versions as there are stores - so how many stores do they have?

[Jeff M]

IIUC, there could be (at least in theory) as many versions as there are stores - so how many stores do they have?

There could be, yes, but it usually doesn't happen that way. They have around 20 stores.

Right now I use a spreadsheet and I add a column each time I learn of something that might version, then with some fancy sorting I can usually figure it out.

I'd rather have a database where a user could enter a deal and the stores it's good for. Then at the end, pull a summary report to see how many unique variations of a page I have.

[comment]

I'd rather have a database where a user could enter a deal and the stores it's good for.

So how do you see that (entering the stores)? Would they checkmark the applicable stores or create join records to the Stores table?

Another question I should have asked earlier: suppose you have a table of Specials with fields for:

• AdID

• PageNumber

• Product

• Price

• Stores

In this table, each record would be one special price for a group of stores, so using your example above:

• AdID: 1234

• PageNumber: 1

• Product: PEPSI

• Price: $2.99

• Stores: 1¶4¶5¶12

and so on. Will there be any other offers on the page, other than all specials whose page is 1?

[Jeff M]

In this table, each record would be one special price for a group of stores, so using your example above:

• AdID: 1234

• PageNumber: 1

• Product: PEPSI

• Price: $2.99

• Stores: 1¶4¶5¶12

and so on. Will there be any other offers on the page, other than all specials whose page is 1?

That might work, but I'm not seeing how I would get there by importing the spreadsheets.

For instance, the first spreadsheet might look like:

Store Page Product Price

1 2 PEPSI 2.99

4 2 PEPSI 2.99

While the next spreadsheet might look like:

Store Page Product Price

2 2 COKE 3.49

3 2 COKE 3.49

And the final spreadsheet might look like:

Store Page Product Price

1 2 BUD 8.99

2 2 BUD 9.99

3 2 BUD 9.99

4 2 BUD 9.99

If all these were imported into a table, I'd end up with 1 item for store 2, 1 item for store 1 and 2 items for stores 3 & 4

It would end up creating 3 versions of page 2.

1. PEPSI 2.99, BUD 8.99 (good for store 1)

2. COKE 3.49, BUD 9.99 (good for store 2 & 3)

3. PEPSI 2.99, BUD 9.99 (good for store 4)

[Vaughan]

Forget importing spreadsheets. Any half-decent database is relational and importing spreadsheets is not a trivial process.

If you design the data structure and interface well, entering data directly into the database will be easier and faster than entering it into a spreadsheet.

Regarding the setup of the ads, I'd start with a "default" set of products and prices. Each store gets a set of these, that can then be modified to suit the store: prices changed, products added or removed.

I'm currently working on a system for a performing arts company, and they wanted to set up next years 260+ courses in a spreadsheet and import them. The client has a huge issue with course setup because each year they spend several days agonising over the program in Excel then entering the data into the database.

My solution: I discovered that next years courses will be the same as this years courses (give or take a couple here and there). So rather than work out what courses to run I got the client to work out what would be DIFFERENT from this years's program, and work on the exception. I then set up a process whereby the client finds the current courses, omits those they want to exclude, then clicks a button and viola! next year's courses magically appear. Creating 264 courses took less than 1 minute. The scripting took less than an hour.

The general principle is to create a set of defaults that meets the majority of needs, then the client needs only work on the exceptions.

[Jeff M]

I gotta say I 100% agree with you, but in this case I may not have a choice unless I want to be the data entry guy. You see, I may not be able to get them to change the way they enter the data, which means the spreadsheets may not go away.

If I were to create some type of unique value (via concatenation or a calculation field), how long could it be and have FileMaker still evaluate it? Seems like in early versions of FileMaker it would only evaluate the first 8 or 10 characters? What about now in versions 10/11?

[comment]

I'll have to disagree with Vaughan here: I think your data entry, whether manual or by import, is strictly by the book. This is an interesting puzzle - but you haven't answered my question: is there a "default" content for each page? Do the imported records represent CHANGES to a default content of the page - or are they the sole content?

[Jeff M]

So sorry, I missed that question.

These are pages that are build based on content previously provided. For instance, I'll get a list (spreadsheet) of products that will be on page 2. I'll build the page, then revise it a few times over the course of a week or two. By the time I'm ready to figure out versions, the content on the page won't match the original spreadsheet exactly. This is why I'm taking the approach of only considering the version-related data.

One of those products in the original instructions (or perhaps via a manual revisions of the page) would be to put COKE on the page. Then, I'll get a spreadsheet from the customer listing all the possible switchouts for that block on the page. In other words...leave as COKE for these stores. Leave as COKE but change the price for these stores. Change to PEPSI for these stores. And so on. Later I may get another spreadsheet in regards to another product block on the same page where the original product on the page was BUD. The spreadsheet may say that these stores get the BUD at 8.99, but these other stores get the BUD at 9.99.

I hope I'm making sense. Does that help?

I could put together some mock up spreadsheets if you want.

[comment]

So there's another attribute here, beside Ad, Page, Store, Product and Price - and that is the Block, correct? How do they identify that? For example "Change to PEPSI for these stores" - where is the "Change COKE to PEPSI for these stores" part?

[Jeff M]

The block isn't identified in data. I only mentioned it to make sure you understood that sometimes there can be multiple products with versioning changes happening.

[Jeff M]

More info...

I was trying to simplify a bit earlier, but I fear I may have confused. Truth is, the guy who is in charge of soda may not know the page the item will be on. He'll just want to let me know that in some stores, they'll need PEPSI in the ad, while in others they'll need COKE. So, for the sake of this discussion...assume you have an ad built, then you get the attached spreadsheets.

I'm thinking during my import I would add in the ad week number, year and the page the item affects.

So I would end up with a table that has the following fields.

YEAR

WEEK

PG

STORE

PRODUCT

SIZE

PRICE

Archive.zip

[comment]

assume you have an ad built, then you get the attached spreadsheets.

...

I'm thinking during my import I would add ... the page the item affects.

But how do YOU know which page the item affects? More precisely, how do you know which existing item does the imported item replace?

[Jeff M]

But how do YOU know which page the item affects? More precisely, how do you know which existing item does the imported item replace?

My long term goal is to maintain the data for the whole thing, so I can simply analyze the data to learn where the items are.

For now, however, I'll have to enter the item, the page it falls on (which I learn by a visual inspection of the ad) and have the database tell me what to do from there.

[comment]

Let me suggest a different way of looking at the problem. For this, we need to ask what exactly is a "version".

I say that a version is group of stores that have exactly the same offers on the page. The key word here is "stores". If you don't look at this from the higher point-of-view of stores, you cannot see the entire picture.

Here I must retract my earlier statement: your data entry is NOT by the book. You are entering duplicate information. Instead of:

OfferID   Store   Product   Price

123       2       COKE      3.49

124       3       COKE      3.49

You should have:

OfferID   Product   Price

123	  COKE      3.49

Then, in another table:

OfferID   Store

123       2

123       3

If you had that, you could make a calculation in the Stores table concatenating all store's offers on a given page - then sort and group the stores by that calculation.

I suppose you're not going to be able to change the way you get the data - so you need a way to simulate the missing table and the unique OfferID. This could be done by concatenating all offer's attributes (except store) into a single field, then defining a self-join relationship of the Offers table matching on Ad, Page and store. A calculation listing the concatenated field across the self-join is going to return the same results for all records that share the same list of offers on page - IOW, this group of records is a "version".

[Jeff M]

Let me suggest a different way of looking at the problem. For this, we need to ask what exactly is a "version".

I say that a version is group of stores that have exactly the same offers on the page. The key word here is "stores". If you don't look at this from the higher point-of-view of stores, you cannot see the entire picture.

Here I must retract my earlier statement: your data entry is NOT by the book. You are entering duplicate information. Instead of:

OfferID   Store   Product   Price

123       2       COKE      3.49

124       3       COKE      3.49

You should have:

OfferID   Product   Price

123	  COKE      3.49

Then, in another table:

OfferID   Store

123       2

123       3

If you had that, you could make a calculation in the Stores table concatenating all store's offers on a given page - then sort and group the stores by that calculation.

I suppose you're not going to be able to change the way you get the data - so you need a way to simulate the missing table and the unique OfferID. This could be done by concatenating all offer's attributes (except store) into a single field, then defining a self-join relationship of the Offers table matching on Ad, Page and store. A calculation listing the concatenated field across the self-join is going to return the same results for all records that share the same list of offers on page - IOW, this group of records is a "version".

I'm afraid I'm not fully understanding your suggestion. I put together a file with 3 tables, STORES, OFFERS, OFFER_STORE_LU.

I added some sample data. Perhaps when you have time you could look at it? I'm not sure I'm understanding how to get the unique values based on this structure. Perhaps I put it together wrong.

Thanks!

Jeff

Versions.fp7.zip

[comment]

Very roughly:

Versions2.zip

[Jeff M]

Very roughly:

Okay, yes, I can see it now. It does make more sense to break it into offers, then simply summarize the unique lists of offer ID's. My data entry process will just need a good deal of dummy proofing to make sure the same offers aren't entered multiple times and that records are created for the appropriate offers in the lookup table. I think this can get me there though. I could even minimize the data entry needed by allowing entry by store number ranges, etc.

Thanks for the suggestions, I think this may be the way to go!

[comment]

As i said, it could be done with the data the way you enter it now - it's just more complicated.