Hi
I'm trying to learn how to use the data model effectively and I'm fairly confident in the basics of importing tables and joining them, adding columns etc. I'm struggling to understand how I can deal with things that change over time. I'm trying to solve the following problem using a data model.
I have two marketing campaigns - A and B. Both campaigns are run over a year and I have estimated how many users will register with each campaign. I also have an estimate of how much each user spends each month and this amount decreases over time.
I want to pass this information into my data model so that I can use a pivot / pivot chart to show the following for each Campaing:
1. Number of users at any given time (i.e. Sum of year to date registrations)
2. Monthly spend at any given time (Number of registrations x Monthly spend based on time between registration and reporting date)
3. Year to date spend (Cumulative spend based on monthly spend above)
I can probably work out how to do 1 and 3, but it's 2 that I'm really struggling with. If anyone can point me in the right directions it would be really appreciated.
Below the data I have. The format can be changed if required.
I'm trying to learn how to use the data model effectively and I'm fairly confident in the basics of importing tables and joining them, adding columns etc. I'm struggling to understand how I can deal with things that change over time. I'm trying to solve the following problem using a data model.
I have two marketing campaigns - A and B. Both campaigns are run over a year and I have estimated how many users will register with each campaign. I also have an estimate of how much each user spends each month and this amount decreases over time.
I want to pass this information into my data model so that I can use a pivot / pivot chart to show the following for each Campaing:
1. Number of users at any given time (i.e. Sum of year to date registrations)
2. Monthly spend at any given time (Number of registrations x Monthly spend based on time between registration and reporting date)
3. Year to date spend (Cumulative spend based on monthly spend above)
I can probably work out how to do 1 and 3, but it's 2 that I'm really struggling with. If anyone can point me in the right directions it would be really appreciated.
Below the data I have. The format can be changed if required.
| Sample Data.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Number of Registrations | Monthly Spend | ||||||||
| 2 | Registration Month | Campaign | Registrations | Months since registration | Campaign | Spend | ||||
| 3 | 01/01/2021 | A | 10 | 0 | A | 50 | ||||
| 4 | 01/02/2021 | A | 15 | 1 | A | 45 | ||||
| 5 | 01/03/2021 | A | 15 | 2 | A | 41 | ||||
| 6 | 01/04/2021 | A | 10 | 3 | A | 37 | ||||
| 7 | 01/05/2021 | A | 15 | 4 | A | 33 | ||||
| 8 | 01/06/2021 | A | 20 | 5 | A | 30 | ||||
| 9 | 01/07/2021 | A | 15 | 6 | A | 27 | ||||
| 10 | 01/08/2021 | A | 20 | 7 | A | 24 | ||||
| 11 | 01/09/2021 | A | 15 | 8 | A | 22 | ||||
| 12 | 01/10/2021 | A | 20 | 9 | A | 20 | ||||
| 13 | 01/11/2021 | A | 15 | 10 | A | 18 | ||||
| 14 | 01/12/2021 | A | 20 | 11 | A | 16 | ||||
| 15 | 01/01/2021 | B | 10 | 0 | B | 50 | ||||
| 16 | 01/02/2021 | B | 12 | 1 | B | 40 | ||||
| 17 | 01/03/2021 | B | 14 | 2 | B | 32 | ||||
| 18 | 01/04/2021 | B | 16 | 3 | B | 26 | ||||
| 19 | 01/05/2021 | B | 18 | 4 | B | 21 | ||||
| 20 | 01/06/2021 | B | 20 | 5 | B | 17 | ||||
| 21 | 01/07/2021 | B | 22 | 6 | B | 14 | ||||
| 22 | 01/08/2021 | B | 24 | 7 | B | 11 | ||||
| 23 | 01/09/2021 | B | 26 | 8 | B | 9 | ||||
| 24 | 01/10/2021 | B | 28 | 9 | B | 7 | ||||
| 25 | 01/11/2021 | B | 30 | 10 | B | 6 | ||||
| 26 | 01/12/2021 | B | 32 | 11 | B | 5 | ||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A16:A26,A4:A14 | A4 | =EDATE(A3,1) |
| H4:H14 | H4 | =ROUND(H3*0.9,0) |
| H16:H26 | H16 | =ROUND(H15*0.8,0) |
