As data analytics professionals, we often have to find a correlation between a business's performance and factors affecting the business's performance. We also would like to analyze our performance along various dimensions such as date hierarchy, location hierarchy, product hierarchy, etc.
One of the major analytics exercises is to predict the performance of a business on various dimensions of time information such as the day of the week, day of the month, special days like New Year, Black Friday, Christmas Eve, or anything like that and then the quarter of the year, a month of the year, etc.
As so many factors come into play, one approach to understanding these dimensions would be to create value for each dimension for each transactional record through data transformations. This is a very resource-intensive process. We can use the cross-mapping technique to help us quickly create additional dimensions for any transaction date.
How do we go about it? At first, we create a table with all the dates of a particular year (or period of interest) in an Excel workbook. For every date, we also identify whether it is a weekday or weekend day, what day of the week, day in the month, day in the year, and whether it is a special day, etc. This table can be repeated for every new year before the new year sets in.
Let’s look at the sample transaction data
Then, we cross-map date information in every transaction (can be done in Excel using VLOOKUP or in the database using SQL join). Using the cross-map table, we can get all necessary analytics dimensions data.
I have created the cross-map table for time information. Similar cross-map tables can be made for location dimensions (Area/City/State/Country) and product hierarchies.
Learn the In-demand Data Analysis Skills and stay on top of industry Trends with our CBDA Training.
I am attaching the template I created for 2024 for our company, Adaptive US. Also, let me know if we can think of other types of cross-map templates as well.