Where to start with sales analytics? All analytics indicators are divided in accordance with the 2Q1D principle into 3 types: qualitative, quantitative and development indicators. 2Q1D involves the use of sales analytics methods using a specific universal algorithm.

Quantity means analytics of sales indicators in a quantitative context. Here you track how effectively the system works: cold calls, meetings, traffic from a website or blog, internet marketing tools. By doing this analysis, you learn how to maximize the entrance or “throat” of the funnel.

Quality combines a group of sales analytics methods that are aimed at measuring quality indicators. How to compose it within the framework of quality? Measure and use methods such as:

1. Base segmentation

• by business area (B2C, B2B, B2G);
• by contract size;
• on the subject of the transaction

1. ABC XYZ – analysis
2. Study of customer and product migration in ABC XYZ categories

Using such sales analytics, you take care of expanding the walls of the funnel along its entire length.

Developing. It's about business development. The goal is to create new funnels by product and channel. Only after working on quantity and quality, launch new products and connect new channels. Then the pace and quality of development are assessed, and standard sales indicators are measured.

Sales Analytics: ABCXYZ Research

Sales analytics in a company starts from the current base. It will allow you to understand which customers buy more and more often, as well as which products they buy more and more often. Why do you need this? Obviously, after such research, you will be able to identify representatives of your target audience with a high degree of accuracy, which in turn will ensure revenue growth.

The letters ABC XYZ mean:

• Group A - customers/products with the highest volumes of purchases
• Group B - clients/products with an average level
• Group C - customers/products with small purchase volumes

• Category X - those who contact you most often, or the most popular products
• Category Y - counterparties with irregular requests or irregularly shipped products
• Category Z - those who make single purchases or products with single and unpredictable consumption.

Sales analytics: working with the current database

Current clients should be handled by separate managers. You should not combine these responsibilities with work to attract new customers. Regular ABCXYZ analysis will help you control:

• migration of buyers from category to category,
• set personal revenue plans for clients,
• plan growth in up-sale (same products) and cross-sale (cross products),
• draw up motivation for employees working with the current base.

It is important to encourage repeat business. This can be done in various ways, including special loyalty programs, which include gifts, free seminars or master classes, discounts and promotions on assortments, etc.

Sales analytics: customer loyalty

Customers in group 2 are also important: their suggestions about what should be changed to improve their score can become the basis for reducing customer churn and improving future performance.

Sales analytics: penetration

If the company has a periodic cycle of sales of goods/services, then the analytics should include such an indicator as the share in the client. It is important to understand how many products the buyer buys from your company, and for which ones he turns to competitors. At the same time, it is also necessary to evaluate what your share is in up-sale and cross-sale potential.

You can obtain data in three ways:

• statistical (available information on the market, the number of companies operating in it, and the number of consumers is collected);
• survey of clients on developed scripts;
• survey through a market research service.

For example, a company supplies one contractor with 40 reams of paper every month. The total volume of purchases is 100 packs. We calculate the share in the client:

40: 100 = 0.4 or 40%.

Now we need to think about how and by what means to increase this figure: offer a better price, convenient delivery, expand the range of stationery, etc.

Sales analytics: reporting system

What is analytics in sales? This is primarily about setting up the reporting system. Reports for sales analytics are generated according to 2 main principles: functional and structural.

In accordance with the functional principle, sales analytics reflects what is happening in the company’s business processes as a whole.

And the structural principle implies analytics in sales by division.

Functional principle

Lead generation. To conduct sales analytics in this area, you need to monitor 2 areas:

1. total number of leads entering the funnel from all channels
2. Sales performance analytics depending on the channel through which the lead came

Lead conversion. This is the area of ​​primary sales. The most important thing about it is to increase the percentage of successfully closed transactions as part of incoming traffic. Here we analyze 2 parameters:

• quality of leads - their qualification for compliance with the portrait of the target audience;
• conversion when moving from stage to stage.

