Creating Records For Non Existent Transactions

Introduction

Sometimes when your analyzing sales you may want to look at products that are not being sold. Or maybe for a service provider you may want to see territories where you have no subscriptions. Unfortunately, data typically does not contain this information as it will only contain sales.

The use case will be looking at products that haven’t been sold over time which is outlined below.

Product Category Use Case

In the use case we want to look at products that haven’t sold per month so the granularity defined for the analysis is product month. Now we need to figure out how to create a record for every product month that a product was not sold. We are going to need to create a new field to associate each transaction to a month so we can properly identify the new records.

Step 1: Create the Month-Year Identifier in the fact table in question (transactions).

This field will be used to identify the months when a product did sell. Since we still want to be able to use the date field to filter, we are going to use the first of every month.

Createdate(getyear(transaction_date),getmonth(transaction_date),1)

The resulting column with be a month year Identifier. Lets call it Month-Year

Step 2: Create a custom table that identifies the distinct Month-Year

Select Distinct T.[Month-Year]
From [Transactions] T

Step 3: Create a distinct product list table. We will call it products.

Select Distinct T.[Product ID]
From [Transactions] T

Step 4: Cross Join the distinct Month-Year Table with the distinct product list table.

This will give us a record for every single product-month-year

Select MY.[MonthYear], P[Product ID]
From [Distinct Product Table] P
Cross Join [Distinct Month Year Table] MY

Step 5: Lets use a left outter join between the Product-Month-Year table and the transactions table.

This will give us a record for every product-month-year that has not had a transaction in that time period.

Select PMY.[Product ID], PMY.[Month-year]
From [Product-Month-Year Table] PMY
Left outter Join [Transactions] T on
PMY.[Product ID]=T.[Product ID] and PMY.[Month-year]=T.[Month-Year]

Step 6: Union the Products not sold records to the Transaction Table.

We will align the transaction date with the month year of the not sold table

Select T.TransactionDate, T.[Amount], T.[Quantity], T.[Product ID]
From [Transactions] T

Union All

Select PNS.[Month-year],0,0,T.[Product ID]
From [Products Not Sold Table] PNS

Now when filtering all records to transactions with a quantity of 0, you will get a list of the products that have not sold by month.

intapiuser

Posted 3 years ago

0 comments