PowerPivot for Ecommerce Analysis

Hello data peeps, I’ve been told I have some valu­able Excel and Pow­er­pivot tricks in my head so I thought that I might as well share them. I know that I’ve spent many hours suf­fer­ing LEARNING.. the ins and outs of tak­ing hor­ri­bly for­mat­ted data and mak­ing it usable in Excel and Pow­er­Pivot, so I’ve decided to start putting my expe­ri­ences down on paper (err code?) with real world exam­ples. I also tend to do a good job aggre­gat­ing the knowl­edge of oth­ers, so I’ll pass these learn­ings along to you guys. If I’m not being clear enough in an exam­ple or you think there is an error in my ways, feel free to let me know.

I’ll be post­ing com­mon work­flows and tech­niques I use for prep­ping data from some pop­u­lar ecom­merce chan­nels (Shopify, WooCom­merce, Ama­zon, Ebay, Etsy), and also Quick­books. For any­one who’s had issues work­ing with Quick­books data, I feel your pain. My goal is to address some of the main issues that I’ve run into with refor­mat­ting, reor­ga­niz­ing, and import­ing data (peo­ple, the orig­i­nal ETL tool!) into Pow­er­Pivot and Access for analy­sis. Espe­cially on a reocur­ring basis. I hate when I setup some­thing really nice and then it breaks when I try to add new data to it…grr.…    Once these are cov­ered I’ll go through some ways to setup Pow­er­Pivot data mod­els and dash­boards for analy­sis. I’m sure I’ll also touch on some other data related top­ics but these will be the main focus.

For the Pow­er­Pivot 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 cre­at­ing a dash­board or specif­i­cally dig­ging for insights I try to fol­low a one-to-many or many-to-one design, then start slic­ing the data. Oth­er­wise I’ll tend suf­fer from paral­y­sis by analy­sis and start falling down […]