I'm attempting to do a basic analysis of email promotion response windows, as I feel like I've noticed changes in patterns over the last year. Is there a (not-highly-custom set up) way of pulling First Order and Last Order dates per Source in Analytics?
While I'm reasonably comfortable that I could accurately wrangle a data set to export and then manipulate in a spreadsheet, this sounds dreadful as a project and I'd love a path into it that doesn't require data scrubbing first.I did try to search for existing threads like this and found nothing, but please feel free to point me to something already posted as your reply. Thank you!
Hi Jamie,
This got me interested to take a look at the same data for us. I found this Sisense post which suggests adding a new field to your Elasticube which would convert the date to an integer so you can easily grab a Max/Min, and then convert back to a useable date:
https://community.sisense.com/t5/knowledge/calculating-values-for-the-most-recent-date/ta-p/8732
I am not going down that road just yet, but I was able to create a couple widgets which separately grab the earliest and latest order dates when looking at a single source. Those are just pivot tables with order dates filtered on Top (or Bottom) 1 day by [Max Calendar Day of Year]. I haven't been able to get both dates easily in one widget, nor have it work accurately when looking at more than one source at a time, but hopefully that's somewhat helpful! Otherwise, I'll continue lurking here for other good ideas. :-)
Steve
Interesting--thanks!!I ended up doing this by hand via an export and it wasn't nearly as gnarly an exercise as I'd expected, since I had a somewhat limited scope of sources to inspect. (And all in, I only managed to prove that the purchase pattern HADN'T changed, so I'm back to figuring out what question to pursue as my strategy base.)The custom field is probably still too much advanced set up for me at this point, but the Bottom/Top filtering is quite intriguing.
If you have Source on rows of a Pivot table widget, you could create values for:
MIN( [Calendar Year] * 10000 + [Calendar Month] * 100 + [Calendar Day of Month] )MAX( [Calendar Year] * 10000 + [Calendar Month] * 100 + [Calendar Day of Month] )
Where each of those value fields is selected from the ORDER DATE dimension.