Development of a client base. This is already advanced analytics or sales analysis based on 5 indicators.

1. Penetration or share in the buyer
2. Net Promoter Score (NPS)
3. Customer Lifetime Value (LTV)
4. Customer Retention Rate (CRR)
5. Average Revenue per Customer (ARC)

Finance. Sales analytics is primarily a calculation of the profitability of activities for each client. Companies often operate at a loss even with high turnover. Do you value your large but problematic counterparties? Now calculate how beneficial it is for you to cooperate with them.

Structural principle

According to this principle, reports are compiled based on the indicators of 3 key divisions of the commercial area.

1. Marketing – ensures lead generation and supports the deal throughout its progress through the stages
2. Sales – directly affect revenue
3. Finance – monitors the profitability of the enterprise

Sales analytics: daily monitoring of plan and fact

Daily as well as weekly reporting and monitoring constitute an important part of all sales analytics. The methods that are used are filling out 2 forecast and 2 control forms in.

Forecast forms

1. “Weekly payment plan.” As an example of sales analytics for this form, we provide the following table.

A similar table is filled out by each salesperson weekly so that goals can be formed for the next week. A week is a critical period, since during it at least a quarter of the entire monthly plan must be closed. How to do sales analytics on a “weekly payment plan”?

1. Create a similar form in CRM
2. Oblige managers to fill it out at a certain time. To do this, make appropriate changes to the motivation: add and introduce non-monetary fines for violating the filling regulations.
3. Upload completed forms weekly to discuss plans at a big meeting, such as Monday.

2. “Payment plan for tomorrow.” To conduct sales analytics in a timely and correct manner, you need to constantly monitor the progress of weekly plans along the pipeline. The “payment plan for tomorrow” form will help you with this.

This form is completed at the end of the working day to plan activities for tomorrow. The manager should track daily changes on this form and check the “weekly payment plan”

Control forms

1. “The fact of payment for today.” Sales analytics targets are monitored using the “Actual payments for today” report. How to do sales analytics on a daily basis? To do this, employees fill out such a table twice a day.

Set checkpoints when sellers enter data on this form. For example, they can do this the first time before lunch, and the second time half an hour before the end of the working day. Such control is justified, as it stimulates the staff to move towards the daily goal, and will allow the manager, by intervening in time, to correct the situation.

2. “Board” report. Generated automatically as a result of preliminary sales analytics. Example:

All data in this format is displayed on a video panel for public viewing.

Why do you need whiteboard sales analytics? Using it, in a few seconds you can determine how things are going with the implementation of the plan.

The first column deserves special attention - “percentage of plan completion for the current day.” It should not be confused with an absolute indicator, or rather with an absolutely useless indicator that simply reflects the percentage of plan closure.

Indeed, if we imagine a situation where the manager has fulfilled approximately 70% of the forecast, and at the same time we are in the middle of the third weekly cycle. What does this tell us? Can we understand whether a person copes with the tasks assigned to him or not? Obviously, we will not get answers to this question.

Therefore, “percentage of plan completion for the current day” is an indicator of pace. Thus, he “reports” how much the plan will be fulfilled by each of the employees if he continues to work at the same rhythm.

From the above “board” it is clear that Sidorov is doing very badly, Ivanov needs to speed up a lot, and only Petrov is working with almost 100 percent efficiency.

To calculate the rate of plan implementation, the following formula is loaded into CRM:

Current fact: (Monthly plan: total number of working days in a month x number of days worked in a month) x 100

Sales analytics: call accounting

Analytics on sales of goods and services is also carried out based on qualitative and quantitative indicators on calls.

How to do sales analytics: cold calling - qualitative characteristics

1. We draw up technological maps (employee development sheets) - a list of skills necessary for successful closing of the transaction.

2. We listen to 2-3 conversations of each seller every month and put them next to the skills (checkpoints): “1” - applied and “0” - did not apply.

