Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

[seerauber]

Hi all,

I am relatively confident an answer for my question already exists, but my googlefu/excel vocabulary has proven too weak to find it. I apologize!

I am working on pulling in sales data and creating a chart. I have about 200 SKUs, and Prior Year sales data (by month), YTD sales (by month) and forecasted sales (by month) - ultimately there are additional pieces of data I'd like to pull in like conversion, traffic, ad spend, etc.

What I need to do is create a pivot table/chart that will allow me to select a product and see the performance across all the relevant measures.

Pretty basic stuff, except I can't figure out how to format my data to do this without separating each SKU and its sales data into its own table/tab. If I've explained this intelligibly, could anyone point me in a website or how-to that will help me?

Thank you!!

 

[alansidman]

Re: Help create Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

Really need to see some sample data to help you sort this out. Suggest you post a little bit of data you currently have and then what you expect it to look like. Then we can offer up some viable solution. Your current explanation is a bit vague.

 

[seerauber]

Re: Help create Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

Really need to see some sample data to help you sort this out. Suggest you post a little bit of data you currently have and then what you expect it to look like. Then we can offer up some viable solution. Your current explanation is a bit vague.

Apologies and thank you for the help. I've included sample data with an image. You can see the columns of data, and the chart. Ideally I'd like to have a single pivot chart that I can select one or more product do display, with slicers to select which data gets charted.


I have many of these individual sheets, but what I would like to be able to do going forward is combine the data for each product onto a single worksheet, and have the pivot table/chart use that single sheet as the data source. I can send an example of the raw data if necessary, though I'll have to take the time to try and anonymize it.

Is this more understandable? I feel sure that what I want is doable, I just lack the vocabulary to find the solution.

Thanks to you for your time and help!!

Date	AD Sessions	AMS Spend	AMS Revenue	Total Revenue	Units	Rev-AMS	ASP	Rev +/-	Ad Share	Ad Units	Ad Conversions	Total Sessions	Total Conversion
26-Jan	235	$ 472.46	$ 1,044.14	$1,492.61	62	$ 448.47	$ 24.07	-	70.0%	43	18.5%	275	24.1%
2-Feb	148	$ 305.72	$ 491.60	$894.00	37	$ 402.40	$ 24.16	-40.1%	55.0%	20	13.7%	295	25.4%
9-Feb	226	$ 430.08	$ 877.28	$2,180.73	92	$ 1,303.45	$ 23.70	143.9%	40.2%	37	16.4%	295	24.3%
16-Feb	188	$ 310.54	$ 479.60	$994.09	41	$ 514.49	$ 24.25	-54.4%	48.2%	20	10.5%	315	26.7%
23-Feb	152	$ 274.15	$ 801.32	$1,157.73	49	$ 356.41	$ 23.63	16.5%	69.2%	34	22.3%	325	29.5%
2-Mar	159	$ 341.49	$ 747.38	$1,047.61	43	$ 300.23	$ 24.36	-9.5%	71.3%	31	19.3%	337	27.6%
9-Mar	183	$ 418.86	$ 647.44	$1,036.50	43	$ 389.06	$ 24.10	-1.1%	62.5%	27	14.7%	349	29.8%
16-Mar	155	$ 352.07	$ 917.24	$986.99	41	$ 69.75	$ 24.07	-4.8%	92.9%	38	24.6%	361	30.2%
23-Mar	173	$ 437.68	$ 1,019.16	$1,305.32	54	$ 286.16	$ 24.17	32.3%	78.1%	42	24.4%	373	29.5%
30-Mar	208	$ 484.98	$ 1,072.12	$1,707.66	71	$ 635.54	$ 24.05	30.8%	62.8%	45	21.4%	385	30.9%
6-Apr	377	$ 747.37	$ 1,357.88	$2,291.68	95	$ 933.80	$ 24.12	34.2%	59.3%	56	14.9%	397	31.2%
13-Apr	353	$ 629.99	$ 1,565.68	$3,086.60	128	$ 1,520.92	$ 24.11	34.7%	50.7%	65	18.4%	409	32.1%
20-Apr	431	$ 902.00	$ 2,186.00	$ 4,054.00	168	$ 1,868.00	$ 24.13	31.3%	53.9%	91	21.0%	421	28.9%
27-Apr	488	$ 1,052.00	$ 2,006.00	$ 3,884.00	161	$ 1,878.00	$ 24.12	-4.2%	51.6%	83	17.0%	433	33.3%
4-May	987	$ 2,541.00	$ 4,373.00	$ 5,288.00	218	$ 915.00	$ 24.26	36.1%	82.7%	180	18.3%	445	33.8%

<tbody>
</tbody>

 

[GR00007]

Re: Help create Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

Your sample data has a few challenges:
- The row date is by week and you want to report monthly, any thoughts on how to split a week between months correctly
- Don't see a product to select
Suggestions:
- Pivot the data then use slice & dice to add product selection and any other filter criteria desired (it has to be in the raw data though)
- In pivot recalculate percentages based on raw data - using percentages to calculate percentages is a good way to cause problems
- You can add 'helper' columns in the raw data to delineate the year/month however you want, or utilize Pivot's calculated field to create what you need.

 

[seerauber]

Re: Help create Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

Your sample data has a few challenges:
- The row date is by week and you want to report monthly, any thoughts on how to split a week between months correctly
- Don't see a product to select
Suggestions:
- Pivot the data then use slice & dice to add product selection and any other filter criteria desired (it has to be in the raw data though)
- In pivot recalculate percentages based on raw data - using percentages to calculate percentages is a good way to cause problems
- You can add 'helper' columns in the raw data to delineate the year/month however you want, or utilize Pivot's calculated field to create what you need.

Thank you!

A big follow up question I have - is there a way to structure my data within a single table so that I can create a pivot table/pivot chart as you describe?

Currently I have around 200 worksheets containing data similar to my example above - one for each product. I actually manually construct that data from two different raw sources (sales data dump, marketing data dump. Could I just maintain two worksheets for each raw data source and somehow structure the Pivot Table or Pivot Chart to sum up by sku and time period, and draw the above graph based on a SKU slicer?

There is not any actual date-data within my raw data - I manually pull the data based on specified range, so I'll probably have to manually input the date column myself no matter what..

Below examples of my two raw data sources - each SKU will have a record for each time period (be it a week, or month, etc.)

​MARKETING DATA

Advertised SKU	Impressions	Clicks	(CTR)	(CPC)	Spend	7 Day Total Sales	(ACoS)	(RoAS)	7 Day Total Orders (#)	7 Day Total Units (#)	7 Day Conversion Rate
N5-DUMMY	71411	194	0.2717%	$ 3.33	$ 645.67	$ 485.62	132.9579%	0.75	19	19	9.7938%
1C-DUMMY	51074	107	0.2095%	$ 2.66	$ 284.71	$ 389.70	73.0588%	1.37	14	15	13.0841%
T4-DUMMY	67346	212	0.3148%	$ 2.17	$ 459.53	$ 901.26	50.9875%	1.96	37	37	17.4528%

<tbody>
</tbody>

SALES DATA

Title	SKU	Sessions	Session Percentage	Page Views	Page Views Percentage	Buy Box Percentage	Units Ordered	Unit Session Percentage	Ordered Product Sales	Total Order Items
Product 1	T4-DUMMY	292	20.10%	394	20.25%	97%	93	31.85%	$1,588.69	77
Product 2	1C-DUMMY	254	17.48%	341	17.52%	100%	62	24.41%	$1,492.61	62
Product 3	N5-DUMMY	200	13.76%	265	13.62%	99%	43	21.50%	$1,124.03	42

<tbody>
</tbody>

 

[GR00007]

Re: Help create Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

It appears as though the examples given are actually reports that you would like to have an overall view of for analysis.
If that is so, do you have a way of getting a dump of the real raw data:
Marketing: (for import to Excel)
- Date (month, day, and year - or year, week - or whatever criteria to report - consistent with Sales Data extract) Aggregate marketing data on this criteria
- SKU
- Impressions, clicks, CPC, Spend, Sales, Order count, Unit count, PLUS know the calculation necessary for CTR, ACoS, RoAS values
Sales: (for import to Excel)
- Date (month, day, and year - or year, week - or whatever criteria to report - consistent with Marketing Data extract) Aggregate sales data on this criteria
- SKU
- Title (if wanted) Sessions, Page views, Units Ordered, Sales, Items PLUS know the calculation necessary for percentages
For both of these, I'd suggest gather data by date (month, day, year) and let the pivot summarize data and calculate percentages - giving flexibility for analysis.
Make a new sheet that will use data from sales and marketing that can then be the data source for your pivot and subsequent slice and dice.

 

[seerauber]

Re: Help create Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

It appears as though the examples given are actually reports that you would like to have an overall view of for analysis.
If that is so, do you have a way of getting a dump of the real raw data:
Marketing: (for import to Excel)
- Date (month, day, and year - or year, week - or whatever criteria to report - consistent with Sales Data extract) Aggregate marketing data on this criteria
- SKU
- Impressions, clicks, CPC, Spend, Sales, Order count, Unit count, PLUS know the calculation necessary for CTR, ACoS, RoAS values
Sales: (for import to Excel)
- Date (month, day, and year - or year, week - or whatever criteria to report - consistent with Marketing Data extract) Aggregate sales data on this criteria
- SKU
- Title (if wanted) Sessions, Page views, Units Ordered, Sales, Items PLUS know the calculation necessary for percentages
For both of these, I'd suggest gather data by date (month, day, year) and let the pivot summarize data and calculate percentages - giving flexibility for analysis.
Make a new sheet that will use data from sales and marketing that can then be the data source for your pivot and subsequent slice and dice.

Thank you again GR00007,

I don't mean to badger you with follow up questions, but can you advise me or point me in the direction of a resource that could walk me through what you suggest?

I am envisioning this process:

1) Download weekly data dumps of Sales and Marketing raw data
2) Adding Date date to each (probably just a "Week Ending" Column)
3) Appending each successive weekly dump to an aggregate "Sales" and aggregate "Marketing" worksheet
4) Combing both files into a pivot table

I am not skilled at Pivot Tables (yet! ) so I am unsure of how to ensure the pivot table gathers the data by week/month/year (ideally this would be user-selectable with a slicer or something). Further, in the marketing data dump, there are 2 or more rows for each SKU - so I need to figure out how to make the pivot table both gathers the SKU level data by week, while also summing data per SKU/Week from the marketing data dump.

Below is an example again for one SKU/1 week of marketing raw data dump:

Start Date	End Date	Portfolio name	Currency	Campaign Name	Ad Group Name	Advertised SKU	Advertised ASIN	Impressions	Clicks	(CTR)	(CPC)	Spend	7 Day Total Sales	(ACoS)	(RoAS)	7 Day Total Orders (#)	7 Day Total Units (#)
May 05, 2019	May 11, 2019	Not grouped	USD	DUMMY | Manual | CPC	0J-M0VS-DUMMY	SKU 1	ASIN 1	86448	143	0.1654%	$ 2.44	$ 348.68	$ 462.62	75.3707%	1.33	18	19
May 05, 2019	May 11, 2019	Not grouped	USD	DUMMY | Auto | CPC	0J-M0VS-DUMMY	SKU 1	ASIN 1	977	5	0.5118%	$ 0.37	$ 1.85	$ 0.00		0.00	0	0
May 05, 2019	May 11, 2019	Not grouped	USD	DUMMY | Manual | CPC	0J-M0VS-DUMMY	SKU 1	ASIN 1	370	4	1.0811%	$ 0.93	$ 3.70	$ 73.94	5.0041%	19.98	3	3
May 05, 2019	May 11, 2019	Not grouped	USD	DUMMY | Manual | PAT | CPC	Product Targeting	SKU 1	ASIN 1	15689	11	0.0701%	$ 1.45	$ 15.97	$ 0.00		0.00	0	0

<tbody>
</tbody>

 

[GR00007]

Re: Help create Pivot Chart to show PY, YTD, Forecast figures for multiple SKUs

Looks good, and appending data weekly to the same sheets would require just a refresh to reflect the addition on the pivot.
There are many tutorials on pivots, this is just one with a business focus including sales and commissions: https://excelwithbusiness.com/blog/pivot-table-how-to-guide/
...this one gets into details: https://exceljet.net/excel-pivot-tables --- many many others, I would suggest start simple then dive into enhancements to aid analysis.
You mention monthly - do you have a 4/4/5 calendar - that's one where whole weeks are grouped into two four week months followed by a five week month (364 days - having to add a week about every seven years) There's pluses and minuses to that, it's up to your accountants to decide. You could have a calendar tab with a lookup by date to find the associated month.