How to match text strings to corresponding data to create a calculation?
Hello, William here. I am a Web Analytics specialist and use excel allot!
Naturally after building a spreadsheet for my customer they asked for another that I am not sure how to proceed.
I have 50K line table with Search Phrases data (back to FY2004) in the following format:
year, Rank, Phrases, Engines, Referrals, % Referrals, Source, Status, Comment
Because I am a government contractor all original data has to be displayed in the data set, therefore, rank, % Referrals is just junk data and can be forgotten about.
Year - this field is Fiscal Year or Fiscal Year + Quarter for example FY2008, FY2009, FY2008-Q1 and so on.
Phrases - is the search phrase from a commercial search engine such as Google or yahoo and looks like "renewable energy"
Engines - list the specific engine that the search phrase came from - the only one I am interested in is "total"
Referrals - how many times the phrase was used
Source - there are a couple of web sites that was combined into the list
Status - this was a through back from another table where I can discard or retain a specific lines as need be.
Therefore, the data looks like this:
Year Rank Phrases Engines Referrals % Referrals Source Status Comment
FY2004 1 renewable energy Google 4,777 0.4930% EERE Total Discard
FY2004 1 renewable energy Yahoo 2,988 0.3080% EERE Total Discard
FY2004 1 renewable energy MSN 1,338 0.1380% EERE Total Discard
FY2004 1 renewable energy Google Australia 761 0.0790% EERE Total Discard
FY2004 1 renewable energy Google UK 737 0.0760% EERE Total Discard
FY2004 1 renewable energy Google Canada 262 0.0270% EERE Total Discard
FY2004 1 renewable energy Google Germany 167 0.0170% EERE Total Discard
FY2004 1 renewable energy Yahoo Hong Kong 118 0.0120% EERE Total Discard
FY2004 1 renewable energy AOL NetFind 111 0.0110% EERE Total Discard
FY2004 1 renewable energy Yahoo Australia & NZ 93 0.0100% EERE Total Discard
FY2004 1 renewable energy Other 494 4.1700% EERE Total Discard
FY2004 1 renewable energy Total 11,846 1.2220% EERE Total Retain
I am being asked to build a chart that allows our search manager to select two time frames and then produce the difference and percent. For example compare FY2008 to FY2009 and show the change in difference between the usage of the specific search phrases.
I thought I could use pivot tables but this did not work out as I had hoped. The difficulty is not displaying the first year data but matching up the comparison year search phrase data to compute the difference.
I need a table that would look like this:
Year: FY2009 (selectable) Year: FY2009 (selectable) Difference % Difference
Search Phrase: Referrals Referrals
Renewable energy 11,846 12,234 388 xx%
Bla
Bla
Bla
Any suggestions on how I can match up the second years data to the first years search phrase (text) to complete the calculation?
Thanks,
William
Hello, William here. I am a Web Analytics specialist and use excel allot!
Naturally after building a spreadsheet for my customer they asked for another that I am not sure how to proceed.
I have 50K line table with Search Phrases data (back to FY2004) in the following format:
year, Rank, Phrases, Engines, Referrals, % Referrals, Source, Status, Comment
Because I am a government contractor all original data has to be displayed in the data set, therefore, rank, % Referrals is just junk data and can be forgotten about.
Year - this field is Fiscal Year or Fiscal Year + Quarter for example FY2008, FY2009, FY2008-Q1 and so on.
Phrases - is the search phrase from a commercial search engine such as Google or yahoo and looks like "renewable energy"
Engines - list the specific engine that the search phrase came from - the only one I am interested in is "total"
Referrals - how many times the phrase was used
Source - there are a couple of web sites that was combined into the list
Status - this was a through back from another table where I can discard or retain a specific lines as need be.
Therefore, the data looks like this:
Year Rank Phrases Engines Referrals % Referrals Source Status Comment
FY2004 1 renewable energy Google 4,777 0.4930% EERE Total Discard
FY2004 1 renewable energy Yahoo 2,988 0.3080% EERE Total Discard
FY2004 1 renewable energy MSN 1,338 0.1380% EERE Total Discard
FY2004 1 renewable energy Google Australia 761 0.0790% EERE Total Discard
FY2004 1 renewable energy Google UK 737 0.0760% EERE Total Discard
FY2004 1 renewable energy Google Canada 262 0.0270% EERE Total Discard
FY2004 1 renewable energy Google Germany 167 0.0170% EERE Total Discard
FY2004 1 renewable energy Yahoo Hong Kong 118 0.0120% EERE Total Discard
FY2004 1 renewable energy AOL NetFind 111 0.0110% EERE Total Discard
FY2004 1 renewable energy Yahoo Australia & NZ 93 0.0100% EERE Total Discard
FY2004 1 renewable energy Other 494 4.1700% EERE Total Discard
FY2004 1 renewable energy Total 11,846 1.2220% EERE Total Retain
I am being asked to build a chart that allows our search manager to select two time frames and then produce the difference and percent. For example compare FY2008 to FY2009 and show the change in difference between the usage of the specific search phrases.
I thought I could use pivot tables but this did not work out as I had hoped. The difficulty is not displaying the first year data but matching up the comparison year search phrase data to compute the difference.
I need a table that would look like this:
Year: FY2009 (selectable) Year: FY2009 (selectable) Difference % Difference
Search Phrase: Referrals Referrals
Renewable energy 11,846 12,234 388 xx%
Bla
Bla
Bla
Any suggestions on how I can match up the second years data to the first years search phrase (text) to complete the calculation?
Thanks,
William