Greetings! I could sure use some help with a challenge I’m having. I have spent tons of time working on this, without success. I’ve tried everything I know how to try, and must come to the conclusion that I simply don’t yet know enough to do it. It’s probably far simpler than I’m trying to make it.
Attached is an image of the tables and relationships, with all the extraneous fields removed to keep it simple.
- When people make a purchase, it goes into a batch.
- Batches are processed at certain intervals. Customers may or may not have a purchase in each batch.
- Someone can become a customer before they make a purchase as part of a batch.
- This means that a new customer may or may not make a purchase the first time they can. For example, person XYZ might become a customer on July 4, 2018, and the next batch is August 1, 2018. They may or may not have a purchase in that batch.
- Each person’s first opportunity to make a purchase in a batch is represented by a batch date in the person table: first batch chance.
- The range of “first batch chance” is limited to actual batch dates, which are represented in the “batch” table as “Batch Date”.
- Each time a person makes a purchase in a batch, a record is created in the “Batch Purchase” table. The entry in that table is tied to that person and the specific batch.
- Each batch can have many purchases.
So, the relationships:
- Batch:Batch ID with Batch Purchase:Batch ID
- Person:Person ID with Batch Purchase:Person ID
- Batch:Batch Date with (INACTIVE) Person:First Batch Chance
I’m trying to find several pieces of information here, but the one I’m hung up on is as follows:
- For each batch, how many people had that as their first possible purchase date, and actually made a purchase? For example:
- For the August 1 batch, 2400 customers had that as their ‘first batch chance”.
- Of those 2400 new customers, 1300 actually had a purchase in that batch.
- Those two numbers allow me to calculate what % of new customers actually purchased in their first possible chance.
So, really, what I have been trying to build is a measure that calculates (counts) how many people actually made a purchase in their first possible batch.
I have spent a ton of time on this, and tried a hundred different things, to no avail. Any assistance anyone could provide would be greatly appreciated. If you need any more details or information, please let me know.