Message Boards

GROUPS:

Business Analytics Data Science Economics Graphics and Visualization Graphs and Networks Wolfram Language

1

[Notebook] Visualization of Direct and Indirect Sales

Fredrik Doberl

Posted 7 months ago

561 Views

|

0 Replies

|

1 Total Likes

Follow this post

|

Advanced Analytics - Visualizing Direct and Indirect Sales Diagram - Analyzing and Visualizing Direct Indirect Sales Out[]= In the Nordic IT related professional services market, it is common for customers to strive for having a few suppliers. This means that niche companies sometimes have to sell their services through a large company - a middleman - referred to by the client’s purchase department. The contracts for the large companies are time limited which means that the niche companies contract with the client are at risk. As a part of recent advisory project with a client , I wanted to visualize how a company had most of its revenues in direct relationships and - for the indirect revenues - that the relationships with its customers survived the change of middleman. In a dialogue with the client we came up with the graph above. An explanation: - The company itself is the light grey node in the middle. - An edge to a light green node shows a direct relationship. An edge to a yellow node shows an indirect relationship to an indirect customer. Indirect customer are coloured to in darker green. Light orange nodes are middlemen that have changed during the period. - The size of green nodes shows the revenue during the last 18 months. The time window (18 months) was selected based on project length, not financial year. The visualization shows that most of the clients are direct. Also, it shows that in some cases the relationship to indirect customers survive the change of direct supplier. These are the light orange nodes. Work flow - Analyzing and Visualizing Direct and Indirect Sales C:\Users\FDO\OneDrive\Documents\_MyResearch\Advanced analytics - Cases\\Direct Indirect sales\aData.json Out[]= Anonymized actual data from the the case: container=Import["https://www.wolframcloud.com/obj/ScieranObjects/Published/aData.json"];$structuralBaseWIndicatorsMm=Association[#]&/@container;RandomChoice@$structuralBaseWIndicatorsMm In[]:= name C 1 ,total revenue45568.6,directInvoicing45568.6,inDirectInvoicing0,inDirectPartners{} Out[]= The explanation of the keys in the list of Associations are: - “name”, name of the client - “total revenue”, total revenue during the period - “directInvoicing”, direct invoicing to the client - “indirectInvoicing”, indirect invoicing to the client - “inDirectPartners”, list of companies that acts as middlemen. This is not the ideal format but given the client’s specific configuration of CRM and ERP systems and limited resources for extracting & compiling the data it was the format that it was agreed to work with. Building the graph First, for each customer, we find the indirect partner and delete duplicates. $inDirectList=((Query[All,"inDirectPartners"]@$structuralBaseWIndicatorsMm)//Flatten//DeleteDuplicates//Sort) In[]:= { I 1 , I 2 , I 3 , I 4 , I 5 , I 6 , I 7 , I 8 } Out[]= Second, we create the subgraph with the company and its invoice partners. invoiceGraphList="C"#&/@$inDirectList In[]:= {C I 1 ,C I 2 ,C I 3 ,C I 4 ,C I 5 ,C I 6 ,C I 7 ,C I 8 } Out[]= Third,we get the name and the indirect partners. Note that an empty indirect list implies that the relationship is direct. grafListPrel={#["name"],#["inDirectPartners"]}&/@$structuralBaseWIndicatorsMm;grafListPrel//Short In[]:= {{ C 1 ,{}},147,{ C 149 ,{}}} Out[]//Short= An empty second element implies a direct relationships. Replace the empty ones with “C”, that is, the company itself. grafList=grafListPrel//.{}{"C"};grafList//Short In[]:= {{ C 1 ,{C}},147,1} Out[]//Short= A helper function Clear@linkAtoSublist;linkAtoSublist[{a_,sub_List}]:=a#&/@sub; In[]:= Join the two graphs, the first with the indirect and the second with the direct. g=Join[(linkAtoSublist[#]&/@grafList)//Flatten,invoiceGraphList];g//Short In[]:= { C 1 C,158,C I 8 } Out[]//Short= The initial graph is then: Graph[g] In[]:= Out[]= The initial graph clearly shows that most customers are direct. But it is missing the the relative size and the interesting property that the company has survived changes of intermediaries isn’t highlighted. Annotate the graph The goal is to high light different subsets of the nodes First, we identify all graph elements that are direct customers. The code below detects all graph components where at least one is the company "C". directCustomers=Cases[g,a_b_a/;b"C"] In[]:= { C 1 , C 3 , C 4 , C 5 , C 7 , C 8 , C 9 , C 10 , C 11 , C 12 , C 13 , C 15 , C 16 , C 17 , C 18 , C 20 , C 21 , C 22 , C 24 , C 25 , C 26 , C 27 , C 28 , C 30 , C 31 , C 34 , C 35 , C 36 , C 37 , C 39 , C 40 , C 41 , C 43 , C 44 , C 45 , C 46 , C 47 , C 49 , C 50 , C 51 , C 54 , C 55 , C 57 , C 58 , C 59 , C 60 , C 61 , C 62 , C 65 , C 67 , C 68 , C 70 , C 71 , C 72 , C 73 , C 74 , C 75 , C 76 , C 77 , C 80 , C 81 , C 83 , C 85 , C 86 , C 87 , C 89 , C 91 , C 93 , C 94 , C 95 , C 96 , C 97 , C 98 , C 99 , C 100 , C 101 , C 103 , C 104 , C 105 , C 106 , C 109 , C 110 , C 111 , C 112 , C 114 , C 116 , C 117 , C 118 , C 119 , C 121 , C 122 , C 123 , C 124 , C 125 , C 126 , C 128 , C 129 , C 130 , C 132 , C 133 , C 134 , C 135 , C 136 , C 137 , C 138 , C 139 , C 140 , C 142 , C 143 , C 147 , C 149 } Out[]= Second, identify the indirect customers. indirectCustomers=Cases[g,a_b_a/;And[MemberQ[$inDirectList,b],a≠"C"]]//DeleteDuplicates In[]:= { C 2 , C 6 , C 14 , C 19 , C 23 , C 29 , C 32 , C 33 , C 38 , C 42 , C 48 , C 52 , C 53 , C 56 , C 63 , C 64 , C 66 , C 69 , C 78 , C 79 , C 82 , C 84 , C 88 , C 90 , C 92 , C 102 , C 107 , C 108 , C 113 , C 115 , C 120 , C 127 , C 131 , C 141 , C 144 , C 145 , C 146 , C 148 } Out[]= Third, we prepare the weights for the size. Here the total revenue for the customer is used to create an association with the relative size. Also, we create a size parameter for nodes that merely are there for place holding (the company itself and the middlemen) custAndPropAssoc=<\|#["name"]->#["total revenue"]/($structuralBaseWIndicatorsMm[[All,"total revenue"]]//Total)&/@$structuralBaseWIndicatorsMm\|>;(check<\|Map\|>patternvs<\|\|>Map)zeroInv=<\|(#(Values@custAndPropAssoc//Mean)&/@Join[$inDirectList,{"C"}])\|>;allVertexWeight=Join[custAndPropAssoc,zeroInv];allVertexWeight//Short In[]:=  C 1 0.000432433,156,C0.00671141 Out[]//Short= Last, we create the graph by using the HighlightGraph function $highG=HighlightGraph[g,{Style[directCustomers,Green],Style[$inDirectList,Yellow],Style[indirectCustomers,Darker@Green]},PlotLabel"Direct customers, invoice partners and indirect customers. Size~part of Revenues",VertexSizeThread[Keys[allVertexWeight]Rescale[Values[allVertexWeight],{Min@Values[allVertexWeight],Max@Values[allVertexWeight]},{5,35}]],ImageSizeLarge,GraphLayout{"SpringElectricalEmbedding","Tolerance"0.9}] In[]:= Out[]= Identify all customers that are indirect and their indirect partners except for C itself subG=Cases[g,a_b_/;StringContainsQ[b,"I"]]//DeleteCases[#,"C"_]&;subG In[]:= { C 2  I 4 , C 6  I 4 , C 14  I 7 , C 19  I 4 , C 19  I 5 , C 23  I 7 , C 29  I 4 , C 32  I 7 , C 33  I 4 , C 38  I 4 , C 42  I 8 , C 48  I 4 , C 52  I 4 , C 53  I 1 , C 53  I 2 , C 56  I 5 , C 63  I 6 , C 64  I 4 , C 66  I 4 , C 69  I 5 , C 78  I 4 , C 79  I 4 , C 82  I 4 , C 84  I 7 , C 88  I 4 , C 90  I 6 , C 92  I 3 , C 102  I 4 , C 107  I 4 , C 107  I 5 , C 108  I 5 , C 113  I 3 , C 115  I 6 , C 120  I 7 , C 127  I 4 , C 131  I 7 , C 141  I 4 , C 144  I 7 , C 145  I 6 , C 146  I 4 , C 148  I 4 } Out[]= Find the customers with more than one indirect partner. These are the ones where the customer has changed the partner but kept C (based on that C has sent invoices) changeCust=ReplaceList[subG,{___,a__,___,a__,___}a] In[]:= { C 19 , C 53 , C 107 } Out[]= For these customers, identify the indirect partners changeI=Cases[g,a_b_/;MemberQ[changeCust,a]b]//DeleteDuplicates In[]:= { I 4 , I 5 , I 1 , I 2 } Out[]= Add styling to those nodes in the graph HighlightGraph[g,{Style[directCustomers,Green],Style[$inDirectList,Yellow],Style[indirectCustomers,Darker@Green],Style[changeI,LightOrange]},PlotLabel"Direct customers, invoice partners and indirect customers. Size~part of Revenues",VertexSizeThread[Keys[allVertexWeight]Rescale[Values[allVertexWeight],{Min@Values[allVertexWeight],Max@Values[allVertexWeight]},{5,35}]],ImageSizeLarge,GraphLayout{"SpringElectricalEmbedding","Tolerance"0.9}] In[]:= Out[]= Discussion - Visualization Direct/Indirect Sales An traditional visualization of direct vs indirect sales is a pie chart, see below. In the project, this diagram would have lost relevant information that was available in the CRM and ERP systems. Information that showed the strength of the company’s relationship to its customers despite the middlemen. Also, in the pie chart, information about the relative size of the customers and the number of middlemen are lost. $inDirectD=PieChart[{(Query[All,"directInvoicing"]@$structuralBaseWIndicatorsMm)//Total,(Query[All,"inDirectInvoicing"]@$structuralBaseWIndicatorsMm)//Total},ChartLabels{"Direct","Indirect"},ChartStyle{Green,Darker@Green}] In[]:= Out[]= In the advisory project for the client, the visualization generated good dialogues about the company’s “go to market” strategy and how this could be developed as part of a potential acquisition.

POSTED BY: Fredrik Doberl

Answer

Reply to this discussion

Reply Preview

Attachments

Remove Add a file to this post

Follow this discussion

or Discard

Group Abstract

Enable JavaScript to interact with content and submit forms on Wolfram websites. Learn how »