Creating a dynamic report tab

[pauloconnor]

Hi all,

Hope you can help in this fairly complex query I have.

I am trying to generate a good looking, dynamic report tab that runs off another tab that contains ugly raw data for website traffic. This should then drive a couple of charts based on the parameters I specify.

The Raw Data

This is daily data for keywords that we advertise on through Google. Associated with each keyword are various fields:

• Clicks
• Cost
• CPC (Cost divided by clicks)
• Quotes
• Sales
• CPQ (cost divided by quotes)
• CPS (cost divided by sales)
• CTQ (quotes divided by clicks)
• QTS (sales divided by quotes
• CTS (sales divided by clicks)
• Avg Pos

There are maybe 30 keywords - each with daily information for the above fields.

The layout of the raw data is:

Column A: Date
Column B: Keywords
Column C: Clicks

and so on.......

The Report

This is the nice looking report where we can pull in the required bits and bobs in a nice format. Because some of the above fields are calculations I've not been able to achieve what I need using a normal pivot report.

What I would like to be able to do:

Populate a summary report using the above raw data but be able to specify certain things such as:

• The date range I want to see data for
• The specific keyword(s) I want to see data for

For example v- we are feeding the daily information into the raw report each morning. One day I might want to see summary stats for a particular data rang on maybe 3 of the keywords so I'd somehow like to select appropriate date ranges and keywords from drop down lists and then I see the summary stats for the appropriate date range broken down by each keyword.

I'd also like a total underneath this report to add up or calculate the totals and averages for the data based on the parameters I specify.

Finally, given all of the above, I would like to run a dynamic chart based on the raw data but taking into account the filters I specify on the slick report sheet.

So....if I set the date range as From: 01/03/09 to 12/03/09 I can see daily clicks, costs etc for the particular keywords I specify in the drop-down menu or what-have-you

Now...

Is this possible or have I been flogging a dead horse????

Many, many thanks in advance,

Paul.

[Cheeky Charlie]

Probably could use a pivot table.

Post a trimmed example of your raw data and I will try to show you how.

CC

[pauloconnor]

Hi there,

Many thanks for your response. I've attached some dummy data.

Cheers,

Paul.

[pauloconnor]

Bump

Still stuck on this one so would appreciate any help!

[Cheeky Charlie]

Hi Paul, there's a lot going on here - we need a macro to unpivot the raw data in order to allow you to use it in a fresh pivot.
CC

[Cheeky Charlie]

Hi Paul,

I created a macro (see attached) which unpivots the SEO data from the PPC data - this means calculated fields can be sued to achieve everything(?) else. Take a look and let us know how we're doing. The idea is that you should be able to run this quick macro on the raw data then paste it into your report's raw data area and run the rest with the built in flexibility of a pivot table.

I also created calculated fields, not sure if you're comfortable with that, this is done by pivot table menu -> formulas -> calculated field, if you look in the drop down box in the dialogue window you will see how I have created the relevant formulae for CPC etc. because these are built in to the pivot table it means they are calculated on-the fly - this also means they are implicitly summed/averaged so the option is not there to choose (i.e. you don't want to add two CPC amounts to get a total CPC...)

HTH

[pauloconnor]

Cheers for that - looking much better. I bow to your superior Excel knowledge! My understanding of Macros is non-existant however I can get the raw data to arrive in the format you've used there that's no problem.

Some additional things that are really key to this being of use to my team now are:

• Adding in a date filter to the pivot data so we can see performance over a given date range
• Including a combined figure for SEO and PPC for each keyword. Again, on the pivot table.

The idea is that we will dump in the performance data daily and we'd like the pivot data to recognise this without too much interaction.

Thanks for all your help so far,

Paul.

[Cheeky Charlie]

OK, you can select and group ranges within the pivot, which I recommend you experiment with if you haven't already - grouping by month or 7-day blocks, for examples, is very powerful. That said, it's not the easiest way of predefining a date range. The problem is we'd probably need VBA to quickly activate and deactivate the dates in the date field of the pivot table - how do you feel about that?

Secondly - calculated items are like calculated fields except they exist within fields. Best explanation - clicks is a measure, so is CPC, QTS etc. whereas source is a type, a qualifier, a field - within the field of type there are multiple items - PPC and SEO - what we need is an item in the type field which sums the other two items. So - we select somewhere in the source field (I clicked on PPC, cell A5) then we go back to pivot table menu -> formulas -> (this time) calculated item. Into our field we add called "Blend" = PPC+SEO. Make sense? See attachment.

Hopefully these instructions should be enough for you to develop your use of pivot tables further, which I would recommend the only thing better than pivot tables is really good pivot tables...

CC

[pauloconnor]

This is absolutely fantastic!

Probably the final piece of the puzzle now. Although I want to be able to define the date range I'm looking at I don't actually need to see the daily breakdown of data - just a summary would do. So if I select to see 7th to the 17th - I just get the overall PPC, SEO and Blend for that date range.

Thanks so much for all your help again - this is something I wouldn't have figured out on my own!!

[Cheeky Charlie]

Try this, it simply cycles through the items in the date field and hides those dates not within the specified range. If you specify a date range which precludes all the raw data it will crash when trying to make the last date invisible - so don't be that guy.

CC

[pauloconnor]

Brilliant!

Final questions (I think) - I've just tried updating the raw data and amending the keyword names but on the pivot tab they still show keyword 1, keyword 2, keyword 3 etc.

Is there any way to include a refresh button so that if different raw data is added we can ensure the pivot table is up to date?

I only ask because we're looking at rolling this out for multiple clients so would be good to make this thing as fool-proof as possible!!!

Also - when I click on the fields in the pivot tab I can't see the menu that usually open up on the right hand side any more for the pivot settings. This is fine but just wondering how I can see it if I need to.

Thanks again,

Paul.

[Cheeky Charlie]

First question, try this, it (should) purge the fields of the pivot table (when you apply the date range - whether or not you have actually specified dates). I can't test it because I'm not running xl07 and there's an issue with activex controls that I don't understand. If it doesn't work could you please save it in backwards compatibility and upload it (you need to change the save as file type, not just put ".xls" on the end).

Second question, I'm sorry I'm not sure I know what you mean - try right-clicking on the table and "show field list"

HTH

[pauloconnor]

Sorry for the delay in getting back.

I've had a look at it doesn't seem to work - the apply date ranges button doesn't seem to be doing anything. I've also tried selecting new date ranges automatically after changing the names of the keywords but to no avail.

I've saved into the right version of Excel for you and reattached.

Cheers again,

Paul

[pauloconnor]

Hi all,

Just wondering if anyone can help me out with this please?

Cheers,

Paul.

[pauloconnor]

Hi all,

Sorry to be a pain but I'm still hoping for help on this - can anyone with the knowledge take a look?