Acquire an Average over a specified Timewindow which is different for each Respondent

Question

Hi all,

I'm currently working on an adoption dataset in which Company X introduces an additional service Y to its portfolio.

The sample runs from January to August of the same year (so 8 months of data) and the additional service Y is introduced in April.

Naturally, every customer adopts at a unique given time between April and August and those that do not adopt within the timeperiod are given an adoptiondate which equals the end of the sample period (censoring).

Now, imagine the following, if I want to know the Average Session Duration prior to the adoption date, I would ideally like to have this average from the start of the sample until the adoption date. On the other hand, I would also like to see the Average Session Duration post-adoption, which would run from the adoption date to the end of the sample. In this case I would only want these averages for anyone who adopts before July so there's at least 2 months used in building this average. These averages would be different for every customer in the dataset, but how do I obtain such averages relative to the adoption date?

I'm very interested in hearing your thoughts.

Kind Regards,

Wesley Klabbers

MarqueeCrew · Accepted Answer

Wesley,

I had created a moving calculation (average & beyond) macro using the generate rows tool.  You might want to take a look at it for some hints as to how to solve this puzzle.

Thanks,

Mark

https://gallery.alteryx.com/#!app/MovingCalculation/555e4b2e6ac90f17fc94c89d

RodL · Accepted Answer

I'm thinking this would work...

Determine what is the adoption date for each customer and join that list to your data on Customer ID.

Then set a formula that provides a before/after flag as to whether it is before or after the adoption date.

Then use a Summarize tool and group by Customer ID and the Before/After flag and average the Session Duration field. It should give you for each customer the average duration for the 'befores' and the 'afters' separately.

Note: It would probably be in the formula tool, you would need to add into the determination of the yes/no flag any parameters that assign the two month restriction that you mention.

If you need the before and after average in the same record, you could just add a Filter tool to the result of your Summarize and filter the Befores one way and the Afters the other and then join back on Customer ID.