3. We evaluate each call using the traffic light system: green - more than 80% of the checkpoints from the technological map have been passed in the conversation; yellow - 60−80 completed; red - less than 60% completed.

If you have a yellow-red canvas in front of you, something has gone wrong in the department. As measures to change the situation, use: trainings, recruitment of new personnel, dismissal of individual managers.

How to do sales analytics: cold calling quantitative data

The more high-quality telephone conversations with representatives of your target audience, the higher the revenue. Therefore, we control the number of calls:

• number of incoming;
• number of outgoings;
• daily plan;
• fact per day;
• indicators for the entire department and each employee individually.

Another important quantitative indicator is the duration of the call. The point here is not to have salespeople talk less or longer, but to find the optimal duration for an effective call.

Sales analytics: returning lost customers

Analytics is extremely important in the process of winning back abandoned customers. Explain the reason, prepare the correct script, organize regular work in this area - all this can be done with summary data at hand.

After talking with the “failed” customers, the reasons are clarified: perhaps there was some kind of conflict, they didn’t get along with the new manager, the product did not meet the declared quality, or they simply haven’t called for a long time.

Having analytics in this segment, you can draw up an action plan to: improve the quality of a product or service, offer an exclusive contract, send a nice gift, etc. Try to please your departed client!

Sales analytics: attracting new customers

As in working with the current base, individual managers should be involved in attracting new customers: according to department statistics, this results in a 2-3 times increase in the number of customers.

And in this direction it is also necessary to conduct analytics. How and to what extent the client base has been updated over the past six months, what is the conversion from leads to deals, what is the funnel for working with new clients - these and other thematic reports should be at hand of the manager.

Having research on new clients will also help prepare proposals that will increase the average bill. For example, offer additional products to the main product, include kits or improved versions of the product at a higher price, or award a bonus for the average bill.

Sales analytics: non-material motivation

Various professional competitions can also motivate staff. For most of the team, it is very important not to lose face, to gain general recognition, so excitement can be used to support sales.

It is important to determine the purpose of the competition. For example, who will fulfill 50% of the plan faster, or who will complete the monthly plan in 3 weeks, the sale of an old collection, who will sell more goods from a certain manufacturer.

You can invite the team to compete both for a tangible prize (a weekend trip to a recreation center, a coupon to a spa, dinner for two in a restaurant, etc.), and for the opportunity for the winner to receive a pennant, sit on the boss’s chair, etc. .d.

Be sure to conduct analytics - which competitions are most interesting to your employees in order to identify the most relevant forms of non-financial motivation.

Analyze means making a decision using your own The entire process of making a correct and thoughtful decision can be divided into three steps. First, you should collect as much information as possible. Then it needs to be carefully analyzed and a final decision made based on the conclusions drawn.

How analytical skills help solve problems

It is worth noting the importance of this stage. And in this matter it is better to use your own experience, since other people’s advice will not help you, but rather, on the contrary, will interfere. Therefore, listen less to those who are not interested in solving your issue. Some people do not need to be explained what it means to analyze; it is given to them by nature. But if you are deprived of this skill, then you need to train your ability to analyze and develop. You can achieve results in any field of knowledge, but this will require some effort.

How to analyze correctly - simple training

You need to start training with simple exercises. For example, you should read a short text and try to determine which part is the most important. To do this, you need to make a plan for the main events that are described in the text.

This exercise will help you learn to identify the most important things from the mass of information received. This will be enough to achieve success in almost any field of activity. Just remember, overdoing this exercise will not do you any good. A large amount of unnecessary information will not help you learn to analyze. It's just

Analysis in problem solving

Solving physical and mathematical problems will also help you learn how to correctly analyze the information received. In the exact sciences, to find the answer, you first need to analyze the given conditions. Only in this case will you understand. Since analyzing is not so easy, it is worthwhile, while performing this exercise, to try to structure the solution stages in a certain sequence.

