Buyer LTV Path

CurtisS

We have a sales funnel with multiple products within it. Product A (with three price points), B, C, D and they get progressively more expensive. The dataset I have being pulled in includes all of the relevant information. It records every single transaction, and includes: DateTime, email, product purchased, purchase amount

 

If a contact purchases multiple times, they will have multiple rows specific to that product purchased. The one thing that will be the same is their email.

 

I'm trying to create a card that is a table and shows the count of each product path purchased by column. The far left column will have Product A and its three products. The column to its right will have Product B, and to its right will be Product C, etc. The counts in each column represent the people who have purchased all options. 

 

There would be a single row of Product A price option 1 with a count that shows only those who purchased that one product and nothing more. 

There would be another row of Product A price option 1 with a count in Column A showing a different number and also has a number in Column B for those who purchased Product A price option 1 as well as Product B. 

 

And so on. I think once this is solved, the rest of the cards will be easy enough to create.

 

I tried creating this in a Sumo card pivot table, and it gets me close. I can see each contact and the different products they purchased, but the final step is combining the products. And combining the products on a datset or even BeastMode level I don't think will work because then I'd lose the pathing. 

 

Any help is appreciated. Thanks!

Valiant

Would it be possible for you to provide a generic example of how the dataset is formatted behind the card? 

CurtisS

I wrote up a long reply only to be timed out of this board and lose it all. Rad.

 

https://docs.google.com/spreadsheets/d/15R6NHnENuXyzl7D5QPq6PfJMRrnHVtZXedULvnJJ5_g/edit#gid=0

 

Sheet 1 is an example of the type of card I'd like to made. Keeping in mind, for example, Product A1, in column B... are the count of emails who only bought Product A1 and nothing else. while the number in column F are the emails who bought only Product A1 and Product B and nothing else. 

 

 

GrantSmith

@CurtisS - How is the underlying data in the datasource structured? That will help determine how to best create what you're looking for.

CurtisS

Yeah, sorry - that was part of my original response that got deleted. 

 

In the link above also here: https://docs.google.com/spreadsheets/d/15R6NHnENuXyzl7D5QPq6PfJMRrnHVtZXedULvnJJ5_g/edit#gid=0

 

Sheet2 is what the raw data looks like. I added the column 'Transaction Number' via ETL to track which transaction it is for each buyer. 

 

I have another working version that normalizes the Product_purchased column and turns the products into their own columns and then has a count of each product for each person. 

 

GrantSmith

@CurtisS 

 

You can do it with a MySQL transformation. I plugged your example dataset into the gs_test_data_set referenced below.

 

I utilized two Transforms but you might just need 1 with a seprate dataset. One transform was just generating a list of all of the products. (I called this product_list)

select 'Product A1' as "Product"
UNION
select 'Product A2'
UNION
select 'Product A3'
UNION
select 'Product A4'
UNION 
select 'Product B'
UNION
select 'Product C'
UNION
select 'Product D'

The second was to determine what the first product purchased was (I called this first_product):

select `Email`, `DateTime`, `Product_Purchased`
from `gs_test_data_set`
where `Transaction Number` = 1

And then comes the magic part. It's looking at all products and tying that to the first order then to the products purchased themselves to calculate the sum for each product (includes all proudcts not just the products first purchased).

select
pl.`Product`
, sum(case when t.`Product_Purchased` = 'Product A1' THEN 1 else 0 end) as "Product A1"
, sum(case when t.`Product_Purchased` = 'Product A2' THEN 1 else 0 end) as "Product A2"
, sum(case when t.`Product_Purchased` = 'Product A3' THEN 1 else 0 end) as "Product A3"
, sum(case when t.`Product_Purchased` = 'Product A4' THEN 1 else 0 end) as "Product A4"
, sum(case when t.`Product_Purchased` = 'Product B' THEN 1 else 0 end) as "Product B"
, sum(case when t.`Product_Purchased` = 'Product C' THEN 1 else 0 end) as "Product C"
, sum(case when t.`Product_Purchased` = 'Product D' THEN 1 else 0 end) as "Product D"

from `first_product` fp
left join `gs_test_data_set` t on t.`Email` = fp.`Email` and t.`DateTime` = fp.`DateTime`
right join `product_list` pl on pl.`Product` = fp.`Product_Purchased`
group by pl.`Product`

I did simplify your dataset and removed the $XX from the product names for simplicity.

The frustrating part of this though is that you'll have to manually define every product you sell as a new column but this should give you a framework to utilize going forward.

 

GrantSmith

Interestingly enough my initial response was deleted as well.

 

Here's what I initially wrote:

@CurtisS 

 

You can do it with a MySQL transformation. I plugged your example dataset into the gs_test_data_set referenced below.

 

I utilized two Transforms but you might just need 1 with a seprate dataset. One transform was just generating a list of all of the products. (I called this product_list)

select 'Product A1' as "Product"
UNION
select 'Product A2'
UNION
select 'Product A3'
UNION
select 'Product A4'
UNION 
select 'Product B'
UNION
select 'Product C'
UNION
select 'Product D'

The second was to determine what the first product purchased was (I called this first_product):

select `Email`, `DateTime`, `Product_Purchased`
from `gs_test_data_set`
where `Transaction Number` = 1

And then comes the magic part. It's looking at all products and tying that to the first order then to the products purchased themselves to calculate the sum for each product (includes all proudcts not just the products first purchased).

select
pl.`Product`
, sum(case when t.`Product_Purchased` = 'Product A1' THEN 1 else 0 end) as "Product A1"
, sum(case when t.`Product_Purchased` = 'Product A2' THEN 1 else 0 end) as "Product A2"
, sum(case when t.`Product_Purchased` = 'Product A3' THEN 1 else 0 end) as "Product A3"
, sum(case when t.`Product_Purchased` = 'Product A4' THEN 1 else 0 end) as "Product A4"
, sum(case when t.`Product_Purchased` = 'Product B' THEN 1 else 0 end) as "Product B"
, sum(case when t.`Product_Purchased` = 'Product C' THEN 1 else 0 end) as "Product C"
, sum(case when t.`Product_Purchased` = 'Product D' THEN 1 else 0 end) as "Product D"

from `first_product` fp
left join `gs_test_data_set` t on t.`Email` = fp.`Email` and t.`DateTime` = fp.`DateTime`
right join `product_list` pl on pl.`Product` = fp.`Product_Purchased`
group by pl.`Product`

I did simplify your dataset and removed the $XX from the product names for simplicity.

The frustrating part of this though is that you'll have to manually define every product you sell as a new column but this should give you a framework to utilize going forward.

 

 

CurtisS

Thanks for your response @GrantSmith. I managed to do something very similar to this via an ETL.

 

 

Which allowed me to make column 1 on my table, "Product Names", then subsequent columns the individual products. Aggregating by count yields the number of contacts who purchased that first product as well as any following product. 

 

It was an absolute pain getting to this point but it is resolved.