Follow

Reporting on data captured by the Campaign module

Data captured by the Advorto Campaign module

The Advorto campaign module captures the following data:

»      Referrer URL – this is the address of the web page that the candidates was on before they came through (and applied) to a vacancy on the Advorto-hosted careers site.

»      Landing URL – this is the address of the web page that the candidates were sent to when they came through (and applied) to a job on the Advorto-hosted careers site.

 

Q. Don’t all website stats include this information?

A. Yes and No. The key issue which the Advorto Campaign module addresses is related to candidates who initially click through to a vacancy, but who don’t then apply immediately. It’s quite common for candidates to browse around a client’s careers site or their general website prior to returning to complete an application. Where this happens, the original website where the candidate found out about the vacancy may be lost as, for example, the candidate might finally just do a search on the client careers site for the vacancy to apply. In this case the referring URL would end up being the client’s own website, whereas the original referring URL may have actually been a job board.

The Advorto Campaign module captures the original referrer and landing URL and can match this back to a particular candidate when and if they eventually make an application, regardless of which sites they visit in between first clicking through to the vacancy and eventually making an application.

Q. Is there anything that can be done to make the reports more useful?

A. Yes. The Landing URL may be influenced to help with reporting. A link direct to a vacancy on your Advorto-hosted careers site will be in the following format:

www.YourURL.com/VacancyInformation.aspx?VId=xxxxx

In this case xxxxx is the unique identifier for a particular vacancy (identified by the Vacancy Id – VId).

If nothing were to change and this link was posted on a job board as the link back to the vacancy, then this is the URL which would be captured as the Landing URL.

Additional information may be appended to this URL either automatically or manually in order to help capture more specific information for tracking purposes.

e.g. www.YourURL.com/VacancyInformation.aspx?VId=xxxxx&campaign=Campaign1

The text in bold above won’t impact the page that the candidate is sent to, but the additional information “&campaign=Campaign1” will be captured in the Campaign module Landing URL field.

Some of our partners, such as Indeed and Broadbean already do this automatically as standard (or may be requested to do so).

When posting manually to job boards, clients may also opt to do this manually. One good example of where this can give more granular reporting is where you may post a job on a job board, but also take out a banner advert on the same job board. In this case, the referring URL for both the posting and the banner advert may be the same, so how would you tell which source is working better?

This is where the landing URL comes in handy as you could use two different Landing URLs for the same vacancy.

e.g. for a posting and a banner on Total Jobs, you may opt for

»      www.YourURL.com/VacancyInformation.aspx?VId=12345&source=TotalJobs&Type=Posting

»      www.YourURL.com/VacancyInformation.aspx?VId=12345&source=TotalJobs&Type=Banner1

Here’s some real examples of where additional data has been appended after the standard URL for reporting purposes – it’s always the bit after the VId=xxxxx that we’re interested in, which will always start with the & character:

»      http://careers.ClientName.com/VacancyInformation.aspx?VId=12345&utm_source=Indeed&utm_medium=organic&utm_campaign=Indeed
The extra bit here is &utm_source=Indeed&utm_medium=organic&utm_campaign=Indeed.

»      http://careers.ClientName.com/VacancyInformation.aspx?VId=12345&itjobboard
The extra bit here is &itjobboard.

Q. What if I haven’t done anything to influence the Landing URLs?

A. Don’t worry, where you don’t have anything specific on the Landing URL to use for this purpose, you can still use the referring URL for reporting purposes. This will give you the web page that the candidate came from. Examples of this are:

»      The IT Job Board
http://www.theitjobboard.co.uk/index.php?Mode=LogAdvertAndRedirect&AdvertId=123456&lang=en&url=http%3A%252F%252Fcareers.ClientName.com%252FVacancyInformation.aspx%3FVId%3D12345%26itjobboard&External=1

»      LinkedIn
http://www.linkedin.com/jobs2/view/12345?trk=jobs_sj

»      Monster
http://jobview.monster.co.uk/Contact-Centre-Advisor-Job-Hatfield-Home-Counties-123456.aspx

How do I report on the data captured?

All the data captured by the Campaign module is available in Advorto Analytics (please check with your recruiter what the URL is)

Advorto Analytics is Advorto’s reporting tool for reporting on all data captured in the system. Users who have been provided access to Analytics may login using the same user name and password used to login to the Advorto Backoffice.

Advorto Analytics allows users to create a few different key types of reports. In this case, as the data captured includes full URLs, we want to create a “Raw Data” report first of the required data and then use some simple Excel formulae to transform the data into something more useful to create pivot tables and charts with, within Excel.

 

We will now go through the steps to both extract and manipulate the data as required.

Step 1 – Select the fields in Analytics you want to use in your report

The first step is to pick the fields you want to use in the report. In this case, the key fields related to the Campaign module are within the Candidates section under “Properties”.

 

The fields are usually grouped together and prefixed with “Campaign” as shown below:

»      Campaign - date logged

»      Campaign - landing url (this is the URL of the page the candidate was sent to on your site)

»      Campaign - referrer url (this is the URL of the page they came from)

 

Additional fields to include:

»      If you’re going to be doing counts (e.g. for pivot tables), then you’ll need to include CandidateId as the field to use as the basis for counting.

»      Within the Vacancy section, you may want to include some vacancy classifiers, or Vacancy Opening dates, etc., if you want to be able to filter your report for specific areas of the business, locations or data ranges.

»      Within the Candidates section, you may want to include “Current folder”, which will allow you to report not just on numbers of candidates who applied from each source, but which sources resulted in the most initial rejections, most interviews, most offers, etc.

Step 2 – Add any necessary filters to your report within Analytics

The second step is to add any necessary filters to the report. You may filter for particular date ranges at this point, for example.

 