Practical application of analysis

To process the information received, you need to have such a character trait as perseverance. If the proposed exercises do not seem difficult to you, then you can try to apply the analysis in practice. Please note that it is better to test your strength in situations that do not concern you personally. Otherwise, you will not be able to analyze subjectively; it will be a decision made under the influence of emotional excitement. That is, it will not be one hundred percent correct.

Having a maximally objective attitude towards the situation will help you learn to control your emotions, which will be useful for developing analytical thinking. It is recommended to start with everyday life situations that you can easily explain. For example, try to find the reasons why this or that situation occurred, or explain people’s behavior. Gradually increasing the level of complexity of the analytical problems you solve, you will learn to find the right way out of almost any situation.

If a company has been on the market for a long time, then by default it already operates There are several departments, people clearly understand their tasks, and the owner strives to systematize this process. Work in startups often goes by the motto: “Oh, we’re on fire,” where the owner resembles a novice skier on a difficult route: he seems to have taught the theory, but for some reason in reality it’s scary and he overtakes other skiers. Today we will talk about analytics in startups - a lantern that can brightly illuminate the path to a systemic business.

Many owners whose company is at the startup stage are confident that working with the market begins with advertising. In fact, this is only the third stage.

Starpup begins with the fact that the company is creating a message to the foreign market. Take a close look at the picture below.

People will never buy something they don't know about. If you remember the basic principles of marketing, interest first appears, then a person begins to look for information about what interests him, realizes the need to purchase, compares offers, and only after that purchases a product or service.

That is why the work of a startup begins with creating a need among consumers through marketing communications, after which the need is reinforced through three channels (Web, offline and Mobile) and, finally, advertising is launched. When this moment comes, it's time to start collecting metrics and conducting analytics.

Why can't you start advertising right away? Look at Tesla. At first, the shortcomings of petrol and diesel cars were actively discussed online, large-scale campaigns were launched to save the environment - and when eco-friendly cars caused a furore in the market. However, if Elon Musk had launched a car advertisement at a time when consumers were not even thinking about alternatives to gasoline and diesel, it is likely that the new product would have failed.

FUNNEL ANALYTICS: HOW TO NOT LOSE CLIENTS

The customer journey through your funnel consists of several basic stages, at each of which you need to measure a number of indicators.

STAGE 1. LEAD

A lead is a person who has shown primary interest in your company - for example, he left an email on the landing page in exchange for a trap product.

• Contact information (full name, email, telephone);
• Advertising channels where the lead came from (context, social networks, search);
• Marketing influences, where the lead came from (meeting, webinar).

STAGE 2. CONTACT

At the contact stage, the lead passes to the sales manager and regular communications with the client begin, a need is formed, pain points are identified, and objections are processed.

What indicators to record:

• Personal data – the more, the better (age, birthday, gender, interests, for B2B – decision maker);
• Client status (new, client, rejected). The less time passes from the client entering the funnel, the higher the chances of closing the deal.
• Communication history (number of calls, meetings). Recorded in the CRM system.

STAGE 3. DEAL

The stage at which there is already an agreement. However, it is still at the planning stage, that is, the invoice has not yet been issued.

What indicators to record:

• Probability and amount (calculated depending on the product the client is targeting).
• Contact transaction stage (first contact, negotiations, closed and not implemented, decision making);
• Responsible seller.

STAGE 4. ACCOUNT

The stage at which there is an agreement. The invoice has already been issued.

What indicators to record:

• Invoice status (new, issued, paid). By the way, it is best to remind the client about payment in the first half of the day. We already wrote about this in the article ;
• Payment details;
• Purchased goods, products.

STAGE 5. PAYMENT

The moment when a customer pays for a product or service.

What indicators to record:

• Payment status (accrued, paid, cancelled, changed).
• Payment methods (how the client pays: cash, non-cash, through partners, through electronic money, etc.)
• Amount, currency (partial payment, full payment)

