Subscribe to DSC Newsletter

See What You Can do With One Aster Command....

We would like to know if the customer discounts are having any effect on customer visits. We'll look to see if having a large discount (greater than .10 cents) leads to a greater number of additional purchases made at the store. Specifically, we want to know the date of the first large discount event ( > .10), the size of the discount, and the total number of unique products purchased after that discount. First, construct an nPath query that returns the total number of products purchased after receiving a discount, and the total number of product purchased before receiving a discount.

Click here for more

What would happen if you replaced FIRST(customer_id of DISCOUNT) with FIRST(customer_id of NODISCOUNT)

SELECT * FROM nPath( ON prod.sales_fact

PARTITION BY customer_id

ORDER BY sales_date

PATTERN('^(NODISCOUNT*).(DISCOUNT).(A*)')

SYMBOLS(discount_amount < .10 as NODISCOUNT, discount_amount >= .10 as DISCOUNT, TRUE as A)

RESULT (FIRST(customer_id of NODISCOUNT) as customer_id

, FIRST(sales_date of DISCOUNT) as discount_date

, FIRST(discount_amount of DISCOUNT) as first_large_discount

, COUNT(* of A) as post_discount_visits

, COUNT(* of NODISCOUNT) AS pre_discount_visits)

  MODE(NONOVERLAPPING));

 

Determine the average, minimum, maximum number of days between visits and the count of the total number of visits by gender and frequent buyer level for the months of June and July 2008. Marketing would like this information sorted by gender and frequent buyer level.

 

select

  1. c.gender

, c.frequent_buyer_level

, sum(t.visit_count) total_visit_count

, avg(t.b_sales_date - t.a_sales_date) avg_days_between_visits

, min(t.b_sales_date - t.a_sales_date) min_days_between_visits

, max(t.b_sales_date - t.a_sales_date) max_days_between_visits from nPath (

ON (select s.* FROM prod.sales_fact as s INNER JOIN prod.product_dim as p ON (s.product_id = p.product_id)

where s.sales_date::date between to_date('2008-06-01', 'YYYY-MM-DD') and to_date('2008-08-01', 'YYYY-MM-DD'))

PARTITION BY customer_id

ORDER BY sales_date

MODE(OVERLAPPING)

PATTERN('A.B')

SYMBOLS(

true as A, true as B

)

RESULT(

FIRST(customer_id of A) as customer_id

, LAST(sales_date of B) as b_sales_date

, FIRST(sales_date of A) as a_sales_date

, COUNT(* of ANY(A)) as visit_count

)

) T

, prod.CUSTOMER_DIM C

WHERE T.CUSTOMER_ID = C.CUSTOMER_ID

GROUP BY C.GENDER, C.FREQUENT_BUYER_LEVEL, visit_count

  ORDER BY C.GENDER, C.FREQUENT_BUYER_LEVEL;

 

 

Detect customers that purchase the same category of items in three baskets in a row with a total value of those items greater than $150 just before Halloween (Use the month of October 2008).

Display the customer name, category name and three month basket value in the output.

 

select C.first_name || ' ' || C.last_name, A_all_product_categories, A_basket_value, B_basket_value, C_basket_value

from npath(

ON (select * from npath( ON ( select s.basket_id, p.product_category_name, s.sales_date, s.customer_id, SUM((p.retail_price - p.unit_cost - s.discount_amount) * s.sales_quantity)::DECIMAL(10,2) AS catValue from prod.sales_fact as s INNER JOIN prod.product_dim as p ON (s.product_id = p.product_id) WHERE s.sales_date::date between to_date('2008-10-01', 'YYYY-MM-DD') and to_date('2008-10-31', 'YYYY-MM-DD') group by customer_id, sales_date, basket_id, product_category_name)

PARTITION BY basket_id

ORDER BY product_category_name

MODE(NONOVERLAPPING)

PATTERN('A*')

SYMBOLS ( true as A)

RESULT(first(basket_id of A) as basket_id,

accumulate(product_category_name of A) as all_product_categories,

first(sales_date of A) as sales_date,

first(customer_id of A) as customer_id,

sum(catvalue of A) as basket_value)))

PARTITION BY customer_id

ORDER BY sales_date, basket_id

MODE(OVERLAPPING)

PATTERN('A.B.C')

SYMBOLS (true as A, true as B, true as C)

RESULT(first(customer_id of A) as customer_id,

first(all_product_categories of A) as A_all_product_categories,

first(all_product_categories of B) as B_all_product_categories,

first(all_product_categories of C) as C_all_product_categories,

first(basket_value of A) as A_basket_value,

first(basket_value of B) as B_basket_value,

first(basket_value of C) as C_basket_value)) T,

  1. prod.customer_dim C

WHERE T.customer_id = C.customer_id AND

A_all_product_categories like B_all_product_categories AND

C_all_product_categories like B_all_product_categories AND

  (A_basket_value + B_basket_value + C_basket_value > 150.00);
Detect stores with decreasing basket value and a decreasing number of customers between the starting month and the next month whilst there is an increase in both the basket value and number of customers (basket count) in the third month. In this case review Jan 2008 through Mar 2008 (1st Quarter). We are looking for any stores where there was a dip in customer baskets and basket values in the second month of the quarter. Display the Store Name, Basket Count and Basket Value for each month.

SELECT s.store_name, A_visits, B_visits, C_visits,

A_BasketValue, B_BasketValue, C_BasketValue

FROM npath(

ON

(select store_id, count(distinct basket_id) as visits_count,

AVG((p.retail_price - p.unit_cost - sf.discount_amount) * sf.sales_quantity)::DECIMAL(10,2) AS BasketValue

, date_trunc('month', sales_date) as month_Id

FROM prod.sales_fact sf, prod.product_dim p

WHERE sales_date::date between to_date('2008-01-01', 'YYYY-MM-DD') and to_date('2008-03-31', 'YYYY-MM-DD')

group by store_id, date_trunc('month', sales_date))

PARTITION BY store_id

ORDER BY month_Id

MODE(NONOVERLAPPING)

PATTERN( 'A.B.C')

SYMBOLS(true as A, true as B, true as C)

RESULT(FIRST (store_id of A) as store_id,

FIRST(visits_count of A) as A_visits,

FIRST(visits_count of B) as B_visits,

FIRST(visits_count of C) as C_visits,

FIRST(BasketValue of A) as A_BasketValue,

FIRST(BasketValue of B) as B_BasketValue,

  FIRST(BasketValue of C) as C_BasketValue)) T,

  1. prod.store_dim S

WHERE

A_visits > B_visits AND

B_visits < C_visits AND

A_BasketValue > B_BasketValue AND

B_BasketValue < C_BasketValue AND

  S.store_id = T.store_id;

Click here for more

Views: 166

Comment

You need to be a member of Data Science Central to add comments!

Join Data Science Central

Videos

  • Add Videos
  • View All

© 2020   TechTarget ®   Powered by

Badges  |  Report an Issue  |  Privacy Policy  |  Terms of Service