How To

Working In Excel With Keyword Tracking IDs

ChannelAdvisor is now using longer Tracking ID’s (TID) for Paid Search and Feeds.  While these new longer TIDs pose no issue to tracking performance, they do pose an issue when working on data manipulation within Excel.  Previous TIDs were smaller and fit well within Excel’s limits on number rounding.  The new TIDs are coming in at 18 characters.  There is a workaround for anyone using Excel 2007 or higher.  For anyone using Excel 2003, there is a similar workaround, but you have to go to different menus.

Prerequisites


 

Tasks Related to Keyword Tracking Id's


Excel 2007 Workaround

This workaround is dealing with files being downloaded out of ChannelAdvisor’s interface.

  1. Download the file.
  2. Select Excel format or Tab-Delimited from the performance views within ChannelAdvisor.  
  3. Select CSV format when downloading from the Report Activity section of the Reports tab. 
  4. Save the file to a location that you know.  You will need to navigate to that location in order to open the file.  You can also change the name of the file that ChannelAdvisor produces.
  5. When the download is complete, do NOT open the file from the download dialog box within your browser.  This will cause formatting issues.  Also, do NOT double click the downloaded file name to open it.  This will also cause formatting issues.
  6. Go into Excel, and click Open on the main Excel menu (screenshot below).  
  7. Navigate to your file that was downloaded, select Open button in the dialog pop-up box within Excel.
  8.   
  9.  If a dialog box opens indicating that the file you are attempting to open is in a different format than what is specified by the file extension, select Yes.  (screenshot below)
  10. The Text Import Wizard will open within Excel.  Please follow the following steps to ensure the proper format.
  11. Select File Type as Delimited.
  12.  
  13. Select Next.
  14. Select the appropriate Delimiter.  Most ChannelAdvisor files are Tab Delimited, but you can select CSV in the Report Center reports.
  15.  
  16. Select Next.
  17. Locate the column that contains the Tracking ID (ChannelAdvisor will usually label the column as Tracking ID or something similar.  Select the column heading and then select the Text radio button.
  18.  
  19. Select Finish

 Excel will open the file and you should notice that the column with Tracking IDs is showing fully with no strange formatting.  (Screenshot below)

 

Copying Data into Excel from an external source

There are instances where you may just need to copy data into Excel from an external source such as Google’s Adwords Editor or Bing’s Ad Editor.  Here are options for pasting data to make sure you have the appropriate formatting on the Tracking IDs.

Note: When pasting, always choose Paste Special -> Values. ChannelAdvisor recommend pasting data into a new clean workbook as it is easier to handle formatting.

Option 1 – If you are pasting data that has Tracking IDs contained within a URL, such as ChannelAdvisor’s tracking URLs, then you can copy the data and paste it into the blank workbook in Excel.  You can then format the column that have the Tracking IDs as Text after you paste. You would really only need to do this if you are trying to isolate the Tracking ID from within the Tracking URL.

 

Option 2 – If you plan on copying and pasting data that has just the Tracking IDs, not being contained within other text, then you will need to know which column has the Tracking IDs.  If you don’t know which column contains the Tracking ID, then paste your data into a clean workbook and then locate the column.  (Screenshot below)

 

 

After you know which column the Tracking IDs are contained in, you can open a new workbook and then format the column as Text before you paste the data.  (Screenshot below). Your data should be pasted in as text within that column. This should show the full Tracking ID with no rounding.

 

 

Option 3 – If you are copying and pasting just Tracking ID’s into a spreadsheet,  select the column you want to put those IDs in and then format as Text and Paste Special values.

 

Excel 2003 Workaround

Working inside of Excel 2003 is very similar to the 2007 version except that you have to go to a few different places in order to deal with formatting.  To format the column or cells as Text, you will need to go to the Format menu and select Cells.  You will then choose Text.

To paste special values, you will go to the Edit menu and select Paste Special, and then choose Text.

 

ChannelAdvisor is now using longer Tracking ID’s (TID) for Paid Search and Feeds.  While these new longer TIDs pose no issue to tracking performance, they do pose an issue when working on data manipulation within Excel.  Previous TIDs were smaller and fit well within Excel’s limits on number rounding.  The new TIDs are coming in at 18 characters.  There is a workaround for anyone using Excel 2007 or higher.  For anyone using Excel 2003, there is a similar workaround, but you have to go to different menus.

Prerequisites


 

Tasks Related to Keyword Tracking Id's


Excel 2007 Workaround

This workaround is dealing with files being downloaded out of ChannelAdvisor’s interface.

  1. Download the file.
  2. Select Excel format or Tab-Delimited from the performance views within ChannelAdvisor.  
  3. Select CSV format when downloading from the Report Activity section of the Reports tab. 
  4. Save the file to a location that you know.  You will need to navigate to that location in order to open the file.  You can also change the name of the file that ChannelAdvisor produces.
  5. When the download is complete, do NOT open the file from the download dialog box within your browser.  This will cause formatting issues.  Also, do NOT double click the downloaded file name to open it.  This will also cause formatting issues.
  6. Go into Excel, and click Open on the main Excel menu (screenshot below).  
  7. Navigate to your file that was downloaded, select Open button in the dialog pop-up box within Excel.
  8.   
  9.  If a dialog box opens indicating that the file you are attempting to open is in a different format than what is specified by the file extension, select Yes.  (screenshot below)
  10. The Text Import Wizard will open within Excel.  Please follow the following steps to ensure the proper format.
  11. Select File Type as Delimited.
  12.  
  13. Select Next.
  14. Select the appropriate Delimiter.  Most ChannelAdvisor files are Tab Delimited, but you can select CSV in the Report Center reports.
  15.  
  16. Select Next.
  17. Locate the column that contains the Tracking ID (ChannelAdvisor will usually label the column as Tracking ID or something similar.  Select the column heading and then select the Text radio button.
  18.  
  19. Select Finish

 Excel will open the file and you should notice that the column with Tracking IDs is showing fully with no strange formatting.  (Screenshot below)

 

Copying Data into Excel from an external source

There are instances where you may just need to copy data into Excel from an external source such as Google’s Adwords Editor or Bing’s Ad Editor.  Here are options for pasting data to make sure you have the appropriate formatting on the Tracking IDs.

Note: When pasting, always choose Paste Special -> Values. ChannelAdvisor recommend pasting data into a new clean workbook as it is easier to handle formatting.

Option 1 – If you are pasting data that has Tracking IDs contained within a URL, such as ChannelAdvisor’s tracking URLs, then you can copy the data and paste it into the blank workbook in Excel.  You can then format the column that have the Tracking IDs as Text after you paste. You would really only need to do this if you are trying to isolate the Tracking ID from within the Tracking URL.

 

Option 2 – If you plan on copying and pasting data that has just the Tracking IDs, not being contained within other text, then you will need to know which column has the Tracking IDs.  If you don’t know which column contains the Tracking ID, then paste your data into a clean workbook and then locate the column.  (Screenshot below)

 

 

After you know which column the Tracking IDs are contained in, you can open a new workbook and then format the column as Text before you paste the data.  (Screenshot below). Your data should be pasted in as text within that column. This should show the full Tracking ID with no rounding.

 

 

Option 3 – If you are copying and pasting just Tracking ID’s into a spreadsheet,  select the column you want to put those IDs in and then format as Text and Paste Special values.

 

Excel 2003 Workaround

Working inside of Excel 2003 is very similar to the 2007 version except that you have to go to a few different places in order to deal with formatting.  To format the column or cells as Text, you will need to go to the Format menu and select Cells.  You will then choose Text.

To paste special values, you will go to the Edit menu and select Paste Special, and then choose Text.

 

0
Your rating: None
0
Your rating: None