Going from usage to usage frequency over time

[milpro011]

Hi all,

I have usage patterns by user, with number of actions performed during that specific day (simplified): 

User	Date	#Actions
X	17/03/2016	2
X	18/03/2016	1
X	23/03/2016	4
Y	20/03/2016	6
Y	25/03/2016	2

I'm looking to establish the usage frequency per user. I want to measure it over 14 days and to bucket the users based on how many days they were active: daily - 9 or more days over the period of 14 days , weekly - 5-8 days, occasionally - 1-4 days, Inactive - 0.

I have no issues creating the mentioned usage frequency buckets based on the last 14 days. However, what I would love to create are weekly/monthly usage frequency snapshots as I'd love to display the weekly/monthly evolution of the usage frequency. 

e.g. week 1: 5 daily, 3 weekly, 2 occasionally, 20 inactive

      week 2: 6 daily, 5 weekly, 6 occasionally, 13 inactive

      week 3: 10 daily, 7 weekly, 10 occasionally, 3 inactive

How should I approach this problem? It feels that this has to be a PowerQuery function that would append weekly/monthly usage frequency "sets". Any help, direction would be of immense help. 

Many thanks!

[Anonymous]

Hi @milpro011,

You can refer to below steps to achieve your requirement.

Table.

Add calculate column to calculate the frequency.

frequency = 
var currWeekNum=WEEKNUM(MAX([Date]),1)
Var actionCount =
if(currWeekNum>1,
COUNTX(FILTER(ALL('Calculate Count'),
OR(WEEKNUM([Date],1)=WEEKNUM(EARLIER('Calculate Count'[Date]),1),WEEKNUM([Date],1)=WEEKNUM(EARLIER('Calculate Count'[Date]),1)-1)&&
AND(YEAR([Date])=YEAR(EARLIER([Date])),[#Actions]=EARLIER('Calculate Count'[#Actions]))),[Date])
,
COUNTX(FILTER(ALL('Calculate Count'),
OR(WEEKNUM([Date],1)=WEEKNUM(EARLIER('Calculate Count'[Date]),1)&&
YEAR([Date])=YEAR(EARLIER([Date])),
WEEKNUM([Date],1)=WEEKNUM(Date(YEAR(EARLIER('Calculate Count'[Date])),12,31),1)&&
YEAR([Date])=YEAR(EARLIER([Date]))-1)&&[#Actions]=EARLIER('Calculate Count'[#Actions])
),[Date])//condition to deal with year transfer
)
return
if(actionCount>=9,"daily",if(actionCount>=5,"weekly",if(actionCount>=1,"occasionally","Inactive")))

Create a summary table to show the detail information.

Summary of Week =
var temp= DISTINCT(SELECTCOLUMNS('Calculate Count',"WeekNumber",WEEKNUM([Date],1),"Current frequency",[#Actions]&": "&[frequency]))
return
SUMMARIZE(temp,[WeekNumber],"Detail",CONCATENATEX(FILTER(temp,[WeekNumber]=EARLIER([WeekNumber])),[Current frequency]&", "))

In addition, I find that the "inactive" actions seems not appear on above summary table, I will keep researching to solve this issue.

Regards,

Xiaoxin Sheng

[milpro011]

@Anonymous, thank you so much for putting together the potential solution. However, I do feel that I must have wrongly explained my actual problem. I'll give it another try. 

1. #of actions / day is irrelevant info - my bad. As long as a user has at least 1 action / day - he was active on that day. 

2. I am looking to find how many days were people active over a period of 14 days. So, if in 14 days someone was active 9+ days, then I consider that user to be a daily user, etc. 

Now, what I want to create are weekly or monthly snapshots of my situation. It would be great to be able to view last 12 periods, whether it's months or weeks. 

So, for every period, that has a cut-off date, for example, we take a cut-off date to be the end of a particular week/month:

1. Create a subset of data by disregarding any activity after the cut-off

2. Look back 14 days from that date

3. Create usage frequency buckets / per user for that "cut-off date"

For any period, whether it's taken from the end of the week or month, there can only be one "daily", one "weekly" ..etc. buckets with X, Y, Z users in it respectfully. 

Finally, plot the usage frequency per cut-off date. 

Any clues?

Thanks, m.

[Anonymous]

Hi @milpro011,

It sounds like I have misunderstood your requirement, you want to calculate the frequency by user,right?

If this is a case, I have modify above formula to calculate the users(a bit modify).

Calculate column:

frequency =
var currWeekNum=WEEKNUM(MAX([Date]),1)
Var actionCount =
COUNTAX(FILTER(ALL('Calculate Count'),
AND([Date]>=DATEADD('Calculate Count'[Date],-14,DAY)&&[Date]<=EARLIER([Date]),[User]=EARLIER('Calculate Count'[User]))),[User])
return
if(actionCount>=9,"daily",if(actionCount>=5,"weekly",if(actionCount>=1,"occasionally","Inactive")))

Summary of Week =
var temp =DISTINCT(SELECTCOLUMNS('Calculate Count',"WeekNumber",WEEKNUM([Date],1),"User",[User],"frequency",[frequency]))
var result=SELECTCOLUMNS(DISTINCT(SELECTCOLUMNS(temp,"Week",[WeekNumber],"User",[User],"frequency",
if(CONTAINS(FILTER(temp,[User]=EARLIER([User])&&[WeekNumber]=EARLIER([WeekNumber])),[frequency],"daily"),"daily",
if(CONTAINS(FILTER(temp,[User]=EARLIER([User])&&[WeekNumber]=EARLIER([WeekNumber])),[frequency],"weekly"),"weekly",
if(CONTAINS(FILTER(temp,[User]=EARLIER([User])&&[WeekNumber]=EARLIER([WeekNumber])),[frequency],"occasionally"),"occasionally","Inactive"
))))),"WeekNumber",[Week],"Current frequency",[User]&": "&[frequency])
return
SUMMARIZE(result,[WeekNumber],"Detail",CONCATENATEX(FILTER(result,[WeekNumber]=EARLIER([WeekNumber])),[Current frequency]&", "))

Notice: I add a method to filter the low state.

If above still not in the right direction, please feel free to let me know.

Regards,

Xiaoxin Sheng

[milpro011 · Accepted solution]

@Anonymous, thank you so much for taking the time. I eneded up just doing it step by step. 

1. I added 5 dimensions in my original table, one for each period with the period date

2. I created 5 group queries, each grouping on a different dimension

3. I merged the 5 queries and got one master table with bucets per period