STAGE 6. RE-TRANSAL

A repeat transaction is always more important than the first one, because at the start you get a loyal client, for whose attraction you do not need to pay extra.

What indicators to record:

• Probability and amount (if your product or service involves repeated purchases or use, set the probability and amount to the same as for the first purchase);
• Client category (VIP, premium, standard). At this stage, you indicate the solvency of consumers.
• Purchase history.

CRM SYSTEM: AN ESSENTIAL TOOL FOR THE SALES DEPARTMENT

A CRM system is an application that records customer data and the history of interactions with them, which allows you to optimize marketing, improve the quality of sales and the level of service, as well as set up transparent work of the sales department.

In a single CRM system, you can combine all the data described in the previous section, as well as conduct analytics. You don't have to maintain dozens of tables at the same time and manually compile reports.

The basis of analytics is the availability of accurate numbers. The CRM system allows you to collect them.

Advantages of using a CRM system:

• Collecting information about the client (by going to the client’s profile, you can see his personal data, communication history, place in the sales funnel, likelihood of a transaction, and so on);
• Quality control of sales staff;
• Receiving operational reporting about the client in the sales channel;
• Preserving the history of communication with the client;
• Automation of business processes.

A CRM system relieves sales managers of most of the routine work, freeing up working time for calls and meetings. For more tips on optimizing your sales department, read the article .

TYPES OF REPORTS IN A STARTUP

To move to the stage of a systemic business, it is important to establish a reporting system in the company at the startup stage. There are several types of them:

• operational reports;
• control reports;
• financial reports;
• analytical reports.

OPERATIONAL REPORTS

Daily reports on marketing, leads, sales, shipments, which are necessary for the full existence of the company. They allow you not only to monitor the state of the business, but also to quickly identify shortcomings in any functional area and correct them.

CONTROL REPORTS

Reports that help track the quality of employee work. It is advisable to check on the day the report is submitted. There will always be those who did their job poorly. If you hold a debriefing the next day, then it will be an “after the fact” action, where you will achieve nothing other than offending the employees. By reading the results on the day of submitting the report, you receive:

• you can quickly react to what has not been accomplished and correct the situation;
• the employee will have the night to comprehend the situation and select options for solving the problem;
• there is no demotivation due to public criticism of an employee.

Reports on the results are always generated in the context of “plan-actual”.

ANALYTICAL REPORTS

An analytical report shows why a particular situation occurred. In addition, with the help of analytical reports you can prioritize the project. These reports have several basic differences:

• they are based on solid statistics with concrete numbers;
• comparisons are made with the same period (for example, Tuesday-Tuesday, May 1 - April 1, March 2017 - March 2018, etc.)

At the same time, when studying analytical reports, you must identify influencing factors (what influences the achievement of results) and evaluate the work on a point scale. What it will be - five-point, ten-point or one-hundred-point - you can determine yourself. For business growth, in addition to the above, it is important to maintain a marketing report, lead generation report and logistics report.

FINANCIAL REPORTS

Financial statements are related to numbers, and their main indicators are income and expenses. They provide the opportunity to analyze and calculate financial flows and results for the future. You can evaluate how far you have actually moved away from the plan. If you have no experience with reporting, read the article .

When a business becomes successful, the owner is responsible for its development. Read Liftmarketing tips and achieve results.

How does he decide?

Using a webhook, we push leads to Google Sheets via Zapier, and from Google Sheets we push data to Google Analytics using the same Zapier.

Now let's set everything up step by step

Let's go through the logic

What lead sources can we have?

• Forms on the site
• Call tracking
• Online chats
• Callbacks

80% of services have a function such as a webhook, for example, when calling a dynamic number, call tracking sends data about the visitor to the URL that we specify.

What URL should we give? - We need a mechanism that can take the data, select what we need from it, and insert it into Google Sheets as a new row. This task will be performed by the Zapier service, being a connector.

