Repository files navigation

This repository contains scripts and functions for creating a database, creating views, managing user roles, and forming personal offers aimed at the growth of the average check, increasing the frequency of visits, and cross-selling.

• RetailAnalitycs in postgresql
  • Table of Contents
  • Database Creation
  • Views Creation
  • User Roles
  • Forming Personal Offers for Average Check Growth
  • Forming Personal Offers for Increasing Frequency of Visits
  • Forming Personal Offers for Cross-Selling
  • Input Data
    • Personal information Table
    • Cards Table
    • Transactions Table
    • Checks Table
    • Product grid Table
    • Stores Table
    • SKU group Table
    • Date of analysis formation Table
  • Output data
    • Customers View
    • Purchase history View
    • Periods View
    • Groups View
  • License

To create the database and tables described in the Input data, follow the steps below:

1. Execute thepart1.sql script provided in the repository.
2. This script will create the necessary tables and also include procedures for importing and exporting data for each table from/to CSV and TSV files.
3. Make sure to upload the required CSV and TSV files from the datasets folder to the repository.

To create the views described in the Output data, follow the steps below:

1. Execute thepart2.sql script provided in the repository.
2. This script will create the views and include test queries for each view.

To set up user roles and their permissions, follow the steps below:

1. Execute thepart3.sql script provided in the repository.
2. This script will create the roles and assign the following permissions:
   • Administrator: This role has full permissions to edit and view any information, as well as start and stop the processing.
   • Visitor: This role only has permission to view information of all tables.

To form personal offers aimed at the growth of the average check, follow the steps below:

1. Execute thepart4.sql script provided in the repository.
2. This script contains a function that determines offers based on the average check calculation method, first and last dates of the period, number of transactions, coefficient of average check increase, maximum churn index, maximum share of transactions with a discount, and allowable share of margin.
3. The function will output the customer ID, average check target value, offer group, and maximum discount depth for each offer.

To form personal offers aimed at increasing the frequency of visits, follow the steps below:

1. Execute thepart5.sql script provided in the repository.
2. This script contains a function that determines offers based on the first and last dates of the period, added number of transactions, maximum churn index, maximum share of transactions with a discount, and allowable margin share.
3. The function will output the customer ID, period start date, period end date, target number of transactions, offer group, and maximum discount depth for each offer.

To form personal offers aimed at cross-selling, follow the steps below:

1. Execute thepart6.sql script provided in the repository.
2. This script contains a function that determines offers based on the number of groups, maximum churn index, maximum consumption stability index, maximum SKU share, and allowable margin share.
3. The function will output the customer ID, SKU offers, and maximum discount depth for each offer.

This project is licensed under theMIT License. Feel free to use and modify the code according to your needs.