Business Optimization with Data-Driven Dynamic Pricing Model
When I was a kid growing up, my Dad used to teach me that science is everywhere. Now, as a Data Science professional, I comprehend that Data Science, too, is everywhere. Makes sense?
Haha. Probably not.
Let me elaborate: Data Science is a skillset which can be applied to any domain, as any domain in and of itself contains information and data science involves utilizing techniques to better organize and make sense of information; this is all what data analysis, data engineering, and machine learning and predictive modeling are about!
Thus, this time, I chose to augment my portfolio further and contribute with my skills for even better decision-making. Having already done large-scale projects across a variety of huge domains like healthcare, sports analytics, social science, and retail; I chose to embark on another mass-scale data science project in the retail domain — the domain of supermarkets, seasonal trends analysis, and sales optimization.
In a different take to my usual course of action displayed in my previous works, this project will look to not only utilize cutting-edge data analysis, machine learning, and generative AI technologies combined with a structured, holistically meticulous methodology to work; rather, it will go beyond to also implement Data Engineering techniques (e.g., ETL pipelines using Apache Spark) using big data tools and exhibit a well-rounded skillset optimal for the entire landscape of data science.
The end-goal of this project will be to optimize business strategies and ultimately develop a dynamic pricing model for stores.
All the information and work in this project is open-source and free for anyone to take inspiration from and use as needed. Ours is a wonderful field filled with hard-working and well-intentioned professionals — many of whom I have myself been lucky to have taken inspiration from — and this is my way of giving back.
Background, Overview, and the Data
Three distinct datasets — pertaining to information regarding retail stores — were downloaded on to GitHub from the Kaggle page: Retail Data Analytics. Once saved, the datasets were each web-scraped, using the BeautifulSoup library in Python, on to a Python notebook in Google Colab.
The three datasets, on the whole, encompass historical weekly sales data for 45 stores located in different regions:
- Features: Contains additional data related to the store, department, and regional activity for the given dates.
- Sales: Contains historical sales data (February 2010 to November 2012).
- Stores: Contains anonymized information about the 45 stores, including the size of store.
Once in Python, the three datasets were merged into one dataframe based on the Store, then inspected for null values and duplicate rows, inappropriate data types, mismatching values by columns, etc; and cleaned extensively. Column names were also standardized, and some new columns were created for future analyses, like the Month column and the StoreSizeCategory column engineered from quantile-based binning based on store size.
Lastly, categorical columns requiring proper ordering were also ordered thus, using the pd.Categorical() function, and the cleaned, “ultimate” dataframe was now saved to a local folder for this project for ongoing analyses in newer notebooks.
Bulk of the Work: Advanced Data Analysis, Data Visualization, Data Engineering, Statistical Testing, and Modeling
Advanced Analytics in Python
In a new Google Colab notebook on Google Cloud Platform (GCP), the “ultimate” dataset was loaded in and proper, deep analysis began, with the aim being not only to unearth some amazing patterns which can be the key to optimizing the retailers’ business, but also to go above and beyond and surmise hypotheses to test, develop insightful predictive models, and ultimately drive innovation, growth, and efficiency for individuals and organization.
I urge you to check out all of my detailed Python (Google Colab, Jupyter) notebooks for this project, here in its GitHub repository. For now, here are some highlights from the analysis:
- A sample portion of analytical insights about key metrics by Store Size Category:
- Bar Chart each of total revenue earned from sales per month as well as average sales revenue per month, supplemented with a trend line for each:
- Feature Engineering: a new “Value” column which is the product of Total Sales Amount Generated in the month and the Amount of Purchases in the month; thus, the Total Revenue Generated by Month:
- Average Weekly Sales per Month, and the Percentage Change of this from month to month:
In order to optimize pricing, it is important to understand the stores’ selling patterns and their customers’ purchasing behaviors — thus, these aforementioned endeavors were undertaken. The following is a summary of key findings:
- Generally, we see a drastic increase in sales from January to February.
- Sales have only increased from September-onwards, and November and December recorded the highest increases in sales along with February.
This is explainable due to November and December containing a decent share of special sales periods such as Black Friday, End-of-Year/New Year, and Thanksgiving. - April, June, and July exhibited the highest recorded revenues and amount of products purchased, but do not induce incredible changes in customer behavior (sales actually declined a bit from June to July).
- I feel confident in concluding that November and December would have easily been the months producing the most revenue, had there been more recordings of purchases during these months.
Evidence: highest average sales in these months and higher customer purchasing behavior increases in these Months despite lower count of purchases. - If the last point was true; April, June, and July would still remain among the top most-revenue-generating months of the year, proven by their high amounts of average sales and large sample size of purchases.
Data Visualization & Dashboard Design
Taking a parallel approach to visualizations in Python, an interactive dashboard representative of the project was developed in Tableau for readers to view key metrical representations by store, or multiple stores aggregated, or all stores combined.
This was important too, as the importance of a centralized repository for visuals in a specialized visualization tool like Tableau or Power BI was recognized, especially for a project of this scale and nature.
This dashboard was backed up by sophisticated analytics in the form of Calculated Fields (e.g., DiscountToSalesRatio) and Quick Table Calculations (e.g., Rolling % of Total Revenue Generated by Date), and boasts a variety of different graphs including and not limited to pie charts and horizontal bar plots.
You are welcome to view it here.
Data Wrangling, Small-Scale Data Engineering, and ETL Pipelines in Databricks
When it comes to opting for a Cloud Data Platform to manage and execute your project, there is, thankfully, no shortage of options; from Google Cloud Platform (GCP) to Snowflake to Databricks — you name it. Despite my experience with the former, I chose Databricks for this project because I truly appreciated its capabilities during my recent “Generative AI Fundamentals” certification and wanted to further hone my skills in it.
This phase of data extraction and wrangling is predominantly in Structured Query Language (SQL), so before I go into details, here is my complete SQL code for you to view.
In Databricks, I began by building and scheduling an Extract, Transform, and Load (ETL) pipeline, written in PySpark code, for automated data ingestion and transformation every two days.
Assigned as a “Job” in the “Jobs & Pipelines” section, this feat of small-scale Data Engineering loaded in, cleaned, and organized the amalgamated, final dataset for SQL-based wrangling and analysis.
The following data extraction (DDL) and manipulation (DML) using SQL in Databricks paved the way for data modeling and analysis from fresher angles to help optimize business procedures and outcomes. Window functions, complicated JOINs, CTEs (Common Table Expressions), and Subqueries were among the array of complex SQL querying performed.
For example:
This amazing query — showcasing a combination of elements of Common Table Expressions (CTEs), Window functions, and JOINs — helped locate all those Stores (and their respective Departments) in which sales across the preceding week (measured by the ‘Date’) totaled the 90th percentile of total sales or above.
This would allow the store operators to note out the micro-trends of Stores within the year and analyze which stores boast the highest revenue earnings and how certain stores perform at given time periods throughout the year.
Get Ali Jazib Rizvi’s stories in your inbox
Join Medium for free to get updates from this writer.
Another highlight from SQL were the linear correlation measures — quantified using CORR(), REGR_SLOPE(), and REGR_INTERCEPT() — for certain key predictors with sales, refined to be displayed as a two-decimal place value with a “%” sign and transposed using UNION ALLs:
From the analysis, it is quite clear that none of the potential predictor variables have a significant relationship with the target variable of sales, besides store size — even it exhibits a weak relationship.
Thus, using SQL, I was also able to rule out a lot of possible predictors for the subsequent machine learning and modeling phase.
Again, I passionately encourage you to check out my complete SQL code, here, as well as the full repository for this project along with my other works as well.
K-Means Clustering
Once the SQL phase of the project reached its due course, the focus shifted back to Python in Google Colab and analysis resumed. Boxplots, Regression Plots, and Histograms were made, in addition to the analysis already done, to probe the data even better.
Subsequently, it was decided that the unsupervised machine learning algorithm of K-Means Clustering will be built and implemented for optimal customer segmentation.
To proceed with this plan, through feature engineering a “Transaction ID” column was made to organize product revenues by and then structure an aggregated measures’ dataset around it.
Then, a new dataframe containing aggregate measures of MonetaryValue (total revenue earned from the TransactionID in-question), Frequency (total count of purchases by the TransactionID in-question), and Recency (last purchase date from the TransactionID in-question) for each TransactionID was created.
Using an Elbow plot, it was determined that the optimal amount of clusters to utilize was 5. Ultimately, the K-Means Clustering model yielded:
Cluster Analysis followed, with the assistance of Violin Plots:
Below are the findings of the Cluster Analysis and their interpretations for business growth:
Summary of Clusters:
- Cluster 1: Retain
- Cluster 2: Re-Engage
- Cluster 3: Re-Engage More Strongly
- Cluster 4: Reward
- Cluster 5: Re-Engage More Strongly
Cluster 1: Generate Average revenue, but shop the most regularly, as indicated by their Very Low recency and High frequency.
Should value them and keep them engaged to the business with periodic discount vouchers and promotional events.
Clusters 3 and 5: Very High recency measures (i.e., purchased only periodically), Low or Average-with-concentration-on-lower-end frequency of purchases, and Very Low revenue generated.
Need to re-engage these customers back into the business with better marketing strategies and purchase incentives.
Cluster 2: Very similar to clusters 3 and 5 but with the better giveaway of Lower recency.
Need to re-engage these customers and give them incentives to spend more.
Cluster 4: High recency, Medium frequency, and Highest revenue generated. Clearly the group most profitable to the business.
Must value them as prime clients of the retail industry, and keep-by-rewarding them with regular discounts and incentives, etc.
These actionable insights from the K-Means Clustering algorithm can prove to be fruitful and especially in conjunction with the dynamic pricing model, as we will see in a bit.
The Dynamic Pricing Model
Applied Statistics & Hypothesis Testing
The time to hone in on the essence of this adventure in retail analytics was here: it was time to move on to finally building the Dynamic Pricing Model.
In order to proceed with that, however, I needed a game plan: the dynamic pricing model will be for the upcoming year-and-a-half and will work on a monthly basis.
Therefore, statistical testing must preliminarily be done to confirm that there are serious differences in sales revenue by month. Then, if results permit, I will finally step forward and craft the model with the help of Time Series Analysis & Forecasting.
Sounds good? Probably yes— and my network of fellow data scientists and analysts would agree.
So it began. A/B testing in the form of ANOVA (Analysis of Variance) as more than three groups (i.e., months) were being compared, was conducted to test for statistically significant differences among months of the year and corresponding differences in average sales.
The ANOVA result was statistically significant, with January (“T.Jan”) used as the month of reference:
This ANOVA result confirmed that there is indeed valid variation in sales across the months of the year.
Hence, it was time to finally move on to the prime objective of this entire project, the dynamic pricing model, which will be made through the help of a time series model.
Dynamic Pricing Model: Time Series Analysis & Forecasting
Meta’s Prophet forecasting tool was used to generate predicted sales revenue for the next 18 months. The results of the model were also visualized to help identify trends within the forecast, be they monthly or quarterly, and single out explanatory factors behind lower forecasts as well as target times of higher forecasts to optimize selling strategies.
Differences between the forecasted sales on the original data, from the model, and the actual sales from the data were also quantified and in raw- and percent-change form, both; to holistically analyze the model’s predictive powers and comprehend how it is planning to extrapolate its findings to the future.
A function was then defined to algorithmically churn out dynamic price change suggestions based on the percentile of forecasted sales’ monthly shifts — for example, if the monthly shift was predicted to be in the 80th percentile of all forecasted monthly sales changes, then a 10% price increase would be recommended.
This would be, essentially, the dynamic pricing model, in % values, and the following is the Dynamic Pricing Model in its truest essence, for the next 18 months:
A crucial anecdote for decision-makers in the business: dynamic pricing may be scaled by the customer segmentation results from the K-Means Clustering model.
The corporate think-tanks behind mass-scale retailers are encouraged to deeply inspect their selling strategies, micro-revenue streams, and niche product lines and informatics to then formulate plans on how to align dynamic pricing with the customer segment they are most likely to face in a given period of the year, for example.
Ending Thoughts
Like my other large-scale projects, this was another in which I got to do meaningful work replicating a real-world scenario and apply and refine my skills and craft overall.
However, unlike my previous work, in this project I went beyond my realm of (orthodox) expertise and training and into lightweight data engineering in the form of utilizing Cloud Data Platforms and tools like Apache Spark for ETL pipelines, etc.
I thoroughly enjoyed this project and believe any corporate think-tank can imbibe a lot of useful information from my findings — elaborated in their respective chapters along the journey — to implement for his or her own retailer and use case.
For any inquiries or if you would like to just chat, feel free to check out my GitHub profile and reach out to me using any of my given platforms.
A message from our Founder
Hey, Sunil here. I wanted to take a moment to thank you for reading until the end and for being a part of this community. Did you know that our team run these publications as a volunteer effort to over 3.5m monthly readers? We don’t receive any funding, we do this to support the community.
If you want to show some love, please take a moment to follow me on LinkedIn, TikTok, Instagram. You can also subscribe to our weekly newsletter. And before you go, don’t forget to clap and follow the writer️!