Source > Zapier > Google Sheets > Zapier > Google Analytics

It would be most logical to start with the sources; let’s implement the combination using a real example. In our case, the sources are Roistat call tracking and forms on the website.

Let's start with "Source > Zapier > Google Sheets"

Go to the website zapier.com and register, click Make a Zap!

After this, a working window will open, let's look at the interface:

1. Home page where all Zaps (hereinafter referred to as “connectors”) are located
2. Connector logic, for example 1 - receive, 2 - pull out values, 3 - send
3. The working window of the tab selected on the left, now we are asked to select the first widget
4. Connector active/inactive

Creating a connector for Roistat

Select the built-in connector:

Click Catch Hook and Save + Continue:

Go to the Set Up Webhook tab and copy the URL for receiving the webhook, let it be in the buffer for now.

Go to Set Up Options and click Continue:

Now we are offered to make a test request using a webhook, we simultaneously open ROIstat and go to the call tracking script settings:

We paste our URL inside, apply and save the script, now you need to go to the site and make a test call. I hope that call tracking is working correctly for you.

According to the ROISTAT documentation, the webhook transmits the following data

After the call, you will see in Zapier that it has received a webhook, you need to click Continue and make the next widget.

Let's set a name for the Get from ROISTAT connector before we forget. In the search, enter and select Google Sheets

We create a new table in Google Drive where we will store all the data and enter the names of the columns. In our case it is:

• Lead source
• Contact
• Email
• Google Client ID
• Source
• Revenue
• Transaction status
• Lead quality
• Comment (if any)

You need to understand that in call tracking we have repeated calls, and it would be wrong to create new leads (lines) that duplicate the previous ones. To do this, before each adding a line (lead), we look in the Contact (phone number) column to see if it already contains the phone number we want to add. Let me remind you that we already received the phone number via a webhook.

Now we automatically have the columns from the table that we created earlier. For each column, we need to select the value and webhook that we received earlier. That is, then these values ​​will be inserted into the columns that we will now select.

By clicking on a field, a drop-down list appears with the field name and value, which contains the wubhook from Roistat.

For example, in the “Date” column we want to insert the “Date” value of the webhook, and this needs to be done for each field:

Click Continue and send the Fetch & Continue test string:

That's it, now Zapier offers us to activate the connector, which we are happy to do. The first connector is ready:

Client interaction with table

We have a table that receives new leads, now we need to teach the client to interact with it and record sales.

To do this, we previously created 4 columns:

With revenue, everything is clear how to make a fixed set of values, for this we create a separate sheet and for each field we make a set of values ​​for selection:

Right-click on the status cell and select Data validation:

A window with settings appears, in it click on the sign to select the values ​​to select:

Select the range of statuses:

That’s it, now we have a fixed set in the “Status” field:

We do the same with the “Lead Quality” column and select lead grading; it is better to do this in numbers.

Let's implement the link “Google Sheets > Zapier > Google Analytics”

To do this, we create a new connector Make a Zap!

We set the name of the Google Sheets to Google Analytics connector, select Google Sheets as the widget, as we did earlier, but now we select not the creation of a new row, but New or Updated Spreadsheet Row (Checking new or updated rows):

We select an account, as we did before, we will not repeat ourselves, then select the same document on Google Drive, sheet and Trigger Column. The point is that the connector checks the sheet every 15 minutes and looks at the trigger column, in our case, this is “Deal Status”; if the value in it has changed, then it sends the data to Google Analytics.

You need to create conditions and filters:

We test the widget, click Fetch & Continue to get the data to create the next widget.

For the next widget, select Filter:

Select Only continue if..

Select the “Transaction Status” column in the first field, then (Text) Contains and “Sold”,

In total, we get the condition:

If the new or updated row has a value in the Deal Status column that contains the word “Sold,” then we send the data to Google Analytics, and if not, then we do nothing.

