With the holiday shopping craziness over, we wanted to use Power BI to analyze the last 5 years of research around Black Friday and Cyber Monday including spending, new trends in online shopping and the use of different devices.
With Power Query we were able to import and transform data available from the NRF Foundation, IBM Research and Adobe’s Blog.
There are some interesting insights based on the results:
Total Black Friday weekend spending went down from $59.05bn in 2012 to $57.43bn in 2013, even though the percentage of adults saying that they would go out for holiday shopping that weekend increased year over year. This is explained by the decrease in average spending per shopper based mostly on lower retail prices. Also, the percentage of people completing their holiday shopping over the weekend has slowly increased over the last 5 years from 8% on 2009 to 11% on 2013.
When we take a look at the type of gifts that are more popular over Black Friday weekend, clothing has held the number one spot for the last 5 years, with Consumer Electronics and Media, in general, fighting for second place.
With online sales, we can see how shopping habits in America are changing. For shoppers buying online over the Black Friday weekend, 43.7% of their budget was spent online. Black Friday online sales for 2013 reached almost $2bn and are up 65% when compared to 2011.
Mobile devices are becoming more and more important when it comes to online shopping: almost 40% of traffic and more than 20% of sales were generated from a mobile device for 2013. Also, as you can see smartphones drive a lot of traffic but tablets are driving a higher percentage of sales.
Now, looking at Cyber Monday sales, the percentage of buyers that declared participating in promotions on this day has increased consistently over the last 5 years from 42% in 2009 to 55% in 2013. When it comes to mobile devices, the same type of behavior we saw on Black Friday pops up: traffic is driven by smartphones but sales are driven by tablets.
This post includes the Black Friday file attached below so you can play around with the Power View interactive dashboards and get more insights about holiday shopping behavior. Enjoy!
I’d like to get someone's opinion on whether the set up I have is most efficient or if there are any other recommendations.
I currently have 3 tables.:
1st table is named “stock” and has 1Million rows
2nd table is named “optimization” and has 400K rows
3rd table is named “fill rate” and has 100K rows
The “stock” table is downloaded via sql from our MRP system
The “optimization” table is also downloaded via sql from our MRP system
The “fill rate” table is downloaded from SAP via business warehouse.
Once all three are downloaded, I import all three via text files into Microsoft Access.
I then create a query to join the three tables and bring in the necessary columns and I also create calculated columns .I run the query and make it a new table in Access and I name the table ‘analysis’ in Access
After those steps I connect powerpivot to the table in Access named ‘anaylsis’ and begin to perform my analysis in Powerpivot.
Is this the most efficient method to use? If I were to use SQL Server Express instead of MS Access would it make this process easier and quicker to run? What are your thoughts?
P. Jones - why are you first putting it in access? where do the first 3 tables reside? Might be a solution to pull them directly into Power Pivot, and define relationships or DAX queries there. If it doesn't import directly into PowerPivot you could also consider using Power Query to import it, build your expressions and calculated columns and load it into PowerPivot. - Feel free to follow up with me directly
Comments in this blog are open and monitored for each post for a period of one week after the posting date. If you have a specific question about a blog post that is older than one week, please submit your question via our Twitter handle @MSPowerBI