I have a kind of table below as a data source. The total count of Sites is 85, and overal count of rows is 219 000 000 for 1 year data.
The goal is to calculate repeat sales by week. Need to visualise sum of sales and count of distinct client whith come back to the same site on next week. In my example this is client 1 whith come back 2 times to site A. In additional will be great to calculate % of reapet sales client to all clients by site and week. Sites and weeks will be as a parameters with multiselection optioon.
|Site ID||Customer ID||week||Sales|
I imagine that the results table could be something like this:
|Site ID||repeat sales (Client)||repeat sales (Sales Amount)||% to total sales amount||repeat sales (Client)||repeat sales (Sales Amount)||% to total sales amount||repeat sales (Client)||repeat sales (Sales Amount)||% to total sales amount|
Could somebody suggest a solution for this case?
Thanks for any help