But we need to send data to Google Analytics in 3 cases: “Rejected”, “Sold” and “Thinking”. Now we only ship when “Transaction Status” = “Sold”, so we add two more OR conditions for “Refused” and “Thinking”.

We click Fetch & Continue and see that it checked the last line and says that it does not fit any of the conditions, which is what we need, since we do not have a condition for the “In Processing” status.

Our next widget is Google Analytics:

Select Create a Measurement:

Event Action - Pass the value

Event Label (Just another field) - Passing the lead quality indicator

Event Value (The amount that the event brought) - Just a number, in our case, we want to convey the transaction amount.

Custom User ID (Google Client ID) - without this value, GA will not understand which channel the client came from, that is, we will not be able to understand that the client originally came from the context.

Click Continue and Send Test To Google Analytics, it is important to note that we will not see the event in Google Analytics, since the connector sends the request in debug mode, this is like a GA test mode, so as not to spoil the real statistics with tests.

In response, we will receive such a window, it is important for us that valid is equal to true, this means that the request in GA is valid.

If there are problems, you can look for them in hit, this is the request itself that was compiled.

Activate the connector:

Testing the connector

Change the value of the last row in the table to “Sold”:

Go to the Dashboard home page in Zapier and click Run on our new connector:

A notification will appear that the connector detected notifications in 2 lines and sent 2 events to GA:

Let's check that GA received these events:

We create a goal in GA for the event “Deal Status” - “Sold”:

We create similar goals for other statuses:

Forms on the site > Google Sheets

Now we have completely completed the cycle and done end-to-end analytics, but we still do not take into account leads from forms on the site.

To do this, we set the project developer the task of creating a webhook when submitting forms from the site; this is a simple task that does not require much time.

There are a few things you should consider before setting the task.

• Google Analytics must be installed on the site to transmit Google Client ID
• By default, when sending a webhook, we do not have a source in the form of UTM tags where the lead originally came from, so we wrote one that saves the UTM tags of the visitor’s first visit in Cookie and when sending the form, you can use them. The script can be installed on the site via GTM in the form of a Custom HTML tag, or give it to the project developer for installation.
• Before setting the task, you need to create a connector that will accept the developer’s webhook and send data to Google Sheets. It is better to immediately orient the developer which fields and in which forms you want to receive in the table.

Transferring forms from the site to Google Sheets

The procedure is similar to setting up a connector for ROIStat, so we will try without detailed descriptions. Create a new Make a Zap connector and select the Webhooks widget, with Catch Hook in it:

We give the link for receiving webhooks to the developer, click Continue and in the Edit Options section also Continue, then we are offered to test the URL, for this we need to somehow emit a webhook - go to hurl.it

Paste the URL into the Destination field and select POST from the drop-down list:

Below we get:

This means that the request went away and was accepted, now in Zapier click OK, I did this:

The next widget is Google Sheets, in it we select Create Spreadsheet Row:

Troubleshooting

Zapier

How to Test Connector URL Requests for Lead Additions in Google Sheets

Invalid request in GA

Copy the request:

Go to Hit Builder, insert the request and click Validate hit:

Field settings in connectors are lost

When adding a new column, all settings in the widgets are lost. You can view the history of connectors in Task History

Google Analytics

How to see the composition of events that come to GA

Create a report:

Setting up:

Please note that events in the report will be displayed with a delay of 15 minutes.

I have a GA account in dollars

How to see if goals are working in GA

Yulia Perminova

Trainer at Softline Training Center since 2008.

A basic tool for working with a huge amount of unstructured data, from which you can quickly draw conclusions without fiddling with filtering and sorting manually. Pivot tables can be created in a few steps and quickly customized depending on how exactly you want to display the results.

A useful addition. You can also create PivotCharts based on PivotTables, which will automatically update as they change. This is useful if, for example, you need to regularly create reports on the same parameters.

