In this newsletter we want to show how Alteryx can be used to simply measure the increase of a market share.
We will analyse two consecutive years (2016 and 2017) and see how the market share has increased. First of all we load the files of the sales by customer and by year. We also have access to the sales potential by postal code and by year.
Summarize and join all data by year and postal code
We summarize the sales data by customer, by year and by postal code. When this is done, they are joined with the sales potential. The “join” fields of the join tool are logically the year and the postal code. All the output anchors of the join tool (left, join and right anchors) are connected to a union tool followed by a data cleansing tool (default configuration).
Why join all three outputs? Because in this way we are sure not to lose the sales potential for a year or a postal code combination for which we have no client sales. Equivalently, we will not lose the client sales for a year or a postal code combinations where, for any reason, we would have estimated no sales potential. We now have one file with the sales and the sales potential for 2016 and 2017 by postal code.
Compare the marketshare of two consecutive years
In our next article we will do the analysis at postal code. But for this one we will take a closer look at the evolution of the market share between two consecutive years.
In order to do this, we summarize the customer sales and the potential, by year, over all postal codes. This allows us to calculate the market share, for each year, by dividing the total sales by the total potential.
To compare the market share of 2016 and 2017, we want both to be a column instead of a row. To do this, we use the cross tab tool, with the field “Year” used as new column header. The crosstab tool will result in two new columns “2016” and “2017”. In our current simple example it is clear that we are talking about market share.
The cross-tab tools creates new fields with a standard name that can create confusion when we are using more complex workflows. A good way to tackle this problem even for many fields is to use the Dynamic rename tool located in the Developer toolbox. Here we select the “2016” and “2017" fields, which will be referred as “[_CurrentField_]” in an expression, and we add the suffix “Market share ” to each of these selected fields.
Calculate the relative increase of the market share
Finally, we calculate the relative increase of market share with a formula tool. We observe that the relative increase is negative. In fact, we have a very marginal decrease of the market share, we can thus talk of stability.
However, even if the market share is globally stable, geographic differences could exist. In the next arrticle we will analyse how the market share varies between 2016 and 2017 in the different postal codes.