Customer Visitation Behavior: Identifying Deviations from Cumulative Averages with Power BI
Understanding and tracking customer behavior is vital for any business looking to optimize marketing strategies, enhance customer retention, and improve overall performance. One effective method for achieving this is to monitor customer visitation patterns and identify when their behavior deviates from the norm. This publication explores the concept of using cumulative averages of monthly visits to gauge changes in customer engagement and offers guidance on how to implement this approach effectively.
Begin by calculating the cumulative average of monthly visits for each customer. This average represents the typical or expected visitation pattern for a given customer. By having this baseline, we can then compare it with their current visitation data to determine whether they are visiting more or less frequently than usual.
Why to categorize by Cumulative Average of Visits:
— Provides a long-term view of customer loyalty and engagement.
— Reflects overall customer history and commitment.
— Useful for identifying your most loyal and valuable customers over time.
— Effective for loyalty programs and customer retention strategies.
— Helpful in identifying long-term trends in customer behavior.
Let’s assume that your dataset looks like the below:
Import your dataset into Power BI…and then let’s start to create the necessary column!
My_CumulativeAvgVisits =
//set the variables for the calculations
VAR CurrentCustomerID = Customers[Customer_ID]
VAR CurrentMonth = Customers[Month]
//we want to calculate for each customer by moving month
VAR FilteredTable =
FILTER(
Customers,
Customers[Customer_ID] = CurrentCustomerID &&
Customers[Month] <= CurrentMonth
)
//sum the visits for all months
VAR CumulativeSum =
SUMX(
FilteredTable,
Customers[Visits]
)
//count the months that he visits
VAR CumulativeCount =
COUNTX(
FilteredTable,
Customers[Visits]
)
RETURN
IF(
CumulativeCount = 0,
BLANK(),
//devide the sum of visits by the count of months by customer for each month
CumulativeSum / CumulativeCount
)Good! What did you just make? Take a look of a customer focus sample.
The customer ‘842374’ was more frequent visitor on May’22. The key step in this approach is to compare the customer’s current monthly visits with their cumulative average. The deviation can be calculated by subtracting the cumulative average from the current month’s visitation data.
You can create a flag using another custom column with any condition that suits your business needs. At the moment, we want to flag those having more, less or same visits following their cumulative average vs monthly visitation score.
My_CheckMonthlyVisitation =
SWITCH(
TRUE(),
Customers[My_CumulativeAvgVisits] > Customers[Visits], "less",
Customers[My_CumulativeAvgVisits] = Customers[Visits], "same",
"more"
)Plot it by using a 100% stacked bar and voila! Seems that 2 out of 10 customers increase their visits in October while this is a downward trend since January.
When deviations are detected, it’s time to take action. Strategies can be tailored to the specific customer group based on their visitation patterns:
- For customers with lower-than-usual visitation, consider sending targeted marketing promotions to re-engage them?
- For customers with higher-than-usual visitation, offer rewards or incentives to encourage continued engagement?
Customizing the time period for cumulative averages and the specific thresholds is crucial for adapting this approach to your unique business needs. Regular review and adjustment of these parameters ensure the accuracy and relevance of your analysis. The choice of whether to categorize your customers by their cumulative average of visits or by monthly visits depends on your specific business goals and the nature of your customer base.
Hope that helps!
Don’t forget to subscribe to
and join our Power BI community