How to work

The source data can be anything: data on sales, shipments, deliveries, and so on.

1. Open the file with the table whose data you want to analyze.
2. Go to Insert tab → Table → PivotTable (for macOS, on the Data tab in the Analysis group).
3. The Create PivotTable dialog box should appear.
4. Customize the display of the data you have in the table.

We have a table with unstructured data. We can systematize them and customize the display of the data that we have in the table. “Amount of orders” is sent to “Values”, and “Sellers”, “Date of sale” - to “Lines”. According to data from different sellers for different years, the amounts were immediately calculated. If necessary, you can expand every year, quarter or month - we will get more detailed information for a specific period.

The set of options will depend on the number of columns. For example, we have five columns. They just need to be positioned correctly and choose what we want to show. Let's say the amount.

You can detail it, for example, by country. We are moving “Countries”.

You can view the results by seller. We change “Country” to “Sellers”. For sellers, the results will be as follows.

This method of visualizing georeferenced data allows you to analyze data and find patterns of regional origin.

A useful addition. There is no need to register the coordinates anywhere - you just need to correctly indicate the geographical name in the table.

How to work

1. Open the file with the table whose data you want to visualize. For example, with information on different cities and countries.
2. Prepare data for display on the map: “Home” → “Format as table”.
3. Select a range of data to analyze.
4. On the Insert tab there is a 3D map button.

The points on the map are our cities. But we are simply not very interested in cities - it is interesting to see information associated with these cities. For example, amounts that can be displayed through the height of a column. When you hover the cursor over the column, the amount is shown.

The pie chart by year is also quite informative. The size of the circle is determined by the sum.

3. Forecast sheet

Often, business processes exhibit seasonal patterns that must be taken into account when planning. The forecast sheet is the most accurate forecasting tool in Excel than all the functions that came before and are now. It can be used to plan the activities of commercial, financial, marketing and other services.

A useful addition. To calculate the forecast, data for earlier periods will be required. The accuracy of forecasting depends on the amount of data by period - preferably no less than a year. You want equal intervals between data points (for example, a month or an equal number of days).

How to work

1. Open a table with data for the period and the corresponding indicators, for example, from year to year.
2. Select two rows of data.
3. On the Data tab, in the group, click the Forecast Sheet button.
4. In the Create Forecast Sheet window, select a graph or histogram to visually represent your forecast.
5. Select the end date for the forecast.

In the example below we have data for 2011, 2012 and 2013. It is important to indicate not numbers, but rather time periods (that is, not March 5, 2013, but March 2013).

To make a forecast for 2014, you will need two series of data: dates and the corresponding indicator values. Select both data series.

On the “Data” tab, in the “Forecast” group, click on “Forecast sheet”. In the “Create a forecast sheet” window that appears, select the forecast presentation format - graph or histogram. In the “Completion of forecast” field, select the end date, and then click the “Create” button. The orange line is the forecast.

4. Quick analysis

This functionality is perhaps the first step towards what can be called business analysis. It's nice that this functionality is implemented in the most user-friendly way: the desired result is achieved in just a few clicks. There is no need to count anything, no need to write down any formulas. Just select the desired range and choose what result you want to get.

A useful addition. You can instantly create different types of charts or sparklines (micrographs right in the cell).

How to work

1. Open a table with data for analysis.
2. Select the range required for analysis.
3. When you select a range, the “Quick Analysis” button always appears at the bottom. She immediately suggests performing several possible actions with the data. For example, find the results. We can find out the amounts, they are listed below.

Quick Analysis also has several formatting options. You can see which values ​​are larger and which are smaller in the histogram cells themselves.

You can also put multi-colored icons in the cells: green - the highest values, red - the lowest.

We hope that these techniques will help speed up your work with data analysis in Microsoft Excel and quickly conquer the heights of this complex, but so useful application from the point of view of working with numbers.