PowerPivot for Ecommerce Analysis

Hello data peeps, I’ve been told I have some valuable Excel and Powerpivot tricks in my head so I thought that I might as well share them. I know that I’ve spent many hours suffering LEARNING.. the ins and outs of taking horribly formatted data and making it usable in Excel and PowerPivot, so I’ve decided to start putting my experiences down on paper (err code?) with real world examples. I also tend to do a good job aggregating the knowledge of others, so I’ll pass these learnings along to you guys. If I’m not being clear enough in an example or you think there is an error in my ways, feel free to let me know.

I’ll be posting common workflows and techniques I use for prepping data from some popular ecommerce channels (Shopify, WooCommerce, Amazon, Ebay, Etsy), and also Quickbooks. For anyone who’s had issues working with Quickbooks data, I feel your pain. My goal is to address some of the main issues that I’ve run into with reformatting, reorganizing, and importing data (people, the original ETL tool!) into PowerPivot and Access for analysis. Especially on a reocurring basis. I hate when I setup something really nice and then it breaks when I try to add new data to it…grr….    Once these are covered I’ll go through some ways to setup PowerPivot data models and dashboards for analysis. I’m sure I’ll also touch on some other data related topics but these will be the main focus.

For the PowerPivot naysayers…

How can you design an effective, useful, and appealing dashboard?

Step 1, Pick your chart. My thoughts, try not to look at too many things at once. In fact, when I’m creating a dashboard or specifically digging for insights I try to follow a one-to-many or many-to-one design, then start slicing the data. Otherwise I’ll tend suffer from paralysis by analysis and start falling down […]