It would seem that it can be difficult to hold contests in social networks. But in practice, everything is not as simple as it seems at first glance.

Many SMMs are forced to go through all the circles of hell until they automate the maximum number of processes during non-typical contests. So we want to share some of the tech hacks we use.

Forecast competition

What could be easier than collecting the numbers in a table and a couple of sortings to find the winner? But if the page with comments is generated dynamically (that is, the option of parsing without a headache disappears), and people like to write a lot of extra things in addition to numbers, and leave the numbers themselves in different formats.

Yes, any calls to do everything in a single format are usually ignored. The audience does not like to read the terms of the competition – Monkey see – Monkey do, they will do almost like everyone else, regardless of the rules.

What did our marketer (with a technical background) do? The data cleaning process in Excel was automated with a few VBA macros. Let’s talk about everyone.

Before starting, copy the data into the table using the old “Ctrl + C” – “Ctrl + V” method. And we duplicate the same data in the adjacent cell, we will need it so that we can compare the result of the macros with the original.

First of all, you need to remove everything except numbers and decimals. The first macro does just that:

Const AlfaBet As String = "=-/\()`'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯабвгдеёжзийклмнопрстуфхцчшщъыьэюя"

Sub Macros1CharDel()
    Set R = Application.Selection
    
    R.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    R.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    R.Replace What:="+", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    R.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    R.Replace What:="~?", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    R.Replace What:="~!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    R.Replace What:="~#", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    R.Replace What:="~;", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    R.Replace What:="~@", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    R.Replace What:=":", Replacement:="%", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    Dim n As Integer, i As Integer, S() As String
    n = Len(AlfaBet): ReDim S(1 To n)
    For i = 1 To n
        S(i) = Mid(AlfaBet, i, 1)
        R.Replace What:=S(i), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next i
End Sub

We specify the alphabet, which we will then use in a loop, removing all characters from the list.

Next comes the preliminary combing of the data:

1. We change all dots to commas, they act as a decimal sign in Excel.

2. We remove all the gaps.

3. Next, we delete the special characters one by one (it will not work in the cycle, it captures only one character, and in macros they must be escaped with a tilde).

4. For convenience, we replace the colon with a percent sign, otherwise we risk getting cells with “time” as a result after removing extra characters.

Finally, the data is ready to remove the characters from the specified string at the very beginning.

The second macro deletes previously marked dates:

Sub Macros2DatesDel()
    Set R = Application.Selection
    
    R.Replace What:="*%*", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      
End Sub

It seems that everything should be ready, but we are faced with the idiocy of Excel, which overrides the data format in the cell only when manually entered, and after the macros work, the former text remains text, even if there are only numbers inside the cell.

There is a way out, we make Excel work by changing the cell format with the third macro:

Sub Macros3DoNumbers()
    For Each Cell In Selection
    If Cell.Value <> "" And IsNumeric(Cell.Value) Then
        Cell.Value = (Cell.Value * 1)
        Cell.NumberFormat = "General"
    End If
    Next
End Sub

And now we have the desired result. You can already visually determine where the numbers are. But, nevertheless, the received data spoils the records of likes (those 1, 2 or 3 …).

Let’s make the last macro:

Sub Macros4DelLittleNumbers()
    For Each Cell In Selection
    If Cell.Value < 1000 And Cell.Value > 0 Then Cell.Clear
    Next
End Sub

For them, we removed everything that is less than a thousand. But negative values ​​were not affected, if an error crept in somewhere, and the dash that stuck turned the number into a negative one.

Next, a minimum of manual labor, let’s scroll down the list, reviewing the list for anomalies. Anomalies will be aligned to the left, and correct numbers to the right, so there is no need to read them.

We quickly correct mistakes, since we have the original on the right for comparison. Everything, we transfer to the page with sorting the difference from the number by module =ABS(A2-B$2)and sort ASC.

The list of applicants is ready, then the boring advertising routine. Below is a simple gif illustrating the stages of macro operation:

Quiz with coupons

The following is a small life hack for the contest, in which each of the age participants is awarded a number of tickets equal to the number of correct answers.

It would be possible to bother with the search for the necessary functions inside Excel, but it will be easier and faster to resort to the help of PHP.

First stage: We export the quiz answers to CSV and count the correct answers in each column. As a result, we get a list: links + correct answers.

We copy to get rid of duplicates (when inserting, specify: only unique values) on the column with names. All retries are erased – profit.

We import the received CSV into any database, I have mysql at hand. And then sleight of hand and no fraud:

<?php
$link = mysqli_connect("хост", "логин", "пароль", "база");
mysqli_query($link, "SET NAMES utf8");
$names = mysqli_query($link, "SELECT * FROM `таблица`");
$number=1;
while ($arr = mysqli_fetch_array($names)) { while ($arr[2]>0) {echo $number++.';'.$arr[1].'<br>'; $arr[2]--;}}
?>

The simplest script “hands out tickets” by repeating each participant the required number of times with a global counter.

We save the text as CSV and insert the received data into Google Doc, from which the winner will be chosen live.

And here everything is simple, we select applicants by vertical viewing by number from the randomizer: =VLOOKUP(C9;'Список билетов'!A:B;2;0)

We check the adequacy of the winners and that’s it!

Contest with comments

And if that wasn’t enough for you, catch another mini life hack for the next type of competition.

According to the conditions, you just need to leave a comment under the publication. And we, as marketers, get the list of participants without resorting to unnecessary tools.

We make the good old “Ctrl + C” – “Ctrl + V” comments in the table (while preserving the original formatting).

Next, we run the simplest link delivery macro on a new sheet:

Sub доставательссылок()
With ActiveSheet
For I = 1 To .Hyperlinks.Count
.Hyperlinks(I).Range.Offset(0, 1).Value = .Hyperlinks(I).TextToDisplay
.Hyperlinks(I).Range.Offset(0, 2).Value = .Hyperlinks(I).Address
Next I
End With
End Sub

The first column contains the text of the link (First and Last Name), and the second column contains the link itself.

And now we remove the links to publications from the list:

Sub удалятельпостов()
    Set R = Application.Selection
    
    R.Replace What:="*wall*", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      
End Sub

Done! We remove duplicates and insert them into the randomizer.

---

We hope it was useful. And someone’s social networks will now be filled with deeper contests than simple guesses with connected bots.

And if you have ideas for new activities or simplification of the given schemes, share them in the comments.