Forum Discussion
Frequent visitors
For all the Power Bi experts and colleagues Eric_Zhang, ImkeF, konstantinos, OwenAuger, AlbertoFerrari, Phil_Seamark, marcorusso
I have data for 5 years from 2011 to 2015. In that time many visitors have come to a particular store and quite a few have come multiple times. I would like to see the buying behavior of persons that I can categorize as frequent visitors. The criteria for that is 7 or more visits in a 12-month period starting from the first visit as the index date for that customer. If that is complex then just a 12-month period generically might be ok too. And these customers that get marked as frequent visitors, I want to then study their products purchased, volumes etc. So any advice on how to go about marking customer as frequent visitor
10 Replies
What grain would you like the output?
Could we bucket the data into Calendar months rather than individual days?
eg, create a summary table that holds values for every customer for every month with a flag to say if that customer met your conditions of being a "frequent" customer at that time?
Is it correct that you want a global flag (rather than a time-dependent flag) per customer, such that a customer is "Frequent" if that customer made 7+ visits during the 12 month period from that customer's first visit (regardless of subsequent visits)?
If so, if you have a Customer table you could add that as a calculated column.
Could you share your data model structure? Table/column names & relationships for relevant tables.
Many thanks for your replies
The "Frequent flag" ideally needs to be for a 12 month period for a customer and then check again in the next 12 month period if that customer was again a "frequent visitor" in the subsequent period. So a customer could be flagged as "frequent" in a 12 month period starting from their 1stever visit but in the subsequent 12 month period they may have less than 7 visits so for that period the same customer is not flagged as "frequent". So it is a time-dependent flag - but solutions for both a global flag as well as a time-dependent flag would be appreciated.
The bucketing could be possible but I am not sure of the how and what of it and whether it will enable to answer the questions that I am wishing to analyse
See images below - the ed_visit_identifier is the visit ID, the mrn is the unique cutomer ID, and all images are sections of the fact table which is called 'Presentations'. The relatiosnhips are not so important as most of the data is in the fact table itself
date & time details of visitsvisit IDcustomer ID
Thanks
Abhijeet
I've mocked up a sample model using dummy sales and dummy customers.
https://1drv.ms/u/s!AtDlC2rep7a-oCN-ukeBcAxKPxjY
In this model I create a summary table called 'Customer By Month' which is aggregates the 'Sales' table to each customer and month. I then add a calculated column to this table called Customer Frequent which, for each month, counds the number of times the customer has been active in a previous month. If they are active each month it marks these accordingly.
This provides a picture over time of when the customer was active and when they wern't which you can see in the basic matrix visual on Page 1
This model can be adjusted to look back a rolling 12 months and flag where the count is >= 7 if you like
It is not just the flagging.But based on flagging I need to do things like
- how many cutomers are frequnt visitors overall
- Number of 12‐month periods with Frequent visitors and how many frequent visitiors in each period
- deomographuc breakdown of just the frequent visitiors by each year etc.
Hopefully my images will give you the info
Abhijeet
