\n
![](\"https:\/\/images.squarespace-cdn.com\/content\/v1\/56713bf4dc5cb41142f28d1f\/1505251951491-PPWE3JPXURFDOZVAWC99\/Data%2BLake%2BAnalytics.png?format=1500w\")
<\/p>\n
\n<\/p>\n
Next, start learning about your data sources<\/strong>. Where did it come from? How was it encoded? (By human or machine?) How is it stored? Are there discontinuities? Maybe they converted from an AS\/400 at some point and everything thereafter has more decimals or more values. Once you\u2019ve documented and understood your sources, you have reached the second stage: data sloughs (note, you have more than one).<\/p>\n Now comes the fun part:\u00a0cleaning up your data<\/strong>. This means getting rid of the duplicates, dealing with the blanks, and fixing data type problems. It might mean harmonizing dates like 02\/03\/04 (Canada) and 02\/04\/03 (US), time zones, or even daylight savings time. Count the records in each of your tables and make sure the totals make sense. Maybe your extract only took the last year instead of the last five. Your goal here is to fix the obvious issues in each of the data elements. This is just cursory cleaning, but you now have reached stage three: data ponds.<\/p>\n After cleaning, it\u2019s on to organizing your data<\/strong>. For most analyses (and to help with your cleaning) this means a single table. One. One table to rule them all.<\/p>\n Start by unwinding your cross-tabs so that each data element is only in a single column. Set up keys and then join the tables together. You might have to use dates or lat-longs or fuzzy join on names or addresses. You might end up with a really wide table with repeating elements. That\u2019s fine. Space is cheap.<\/p>\n Make sure each variable (column) is properly described and understood. Is rec_date<\/em>\u00a0the date the order was received or the date the record was created? You might even want to build a data dictionary.<\/p>\n This is boring work, but once you\u2019re done it, you\u2019ve reached level four. Your ponds are now consolidated into a single data pond.<\/p>\n Now it\u2019s time to create new data<\/strong>. What? Create new data? You heard me right. Very rarely will your dataset contain all the variables you want. The real gold is when you combine existing fields to form new ones. Here are some examples to prime your thinking:<\/p>\n If you have drive time and distance, calculate the average speed.<\/p>\n<\/li>\n If you have revenue and quantity, calculate the average price.<\/p>\n<\/li>\n If you have yearly revenues, create a percentage change in revenue<\/p>\n<\/li>\n If you have age specific population by year, subtract this year\u2019s eighteen-year-old population from last year\u2019s seventeen-year-olds to get a net migration.<\/p>\n<\/li>\n If you have donation data where extreme values dominate, put them into log space.<\/p>\n<\/li>\n<\/ul>\n You get the picture. You are value-adding like a boss here.<\/p>\n Pay special attention to categorical data \u2013\u00a0it can be incredibly valuable. It often makes sense to create new categories out of continuous variables (high, medium, or low), or to consolidate existing ones. Sometimes, you need to re-categorize the past to match up to the current category definitions. Other times, you need to group something up from 90 categories to five super-categories.<\/p>\n Let your original business question be your guide, but don\u2019t be afraid to venture outside of the specific problem. Follow your intuition. Sometimes you\u2019ll identify trends or errors in these new data elements that weren\u2019t obvious in the raw data.<\/p>\n Weeks will pass.<\/p>\n Finally, you have your dataset assembled. You\u2019ve cleaned up the obvious issues and you have a pretty good idea of what you have. Can you start modelling now?<\/p>\n No, not even close. You\u2019re only at level five. You\u2019ve got yourself a little data lake, but its waters are brackish.<\/p>\n It\u2019s time to start digging into the data content<\/strong>. This will perhaps be the longest stage, but it accomplishes two things: it ensures each variable (including your new ones) is internally consistent and it ensures that your relationships are logical.<\/p>\n Proceed visually. Summary statistics are not your friend \u2013 they may in fact lead you astray<\/a>.<\/p>\n Internal consistency is achieved through histograms and the like. Start by plotting a frequency distribution of each variable, one at a time. Look closely at these for gaps, peaks, or outliers. Sometimes you\u2019ll get a normal or lognormal distribution, sometimes it will be uniform. Ask yourself what you should expect to see before you peek.<\/p>\n Let\u2019s say you want to plot net migration by age. What do you expect to see? Uniform? Perhaps some age-related shifts around university?<\/p>\n<\/div>\n\n