Hi,
I'm working with web traffic data where I have pageviews of different pages of a website. Some of the pages have UserID in the URL while some don't.
The data looks like this:
Web_Visit_Date | WebPage | UserID | User_Account_Created | PageViews |
2/2/2021 | webpage/createprofile | 25 | ||
2/6/2021 | webpage/createprofile | 12 | ||
2/12/2021 | webpage/myprofile/u99 | 99 | 2/12/2021 | 21 |
2/12/2021 | webpage/myprofile/u34 | 34 | 2/12/2021 | 21 |
2/12/2021 | webpage/myprofile/u47 | 47 | 2/6/2021 | 1 |
2/12/2021 | webpage/createprofile | 15 | ||
2/13/2021 | webpage/myapps/u34 | 34 | 2/12/2021 | 11 |
2/13/2021 | webpage/myapps/u99 | 99 | 2/12/2021 | 3 |
2/13/2021 | webpage/myapps/u47 | 47 | 2/6/2021 | 2 |
2/13/2021 | webpage/myprofile/u55 | 55 | 2/13/2021 | 19 |
2/13/2021 | webpage/myapps/u55 | 55 | 2/13/2021 | 10 |
2/13/2021 | webpage/myprofile/u81 | 81 | 2/12/2021 | 41 |
2/13/2021 | webpage/createprofile | 75 | ||
2/14/2021 | webpage/newapp/u34 | 34 | 2/12/2021 | 5 |
2/14/2021 | webpage/newapp/u47 | 47 | 2/6/2021 | 12 |
2/14/2021 | webpage/newapp/u55 | 55 | 2/13/2021 | 7 |
2/14/2021 | webpage/newapp/u81 | 81 | 2/12/2021 | 41 |
2/14/2021 | webpage/createprofile | 23 | ||
2/15/2021 | webpage/createprofile | 9 | ||
2/16/2021 | webpage/myprofile/u19 | 19 | 2/15/2021 | 21 |
2/16/2021 | webpage/myapps/u19 | 19 | 2/15/2021 | 11 |
2/16/2021 | webpage/newapp/u19 | 19 | 2/15/2021 | 5 |
I want to create a view where I can see the total pageviews of the 'webpage/createprofile' by date and the total number of accounts created on that date.
Desired View: Create Profile Visits vs. Accounts Created | ||
Date | Create Profile Pageviews | No. of Accounts Created |
2/2/2021 | 25 | 0 |
2/6/2021 | 12 | 1 |
2/12/2021 | 15 | 3 |
2/13/2021 | 75 | 1 |
2/14/2021 | 23 | 0 |
2/15/2021 | 9 | 1 |
I'm not able to figure out how to get the number of accounts created for each date because every user has visited one or the other page on multiple days and taking a distinct count of 'User_Account_Created' column isn't giving the correct number of accounts created.
I'm thinking I'll need to something at script level but not sure.
Any help would be greatly appreciated.
If you use the condition that the User_Account_Created is equal to Web_Visit_Date, you can have those expressions with set analysis:
Count({<Web_Visit_Date={"=Web_Visit_Date=User_Account_Created"}>} Distinct UserID)
JG