Frequent visitors

[CAPEconsulting]

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

[Phil_Seamark]

HI @CAPEconsulting

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

[Phil_Seamark]

This model can be adjusted to look back a rolling 12 months and flag where the count is >= 7 if you like

[CAPEconsulting]

@Phil_Seamark

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

[CAPEconsulting]

@Phil_Seamark @OwenAuger @ImkeF @marcorusso

So following from my images what I have done till now is add a calculated column

Visit Counter = RANKX(CALCULATETABLE('Presentations', ALLEXCEPT('Presentations', 'Presentations'[mrn])), 'Presentations'[arrival_time],,ASC)

So this gives eachunique consumer and their visits a chronologocal sequence based number

Then another calculated column 

Visit 7 or over = IF('Presentations'[Visit Counter] >= 7, "Index", BLANK()) so this is whenever a cisitir has the 7th visit

Now what I need is another calculated column for each consumer IF [Visit 7 or more] = Index then subtraction of time like DATEDIFF for the arrival date of 7th visit - arrival time of 1st visit -AND- arrival date of 8th visit - 2nd visit AND SO ON for all visits of all consumers whose Visit Counter is >=7. ANY ADVISE ON NHOW TO DO THAT

Then that in that calculated column we could see if the difference is lesser than 12 months will mean that the visitor had >= 7 visits in a 12 month period. ANY ADVISE ON HOW TO MAKE THIS ALL DYNAMIC rather than just calculated static columns

Abhijeet

[CAPEconsulting]

Hi all,

If have kind of solved a iterating 12 month period problem. But not a 12 month bin from an index date for unique customer

I have made up a few calculated columns as follows: -

Visit Counter = RANKX(CALCULATETABLE('Presentations', ALLEXCEPT('Presentations', 'Presentations'[mrn])), 'Presentations'[arrival_time],,ASC)

Visit Index = IF('Presentations'[Visit Counter] >= 7, "Index", BLANK())

Visit Minus = IF('Presentations'[Visit Index] = "Index", 'Presentations'[Visit Counter] - 6, BLANK())

Visit Difference =

VAR later = IF('Presentations'[Visit Index] = "Index", 'Presentations'[arrival_date], BLANK())

VAR previous = LOOKUPVALUE('Presentations'[arrival_date], 'Presentations'[Visit Counter], 'Presentations'[Visit Minus], 'Presentations'[mrn], 'Presentations'[mrn])

RETURN

DATEDIFF(previous, later, MONTH)

Visit Frequent = IF('Presentations'[Visit Difference] <= 12 && 'Presentations'[Visit Index] = "Index", "Frequent", "Usual")

And then finally a Measure as 

Frequent Visitors = CALCULATE(DISTINCTCOUNT('Presentations'[mrn]), 'Presentations'[Visit Frequent] = "Frequent")

Any improvements or alternatives you would like to suggest?

Abhijeet

[CAPEconsulting]

Hi all including@Phil_Seamark and @OwenAuger, Eagerly waiting for some advise,

What I am after is the 1st visit of vistor becomes an index date and therafter from that date we have 12 month bins specific to that visitor and then to see how many 12 month periods did a visitior visit in and in those 12 month bins how many times they visited 7 or more times.

The creation of an Index date based on the 1st visit of each person is fine but creating the 12 month bins for each visitor based on their 1st ever visit is my question - how to do that and then how to calculate no. of visits in each those bins for each unique visitor.

Any advise would be appreciated.

Abhijeet

[CAPEconsulting]

@marcorusso and/or @AlbertoFerrari 

Any advise on this question please

Abhijeet

[OwenAuger]

@CAPEconsulting

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.

[CAPEconsulting]

@Phil_Seamark and @OwenAuger

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

[Phil_Seamark]

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?