Bubi, baptize, peaks. Or its own CRM-system in Google Tables for a couple of hours
Since my article will touch gambling a little bit, I will immediately make a reservation that I do not pursue any advertising goal. I want to talk about how we made a convenient reporting system, using Google signs and touch on some points, perhaps, it will be useful for someone.
A few words about the essence of our enterprise: we trained (free of charge) and sponsored to play online poker (as it is called in gaming circles - without inconvenience, that is, if a person lost our money, then nothing should be left). Now the project is no longer there, but at the peak the number of our students exceeded 50 people.
When your company has 5 clients, you can use anything in the calculations with them, even if you can draw with chalk on the children's board, noting the receipt and sending of money and other nuances. Problems begin when the number of customers increases and when, as in our case, you need to constantly collect feedback from them so that you can quickly intervene in the process if something goes wrong. Our children had to convey certain figures to us on the basis of each day’s work, well, let it be the work date and the balance (I’ll simplify it a bit).
What to use for storing and processing this data, how to build the necessary report from them for the necessary period? Of course, the best CRM-ku, with a user-friendly interface and many useful nishtyakov, but the development and finishing will cost money and take some time, plus each update, if you need something, will also have to wait.
')
And as you might have guessed, we used Google Plates. The whole system consisted of two parts, the client part (files of the same format, in which the guys regularly entered their reports) and the managerial part, in which we could view all the results from these files in real time and in the necessary section.
It looked like this file of our student, one line - one day and its results. I'll try to talk about important points that can be taken into account in client files to minimize errors.
How does this even work? At first we are doing a client file in which we are trying to take into account everything that is needed. We call his template, the progenitor, somehow and further, make a copy for each of our clients and use the access settings to “share” this copy only for his gmail address. We, as the creator of the file will have the rights of the owner.
Privacy By selecting a tick from Privacy, as in the screenshot below, you can ensure that our editor (the client who receives this document for filling) will not be able to add other editors or will not be able to make the file open to everyone.
Validate as you type. It is natural for people to make mistakes when they fill in labels, the main error is the wrong format when entering dates / values. For example, the user has a comma separated integer and fractional separator - and he enters values with a dot or with a space, such a record does not turn into a number and we will not be able to work with it further.
How to solve? When you create a report for each column that will fill the client, set the verification data. Look at the screenshot, now you can enter only a valid date in column B, and even from 2014, if the user tries to enter something absurd, he will immediately see an error.
Formula protection. In our doc there can be different formulas, how to make sure that the user does not break them, even by accident? We will use the sheet protection, we will open for editing only certain columns, the rest will be able to be corrected only by us, as creators and owners of the file.
How to get information from all files? Ok, we distributed 20-50-100 files to our customers, they fill them out regularly. Now I will show how we can gather information from them.
You can collect something from other tablets in two ways, using the Importrange formula or using a script that cycles through the client files and copies them into our consolidated document and then you can get the necessary data from this array using formulas.
Consider importrange, with the help of this formula we can transfer the range of one file to another or do some actions with this range. Let's start with the structure, in our pivot table there will be links to all our files in column A. Next, let's say we decided to calculate the total amount for column F of each file, the formula will look like this:
And this is how you can display the largest date in the column B of each client file:
The example is a little more complicated, so from the plates you can display the results for the selected period (in the cells b1 and c1 of the summary file, enter the dates):
If you simplify, it looked like our system. A large number of identical client files (with strict filling rules to reduce user errors to a minimum) and summary tables that collected the necessary indicators and where only a limited circle of people had access. With the help of conditional formatting, you can tint indicators that you should pay attention to, you can draw any graphs, in general, you can do the analytics you want.
As a result, we created a reporting system at our knees, in addition, a free one. If the topic is interesting, then I will continue about Google Docs and tell interesting and complex cases with formulas or about scripts.
PS If someone wants to look at the formulas from the summary part close up, here is the document (File → Create a copy so that you can edit it).
A few words about the essence of our enterprise: we trained (free of charge) and sponsored to play online poker (as it is called in gaming circles - without inconvenience, that is, if a person lost our money, then nothing should be left). Now the project is no longer there, but at the peak the number of our students exceeded 50 people.
When your company has 5 clients, you can use anything in the calculations with them, even if you can draw with chalk on the children's board, noting the receipt and sending of money and other nuances. Problems begin when the number of customers increases and when, as in our case, you need to constantly collect feedback from them so that you can quickly intervene in the process if something goes wrong. Our children had to convey certain figures to us on the basis of each day’s work, well, let it be the work date and the balance (I’ll simplify it a bit).
What to use for storing and processing this data, how to build the necessary report from them for the necessary period? Of course, the best CRM-ku, with a user-friendly interface and many useful nishtyakov, but the development and finishing will cost money and take some time, plus each update, if you need something, will also have to wait.
')
And as you might have guessed, we used Google Plates. The whole system consisted of two parts, the client part (files of the same format, in which the guys regularly entered their reports) and the managerial part, in which we could view all the results from these files in real time and in the necessary section.
It looked like this file of our student, one line - one day and its results. I'll try to talk about important points that can be taken into account in client files to minimize errors.
How does this even work? At first we are doing a client file in which we are trying to take into account everything that is needed. We call his template, the progenitor, somehow and further, make a copy for each of our clients and use the access settings to “share” this copy only for his gmail address. We, as the creator of the file will have the rights of the owner.
Privacy By selecting a tick from Privacy, as in the screenshot below, you can ensure that our editor (the client who receives this document for filling) will not be able to add other editors or will not be able to make the file open to everyone.
Validate as you type. It is natural for people to make mistakes when they fill in labels, the main error is the wrong format when entering dates / values. For example, the user has a comma separated integer and fractional separator - and he enters values with a dot or with a space, such a record does not turn into a number and we will not be able to work with it further.
How to solve? When you create a report for each column that will fill the client, set the verification data. Look at the screenshot, now you can enter only a valid date in column B, and even from 2014, if the user tries to enter something absurd, he will immediately see an error.
Formula protection. In our doc there can be different formulas, how to make sure that the user does not break them, even by accident? We will use the sheet protection, we will open for editing only certain columns, the rest will be able to be corrected only by us, as creators and owners of the file.
How to get information from all files? Ok, we distributed 20-50-100 files to our customers, they fill them out regularly. Now I will show how we can gather information from them.
You can collect something from other tablets in two ways, using the Importrange formula or using a script that cycles through the client files and copies them into our consolidated document and then you can get the necessary data from this array using formulas.
Consider importrange, with the help of this formula we can transfer the range of one file to another or do some actions with this range. Let's start with the structure, in our pivot table there will be links to all our files in column A. Next, let's say we decided to calculate the total amount for column F of each file, the formula will look like this:
And this is how you can display the largest date in the column B of each client file:
The example is a little more complicated, so from the plates you can display the results for the selected period (in the cells b1 and c1 of the summary file, enter the dates):
If you simplify, it looked like our system. A large number of identical client files (with strict filling rules to reduce user errors to a minimum) and summary tables that collected the necessary indicators and where only a limited circle of people had access. With the help of conditional formatting, you can tint indicators that you should pay attention to, you can draw any graphs, in general, you can do the analytics you want.
As a result, we created a reporting system at our knees, in addition, a free one. If the topic is interesting, then I will continue about Google Docs and tell interesting and complex cases with formulas or about scripts.
PS If someone wants to look at the formulas from the summary part close up, here is the document (File → Create a copy so that you can edit it).
Source: https://habr.com/ru/post/328820/
All Articles