{"id":778,"date":"2015-08-17T16:04:39","date_gmt":"2015-08-17T16:04:39","guid":{"rendered":"http:\/\/www.portatour.com\/blog\/en\/?p=778"},"modified":"2022-02-07T10:29:19","modified_gmt":"2022-02-07T10:29:19","slug":"analyze-field-reps-call-reports-on-a-map-with-excel-and-power-map","status":"publish","type":"post","link":"https:\/\/www.portatour.com\/blog\/en\/analyze-field-reps-call-reports-on-a-map-with-excel-and-power-map\/","title":{"rendered":"Analyze field reps\u2019 call reports on a map with Excel &#038; Power Map"},"content":{"rendered":"<p>Have you always asked yourself which customers your field reps visit and where? Are you interested in visualizing your field\u00a0reps&#8217; call reports on a map and possibly analyzing them together with other key indicators? The new <a href=\"https:\/\/support.office.com\/en-us\/article\/Get-started-with-Power-Map-88a28df6-8258-40aa-b5cc-577873fb0f4a?omkt=en-US&amp;ui=en-US&amp;rs=en-US&amp;ad=US\">Power Map<\/a>\u00a0feature of Excel 2013 and Power Pivot for data analysis is able to create even complex visualizations. The following article will give you\u00a0step-by-step instructions for the setup. For this purpose we use call reports\u00a0entered into\u00a0the <a href=\"https:\/\/www.portatour.com\/en\">portatour<sup>\u00ae<\/sup> field rep software<\/a>\u00a0which can be directly imported into Excel and updated at the push of a button.<\/p>\n<h2><!--more-->What you need<\/h2>\n<ul>\n<li>Your field\u00a0reps&#8217; <strong>call reports<\/strong>, e.g. from the <a href=\"https:\/\/www.portatour.com\/sales-route-planner\">portatour<sup>\u00ae<\/sup> route planner for field reps<\/a>.<\/li>\n<li><strong>Excel<\/strong> 2013 (or higher), <strong>Power Pivot<\/strong> and <strong>Power Map<\/strong>.<\/li>\n<li>A healthy amount of <strong>curiosity<\/strong> and <strong>creativity<\/strong>.<\/li>\n<\/ul>\n<h2><strong>Example: Number of calls per year and state<\/strong><\/h2>\n<p>Below you can see a view of Power Map for Excel. The map shows the number of calls per state, comparing the years 2014 and 2013. We can see that the number of calls has increased in all areas between 2013 and 2014.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-763\" src=\"https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-1024x858.png\" alt=\"Aussendienst Vertrieb Analyse Besuche pro Jahr Excel\" width=\"625\" height=\"524\" srcset=\"https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-1024x858.png 1024w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-300x251.png 300w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-624x523.png 624w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr.png 1123w\" sizes=\"auto, (max-width: 625px) 100vw, 625px\" \/><\/p>\n<h2>Setup: Excel, OData, Power Pivot and Power Map<\/h2>\n<p>The following 4 steps are a precondition for the creation of analyses.<\/p>\n<p><strong>1)<\/strong> Install <strong>Power Map<\/strong>, which\u00a0is automatically available to subscribers of <strong>Office 365 ProPlus<\/strong> and <strong>Office 365 Enterprise E3<\/strong>. Follow the installation instructions for Power Map. In addition, you also need Power Pivot, which is also available for Excel 2013 and must be activated.<\/p>\n<p><strong>2)<\/strong> Your <strong>call reports<\/strong> are imported from the <a href=\"https:\/\/www.portatour.com\/en\">portatour<sup>\u00ae<\/sup> route planner<\/a>. For this purpose an OData interface is provided, which enables Excel to import (and subsequently update) data directly from portatour\u00ae. Go to the menu item <em>Options &gt; Excel data analysis<\/em> in portatour\u00ae Anywhere to obtain instructions for the setup (requires company access with 2+ users). Before you continue, make sure that your call reports are available in Power Pivot in Excel.<\/p>\n<p><strong>3)<\/strong> Now create your <strong>own statistical values<\/strong> in Power Pivot. If, for example, you want to calculate the <strong>number of calls per customer<\/strong>, add another column in your Power Pivot customer table and insert the following formula:<\/p>\n<pre>=COUNTROWS(RELATEDTABLE(Call Reports))<\/pre>\n<p><em>Hint: the &#8220;Relatedtable&#8221; feature only works if you have previously created relationships between the Power Pivot tables.<\/em><\/p>\n<p><strong>4)<\/strong> Now add the <strong>map<\/strong> by selecting <em>Insert &gt; Power Map &gt; Map<\/em> in Excel to\u00a0start the data analysis on the map.<\/p>\n<ul>\n<li>Go to the top right below the heading &#8220;Choose geography&#8221; and select the following &#8220;Customer&#8221;\u00a0fields with geopraphical data: <em>State, Latitude, Longitude, Country, City, ZIP\u00a0<\/em>and<em> Street.<\/em><\/li>\n<li>Underneath, link the respective meaning in Power Map with each selected field (e.g. &#8220;Street&#8221; with &#8220;Street&#8221;).<\/li>\n<li>To the left of each field you can see a circular button. Use it to select one of the visualization levels, e.g. &#8220;State&#8221; in order to classify the maps as shown in our example.<\/li>\n<li>Click &#8220;Continue&#8221;.<\/li>\n<\/ul>\n<p>Now you have set up the map and you can visualize your data on it.<\/p>\n<h2>Analyze call reports<\/h2>\n<p>In order to visualize the <strong>number of calls per year<\/strong> on the map, drag the field <em>Reports &#8211; ID<\/em> into the box for <em>height<\/em> and the field\u00a0<em>Reports &#8211; Date &#8211; Year<\/em> into the box for <em>category<\/em>. Finally, choose the the type of bar chart and change possible display options like colour and height. We already showed the result of the report, here it is again:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-763\" src=\"https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-1024x858.png\" alt=\"Aussendienst Vertrieb Analyse Besuche pro Jahr Excel\" width=\"625\" height=\"524\" srcset=\"https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-1024x858.png 1024w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-300x251.png 300w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr-624x523.png 624w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-besuche-pro-jahr.png 1123w\" sizes=\"auto, (max-width: 625px) 100vw, 625px\" \/><\/p>\n<p>In order to visualize the <strong>average number of calls per customer<\/strong> select the chart type region. Then drag the previously created field <em>Customers &#8211; number of calls<\/em>\u00a0into the box for height. Go to the value options and set the aggregation to average value. This results in the following report:<\/p>\n<p><a href=\"https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-durchschnittliche-besuche-pro-kunde.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-764\" src=\"https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-durchschnittliche-besuche-pro-kunde-1024x858.png\" alt=\"Au\u00dfendienst Vertrieb Analyse durchschnittliche Besuche pro Kunde Excel\" width=\"625\" height=\"524\" srcset=\"https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-durchschnittliche-besuche-pro-kunde-1024x858.png 1024w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-durchschnittliche-besuche-pro-kunde-300x251.png 300w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-durchschnittliche-besuche-pro-kunde-624x523.png 624w, https:\/\/www.portatour.com\/blog\/wp-content\/uploads\/aussendienst-vertrieb-analyse-durchschnittliche-besuche-pro-kunde.png 1123w\" sizes=\"auto, (max-width: 625px) 100vw, 625px\" \/><\/a><\/p>\n<p>The map shows: more calls per customer are achieved in Berlin and Hamburg than in other federal states.<\/p>\n<h2>Many more analyzes possible &#8230;<\/h2>\n<p>You have all customer data and call reports available in Excel\u00a0\u2013 use your curiosity and discover interesting values that you want to analyze and compare.<\/p>\n<p>In case you do not yet use the <a href=\"https:\/\/www.portatour.com\/sales-route-planner\">portatour<sup>\u00ae<\/sup> route planning software<\/a>\u00a0register for a <a href=\"https:\/\/www.portatour.com\/anywhere\/apply\/en\">free trial<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Have you always asked yourself which customers your field reps visit and where? Are you interested in visualizing your field\u00a0reps&#8217; call reports on a map and possibly analyzing them together with other key indicators? The new Power Map\u00a0feature of Excel 2013 and Power Pivot for data analysis is able to create even complex visualizations. The [&hellip;]<\/p>\n","protected":false},"author":6,"featured_media":777,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-778","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-management-best-practices"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/posts\/778","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/comments?post=778"}],"version-history":[{"count":35,"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/posts\/778\/revisions"}],"predecessor-version":[{"id":4512,"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/posts\/778\/revisions\/4512"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/media\/777"}],"wp:attachment":[{"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/media?parent=778"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/categories?post=778"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.portatour.com\/blog\/en\/wp-json\/wp\/v2\/tags?post=778"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}