SQL — Competitor Exclusivity Analysis
Many moons ago, in my youth, like many others, i drank. I visited outlets where legal age requirements are imposed. It’s easy to enter an outlet, only to discover that your favourite brands aren’t there because of the exclusive selling rights agreement that the outlet operator made with specific brand.
Exclusive selling rights are important to artificially wipe out competitions, at least in high traffic outlets with the right target audience, and where your brand presence would be important. As these exclusive selling rights agreement are often hefty in fees, we would want to invest only on the right outlets. And that’s when Competitor Exclusivity Analysis comes in handy for us to rule out outlets that has already been exclusively selling our brands organically without external monetary influences for reasons we will not delve deeper here. After all, am wearing my Data Geek hat when writing this post.
***
Conceptually, here’s what we want to achieve.
And here’s how we can do this.
For small dataset, a simple pivot and excel formula works its magic. One can easily cook out a table that looks like this within minutes.
When it comes to large dataset, you might need to resort to the good old SQL. Again, if the requirement is to examine a list of outlet and competitors within a fixed time frame, it’s probably a few minutes work.
What if you are required to examine a list of competitor branches across different states treating them as non-identical due to logistic and distribution matters, and analyzing it across months?
And making things complicated, what if your competitor list of interest is different in different states? You will often see this in more fragmented industries where the barrier to entry is low and where some of the smaller competitors aren’t of interest/ threat at that point of analysis.
Anyway, here’s how this simple SQL comes in handy. It’s like a Swiss army knife, multi-purpose, cross-industries, cross-companies, cross-use-cases. LOL. 😎
I was using this again recently. And my colleague asked how did i automate it so easily when the previous colleagues couldn’t? 👻😜Well, that’s because, competitor exclusivity analysis isn’t new, and as analysis initiator and logic owner in most companies, i would be accountable to also write my own sql, and automate my own work. It isn’t exactly the same in some companies where analysts are given the luxury of up-down trend commenting without needing to use SQLs.
***
Anyway, keeping the long story short, You will firstly need to prepare
[1] a list of our competitor list of interest in different states.
[2] a stock count report. I won’t be going in detail on the painstaking collaborative process of how we get these back in the retail world. But you will need the transactional table aggregated in the right fashion to support the analysis of interest. Using a simple table here to demonstrate how the code works.
***
Next, we enter the computations section
Stage One
This is where competitors that are not of interest are filtered out, and where a numeric 1 flag is created.
***
Stage Two
This is where the sum operator is leveraged to count the number of competitor brands.
***
Stage Three
This is where the tagging of competitor exclusivity group for each outlet is performed. Each of this outlet is treated as distinct in different state.
***
Final Stage
Lastly, these exclusivity group tags are retagged back to the main table for further analysis.
Assuming that we are an in-house analyst for Competitor A, we can easily eliminate ABC Club, Kuala Lumpur and Sunshine Club, Pulau Pinang from any exclusive selling rights investment, at least at the point of analysis.
We can also see that Sunshine Club, Kuala Lumpur, isn’t selling a specific brand exclusively and the stock count or product volume appears to be high; whereas XYZ Club is pretty highly stocked with Competitor C brands that we would like to ward off in the market for example. Are these outlets that we would want to invest in? 🤔
In real world analysis, we probably won’t see that many competitor exclusive outlets, and there are criteria beyond just product volume to determine if we would proceed with an exclusive selling rights investment. Most of the time, we will end up providing the outlet with some incentive plans to buy more of our products, and attempt to sell-in more aggressively to outlets where we have zero presence on rather than offering an exclusive selling rights agreement.
Again, this same exclusive analysis can be applied for other use cases, eg analysis on whether an online shopper is exclusively shopping from premium malls, or only a specific range of products for better segmentation and targeting.
As always, hope this article helps to simplify your world as a data analyst. 👻 Full Code Block at the End. Till the next time! Tschüss!
***
Full Code Block in SQL.
For Python learner, you may navigate here.
with
table_dim_competitor_universe
(
state
, competitor
, flag_competitor_universe_for_exclusive_reporting
) as
(values
('Kuala Lumpur','Competitor A','Y')
,('Kuala Lumpur','Competitor B','Y')
,('Kuala Lumpur','Competitor C','Y')
,('Kuala Lumpur','Competitor D','Y')
,('Kuala Lumpur','Competitor E','Y')
,('Pulau Pinang','Competitor A','Y')
,('Pulau Pinang','Competitor B','Y')
,('Pulau Pinang','Competitor C','Y')
,('Pulau Pinang','Competitor D','Y')
,('Pulau Pinang','Competitor E','Y')
,('Kuala Lumpur','Competitor Z','N')
)
, table_monthly_aggregated_stock_count_by_outlet_and_state
(
report_month
, state
, outlet_name
, competitor
, stock_count
) as
(values
('2023-12-01','Kuala Lumpur','ABC Club','Competitor A',772)
,('2023-12-01','Kuala Lumpur','Rainbow Club','Competitor D',317)
,('2023-12-01','Kuala Lumpur','Sunshine Club','Competitor B',503)
,('2023-12-01','Kuala Lumpur','Sunshine Club','Competitor C',2064)
,('2023-12-01','Kuala Lumpur','Sunshine Club','Competitor D',2654)
,('2023-12-01','Pulau Pinang','Sunshine Club','Competitor A',2017)
,('2023-12-01','Pulau Pinang','XYZ Club','Competitor C',2780)
,('2023-11-01','Kuala Lumpur','ABC Club','Competitor C',2905)
,('2023-11-01','Kuala Lumpur','Rainbow Club','Competitor D',1115)
,('2023-11-01','Kuala Lumpur','Sunshine Club','Competitor B',1978)
,('2023-11-01','Kuala Lumpur','Sunshine Club','Competitor D',1826)
,('2023-11-01','Pulau Pinang','Sunshine Club','Competitor C',2845)
,('2023-11-01','Pulau Pinang','XYZ Club','Competitor C',1859)
,('2023-12-01','Kuala Lumpur','ABC Club','Competitor Z',30)
)
, table_compute_stage_one as
(
select
date(date_parse(t1.report_month,'%Y-%m-%d')) as report_month
, t1.state
, t1.competitor
, t1.outlet_name
, t2.flag_competitor_universe_for_exclusive_reporting
, 1 as flag_competitor_exists
from table_monthly_aggregated_stock_count_by_outlet_and_state t1
inner join table_dim_competitor_universe t2
on t1.state=t2.state
and t1.competitor=t2.competitor
where t2.flag_competitor_universe_for_exclusive_reporting='Y'
)
, table_compute_stage_two as
(
select
t1.report_month
, t1.state
, t1.outlet_name
, sum(flag_competitor_exists) as count_number_of_competitor
from table_compute_stage_one t1
group by t1.report_month, t1.state ,t1.outlet_name
order by t1.report_month, t1.state ,t1.outlet_name
)
, table_compute_stage_three as
(
select
t1.report_month
, t1.state
, t1.outlet_name
, case
when count_number_of_competitor=1
then 'Competitor Exclusive'
else 'Competitor Non-Exclusive(Shared)'
end as group_competitor_exclusivity
from table_compute_stage_two t1
order by t1.report_month, t1.state ,t1.outlet_name
)
-- the report month format conversion is needed
-- because it's a hardcoded text table
-- you won't need the conversion in real life
select
date(date_parse(t1.report_month,'%Y-%m-%d')) as report_month
, t1.state
, t1.outlet_name
, t1.competitor
, t1.stock_count
, case
when t3.flag_competitor_universe_for_exclusive_reporting='N'
then 'Not in Competitor Universe'
else coalesce(t2.group_competitor_exclusivity,'Recheck')
end as group_competitor_exclusivity
from table_monthly_aggregated_stock_count_by_outlet_and_state t1
left join table_compute_stage_three t2
on date(date_parse(t1.report_month,'%Y-%m-%d'))=t2.report_month
and t1.state=t2.state
and t1.outlet_name=t2.outlet_name
left join table_dim_competitor_universe t3
on t1.state=t3.state
and t1.competitor=t3.competitorReferences:
https://www.sothebys.com/en/articles/the-top-6-most-expensive-hermes-birkin-bags
https://eelianx.medium.com/python-competitor-exclusivity-analysis-f8f6021c8a8
