2
$\begingroup$

I am trying to find a statistical or machine learning tool that replicates this analysis I am doing manually in excel.

Each row in my data set is a user.

User | sites visits | pages viewed | items added to cart | Purchased (1 or 0)

I set up a pivot table so Purchased is in the columns field. I have it set to % of row.

Purchased   Didn't Purchase         Total
 80%              20%                100%

I then add all the other variables as filters in the Pivot Table.

I start by filtering out the people who didn't view 1 page and see how that impacts that % of people who purchased. I then filter out the people who didn't view 2 pages, etc. I then start filtering out other variables to see how the interactions work together.

I am looking for a combination of inputs that leads to an 80% purchase rate. So maybe it's 2 visits, 6 pageviews, and 2 add to carts.

Is there a tool that automates this type of analysis?

$\endgroup$
2
  • $\begingroup$ (i) I believe you could do the whole thing in a handful of lines of R code (or indeed in any number of other programs), but I don't know if that's what you mean (in particular, one might fit a saturated model and then find the closest combination of factors to the required level); (ii) are you after all combinations near 80%, or any single combination? (either could be done) $\endgroup$
    – Glen_b
    Commented Jun 22, 2014 at 22:28
  • $\begingroup$ Is the 80% prespecified, or are you trying to find a combination which matches the aggregate (essentially a kind of average value)? $\endgroup$
    – Glen_b
    Commented Jun 22, 2014 at 22:52

1 Answer 1

1
$\begingroup$

I think it would be easier with a small (toy) example, fully worked; it might be possible to show you what can be done in other things. That is if under your headings, you have maybe 15 or so lines of data values (indent them all 4 spaces), and then how you get the 80% (or whatever value it might be); and give the corresponding value for that data, and then the solution (combination) you'd produce using Excel. Then we might be able to show you how to reproduce that automatically.

I believe one approach would be to fit a model (e.g. an ANOVA-like model in R) that fits the % at the combinations of values - a saturated model for the variables not aggregated over (so it exactly reproduces the observed values); you can then find and extract the fitted combinations that come closest to 80% (or whatever). I imagine the whole thing would be a few lines of R that could be made into a function that would load your data, run the analysis and produce as output the required combination or combinations.

While I don't think you need to produce a pivot table to do any part of that, the equivalent of pivot tables themselves can easily be done in vanilla R, or in several R packages. Two answers to this question shows how to do a pivot table in R via tapply (plain R) or with reshape2; I think several other functions in base R and in popular R packages have the same functionality (e.g. see here, or here and here and here. Searches in StackOverflow or in google provide many more examples.

Lots of programs other than R could do exactly the same things I discuss here.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.