Technical life hacks for contests

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

Many SMM-specialists are forced to go through all the circles of hell until they automate the maximum of processes when holding atypical contests. Therefore, we want to share some of the technical life hacks we use.

Prediction Contest

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

And yes, any calls to do everything in a single format are usually ignored. The audience does not like to read the conditions of the competition – “Monkey see – Monkey do”, they will do it just like everyone else, without looking at the rules.

So what was done by our marketer (with a technical background)? The data cleansing process in Excel has been automated with several VBA macros. Let’s talk about each.

Before starting, we copy the data to the table using the old grandfather’s “Ctrl + C” – “Ctrl + V” method. And we duplicate the same data in the next cell, they will come in handy so that you can compare the result of the macros with the original.

The first step is to remove everything except numbers and decimals. The first macro does exactly 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 set the alphabet, which we will then loop through, removing all characters from the list.

Next comes the preliminary combing of the data:

We change all points to commas, they are the decimal point in Excel.
We remove all spaces.
Further, one by one, we delete special characters (it will not work in the loop, it captures only one character, and in macros they must be escaped with a tilde).
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 characters from the string specified at the very beginning.

The second macro removes the 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 redefines 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. Visually, you can already determine where the numbers are. But, nevertheless, the received data spoils the remaining records of likes (these 1,2 or 3 …).

Making the last macro:

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

With them, we removed everything less than a thousand. But they didn’t touch negative values if an error crept in somewhere, and the stuck dash turned the number into negative.

Further, a minimum of manual labor, scroll down the list, looking through the list for anomalies. Anomalies will be aligned on the left side, and the correct numbers on the right, so you don’t have to read it.

We quickly correct errors, since on the right we have the original for comparison. That’s it, we transfer it to the page with sorting the difference from the number modulo =ABS(A2-B$2)and sort ASC.

The list of applicants is ready, then the boring marketing routine. Below is a simple gif illustrating the stages of macros:

Quiz with coupons

The next small life hack for the contest, in which each of the participants who passed the quiz is awarded the number of tickets equal to the number of correct answers.

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

First step: Export the quiz answers to CSV and count the correct answers in each column. As a result, we get a list: link + correct answers.

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

We import the resulting 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 “distributes tickets”, 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 selected 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 you’re done!

Competition with comments

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

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

We make the good old “Ctrl + C” – “Ctrl + V” comments into the plate (with the original formatting preserved).

Next, we run the simplest macro for getting links 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 Name and Last Name), and the second column contains the link itself.

And now we remove 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

Ready! Delete duplicates and insert into randomizer.

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

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