Retail Projects 1 - Laptop Sales Analysis Intro 1 - Section 128

Search the site...

Geo-targeting Project [28-25]

Let's take a quick look at how the stores were performing in Q2:

proc sql;
create table stat1 as
select store_postcode, sum(price) as total_sales_q1
from sales_q1
group by store_postcode
order by store_postcode;
quit;

proc sql;
create table stat2 as
select store_postcode, sum(price) as total_sales_q2
from sales_q2
group by store_postcode
order by store_postcode;
quit;

proc sort data=target; by store_postcode; run;

data stat;
merge stat1 stat2 target (in=c);
by store_postcode;
if c then target = 1;
else target = 0;

format increase percent.;

increase = total_sales_q2 / (total_sales_q1) - 1;
run;

Did the code fail?

Copy and run the code below to create the input data sets.

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/Sales_Q1.csv"
 out = Sales_Q1
 dbms = CSV
 replace
 ;
run;

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/locations.csv"
 out = locations
 dbms = CSV
 replace
 ;
run;

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/postcode.csv"
 out = postcode
 dbms = CSV
 replace
 ;
run;

proc sql;
create table sales_q1_loc as
select a.*, b.os_x as store_x, b.os_y as store_y
from sales_q1 a, locations b
where a.store_postcode = b.postcode;
quit;

proc sql;
create table sales_q1_loc_pos as
select a.*, b.os_x as cust_x, b.os_y as cust_y
from sales_q1_loc a left join postcode b
on a.customer_postcode = b.postcode;
quit;

filename postcode '/folders/myfolders/Geo-targeting/Data Files/open_postcode_geo.csv';

data all_postcode (keep=postcode os_x os_y);
infile postcode dsd firstobs=2;
input postcode : $8.
      col1 : $10.
      col2 : $5.
      os_x os_y
      col3 
      area : $8.;
if area = "England" and os_x^=.;
run;

proc sql;
create table sales_q1_loc_pos2 as
select a.*, b.os_x as cust_x1, b.os_y as cust_y1
from sales_q1_loc_pos a left join all_postcode b
on a.customer_postcode = b.postcode;
quit;

data sales_q1_loc_pos3;
set sales_q1_loc_pos2;

if cust_x = . then cust_x = cust_x1;
if cust_y = . then cust_y = cust_y1;

drop cust_x1 cust_y1;
run;

data sales_q1_loc_pos4;
set sales_q1_loc_pos3;

distance = round(((store_x-cust_x)**2 + (store_y-cust_y)**2) **0.5, 1);

run;

proc sql;
create table target as
select distinct store_postcode
from sales_q1_loc_pos4
where store_postcode in ('E7 8NW' 'N17 6QA' 'CR7 8LE');
quit;

proc sql;
create table target_postcode as
select a.*,
       round(((b.os_x - a.os_x)**2 + (b.os_y - a.os_y)**2) **0.5, 1) as dist
from all_postcode a, locations b
where b.postcode in
  (select store_postcode
   from target) and 
calculated dist <= 4500;
quit;

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/computers.csv"
 out = computers
 dbms = CSV
 replace
 ;
run;

proc sql;
create table config as
select configuration, count(*) as num_sold, sum(price) as total_sales
from sales_q1_loc_pos4
group by configuration
order by total_sales desc;
quit;

proc import datafile = "/folders/myfolders/Geo-targeting/Data Files/Sales_Q2.csv"
 out = Sales_Q2
 dbms = CSV
 replace
 ;
run;

The STAT table shows the sales for Q1 and Q2 across the 15 stores:

The TARGET column identifies the stores we selected for the direct marketing campaign.

1 = Selected
0 = Not selected

The stores selected for the direct marketing campaign have outperformed the rest of the stores.

The three selected stores reported a sales increase of 8, 9 and 1%.

The rest of the stores are mostly negative in the increment column.