I need to make daily analysis of a report from online database that doesn't allow me to download the data in 'analysis-friendly' way and doesn't always have the same number of rows. I think I need to use combination of vlookup, match, if, index and offset to fill in my 'analysable table'.
Originally the table looks like:
Entry Page Title Doc Type Visits Bounce Conversion
Looking for best funeral insurance plan? 1 TOTAL TOTAL 8 87.50% 0%
Looking for best funeral insurance plan? 1 Short Form TOTAL 1 0% 0%
Looking for best funeral insurance plan? 1 Short Form Paid Search 1 0% 0%
Looking for best funeral insurance plan? 1 Other
Looking for best funeral insurance plan? 2 TOTAL TOTAL 11 27.27% 9.09%
Looking for best funeral insurance plan? 2 Short Form TOTAL 8 0% 0%
Looking for best funeral insurance plan? 2 Short Form Paid Search 8 0% 0%
Looking for best funeral insurance plan? 2 Application TOTAL 1 0% 100%
Looking for best funeral insurance plan? 2 Application Paid Search 1 0% 100%
Looking for best funeral insurance plan? 2 Other
Looking for best funeral insurance plan? 3 TOTAL TOTAL 9 55.56% 11.11%
Looking for best funeral insurance plan? 3 Short Form TOTAL 4 0% 0%
Looking for best funeral insurance plan? 3 Short Form Paid Search 4 0% 0%
Looking for best funeral insurance plan? 3 Application TOTAL 1 0% 100%
Looking for best funeral insurance plan? 3 Application Paid Search 1 0% 100%
Looking for best funeral insurance plan? 3 Other
Looking for the Best Funeral Insurance Plan? TOTAL TOTAL 8 50% 25%
Looking for the Best Funeral Insurance Plan? Short Form TOTAL 3 0% 0%
Looking for the Best Funeral Insurance Plan? Short Form Paid Search 3 0% 0%
Looking for the Best Funeral Insurance Plan? Application TOTAL 2 0% 100%
Looking for the Best Funeral Insurance Plan? Application Paid Search 2 0% 100%
Looking for the Best Funeral Insurance Plan? Other
Total 0 50 42% 16%
and should automatically fill this template:
Visits Short Form Bounce rate Application Conversion Rate
ORIGINAL title
* Paid Search**
* Email Campaigns**
* Affiliate Program**
* Display Campaigns**
Total campaign activity
VERSION 1 title
* Paid Search**
* Email Campaigns**
* Affiliate Program**
* Display Campaigns**
Total campaign activity
VERSION 2 title
* Paid Search**
* Affiliate Program**
* Display Campaigns**
* Email Campaigns**
Total campaign activity
VERSION 3 title
* Paid Search**
* Affiliate Program**
* Display Campaigns**
* Email Campaigns**
Total campaign activity
VERSION 4 title
* Paid Search**
* Affiliate Program**
* Display Campaigns**
* Email Campaigns**
Total campaign activity
VERSION 5 title
* Paid Search**
* Affiliate Program**
* Display Campaigns**
* Email Campaigns**
Total campaign activity
Any tips will be most appreciated!!!!
Bookmarks