Discovering Top-performing Stores Out of 150 Existing to Invest Money into Marketing & Promotion using Tableau
The company we’re looking at today provides cleaning services. You drop off your dirty clothes at their location, clothes get washed and pressed. Then next day you pick them up or clothes get delivered to your location. The dataset contains information about 150 stores, including location (state,city), revenue, marketing spend and region. In addition, 10 of those 150 stores have been opened recently.
All this data will help us answer a few very important questions:
- Which of the two sales regions is performing better?
Based on these metrics:
a) Average revenue per city
b) Average marketing spend per city (the less the better)
c) Average ROMI per city (ROMI = revenue / marketing spend)
BEYOND that we must figure out which of the 10 new locations has the best potential to invest more money into marketing.
First off, let’s VISUALIZE the regions and get average financial figures up:
It’s already obvious that Region 2 (Orange) is outperforming Region 1 (Blue) and this answers question 1 for us right away.
Now let’s turn our data into a scatterplot:
This visualisation shows how certain stores perform based on marketing spend and revenue. We can already pick a few stores to invest more money into based on this graph. For example, stores #64 and #38, where revenue is extremely high but marketing spend is low. Perhaps if we invest more money into marketing for these stores they’ll start making even more money? Maybe.
However, since in this particular business there’s a linear correlation between how many people bring in their dirty clothes and how much revenue the business makes, let’s separate our stores into clusters based on City population. Maybe the more people live in a town, the more money we’ll make? We’ll grab population data from 2015 Census and apply it to what we have:
Very nice! So our theory proved to be true. If you look over the red dot cluster you’ll see that these stores are operating in towns with a lower population of about 100,000 people and therefore revenue does not go above $25,000. Blue cluster shows higher revenue due to higher population also, and so does the orange.
So now we have a better understanding which stores we can pick for further marketing promotion and we can probably just pick the top orange stores with high revenue and low marketing spend to boost revenue even higher via marketing channels. But first let’s figure out just HOW EFFECTIVE our marketing efforts will be based on the data we have. Since we can do that, why not?
Let’s include trend lines in our visualisation. Trend lines will give us a rough formula that’ll show how much revenue we’re making for each dollar spent on marketing:
And instantly here’s what we see:
Red cluster shows that for every Dollar spent on Marketing we’re making $0.94 in revenue.
Blue cluster shows that for every Dollar spent on Marketing we’re making $7.32 in revenue. !
Orange cluster shows that for every Dollar spent on Marketing we’re making $3.17 in revenue.
So now if we toggle on “Highlight New Expansion” to “New” in the top right corner of our visualisation you’ll see the new locations highlighted.
And the stores fit for investing more marketing funds are #146,150,148 and 143. Store #145 has to be looked at in more detail also as it looks very promising based on our research.