Create a Simple Dashboard for Next Purchase Prediction with Power BI
Power BI series | Next Purchase Prediction | Next Visit Day Prediction
4 min readSep 29, 2022
What you should do, if you want to know the next visit of your customers. When and how much?
This is a step-by-step to predict the next purchase using Power BI.
View a sample dashboard by clicking here.
Table of Contents.
- Data for use
- Steps for creating measure
- Start to create ‘Next Purchase Prediction’ using Power BI
- Design your Next Purchase Prediction dashboard
Data for use
- You can download it from Kaggle.
- Get and transform data to a power query editor (To explore and clean data). Then, close and apply. And go to the report view.
- Data period : 1 Jan — 31 Dec 2016
The data shown below is an example.
Steps for creating a measure
In this article, we will follow the steps shown below :
- Create “Date of first purchase”: measures the first date of the transaction.
- Create “Date of last purchase”: measures the latest date of the transaction.
- Create “Total days”: count the number of days with transactions.
- Create “Days since last purchase”: measures the period between a visitor’s most recent purchase and the current visit of each customer.
- Create “Average days between purchase”: calculate the average time between orders for each customer.
- Create “Day above average”: to determine the days that customers visit the store later than expected to make a purchase.
Start to create ‘Next Purchase Prediction’ using Power BI
‘scanner_data’ table
- Create a new measure for the “Date of first purchase”
Date of First Purchase = FIRSTDATE(scanner_data[Date])- Create a new measure for the “Date of last purchase”
Date of Last Purchase = LASTDATE(scanner_data[Date])- Create a new measure for the “Total days”
Total Days = DISTINCTCOUNT(scanner_data[Date])- Create a new measure for the ‘Days since last purchase’
Days Since Last Purchase =VAR DateLastPurchase = [Date of Last Purchase]
VAR LastDateOfYear = CALCULATE(MAX(scanner_data[Date]), all(scanner_data))RETURN
VALUE(LastDateOfYear-DateLastPurchase)
- Create a new measure for the ‘Average days between purchase’
Average Days Between Purchase =VAR FirstDatePurchase = [Date of First Purchase]
VAR LastDatePurchase = [Date of Last Purchase]
VAR TotalDays = [Total Days]RETURN
DIVIDE(LastDatePurchase-FirstDatePurchase, TotalDays,0)
- Create a new measure for the ‘Days above average’
Days Above Average =
[Days Since Last Purchase] - [Average Days Between Purchase]‘tDimdate’ table
tDimDate =ADDCOLUMNS(
CALENDAR(minx('scanner_data',[Date]),MAXX('scanner_data',[Date])),
"Year", YEAR([Date]),
"MonthNum", MONTH([Date]),
"Month", FORMAT([Date],"mmmm"),
"YearMonthNum", FORMAT([Date], "yyyymm"),
"MonthYear", FORMAT([Date], "mmm-yyyy"),
"Q", FORMAT([Date], "\Qq-yyyy"),
"Q-Sort", QUARTER([Date])*4 + MONTH([Date]))
Manage relationship
- To create a relationship between the ‘scanner_data’ table and the ‘tDimdate’ table by using the columns ‘Date’ for both tables.
Design your Next Purchase Prediction dashboard
Let’s take a look at the sample dashboard.
See more by clicking here.
Conclusions
- As you see, it’s extremely simple to understand the next purchase analysis and create a dashboard.
- Power BI can also generate more visually appealing graphics.
- Do not use any code!! So easy to do and results in a wonderful visual.
If you require Power Bi File (.pbix), please visit and download it on my drive.
Thanks.
- Kaggle for providing the dataset.
- Many thanks to Power BI for letting us improve our skills.
- Picture from Flaticon.com
Other analytics dashboards using Power BI.
See you in the next post.
Knowledge Is Endless To Learn. Let’s study together.
I’m available on LinkedIn.
