SQL — Customer Movement Analysis
Customer movement analysis, across different platforms within two time period is the focus of today’s post. The original use case when I first worked on this was on brand ambassador weekly outlet footprint, and that was done in excel.
The beauty of having worked in the data industry since SAP-dominant and pre-Tableau days is that i get to witness the rapid transformation of the industry over the years. But most fundamental skills like analysis stays entirely the same. This same piece of knowledge and code has also been following 👻 me cross — industries and cross use-cases.
Not too long ago, i had been leveraging this little Swiss knife for a similar use case, on customer cross-competitor movement where i currently work. How we get those data? Well, heavy scraping is practiced here, with the data reposted to our platform and internal database. This isn’t a post on the legality of data scraping, hence will not go into that direction. Besides, the inception of the scraping project and cost did not sits with my team.
Anyway, let’s use a simple use case on promotion participation by customers(retailers) on different brands(platforms). Here, Zazada, Shokee and Shine are three key online shopping platforms; whereas Panini Clothings, H&N Fashion and SOS Fashion are 3 retailers (customers — in real life, you will have millions of them that you might then further filter base on the desired customer segments) who hosted their online stores on the online shopping platforms. These data are scraped, and flag_flashsale_participation is tagged based on a series of criteria, including the extend of promotion/ price offs and frequency.
Here, in this example, we are assuming the role of an analytics engineer for Zazada platform, and we will be focusing on the transformation to enable the movement analysis across 2 period, namely last month versus this month, and across platforms/ brands of threat. We want to know if a specific customer is having meaningful participation during our platform promotions/ sales period? or if this customer only participate on competitor’s platform sales? etc… The outcome of the analysis will then help us better understand the drivers of these customer movements and what can we do to better incentify them to participate on our platform.
In excel, a simple pivot allows us to quickly arrive at this output. A simple macro then enables this to be ran for a longer data period. To facilitate the movement analysis, we have a concatenation of ABCD, where
A Previous Month’s Promotion Participation on Own Platform(Brand)
B Current Month’s Promotion Participation on Own Platform(Brand)
C Previous Month’s Promotion Participation on Competitor Platform(Brand)
D Current Month’s Promotion Participation on Competitor Platform(Brand)
This concatenation of ABCD will then be tagged to movement groups and subgroups to enable meaningful movement analysis.
For large dataset, SQL is required. And here’s the expected final output that we will be working towards in this post:
To get started, we will need 3 set of data
1. aggregated monthly promotion participation data
2. competitor universe list
3. movement subgroup classification
There are a few key stages during the transformation process before we arrive at the final output.
> Reshaping of competitor List
> Prepping of relevant period
> Tagging of movement classification
Instead of going through each individual step, code block is provided at the end with comments for each block.
As this is yet another analytics engineering post, I will likewise, not dive into the analysis section. A transpiled version of the same code in Python will be posted when free.
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!
with
/* 01: competitor list */
table_dim_competitor_universe
(
country
, brand
, flag_competitor_universe_for_movement_reporting
) as
(values
('MY','Zazada','Y')
,('MY','Shokee','Y')
,('MY','Shine','Y')
,('SG','Zazada','Y')
,('SG','Shokee','Y')
,('SG','Shine','Y')
,('MY','AminExpress','N')
)
/* 02a: reshape competitor list - ownbrand */
, table_prep_dim_hirer_comp_ownbrand
as
(
select
country
, brand
, 'OwnBrand' as own_comp_group
, flag_competitor_universe_for_movement_reporting
from table_dim_competitor_universe
where flag_competitor_universe_for_movement_reporting='Y'
and brand='Zazada'
)
/* 02b: reshape competitor list - competitor brand */
, table_prep_dim_hirer_comp_competitor
as
(
select
country
, brand
, 'CompBrand' as own_comp_group
, flag_competitor_universe_for_movement_reporting
, country || '|Zazada|' || brand as group_brand_to_brand
from table_dim_competitor_universe
where flag_competitor_universe_for_movement_reporting='Y'
and brand<>'Zazada'
)
/* 02c: reshape competitor list*/
, table_dim_competitor_universe_brand_to_brand as
(
select
t1.country
, t1.brand
, t1.own_comp_group
, t1.flag_competitor_universe_for_movement_reporting
, t2.group_brand_to_brand
from table_prep_dim_hirer_comp_ownbrand t1
left join (select country, group_brand_to_brand from table_prep_dim_hirer_comp_competitor) t2
on t1.country=t2.country
union all
select
t1.country
, t1.brand
, t1.own_comp_group
, t1.flag_competitor_universe_for_movement_reporting
, t1.group_brand_to_brand
from table_prep_dim_hirer_comp_competitor t1
)
/* 02d: final reshaped competitor list*/
, table_dim_hirer_universe_brand_to_brand as
(
select
group_brand_to_brand
, country
, brand
, flag_competitor_universe_for_movement_reporting
, own_comp_group
from table_dim_competitor_universe_brand_to_brand
)
/* 03: data list containing flashsale participation for each customer(brand) on different platforms */
, table_monthly_aggregated_flashsale_participation_scraped
(
report_month
, country
, customer_name
, brand
, flag_flashsale_participation
) as
(values
('2023-12-01','MY','Panini Clothings','Zazada',1)
,('2023-12-01','MY','Panini Clothings','Shokee',1)
,('2023-12-01','MY','Panini Clothings','Shine',0)
,('2023-12-01','SG','Panini Clothings','Zazada',1)
,('2023-12-01','SG','Panini Clothings','Shokee',1)
,('2023-12-01','SG','Panini Clothings','Shine',0)
,('2023-11-01','MY','Panini Clothings','Zazada',1)
,('2023-11-01','MY','Panini Clothings','Shokee',1)
,('2023-11-01','MY','Panini Clothings','Shine',1)
,('2023-11-01','SG','Panini Clothings','Zazada',1)
,('2023-11-01','SG','Panini Clothings','Shokee',0)
,('2023-11-01','SG','Panini Clothings','Shine',0)
,('2023-10-01','MY','Panini Clothings','Zazada',1)
,('2023-10-01','MY','Panini Clothings','Shokee',1)
,('2023-10-01','MY','Panini Clothings','Shine',0)
,('2023-10-01','SG','Panini Clothings','Zazada',1)
,('2023-10-01','SG','Panini Clothings','Shokee',1)
,('2023-10-01','SG','Panini Clothings','Shine',0)
,('2023-12-01','MY','H&N Fashion','Zazada',0)
,('2023-12-01','MY','H&N Fashion','Shokee',1)
,('2023-12-01','MY','H&N Fashion','Shine',0)
,('2023-12-01','SG','H&N Fashion','Zazada',0)
,('2023-12-01','SG','H&N Fashion','Shokee',0)
,('2023-12-01','SG','H&N Fashion','Shine',0)
,('2023-11-01','MY','H&N Fashion','Zazada',1)
,('2023-11-01','MY','H&N Fashion','Shokee',0)
,('2023-11-01','MY','H&N Fashion','Shine',0)
,('2023-11-01','SG','H&N Fashion','Zazada',0)
,('2023-11-01','SG','H&N Fashion','Shokee',1)
,('2023-11-01','SG','H&N Fashion','Shine',0)
,('2023-10-01','MY','H&N Fashion','Zazada',1)
,('2023-10-01','MY','H&N Fashion','Shokee',0)
,('2023-10-01','MY','H&N Fashion','Shine',1)
,('2023-10-01','SG','H&N Fashion','Zazada',0)
,('2023-10-01','SG','H&N Fashion','Shokee',0)
,('2023-10-01','SG','H&N Fashion','Shine',0)
,('2023-12-01','MY','SOS Fashion','Zazada',0)
,('2023-12-01','MY','SOS Fashion','Shokee',0)
,('2023-12-01','MY','SOS Fashion','Shine',1)
,('2023-12-01','SG','SOS Fashion','Zazada',1)
,('2023-12-01','SG','SOS Fashion','Shokee',1)
,('2023-12-01','SG','SOS Fashion','Shine',0)
,('2023-11-01','MY','SOS Fashion','Zazada',1)
,('2023-11-01','MY','SOS Fashion','Shokee',1)
,('2023-11-01','MY','SOS Fashion','Shine',0)
,('2023-11-01','SG','SOS Fashion','Zazada',0)
,('2023-11-01','SG','SOS Fashion','Shokee',1)
,('2023-11-01','SG','SOS Fashion','Shine',1)
,('2023-10-01','MY','SOS Fashion','Zazada',0)
,('2023-10-01','MY','SOS Fashion','Shokee',1)
,('2023-10-01','MY','SOS Fashion','Shine',1)
,('2023-10-01','SG','SOS Fashion','Zazada',0)
,('2023-10-01','SG','SOS Fashion','Shokee',1)
,('2023-10-01','SG','SOS Fashion','Shine',1)
)
/* 04: list containing regrouping of dual-period movement */
, table_dim_movement_grouping
(
flag_prevcurr_owncomp
, movement_group
, movement_sub_group
) as
(VALUES
('NYNN','Gain','New: OwnBrand Exclusive'),
('NYYY','Gain','Comp Exclusive to Shared'),
('NYYN','Gain','Comp Exclusive to OwnBrand Exclusive'),
('NYNY','Gain','New: Shared'),
('YNYY','Loss','Churned: Shared to Comp Exclusive'),
('YNNN','Loss','Churned: OwnBrand Exclusive to NonParticipation'),
('YNNY','Loss','Churned: OwnBrand Exclusive to Comp Exclusive'),
('YNYN','Loss','Churned: Shared to NonParticipation'),
('YYNN','Retained','Retained: OwnBrand Exclusive to OwnBrand Exclusive'),
('YYNY','Retained','Retained: OwnBrand Exclusive to Shared'),
('YYYN','Retained','Retained: Shared to OwnBrand Exclusive'),
('YYYY','Retained','Retained: Shared to Shared'),
('NNYY','Non-User','Comp Exclusive to Comp Exclusive'),
('NNYN','Non-User','Comp Exclusive to NonParticipation'),
('NNNY','Non-User','New: Comp Exclusive'),
('NNNN','Non-User','NA: NonParticipation')
)
/* 05: prepping data list by removing platforms/brands that are not of interest */
, table_compute_stage_one as
(
select
date(date_parse(t1.report_month,'%Y-%m-%d')) as report_month
, t1.country
, t1.customer_name
, t1.brand
, t1.flag_flashsale_participation
, t2.flag_competitor_universe_for_movement_reporting
, t2.group_brand_to_brand
, t2.own_comp_group
from table_monthly_aggregated_flashsale_participation_scraped t1
left join table_dim_competitor_universe_brand_to_brand t2
on t1.country=t2.country
and t1.brand=t2.brand
where t2.flag_competitor_universe_for_movement_reporting='Y'
)
/* 06a: transforming of data list to aggregate and tag previous current period participation */
, table_compute_stage_two as
(
select
report_month
, country
, customer_name
, group_brand_to_brand
, max(case when own_comp_group='OwnBrand' and flag_flashsale_participation=1 then 1 else 0 end) as flag_curr_period_participation_ownbrand
, max(case when own_comp_group='CompBrand' and flag_flashsale_participation=1 then 1 else 0 end) as flag_curr_period_participation_compbrand
from
(
select
report_month
, country
, customer_name
, group_brand_to_brand
, own_comp_group
, sum(flag_flashsale_participation) as flag_flashsale_participation
from table_compute_stage_one
group by 1,2,3,4,5
)
group by 1,2,3,4
)
/* 06b: transforming of data list to aggregate and tag previous current period participation */
, table_compute_stage_three as
(
select
report_month
, country
, customer_name
, group_brand_to_brand
, case when flag_curr_period_participation_ownbrand=1 then 'Y' else 'N' end as flag_curr_period_participation_ownbrand
, case when flag_curr_period_participation_compbrand=1 then 'Y' else 'N' end as flag_curr_period_participation_compbrand
from table_compute_stage_two
)
/* 06c: transforming of data list to aggregate and tag previous current period participation */
, table_compute_stage_four as
(
select
report_month
, country
, customer_name
, group_brand_to_brand
, flag_curr_period_participation_ownbrand
, flag_curr_period_participation_compbrand
, lag(flag_curr_period_participation_ownbrand)
over (partition by group_brand_to_brand, country ,customer_name order by report_month)
as flag_prev_period_participation_ownbrand
, lag(flag_curr_period_participation_compbrand)
over (partition by group_brand_to_brand, country ,customer_name order by report_month)
as flag_prev_period_participation_compbrand
from table_compute_stage_three
)
/* 07: concatenation of previous period participation with current period participation in the format of interest */
, table_compute_stage_five as
(
select
report_month
, country
, customer_name
, group_brand_to_brand
, flag_curr_period_participation_ownbrand
, flag_curr_period_participation_compbrand
, flag_prev_period_participation_ownbrand
, flag_prev_period_participation_compbrand
, flag_prev_period_participation_ownbrand || flag_curr_period_participation_ownbrand
|| flag_prev_period_participation_compbrand || flag_curr_period_participation_compbrand
as flag_prevcurr_owncomp
from table_compute_stage_four
)
/* 08: retagging to the relevant groups */
, final as
(
select
t1.report_month
, t1.country
, t1.customer_name
, t1.group_brand_to_brand
, t1.flag_curr_period_participation_ownbrand
, t1.flag_curr_period_participation_compbrand
, t1.flag_prev_period_participation_ownbrand
, t1.flag_prev_period_participation_compbrand
, t1.flag_prevcurr_owncomp
, t2.movement_group
, t2.movement_sub_group
from table_compute_stage_five t1
left join table_dim_movement_grouping t2
on t1.flag_prevcurr_owncomp=t2.flag_prevcurr_owncomp
)
select * from final
where report_month>=date'2023-11-01'
