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

[wesleyjwk]

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 solution]

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 solution]

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.

[wesleyjwk]

Hi!

I've taken a look at your Macro but it is unfortunately going a bit beyond my knowledge and I can't get it to work the way I need it though. I do think, nonetheless, it should be possible, for anyone with a more advanced Alteryx Knowledge framework.

Thanks.

KR

Wesley

[wesleyjwk]

Thanks for your solution, I got it to work in my workflow, which is very nice.

Step 1. Create Formula's (Boolean's) with the following:

- Before Adoption Flag: "IIF(DateTimeDiff([AdoptionDate],[ga_date],"days")>0 AND DateTimeDiff([AdoptionDate],[ga_date],"days")<=60, 1, 0)" in which [AdoptionDate] is the date of adoption and [ga_date] is the date in which an active online session is recorded.

- After Adoption Flag: "IIF(DateTimeDiff([ga_date],[AdoptionDate],"days")>0 AND DateTimeDiff([ga_date],[AdoptionDate],"days")<=60, 1, 0)" in which [AdoptionDate] is the date of adoption and [ga_date] is the date in which an active online session is recorded.

Step 2. Apply 2 Filter's in which:

- Before Adoption Flag = 1

- After Adoption Flag = 1

Step 3. Apply 2 subsequent Summarize Tools in which:

- Group by Unique Customer ID, Group by Before Adoption Flag and Average X

- Group by Unique Customer ID, Group by After Adoption Flag and Average X