Hello, I'm stuck with an Excel task and would really appreciate if someone can guide me in the right direction.
I have a dataset of active users for a Marketplace (fake numbers, obviously). The dataset has the following columns.
I want to create a table representation that depicts the weekly performance of new users with essential metrics, such as the ratio of postings to Weekly Active Users (WAU), all sorted by the dates of user acquisition and listing dates. Also, want to categorize by city for a better understanding of regional variations.
I have used pivot table and have the structure ready with all the essential metrics. However, since there are 2 dates (cohort_week and article_week) I'm either getting the count of num_users (WAU) wrong or an incorrect sum of num_article (postings). Can someone guide me what I'm doing wrong and direct me to a route I can take to overcome this issue? if you see the data, cohort_week is repeated per city inturn repeating the num_user. However, article_week keeps changing each row and also repeats per city which means the num_article needs to be filtered by article_week to get the sum per that particular article_week.Here's the pictorial depiction of a part of the data. The check column was added by me to use "if" statement to return true when cohort_week = article_week but it didn't help.
Here's how my pivot table is looking:
With the check filter as True, I'm getting the right user count but incorrect sum of postings (new_article).
With the below method, I'm getting the right sum of postings but the user number keeps getting added up giving incorrect user_count.
fyi - the postings:WAU is a calculated field I created in pivot table to show the ratio of postings (num_article)/WAU(num_user in a particular cohort_week).
Do I need to filter the original data before creating pivot. If so how? or what's the best possible solution? Really appreciate the time and guidance folks.
I have a dataset of active users for a Marketplace (fake numbers, obviously). The dataset has the following columns.
I want to create a table representation that depicts the weekly performance of new users with essential metrics, such as the ratio of postings to Weekly Active Users (WAU), all sorted by the dates of user acquisition and listing dates. Also, want to categorize by city for a better understanding of regional variations.
I have used pivot table and have the structure ready with all the essential metrics. However, since there are 2 dates (cohort_week and article_week) I'm either getting the count of num_users (WAU) wrong or an incorrect sum of num_article (postings). Can someone guide me what I'm doing wrong and direct me to a route I can take to overcome this issue? if you see the data, cohort_week is repeated per city inturn repeating the num_user. However, article_week keeps changing each row and also repeats per city which means the num_article needs to be filtered by article_week to get the sum per that particular article_week.Here's the pictorial depiction of a part of the data. The check column was added by me to use "if" statement to return true when cohort_week = article_week but it didn't help.
| Copy of Home Assignment_SOM.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | region | cohort_week | num_user | article_week | num_article | Check | ||
| 2 | British Columbia | 2023-13 | 482 | 2023-13 | 2,047 | True | ||
| 3 | British Columbia | 2023-13 | 482 | 2023-14 | 1,963 | False | ||
| 4 | British Columbia | 2023-13 | 482 | 2023-15 | 1,723 | False | ||
| 5 | British Columbia | 2023-13 | 482 | 2023-16 | 1,653 | False | ||
| 6 | British Columbia | 2023-13 | 482 | 2023-17 | 1,423 | False | ||
| 7 | British Columbia | 2023-13 | 482 | 2023-18 | 1,400 | False | ||
| 8 | British Columbia | 2023-13 | 482 | 2023-19 | 920 | False | ||
| 9 | British Columbia | 2023-13 | 482 | 2023-20 | 540 | False | ||
| 10 | British Columbia | 2023-14 | 553 | 2023-14 | 2,134 | True | ||
| 11 | British Columbia | 2023-14 | 553 | 2023-15 | 1,924 | False | ||
| 12 | British Columbia | 2023-14 | 553 | 2023-16 | 1,712 | False | ||
| 13 | British Columbia | 2023-14 | 553 | 2023-17 | 1,423 | False | ||
| 14 | British Columbia | 2023-14 | 553 | 2023-18 | 1,210 | False | ||
| 15 | British Columbia | 2023-14 | 553 | 2023-19 | 750 | False | ||
| 16 | British Columbia | 2023-14 | 553 | 2023-20 | 589 | False | ||
| 17 | British Columbia | 2023-15 | 576 | 2023-15 | 1,820 | True | ||
| 18 | British Columbia | 2023-15 | 576 | 2023-16 | 1,650 | False | ||
| 19 | British Columbia | 2023-15 | 576 | 2023-17 | 1,560 | False | ||
| 20 | British Columbia | 2023-15 | 576 | 2023-18 | 1,123 | False | ||
| 21 | British Columbia | 2023-15 | 576 | 2023-19 | 920 | False | ||
| 22 | British Columbia | 2023-15 | 576 | 2023-20 | 432 | False | ||
| 23 | British Columbia | 2023-16 | 627 | 2023-16 | 2,312 | True | ||
| 24 | British Columbia | 2023-16 | 627 | 2023-17 | 1,870 | False | ||
| 25 | British Columbia | 2023-16 | 627 | 2023-18 | 1,642 | False | ||
| 26 | British Columbia | 2023-16 | 627 | 2023-19 | 1,350 | False | ||
| 27 | British Columbia | 2023-16 | 627 | 2023-20 | 970 | False | ||
| 28 | British Columbia | 2023-17 | 625 | 2023-17 | 2,310 | True | ||
| 29 | British Columbia | 2023-17 | 625 | 2023-18 | 1,850 | False | ||
| 30 | British Columbia | 2023-17 | 625 | 2023-19 | 1,467 | False | ||
| 31 | British Columbia | 2023-17 | 625 | 2023-20 | 1,048 | False | ||
| 32 | British Columbia | 2023-18 | 741 | 2023-18 | 2,389 | True | ||
| 33 | British Columbia | 2023-18 | 741 | 2023-19 | 1,917 | False | ||
| 34 | British Columbia | 2023-18 | 741 | 2023-20 | 1,542 | False | ||
| 35 | British Columbia | 2023-19 | 752 | 2023-19 | 1,340 | True | ||
| 36 | British Columbia | 2023-19 | 752 | 2023-20 | 2,412 | False | ||
| 37 | British Columbia | 2023-20 | 891 | 2023-20 | 2,089 | True | ||
| 38 | New York | 2023-13 | 55 | 2023-13 | 267 | True | ||
| 39 | New York | 2023-13 | 55 | 2023-14 | 217 | False | ||
| 40 | New York | 2023-13 | 55 | 2023-15 | 175 | False | ||
| 41 | New York | 2023-13 | 55 | 2023-16 | 130 | False | ||
| 42 | New York | 2023-13 | 55 | 2023-17 | 50 | False | ||
| 43 | New York | 2023-13 | 55 | 2023-18 | 21 | False | ||
| 44 | New York | 2023-13 | 55 | 2023-19 | 13 | False | ||
| 45 | New York | 2023-13 | 55 | 2023-20 | 5 | False | ||
| 46 | New York | 2023-14 | 162 | 2023-14 | 872 | True | ||
| 47 | New York | 2023-14 | 162 | 2023-15 | 460 | False | ||
| 48 | New York | 2023-14 | 162 | 2023-16 | 265 | False | ||
| 49 | New York | 2023-14 | 162 | 2023-17 | 210 | False | ||
| 50 | New York | 2023-14 | 162 | 2023-18 | 140 | False | ||
| 51 | New York | 2023-14 | 162 | 2023-19 | 110 | False | ||
| 52 | New York | 2023-14 | 162 | 2023-20 | 56 | False | ||
| 53 | New York | 2023-15 | 189 | 2023-15 | 885 | True | ||
| 54 | New York | 2023-15 | 189 | 2023-16 | 510 | False | ||
| 55 | New York | 2023-15 | 189 | 2023-17 | 432 | False | ||
| 56 | New York | 2023-15 | 189 | 2023-18 | 280 | False | ||
| 57 | New York | 2023-15 | 189 | 2023-19 | 176 | False | ||
| 58 | New York | 2023-15 | 189 | 2023-20 | 123 | False | ||
| 59 | New York | 2023-16 | 198 | 2023-16 | 811 | True | ||
| 60 | New York | 2023-16 | 198 | 2023-17 | 645 | False | ||
| 61 | New York | 2023-16 | 198 | 2023-18 | 465 | False | ||
| 62 | New York | 2023-16 | 198 | 2023-19 | 334 | False | ||
| 63 | New York | 2023-16 | 198 | 2023-20 | 275 | False | ||
| 64 | New York | 2023-17 | 270 | 2023-17 | 1,123 | True | ||
| 65 | New York | 2023-17 | 270 | 2023-18 | 843 | False | ||
| 66 | New York | 2023-17 | 270 | 2023-19 | 723 | False | ||
| 67 | New York | 2023-17 | 270 | 2023-20 | 376 | False | ||
| 68 | New York | 2023-18 | 211 | 2023-18 | 940 | True | ||
| 69 | New York | 2023-18 | 211 | 2023-19 | 734 | False | ||
| 70 | New York | 2023-18 | 211 | 2023-20 | 410 | False | ||
| 71 | New York | 2023-19 | 335 | 2023-19 | 1,321 | True | ||
| 72 | New York | 2023-19 | 335 | 2023-20 | 1,111 | False | ||
| 73 | New York | 2023-20 | 348 | 2023-20 | 1,256 | True | ||
| 74 | Ontario | 2023-13 | 2,766 | 2023-13 | 9,911 | True | ||
| 75 | Ontario | 2023-13 | 2,766 | 2023-14 | 8,123 | False | ||
| 76 | Ontario | 2023-13 | 2,766 | 2023-15 | 6,570 | False | ||
| 77 | Ontario | 2023-13 | 2,766 | 2023-16 | 5,457 | False | ||
| 78 | Ontario | 2023-13 | 2,766 | 2023-17 | 4,321 | False | ||
| 79 | Ontario | 2023-13 | 2,766 | 2023-18 | 3,312 | False | ||
| 80 | Ontario | 2023-13 | 2,766 | 2023-19 | 3,110 | False | ||
| 81 | Ontario | 2023-13 | 2,766 | 2023-20 | 2,321 | False | ||
| 82 | Ontario | 2023-14 | 2,338 | 2023-14 | 8,991 | True | ||
| 83 | Ontario | 2023-14 | 2,338 | 2023-15 | 7,645 | False | ||
| 84 | Ontario | 2023-14 | 2,338 | 2023-16 | 5,421 | False | ||
| 85 | Ontario | 2023-14 | 2,338 | 2023-17 | 3,412 | False | ||
| 86 | Ontario | 2023-14 | 2,338 | 2023-18 | 2,456 | False | ||
| 87 | Ontario | 2023-14 | 2,338 | 2023-19 | 2,134 | False | ||
| 88 | Ontario | 2023-14 | 2,338 | 2023-20 | 1,742 | False | ||
| 89 | Ontario | 2023-15 | 2,503 | 2023-15 | 9,786 | True | ||
| 90 | Ontario | 2023-15 | 2,503 | 2023-16 | 8,543 | False | ||
| 91 | Ontario | 2023-15 | 2,503 | 2023-17 | 6,593 | False | ||
| 92 | Ontario | 2023-15 | 2,503 | 2023-18 | 5,111 | False | ||
| 93 | Ontario | 2023-15 | 2,503 | 2023-19 | 3,216 | False | ||
| 94 | Ontario | 2023-15 | 2,503 | 2023-20 | 2,213 | False | ||
| 95 | Ontario | 2023-16 | 2,959 | 2023-16 | 11,236 | True | ||
| 96 | Ontario | 2023-16 | 2,959 | 2023-17 | 9,943 | False | ||
| 97 | Ontario | 2023-16 | 2,959 | 2023-18 | 7,543 | False | ||
| 98 | Ontario | 2023-16 | 2,959 | 2023-19 | 5,317 | False | ||
| 99 | Ontario | 2023-16 | 2,959 | 2023-20 | 3,157 | False | ||
| 100 | Ontario | 2023-17 | 3,437 | 2023-17 | 13,456 | True | ||
| 101 | Ontario | 2023-17 | 3,437 | 2023-18 | 11,125 | False | ||
| 102 | Ontario | 2023-17 | 3,437 | 2023-19 | 8,528 | False | ||
| 103 | Ontario | 2023-17 | 3,437 | 2023-20 | 5,789 | False | ||
| 104 | Ontario | 2023-18 | 3,225 | 2023-18 | 12,673 | True | ||
| 105 | Ontario | 2023-18 | 3,225 | 2023-19 | 10,987 | False | ||
| 106 | Ontario | 2023-18 | 3,225 | 2023-20 | 8,468 | False | ||
| 107 | Ontario | 2023-19 | 3,593 | 2023-19 | 13,764 | True | ||
| 108 | Ontario | 2023-19 | 3,593 | 2023-20 | 12,112 | False | ||
| 109 | Ontario | 2023-20 | 3,731 | 2023-20 | 14,687 | True | ||
DATA | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F109 | F2 | =IF(B2=D2,"True","False") |
Here's how my pivot table is looking:
| Copy of Home Assignment_SOM.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Check | True | ||||||||||
| 2 | ||||||||||||
| 3 | Column Labels | |||||||||||
| 4 | British Columbia | New York | Ontario | |||||||||
| 5 | Row Labels | Sum of num_user | Sum of num_article | Sum of Posts:WAU | Sum of num_user | Sum of num_article | Sum of Posts:WAU | Sum of num_user | Sum of num_article | Sum of Posts:WAU | ||
| 6 | 2023-13 | 482 | 2047 | 4 | 55 | 267 | 5 | 2766 | 9911 | 4 | ||
| 7 | 2023-14 | 553 | 2134 | 4 | 162 | 872 | 5 | 2338 | 8991 | 4 | ||
| 8 | 2023-15 | 576 | 1820 | 3 | 189 | 885 | 5 | 2503 | 9786 | 4 | ||
| 9 | 2023-16 | 627 | 2312 | 4 | 198 | 811 | 4 | 2959 | 11236 | 4 | ||
| 10 | 2023-17 | 625 | 2310 | 4 | 270 | 1123 | 4 | 3437 | 13456 | 4 | ||
| 11 | 2023-18 | 741 | 2389 | 3 | 211 | 940 | 4 | 3225 | 12673 | 4 | ||
| 12 | 2023-19 | 752 | 1340 | 2 | 335 | 1321 | 4 | 3593 | 13764 | 4 | ||
| 13 | 2023-20 | 891 | 2089 | 2 | 348 | 1256 | 4 | 3731 | 14687 | 4 | ||
right user #,wrong article # | ||||||||||||
With the check filter as True, I'm getting the right user count but incorrect sum of postings (new_article).
With the below method, I'm getting the right sum of postings but the user number keeps getting added up giving incorrect user_count.
| Copy of Home Assignment_SOM.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | cohort_week | (All) | ||||||||||
| 2 | ||||||||||||
| 3 | Column Labels | |||||||||||
| 4 | British Columbia | New York | Ontario | |||||||||
| 5 | Row Labels | Min of num_user | Sum of num_article | Sum of Postings:WAU | Min of num_user | Sum of num_article | Sum of Postings:WAU | Min of num_user | Sum of num_article | Sum of Postings:WAU | ||
| 6 | 2023-13 | 482 | 2047 | 4 | 55 | 267 | 5 | 2766 | 9911 | 4 | ||
| 7 | 2023-14 | 482 | 4097 | 4 | 55 | 1089 | 5 | 2338 | 17114 | 3 | ||
| 8 | 2023-15 | 482 | 5467 | 3 | 55 | 1520 | 4 | 2338 | 24001 | 3 | ||
| 9 | 2023-16 | 482 | 7327 | 3 | 55 | 1716 | 3 | 2338 | 30657 | 3 | ||
| 10 | 2023-17 | 482 | 8586 | 3 | 55 | 2460 | 3 | 2338 | 37725 | 3 | ||
| 11 | 2023-18 | 482 | 9614 | 3 | 55 | 2689 | 2 | 2338 | 42220 | 2 | ||
| 12 | 2023-19 | 482 | 8664 | 2 | 55 | 3411 | 2 | 2338 | 47056 | 2 | ||
| 13 | 2023-20 | 482 | 9622 | 2 | 55 | 3612 | 2 | 2338 | 50489 | 2 | ||
right article #,wrong user # | ||||||||||||
fyi - the postings:WAU is a calculated field I created in pivot table to show the ratio of postings (num_article)/WAU(num_user in a particular cohort_week).
Do I need to filter the original data before creating pivot. If so how? or what's the best possible solution? Really appreciate the time and guidance folks.
