Search that Leads to a Sale - BeastMode or ETL

mczerniak

I have 2 data sets. One is showing all of the search terms entered on our eCommerce site, along with the logged-in user's account info. I want to see if any of these search terms match with products sold in my second data set, which is all of the sales entered in our ERP. The intent is to see if an online search may have led to an offline purchase. The basic criteria is:

 

Customer searches online for Product X

For some reason, does not buy online

Customer instead places a PO through a traditional channel (email, phone order, etc.) that is entered in our ERP

*If this search term matches an ordered item within a 7-day window, we want to flag it. 

 

Essentially, for every search, I want to check our sales data from the next 7 days for matches on the search term and customer number, and isolate those records. 

 

I did this in PowerBi years ago, but i unfortunately lost the work. Trying to determine if this is an ETL and/or BeastMode thing. 

AS

Since this is two datasets, it's definitely an ETL thing (probably SQL, not Magic ETL).

 

Like you said, you'd want to review each search by person and product and time window to see if there's a match in the sales records.

 

Pseudo SQL:

SELECT

search.search_id
,search.user_account_id
,search.date
,search.product_id
,CASE WHEN sale.transaction_id IS NOT NULL THEN 'Purchased' ELSE 'Not Purchased' as flag
,sale.transaction_id
,sale.date

FROM

ecommerce_searches as search
LEFT OUTER JOIN erp_sales as sale
ON search.user_account_id = sale.account_id
AND search.product_id = sale.product_id
AND search.date BETWEEN sale.date - 7 AND sale.date

 

The assumption here (one of them, anyway) is that you're able to identify products searched online with actual products from your product line.