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
To create the database and tables described in the Input data, follow the steps below:
- Execute the
part1.sqlscript provided in the repository. - 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.
- 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:
- Execute the
part2.sqlscript provided in the repository. - 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:
- Execute the
part3.sqlscript provided in the repository. - 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:
- Execute the
part4.sqlscript provided in the repository. - 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.
- 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:
- Execute the
part5.sqlscript provided in the repository. - 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.
- 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:
- Execute the
part6.sqlscript provided in the repository. - 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.
- The function will output the customer ID, SKU offers, and maximum discount depth for each offer.
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Customer ID | Customer_ID | --- | --- |
| Name | Customer_Name | Cyrillic, the first letter is capitalized, the rest are upper case, dashes and spaces are allowed | --- |
| Surname | Customer_Surname | Cyrillic, the first letter is capitalized, the rest are upper case, dashes and spaces are allowed | --- |
| Customer E-mail | Customer_Primary_Email | E-mail format | --- |
| Customer phone number | Customer_Primary_Phone | +7 and 10 Arabic numerals | --- |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Card ID | Customer_Card_ID | --- | --- |
| Customer ID | Customer_ID | --- | One customer can own several cards |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Transaction ID | Transaction_ID | --- | Unique value |
| Card ID | Customer_Card_ID | --- | --- |
| Transaction sum | Transaction_Summ | Arabic numeral | Transaction sum in rubles(full purchase price excluding discounts) |
| Transaction date | Transaction_DateTime | dd.mm.yyyy hh:mm:ss | Date and time when the transaction was made |
| Store | Transaction_Store_ID | Store ID | The store where the transaction was made |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Transaction ID | Transaction_ID | --- | Transaction ID is specified for all products in the check |
| Product in the check | SKU_ID | --- | --- |
| Number of pieces or kilograms | SKU_Amount | Arabic numeral | The quantity of the purchased product |
| Total amount for which the product was purchased | SKU_Summ | Arabic numeral | The purchase amount of the actual volume of this product in rubles (full price without discounts and bonuses) |
| The paid price of the product | SKU_Summ_Paid | Arabic numeral | The amount actually paid for the product not including the discount |
| Discount granted | SKU_Discount | Arabic numeral | The size of the discount granted for the product in rubles |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Product ID | SKU_ID | --- | --- |
| Product name | SKU_Name | Cyrillic, Arabic numerals, special characters | --- |
| SKU group | Group_ID | --- | The ID of the group of related products to which the product belongs (for example, same type of yogurt of the same manufacturer and volume, but different flavors). One identifier is specified for all products in the group |
| Product purchase price | SKU_Purchase_Price | Arabic numeral | The purchase price of the product for this store |
| Product retail price | SKU_Retail_Price | Arabic numeral | The sale price of the product excluding discounts for this store |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Store | Transaction_Store_ID | --- | --- |
| Product ID | SKU_ID | --- | --- |
| Product purchase price | SKU_Purchase_Price | Arabic numeral | Purchasing price of products for this store |
| Product retail price | SKU_Retail_Price | Arabic numeral | The sale price of the product excluding discounts for this store |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| SKU group | Group_ID | --- | --- |
| Group name | Group_Name | Cyrillic, Arabic numerals, special characters | --- |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Date of analysis | Analysis_Formation | dd.mm.yyyy hh:mm:ss | --- |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Customer ID | Customer_ID | --- | Unique value |
| Value of the average check | Customer_Average_Check | Arabic numeral, decimal | Value of the average check in rubles for the analyzed period |
| Average check segment | Customer_Average_Check_Segment | High; Middle; Low | Segment description |
| Transaction frequency value | Customer_Frequency | Arabic numeral, decimal | Value of customer visit frequency in the average number of days between transactions |
| Transaction frequency segment | Customer_Frequency_Segment | Often; Occasionally; Rarely | Segment description |
| Number of days since the previous transaction | Customer_Inactive_Period | Arabic numeral, decimal | Number of days passed since the previous transaction date |
| Churn rate | Customer_Churn_Rate | Arabic numeral, decimal | Value of the customer churn rate |
| Churn rate segment | Customer_Churn_Segment | High; Middle; Low | Segment description |
| Segment number | Customer_Segment | Arabic numeral | The number of the segment to which the customer belongs |
| Main store ID | Customer_Primary_Store | --- | --- |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Customer ID | Customer_ID | --- | --- |
| Transaction ID | Transaction_ID | --- | --- |
| Transaction date | Transaction_DateTime | dd.mm.yyyyy hh:mm:ss.0000000 | The date when the transaction was made |
| SKU group | Group_ID | --- | The ID of the group of related products to which the product belongs (for example, same type of yogurt of the same manufacturer and volume, but different flavors). One identifier is specified for all products in the group |
| Prime cost | Group_Cost | Arabic numeral, decimal | --- |
| Base retail price | Group_Summ | Arabic numeral, decimal | --- |
| Actual cost paid | Group_Summ_Paid | Arabic numeral, decimal | --- |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Customer ID | Customer_ID | --- | --- |
| SKU group | Group_ID | --- | The ID of the group of related products to which the product belongs (for example, same type of yogurt of the same manufacturer and volume, but different flavors). One identifier is specified for all products in the group |
| Date of first purchase of the group | First_Group_Purchase_Date | yyyy-mm-dd hh:mm:ss.0000000 | --- |
| Date of last purchase of the group | Last_Group_Purchase_Date | yyyy-mm-dd hh:mm:ss.0000000 | --- |
| Number of transactions with the group | Group_Purchase | Arabic numeral, decimal | --- |
| Intensity of group purchases | Group_Frequency | Arabic numeral, decimal | --- |
| Minimum group discount | Group_Min_Discount | Arabic numeral, decimal | --- |
| Field | System field name | Format / possible values | Description |
|---|---|---|---|
| Customer ID | Customer_ID | --- | --- |
| Group ID | Group_ID | --- | --- |
| Affinity index | Group_Affinity_Index | Arabic numeral, decimal | Customer affinity index for this group |
| Churn index | Group_Churn_Rate | Arabic numeral, decimal | Customer churn index for a specific group |
| Stability index | Group_Stability_Index | Arabic numeral, decimal | Indicator demonstrating the stability of the customer consumption of the group |
| Actual margin for the group | Group_Margin | Arabic numeral, decimal | Indicator of the actual margin for the group for a particular customer |
| Share of transactions with a discount | Group_Discount_Share | Arabic numeral, decimal | Share of purchasing transactions of the group by a customer, within which the discount was applied (excluding the loyalty program bonuses) |
| Minimum size of the discount | Group_Minimum_Discount | Arabic numeral, decimal | Minimum size of the group discount for the customer |
| Average discount | Group_Average_Discount | Arabic numeral, decimal | Average size of the group discount for the customer |
This project is licensed under the MIT License. Feel free to use and modify the code according to your needs.