We recommend that a filter be included as standard to filter out any candidates for whom this data does not exist, which would be the case if the Campaign module was not enabled from the first day the system went live.

e.g. Landing URL > Is not blank

This will ensure that you only get data returned for candidates who actually have this data captured.

Step 3 – Preview your report in Analytics

Click the “Preview” button at the bottom of the page to preview your raw data report:

»      Note that the initial data returned is limited

»      Click “See more results” to see more data if necessary

Step 4 – Export the data to Excel

There are two ways to export the data

»      Click “Download data as CSV” to download the data on a one time use basis (i.e. the data in Excel will become disconnected from the system)

»      Right click on “Live link” and select “Copy link address” (or equivalent) to use Excel’s data from web features to create an Excel report which maintains a connection with the raw data. See Step 3 onwards on the following page for more information on “Live linked” reports:
https://support.advorto.com/hc/en-gb/articles/202297398-How-to-create-a-live-linked-Excel-report

Step 5 – Manipulate the raw data in Excel to get something more useful/usable for reporting purposes

The Raw URLs included in the report aren’t particularly useful or friendly for reporting purposes, but don’t worry – a couple of Excel formulae can help split the URL up so you can get something which is more useful and, most importantly, consistent for reporting purposes. Just add these to additional columns in Excel to the right of the raw data you downloaded above.

Convert the full Referrer URL to just the base URL

The Referrer URL for the same website may appear in multiple different forms, depending on which page on the site the candidate was on.

e.g.

»      http://www.totaljobs.com/JobSearch/PreExternalApplyOnline.aspx?CompanyURL=http%3a%2f%2fcareers.ClientName.com%2fVacancyInformation.aspx%3fVId%3d12345&CompanyName=ClientName &JobTitle=Human+Resources+Advisor+-+Service+Delivery+Wimbledon&JobRef=ABCDEFG&Email= &JobId=987654321

»      http://www.totaljobs.com/JobSearch/PreExternalApplyOnline.aspx?CompanyURL=http%3a%2f%2fcareers.ClientName.com%2fVacancyInformation.aspx%3fVId%3d12345&CompanyName=ClientName&JobTitle=Health+and+Safety+Advisor&JobRef=ABCDEFG&Email=.&JobId=123456789

In the above two URLs, the only bit we’re really interested in is that the candidates came from Total Jobs – i.e. the http://www.totaljobs.com bit.

We therefore want a formula which just pulls this bit out and gets rid of everything else (and does the same thing for all the other URLs captured):

Here’s one we prepared earlier: =LEFT(A1,SEARCH("/",A1,9)-1))

Assuming the full URL is in cell A1, this searches for the / character starting at character 9 (so we don’t count the first / in http:// or https://), then only shows the number of characters of text to the left of that up to an including the / that was found minus 1 (so we don’t display the / that was found).

Sometimes, it’s useful to add to the above formula by putting an error handler around it, in the following way, where “Formula1” is the above formula:

=IF(ISERR(Formula1,"No referrer URL", Formula1)

The above expression just means that If Formula1 returns an error (e.g. because there is No data in the Referrer URL column for some reason), then display the text “No referrer URL”, otherwise display the result of Formula1.

So, the full formula would be as follows:

=IF(ISERR(LEFT(A1,SEARCH("/",A1,9)-1)),"No referrer URL",LEFT(A1,SEARCH("/",A1,9)-1))

Add an appropriate column heading, e.g. “Source website” and then drag/copy the above formula down to all rows

As you can see, we’re not Excel wizards – it just looks a lot more complicate than it actually is! 

Extract custom tags from the full Landing URL

This is not dissimilar to the above, but this time we just want the bit at the end of the URL, which is everything after (to the right of) the first ampersand character after the ?VId=xxxxx bit of the URL.

i.e. the bit in bold in the following example URLs

»      http://careers.ClientName.com/VacancyInformation.aspx?VId=12345&utm_source=Indeed&utm_medium=organic&utm_campaign=Indeed

»      http://careers.ClientName.com/VacancyInformation.aspx?VId=12345&itjobboard

Here’s another one we prepared earlier: =RIGHT(A1,LEN(A1)-SEARCH("&",A1,1))

This formula searches for the first & character within the text in Cell A1 and returns only the text to the right of that. It’s just slightly more complicated to work out how many characters to remove here

Note that, this time, it’s essential to add an error handling expression around the above formula as not all vacancies will necessarily have a custom Landing URL tag. The same expression used in the 2.2.5.1 works here.

This time the full formula would be: =IF(ISERR(RIGHT(C2,LEN(C2)-SEARCH("&",C2,1))),"No URL tag",RIGHT(C2,LEN(C2)-SEARCH("&",C2,1)))

This just means that the text “No URL tag” will be displayed where there is no custom tag.

Again, add an appropriate column heading, e.g. “Custom Landing URL tag” and drag/copy the formula down to all rows.

Create pivot tables or pivot charts in Excel to report on numbers of candidates from each source

You now have two columns in your raw data within Excel, which will:

»      Be consistent for all candidates, regardless of the exact full URL displayed in the original raw data

»      You can use as a basis to count candidates from the same source

To create pivot tables or charts, just select all the columns in your raw data, including any columns with calculated data, such as would be obtained using the formulae detailed above and go Insert > PivotTable or Insert > PivotChart

»      Choose to add the pivot table/chart to a new worksheet

»      In the “VALUES” section, you should use the unique identifier for each candidate, e.g. CandidateId of ApplicationId and make sure that this is set to a Count, rather than a Sum.

»      In the “ROWS” section, use a relevant calculated source field (one of the new columns you added)

»      Optionally add additional fields to the “COLUMNS” or “FILTERS” as required to further pivot the data, e.g. Source website v. application stage (Current folder).

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk