Buyer LTV Path

CurtisS
CurtisS Member
in Charting

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!

Comments

  • Valiant
    Valiant Coach

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

  • CurtisS
    CurtisS Member

    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
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • CurtisS
    CurtisS Member

    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
    GrantSmith Coach

    @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.

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach

    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.

     

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • CurtisS
    CurtisS Member

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

     

    2020-02-12_15-52-41.png

     

    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. 

     

This discussion has been closed.

Categories